Calcualted Cells Performance Problem

Hello,

Initially the business divided its Practices into two groups, Industrial and Functional which were maintained in the relational source system as two seperate tables. The business has decided to expand the number of practices it reports on, however changes to the relational source system has not fully been implemented. We require reporting on the Budget data now so we've created a dimension named "Practices" which follows the same dimensional structure as the "Inudstry" and "Function" dimensions except that there is an extra parent level of "Practice Type". I would like to hide this new all inclusive "Practices" dimension from end users and effectively look up the corresponding value in the Industry or Function dimension from the Budget fact table.

I have the following calculated cell which achieves the desired results but has rather slow perforamce.

CREATE CELL CALCULATION CURRENTCUBE.[Budget Functional Practice]

FOR

'({[Measures].[Budget]},

[Fiscal Date].[Fiscal].[Fiscal Mo].MEMBERS,

[Function].[Func Practice].[Func Practice Def].MEMBERS)'

AS'

(StrToMember("Measures.[" + Measures.CurrentMember.Name + "_]"),

StrToMember("[Practices].[Hierarchy].[Practice Class].&[Function Practice].&[" + [Function].[Func Practice Def].CurrentMember.Name + "]"),

[Function].[Func Practice].[All])'

I was wondering if:

a) Is a calculated cell the best solution?

b) Is there a more efficient MDX which can be used?

Thank you.

If I am understanding correctly, you have a measure called Budget_ which you want to be the budget at the All functions level. If this is correct the following scope should work and perform significantly better. All of the string and currentmember references would have been slowing things down.

SCOPE ({[Measures].[Budget]},

[Fiscal Date].[Fiscal].[Fiscal Mo].MEMBERS,

[Function].[Func Practice].[Func Practice Def].MEMBERS);

(Measures.[Budget_]) = ([Function].[Func Practice].[All], [Measures].[Budget]);

END SCOPE;

|||

Thank you for the reply Darren, I think this is a step in the right direction.

The budget fact table contains one dimension which accounts for all of our practices (this dimension is hidden from the users). What I want to be able to do is, based on a selected member from either of the visible industry practice or function practice dimensions, lookup the corresponding value in the practices dimesnsion and show the budget measure.

|||

If you're using AS 2005, you might consider an alternative approach, using many-to-many dimensions. But first (if I interpreted your scenario correctly) you would need to set up 2 "Practices" dimension security roles, 1 for users of Function and the other for users of Industry (both roles would have Visual Totals enabled). Role#1 would allow access to the "Function", and Role#2 to the "Industry" member, at the "Practice Type" level - these would also be the Default Members for the respective roles. This would ensure that Function users only access Function data and Industry users likewise. Role#1 would be denied access to the Industry dimension and Role#2 to the Function dimension.

For the many-to-many dimension modelling, there would be a bridge table (could be a named query) which maps Function dimension table rows to the Practices dimension, and a separate bridge table to map Industry. Once a measure group is created for each bridge table, 1 bridging Function and Practices and the other bridging Industry and Practices, both Function and Industry can be configured as many-to-many dimensions for the Budget measure group.