Friday, March 30, 2012

Mapping two measure groups with different dimension usage

Hello.

I am looking for a solution for the following situation:

I have a measure group for my business facts and a measure group for tolerances and targets.

The first measure group uses a date dimension, a site dimension, a project dimension and an employee dimension.

The second group shares all dimensions except the employee dimension.

Lets say I have the business fact sales per hour.
The target value for this fact is stored in the second measure group without the employee info. The targets are valid for all employees at that site, project and day.

When i browse the cube i want see the targets next to the actual data - even on employee level.

Therefore i have to set IgnoreUnrelatedDimensions in the properties of the second measure group to True.
But when i do this i get an entry for every employee present in the employee dimension, showing the tolerance and target.

I am lost at the moment and belive that there must be an elegant solution. Hopefully someone has an idea?

Thanks,
ThomasFrom your description, it's not clear in which cases the IgnoreUnrelatedDimensions doesn't meet your needs - could you describe some scenarios? You could selectively use the MDX ValidMeasure() function in calculations, instead of applying IgnoreUnrelatedDimensions.|||Hello Deepak.

Thanks for your reply.

I'll try to explain my problem better with an example.

Data in my Tolerance FactTable:

DateProjectSiteTolerance_Sales_per_Hour20070320Upsell1Hamburg220070321Upsell1Hamburg220070322Upsell1Hamburg3

Data in my Sales FactTable:

DateProjectSiteEmployeeSales_Per_Hour20070320Upsell1HamburgBob1.520070320Upsell1HamburgMike2.120070321Upsell1HamburgBob2.720070321Upsell1HamburgMike3.320070322Upsell1HamburgBob1.2

When I select 20070322 as Date in the Browser, i get a row for Mike, although he dind't work on 22th.

DateProjectSiteEmployeeSales_Per_HourTolerance_Sales_per_Hour20070322Upsell1HamburgBob1.2320070322Upsell1HamburgMike3

Thanks to a tip from Markus i am using Scope-Statements at the moment

SCOPE([Measures].[Tolerance_Sales_Per_Hour]);
THIS=IIF (ISEMPTY([Measures].[Sales_Per_Hour]),NULL,[Tolerance_Sales_Per_Hour]);
END SCOPE;


But we are not sure, if this is the best solution.

Best regards,
Thomas

No comments:

Post a Comment