Today i was pointed at a good summary for optimising SSAS2005 cubes
In tandem you can use the “shared source” Visual Studio plugin - BIDS for more fine control of SSAS2005 - although use it with care if you don’t know what you’re doing!
Ok Ok I know this is trivial but it’s saved more than my own bacon before…
You’ve built an OLAP (SSAS2005) Cube (and this isn’t your normal job) and some brain box has asked for a calculated member to be available (or alternately you’re adding this as a calc. member to a report and you want it calculated on the fly - see below it’s basically identical)
For example you want to find the ratio of the current bank accounts balance relative to all the others so you add a member of the ilk (in script form this is);
CREATE MEMBER CURRENTCUBE.[MEASURES].[Ratio of Account Balance]
AS iif ( IsEmpty( [Measures].[Account Balance] ),
[Measures].[Account Balance],
( [Measures].[Account Balance] ) / ( ROOT([Account]), [Measures].[Account Balance] )
),
VISIBLE = 1 ;
So, you run the cube in a report or the browser and suddenly that lovely cube response has shot up from sub-10 seconds to never coming back… what could you have done?
Well i’m assuming you’ve got a lot of data ™ - probably hundreds of millions of fact rows and you’ve now stopped SSAS optimising the search path and ignoring the empty bits. So, give it a hint and add the NON EMPTY BEHAVIOR (sic - behaviour - that gets me every time - colour becoming color i get but behaviour is just wrong) tip.
In the GUI simply select the requisite measure field, in the script tab add
NON_EMPTY_BEHAVIOR = { [Account Balance] }
voila! instant speed up and less heart attack!
(note: if this was an inline calc-on-the-fly type member then the full wording would be…
WITH MEMBER CURRENTCUBE.[MEASURES].[Ratio of Account Balance]
AS iif ( IsEmpty( [Measures].[Account Balance] ),
[Measures].[Account Balance],
( [Measures].[Account Balance] ) / ( ROOT([Account]), [Measures].[Account Balance] )
),
NON_EMPTY_BEHAVIOR = { [Account Balance] },
VISIBLE = 1 ;
Chris has an excellent post about custom subtotals using INSCOPE that has been pretty helpful to me.
Using a Matrix with OLAP cubes can sometimes be a bit painful and icky, but this technique makes adding additional info really easy.
Now all we need is access to a proper ternary operator instead of that horrible IIF vb-istic construct and we’d be laughing.