How I create Single Measure Group Upoun two Different Measure Group in SSAS?

Question

Hi All,

I am working with SSAS R2. I have created SSAS Cube for two mesure group

i.e. GroupMeasure 1

         -- abc cost

         -- efg cost

         -- ghi total hrs

      Group Measure 2

          -- OPQ Cost

          -- RST Cost

           -- XYZ Hrs

But I want to add both are in single Measure Group Like

           Associated Measure Group

                  --  abc cost_GroupMeasure 1

                   -- RST Cost_GroupMeasure 2

                  -- ghi total hrs_GroupMeasure 1

                  -- XYZ Hrs_GroupMeasure 2

So please let me know any idia or solution to create this...How I create Single Measure Group Upoun two Different Measure Group in SSAS?

Thank you.

Parikshit               

Answer 1

Parikshit,

If you want to do this so that the measures are presented in a single measure group when the users connect, you can do the following ...

• Do not expose the base measures (abc cost, efg cost, ghi total hrs, OPQ Cost, RST Cost, XYX Hrs) - so do not select them in the perspective.
• Expose the measures as calculated measures only.  So you do select them in the perspective.

CREATE MEMBER CURRENTCUBE.[Measures].[abc cost_GroupMeasure 1] AS [Measures].[abc cost], ASSOCIATED_MEASURE_GROUP='Associated Measure Group';

CREATE MEMBER CURRENTCUBE.[Measures].[RST Cost_GroupMeasure 2] AS [Measures].[RST Cost], ASSOCIATED_MEASURE_GROUP='Associated Measure Group';

...

The measure group they are associated with must exist, but it does not need to be GroupMeasure 1 or GroupMeasure 2

Christian

Answer 2

Please make this changes,

• Open the Data Source View by double clicking it in the Solution Explorer.
• Then right click on the design area and then select New Named Query
• Edit the query as below

select col1,col2

from measure_group1_table

union

select col1,col2

from measure_group2_table

• Above query gives you new measure group which is a  Associated Measure Group

Regards,

YB