Mosha posted this interesting entry in his Blog: http://www.sqljunkies.com/WebLog/mosha/archive/2007/01/28/iif_performance.aspx
MDX CASE is a performance killer in SSAS 2005 and Mosha have many useful advices in this post about how to avoid the problems.
My short question is simply that I would like a follow up on this because the Adventure Works demo cube uses CASE frequently. I also now from reading the book that Edward Melomed participated in that when you write a KPI a lot of things are happening that you cannot see, like the creation of calculated members.
How should I make sure that my KPI:s will not hurt performance of my cubes, if I use CASE to frequently?
Should I rewrite them as IIF:s?
Kind Regards
Thomas Ivarsson
Hello Thomas,
As a person who wrote that chapter, I have to clarify a little bit. For KPIs AS indded creates calculated measures under the covers, but those members are hidden, and won't affect performance of your cube, unless you explicitly call them from MDX. So, MDX expressions used as KPI properties will only affect performance of KPIs themselfs. My gut-feeling tells me that it not so critical to optimize KPIs, I don't expect them to be executed over a large dataset, I expect them to be run on the aggregated values. If you really concerned about performace of KPIs you can consider converting to IIF, but take into consideration that the best solution proposed by Mosha-using SCOPE operator, won't work in this case, since you really need to compare the cell values.
Irina
|||Thank's Irina.
It can be useful to drill down on KPI:s but probably not down to the leaf level.
Regards
Thomas Ivarsson
No comments:
Post a Comment