Further to my recent posts on using SCOPE() statements in a cube and potential issues in using the SCOPE() statement, I came across another issue last week.
In a cube I was developing I had used a SCOPE() statement to ensure that when a particular measure was selected, the dimension member used (for one particular dimension) would be the datamember, so that the measure value displayed would be the "fact table" value and not the aggregated value of the children. (for more on using the DATAMEMBER function see this post).
In the calculation tab of the cube editor I wrote the following:
SCOPE([Measures].[MyMeasure]); THIS=[Portfolio].[Portfolio Name].CURRENTMEMBER.DATAMEMBER; END SCOPE;
However, in user testing accross large volumes of data this measure performed very slowly, taking over a minute to return results.
I tried experimenting with different approaches, and found that the desired results could be achieved without using the scope statement. A calculated measure can be used, that contains the datamember function in its logic:
CREATE MEMBER CURRENTCUBE.[Measures].[MyMeasure2]
AS ([Measures].[MyMeasure,[Portfolio].[Portfolio Name].DATAMEMBER), VISIBLE = 1 ;
This measure produced the same result set but surprisingly the response time reduced to around ten seconds.
If anyone has had a similar experience and has explanation why using the DATAMEMBER function in a calculated measure rather than a SCOPE statement should perform faster please leave a comment and let me know!
No comments:
Post a Comment