{"id":42,"date":"2011-04-17T22:38:00","date_gmt":"2011-04-17T22:38:00","guid":{"rendered":"http:\/\/www.sapbpc.sapag.co.in\/?p=42"},"modified":"2011-04-17T22:38:00","modified_gmt":"2011-04-17T22:38:00","slug":"how-to-create-dimension-formula-in-bpc-7m-with-sql2008","status":"publish","type":"post","link":"https:\/\/www.sapbpc.sapag.co.in\/?p=42","title":{"rendered":"How to create Dimension Formula in BPC 7M with SQL2008"},"content":{"rendered":"

‘Dimension Formula’ is one of the BPC term that is for calulated members.<\/p>\n

For example of calculated member, ROA is one of famous KPI for measuring business peformance. <\/p>\n

This dimension formula is using ‘Custom Rollup’ feature in the Microsoft Analysis services.<\/p>\n

Due to change of architecture of Microsoft Analysis Services from SQL2000 to SQL2005, BPC customers could not use simple dimension formula like
Account A = Account B X Account C (or X constant value like 1.5).<\/p>\n

It was working in SQL2000 but dimension formula didn’t work with SQL2005 if dimension has multiple hierarchies.<\/p>\n

Therefore, SAP released \u2018HOW TO GUIDE’ for creating dimension formula in SQL2005 but there were some problems as below.<\/p>\n

a. Formula should have complex IIF statement
b. If length of formula is too long, user should make cascade account and it will make slower performance.
c. It is hard to maintain because can’t read it easily.<\/p>\n

Note : URL of Dimension formula with SQL2005.
http:\/\/www.sdn.sap.com\/irj\/sdn\/go\/portal\/prtroot\/docs\/library\/uuid\/008d665b-94bf-2a10-78b2-b32ffe04ba73;jsessionid=(J2EE3414900)ID0541202550DB10246661973771485801End<\/a><\/p>\n

In SQL2008, Microsoft seems to fix that internal calculation issue with multiple hierarchies. <\/p>\n

I will explain how to use dimension formula with SQL2008.  (Note : Based on the test result, some complex formula still needs same IIF function as SQL2005 so you need to test it after change formula based on this article.)<\/p>\n

Here is a test scenario that developer has used it for testing SQL2005 dimension formula. <\/p>\n

Dimension formula exists in the entity dimension as below.<\/p>\n

\"image\"<\/p>\n

Entity hierarchy structure will be same as below screenshot.<\/p>\n

\"image\"<\/p>\n

Here is EVDRE result. As you can see, there is no IIF in the dimension formula but all calculation result is correct.<\/p>\n

\"image\"<\/p>\n

If we are using it with SQL2005, it should have IIF statement to get correct calculation result like iif(Acct.H2.CurrentMember is [Acct].[H2].[All Acct.H2],Acct.H1.Cost1+ Acct.H1.Cost2, Null)<\/p>\n

BPC Microsoft platform with SQL2008 is the best combination for better performance and easy maintenance than using SQL2005.<\/p>\n

Especially, for the dimension formula, we can use it like SQL2000 so we don’t need to use IIF statement for the MDX dimension formula.<\/p>\n

I hope it will help all consultant who is struggling with BPC dimension formula.<\/p>\n

SAP BPC Tutorials | SAP BPC Training | SAP BPC Interview Questions |SAP BPC Books<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"

‘Dimension Formula’ is one of the BPC term that is for calulated members. For example of calculated member, ROA is one of famous KPI for measuring business peformance. This dimension formula is using ‘Custom Rollup’ feature in the Microsoft Analysis … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3],"tags":[],"class_list":["post-42","post","type-post","status-publish","format-standard","hentry","category-sap-bpc-tutorials"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=\/wp\/v2\/posts\/42","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=42"}],"version-history":[{"count":0,"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=\/wp\/v2\/posts\/42\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=42"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=42"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sapbpc.sapag.co.in\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=42"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}