Lately while working on a report for one of our customers, I experienced some major performance issues. The report displays various KPIs of a selected business unit which can be compared to another unit. Since the KPI values are taken from two different cubes but had to be displayed in a single matrix, I used LookupCube (see MSDN).

So I had a query structured like this:


WITH
// a member being looked up from another cube
MEMBER lookupMember as LOOKUPCUBE( "cubeName", "SUM(set, someMember)" )

// a member from a measure within the current cube
MEMBER cubeMember as [Measures].[someMeasure]

// other members using the looked up one for further calculation
MEMBER calculatedMember1 as cubeMember + lookupMember
MEMBER calculatedMember2 as cubeMember - lookupMember

select ...

Now using such a setup, I was experiencing major performance issues. I figured out that this is because everytime the lookupMember is used for further calculations, it is being re-evaluated. So when querying 1000 records, LOOKUPCUBE( “cubeName”, “SUM(set, someMember)” ) is called two times per row, so 2000 times per query. This is because it is treated as a reference, not as a value.

So to solve this issue, use StrToValue (MSDN):

WITH
// a member being looked up from another cube
MEMBER lookupMember as LOOKUPCUBE( “cubeName”, “STRTOVALUE(SUM(set, someMember))” )

Follow

Get every new post delivered to your Inbox.