CODEWITHSUNDEEP
ssasolap-cube
Gnuce Lee
Gnuce Lee

Reputation: 81

SSAS Cube: different calculation only for one company

Here's my problem: I have an SSAS cube for multiple production companies. Company data is part of the "machine" dimension:

[Machine].[Company code]

Then I have two measures:

[Measures].[OEE] and [Measures].[OEE for company X]

In this case, I have to use the second measure for company X and the first measure for all other companies.

Is there a way to make a single measure which would display one measure for company X and another measure for other companies?

Upvotes: 0

Views: 77

Answers (1)

Alex Peshik
Alex Peshik

Reputation: 1515

Several scenarios are possible (based on sources, underlying logic etc.)

  1. For physical measures based on columns. Create calculated column in Data Source View with T-SQL: case when {company X} then {column for this company} else {columns for other companies} end. Use it as a physical measure in cube.

  2. For both physical and calculated measures. Hide existing measures, create new calculated measure with MDX: IIF([Machine].[Company code].CurrentMember is [Machine].[Company code].&[X],[Measures].[OEE for company X],[Measures].[OEE]).

  3. If only [OEE] name is allowed for both. Rename existing [OEE], hide both, create new calculated measure as [Measures].[OEE] as IIF([Machine].[Company code].CurrentMember is... from #2 above.

  4. Use SCOPE statement. SCOPE([Machine].[Company code].&[X],[Measures].[OEE]); THIS=[Measures].[OEE for company X]; END SCOPE;

  5. Use direct way: ([Machine].[Company code].&[X],[Measures].[OEE])=[Measures].[OEE for company X];

Please use any of these or update your question with more details.

Upvotes: 2

Related Questions