This article is applied to BPC 5.1 or BPC 7 for Microsoft platform with Microsoft SQL Server 2005 only. SQL 2000 and SQL 2008 are out of scope of this document.
Are you experiencing performance issue with your BPC 5.1 or 7M with SQL Server 2005? It is true that "SPEED" of SSAS (SQL Server Analysis Service) 2005 is not fast as SSAS 2000. SSAS 2005 is robust and more reliable under heavy load but slower than SSAS 2000. That is why some of customers are having trouble after they upgrade old OutlookSoft CPM system to SAP BPC. Because, new BPC 5.1 or 7 was probably came with SQL 2005.
There are lots of intelligent mechanisms in BPC software to make query to database by your report/input schedule faster and faster. But once those intelligent meets MDX query to SSAS 2005, it is not able to avoid to slow performance.
If you are an interesting underlying technology, you already have seen BPC's measure formula for periodic or YTD. If you have not seen it, you can open your BPC cube into Microsoft Visual Studio or SQL Server Business Intelligence Development Studio (BIDS). Both of tools are same development environment.
- 1. Open "SQL Server Business Intelligence Development Studio" from Windows start menu.
- 2. Click "File" menu. Go to "Open" and "Analysis Service Database".
- 3. Type your server and database name. In this article, we are using ApShell.
Once you opened it, you can see lots of information in your BPC cube. I suggest to do not touch anything other than in this document. Many of settings are sensitive to performing BPC.
Double click application name in solution explorer at right screen. In the "Calculations" tab, you can see measures in your application.
For example, let's choose "PERIODIC". In non-legal application like Finance in ApShell, measure formula of PERIODIC is below. It is modified for easy reading.
IIF([ACCOUNT].CURRENTMEMBER.PROPERTIES("ACCTYPE")="INC",
-MEASURES.[SIGNEDDATA],
IIF([ACCOUNT].CURRENTMEMBER.PROPERTIES("ACCTYPE")="EXP",
MEASURES.[SIGNEDDATA],
IIF([ACCOUNT].CURRENTMEMBER.PROPERTIES("ACCTYPE")="AST",
(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([TIME].[MONTH])),
IIF([ACCOUNT].CURRENTMEMBER.PROPERTIES("ACCTYPE")="LEQ",
-(MEASURES.[SIGNEDDATA],CLOSINGPERIOD([TIME].[MONTH])),
-MEASURES.[SIGNEDDATA]
)
)
)
)
In this formula, most important thing is "ACCTYPE" member property of account dimension. Periodic is providing differently by account type. So, measure formula of PERIODIC must check current account member's ACCTYPE property. If it is income, do opposite calculation. If it is expense, return current signeddata. If it is asset, return closing period's signeddata....
Multiple IIF statement is doing this conditional return. This formula is slightly difficult to read and understand. If you are thinking it is easy, let's see formula of multiple hierarchies.
IIF ([TIME].[H1].CURRENTMEMBER.LEVEL.ORDINAL>0,
IIF([ACCOUNT].[H1].CURRENTMEMBER.LEVEL.ORDINAL>0,
IIF([ACCOUNT].[H1].CURRENTMEMBER.PROPERTIES("ACCTYPE")="INC",
-MEASURES.[SIGNEDDATA],
IIF([ACCOUNT].[H1].CURRENTMEMBER.PROPERTIES("ACCTYPE")="EXP"
,MEASURES.[SIGNEDDATA],
IIF([ACCOUNT].[H1].CURRENTMEMBER.PROPERTIES("ACCTYPE")="AST",
(MEASURES.[SIGNEDDATA],CLOSINGPERIOD([TIME].[H1].[MONTH])),
IIF([ACCOUNT].[H1].CURRENTMEMBER.PROPERTIES("ACCTYPE")="LEQ",
-(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([TIME].[H1].[MONTH])),
-MEASURES.[SIGNEDDATA]
)
)
)
),
IIF([ACCOUNT].[H2].CURRENTMEMBER.LEVEL.ORDINAL>0,
IIF([ACCOUNT].[H2].CURRENTMEMBER.PROPERTIES("ACCTYPE")="INC",
-MEASURES.[SIGNEDDATA],
IIF([ACCOUNT].[H2].CURRENTMEMBER.PROPERTIES("ACCTYPE")="EXP",
MEASURES.[SIGNEDDATA],
IIF([ACCOUNT].[H2].CURRENTMEMBER.PROPERTIES("ACCTYPE")="AST",
(MEASURES.[SIGNEDDATA],CLOSINGPERIOD([TIME].[H1].[MONTH])),
IIF([ACCOUNT].[H2].CURRENTMEMBER.PROPERTIES("ACCTYPE")="LEQ",
-(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([TIME].[H1].[MONTH])),
-MEASURES.[SIGNEDDATA]
)
)
)
),
IIF([ACCOUNT].[H3].CURRENTMEMBER.LEVEL.ORDINAL>0,
IIF([ACCOUNT].[H3].CURRENTMEMBER.PROPERTIES("ACCTYPE")="INC",
-MEASURES.[SIGNEDDATA],
IIF([ACCOUNT].[H3].CURRENTMEMBER.PROPERTIES("ACCTYPE")="EXP",
MEASURES.[SIGNEDDATA],
IIF([ACCOUNT].[H3].CURRENTMEMBER.PROPERTIES("ACCTYPE")="AST",
(MEASURES.[SIGNEDDATA],CLOSINGPERIOD([TIME].[H1].[MONTH])),
IIF([ACCOUNT].[H3].CURRENTMEMBER.PROPERTIES("ACCTYPE")="LEQ",
-(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([TIME].[H1].[MONTH])),
-MEASURES.[SIGNEDDATA]
)
)
)
),
NULL
)
)
),
IIF([TIME].[H2].CURRENTMEMBER.LEVEL.ORDINAL>0,
IIF([ACCOUNT].[H1].CURRENTMEMBER.LEVEL.ORDINAL>0,
IIF([ACCOUNT].[H1].CURRENTMEMBER.PROPERTIES("ACCTYPE")="INC",
-MEASURES.[SIGNEDDATA],
IIF([ACCOUNT].[H1].CURRENTMEMBER.PROPERTIES("ACCTYPE")="EXP",
MEASURES.[SIGNEDDATA],
IIF([ACCOUNT].[H1].CURRENTMEMBER.PROPERTIES("ACCTYPE")="AST",
(MEASURES.[SIGNEDDATA],CLOSINGPERIOD([TIME].[H2].[MONTH])),
IIF([ACCOUNT].[H1].CURRENTMEMBER.PROPERTIES("ACCTYPE")="LEQ",
-(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([TIME].[H2].[MONTH])),
-MEASURES.[SIGNEDDATA]
)
)
)
),
IIF([ACCOUNT].[H2].CURRENTMEMBER.LEVEL.ORDINAL>0,
IIF([ACCOUNT].[H2].CURRENTMEMBER.PROPERTIES("ACCTYPE")="INC",
-MEASURES.[SIGNEDDATA],
IIF([ACCOUNT].[H2].CURRENTMEMBER.PROPERTIES("ACCTYPE")="EXP",
MEASURES.[SIGNEDDATA],
IIF([ACCOUNT].[H2].CURRENTMEMBER.PROPERTIES("ACCTYPE")="AST",
(MEASURES.[SIGNEDDATA],CLOSINGPERIOD([TIME].[H2].[MONTH])),
IIF([ACCOUNT].[H2].CURRENTMEMBER.PROPERTIES("ACCTYPE")="LEQ",
-(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([TIME].[H2].[MONTH])),
-MEASURES.[SIGNEDDATA]
)
)
)
),
IIF([ACCOUNT].[H3].CURRENTMEMBER.LEVEL.ORDINAL>0,
IIF([ACCOUNT].[H3].CURRENTMEMBER.PROPERTIES("ACCTYPE")="INC",
-MEASURES.[SIGNEDDATA],
IIF([ACCOUNT].[H3].CURRENTMEMBER.PROPERTIES("ACCTYPE")="EXP",
MEASURES.[SIGNEDDATA],
IIF([ACCOUNT].[H3].CURRENTMEMBER.PROPERTIES("ACCTYPE")="AST",
(MEASURES.[SIGNEDDATA],CLOSINGPERIOD([TIME].[H2].[MONTH])),
IIF([ACCOUNT].[H3].CURRENTMEMBER.PROPERTIES("ACCTYPE")="LEQ",
-(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([TIME].[H2].[MONTH])),
-MEASURES.[SIGNEDDATA]
)
)
)
)
,NULL
)
)
),
NULL
)
)
Above formula is also modified to easy reading. You can see how it's long and complicate. If you have two time hierarchies (calendar, fiscal) and three account hierarchies, you will get same as above formula.
Why it is so long and hard to understand? Because measure formula is combination of multiple of IIF statements. It is related to underlying platform, SQL 2000. I am not writing about backend story. But it is true, it is multiple IIF.
But in case of SQL 2005, we can change this long-difficult IIF to CASE/WHEN statement. It is simple, easy to read. And finally, faster than IIF.
For example, let's change PERIODIC of ApShell.
CASE [ACCOUNT].CURRENTMEMBER.PROPERTIES("ACCTYPE")
WHEN "INC" THEN -MEASURES.[SIGNEDDATA]
WHEN "EXP" THEN MEASURES.[SIGNEDDATA]
WHEN "AST" THEN (MEASURES.[SIGNEDDATA], CLOSINGPERIOD([TIME].[MONTH]))
WHEN "LEQ" THEN -(MEASURES.[SIGNEDDATA],CLOSINGPERIOD([TIME].[MONTH]))
ELSE -MEASURES.[SIGNEDDATA]
END
It's much simpler than before with IIF. And much easier to understand.
How about case of multiple hierarchies? It's below.
CASE
WHEN [TIME].[H1].CURRENTMEMBER.LEVEL.ORDINAL>0 THEN
CASE
WHEN [ACCOUNT].[H1].CURRENTMEMBER.LEVEL.ORDINAL>0 THEN
CASE [ACCOUNT].[H1].CURRENTMEMBER.PROPERTIES("ACCTYPE")
WHEN "INC" THEN -MEASURES.[SIGNEDDATA]
WHEN "EXP" THEN MEASURES.[SIGNEDDATA]
WHEN "AST" THEN (MEASURES.[SIGNEDDATA],CLOSINGPERIOD([TIME].[H1].[MONTH]))
WHEN "LEQ" THEN -(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([TIME].[H1].[MONTH]))
ELSE -MEASURES.[SIGNEDDATA]
END
WHEN [ACCOUNT].[H2].CURRENTMEMBER.LEVEL.ORDINAL>0 THEN
CASE [ACCOUNT].[H2].CURRENTMEMBER.PROPERTIES("ACCTYPE")
WHEN "INC" THEN -MEASURES.[SIGNEDDATA]
WHEN "EXP" THEN MEASURES.[SIGNEDDATA]
WHEN "AST" THEN (MEASURES.[SIGNEDDATA],CLOSINGPERIOD([TIME].[H1].[MONTH]))
WHEN "LEQ" THEN -(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([TIME].[H1].[MONTH]))
ELSE -MEASURES.[SIGNEDDATA]
END
WHEN [ACCOUNT].[H3].CURRENTMEMBER.LEVEL.ORDINAL>0 THEN
CASE [ACCOUNT].[H3].CURRENTMEMBER.PROPERTIES("ACCTYPE")
WHEN "INC" THEN -MEASURES.[SIGNEDDATA]
WHEN "EXP" THEN MEASURES.[SIGNEDDATA]
WHEN "AST" THEN (MEASURES.[SIGNEDDATA],CLOSINGPERIOD([TIME].[H1].[MONTH]))
WHEN "LEQ" THEN -(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([TIME].[H1].[MONTH]))
ELSE -MEASURES.[SIGNEDDATA]
END
END
WHEN [TIME].[H2].CURRENTMEMBER.LEVEL.ORDINAL>0 THEN
CASE
WHEN [ACCOUNT].[H1].CURRENTMEMBER.LEVEL.ORDINAL>0 THEN
CASE [ACCOUNT].[H1].CURRENTMEMBER.PROPERTIES("ACCTYPE")
WHEN "INC" THEN -MEASURES.[SIGNEDDATA]
WHEN "EXP" THEN MEASURES.[SIGNEDDATA]
WHEN "AST" THEN (MEASURES.[SIGNEDDATA],CLOSINGPERIOD([TIME].[H2].[MONTH]))
WHEN "LEQ" THEN -(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([TIME].[H2].[MONTH]))
ELSE -MEASURES.[SIGNEDDATA]
END
WHEN [ACCOUNT].[H2].CURRENTMEMBER.LEVEL.ORDINAL>0 THEN
CASE [ACCOUNT].[H2].CURRENTMEMBER.PROPERTIES("ACCTYPE")
WHEN "INC" THEN -MEASURES.[SIGNEDDATA]
WHEN "EXP" THEN MEASURES.[SIGNEDDATA]
WHEN "AST" THEN (MEASURES.[SIGNEDDATA],CLOSINGPERIOD([TIME].[H2].[MONTH]))
WHEN "LEQ" THEN -(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([TIME].[H2].[MONTH]))
ELSE -MEASURES.[SIGNEDDATA]
END
WHEN [ACCOUNT].[H3].CURRENTMEMBER.LEVEL.ORDINAL>0 THEN
CASE [ACCOUNT].[H3].CURRENTMEMBER.PROPERTIES("ACCTYPE")
WHEN "INC" THEN -MEASURES.[SIGNEDDATA]
WHEN "EXP" THEN MEASURES.[SIGNEDDATA]
WHEN "AST" THEN (MEASURES.[SIGNEDDATA],CLOSINGPERIOD([TIME].[H2].[MONTH]))
WHEN "LEQ" THEN -(MEASURES.[SIGNEDDATA], CLOSINGPERIOD([TIME].[H2].[MONTH]))
ELSE -MEASURES.[SIGNEDDATA]
END
END
ELSE NULL
END
It's also much simpler and easier to understand. Length of formula is not greatly reduced due to the number of hierarchies. But it's still simple anyway.
In my test, new measure formula is giving around 50% of performance improvement of MDX query. In multiple hierarchy case, it is more improved than single hierarchy such as around 80% to 100%.
As above, multiple IIF blocks can change one CASE/WHEN block. It's easier to start from ApShell to make yourself understand structure of it. Then you can try your own application's formula. Since its totally new way of measure formula, you cannot have this formula by update MeasureFormula table in BPC 7M or update tblFormula, tblYTDFormula in BPC 5.1. You must save it in BIDS.
In BIDS, you can easily overwrite measure formula of PERIODIC. Like below picture.
Don't forget to wrap your formula with single quotation marks. Formula must begin with ‘ and end with ‘. After you overwrite it, you should save it and process. To do these jobs, click "File" menu and "Save Selected Items".
If everything is fine, your change regarding measure formula will be saved. Then, click "Database" and "Process...". Otherwise, you will be notified about error.
If you click "Process...", you will get following dialog to change process option. There is nothing to change manually. Just click "Run..." button.
Now it is completed. You can open your report to compare performance of old and new measures. I suggest make copy one of your applications and updating new application's measure. Then you can open and compare two application's performance using same report or input schedule. If you are experienced about MDX query, you can simply execute some MDX queries to compare performance. This new formula is making better peformance and faster return in both of cached or non-cached query. Other QTD and YTD formulas can be changed with same manner as well.
Information regarding customizing, tuning in my blog is not SAP official. Customizing in your production server is at your own risk.
1 Comment