Hi friends

Need help regarding the MDX .I do have one Mapping table M1 with measure values.

A0   A1   A2   M1     M2
1    2      3    100    960
2    3      4    140    680
1    4      7    520    660
2    5      9    550    630

and one dimension table d1 having 20 records.
with columns as
id value         mapping column
1   Red            A0
2   orange        A0
3   green        A0
4   blue        A0

So when the Dimension table is joined with Fact/Mapping table i want my Output in cube as as :

OutPut:
In rows as Red  (A0)   and column M1(Measure) as 620. (Sum of 520+100)

Logic will work as follows in Fact Map Table.

If i go for filters(in Microsoft excel) in Mapping Table. While Selecting the A0 and filter Value as 1
The below data is displayed :

Red        A0   A1   A2   M1     M2
        1    2      3    100    960
        1    4      7    520    660


A0 (1 value or Other) is basically an ID .which is logically Build with RED (Dimension ID).Same as A0(2 Value) is with Orange.

Same way if any other filter is selected. it should work with same way(Filtering the data in Mapping table and then Sum of measures)

Please help . I neeed to build this logic for Fact tables.
Should i go for NAMED Sets or calculated member for the Fact logic(Filter)

        

Hi Rakesh ,

As I understand, you can't create a dynamic relationship between the fact table and the dimension table .

What we can do is to use calculated members/scopes to define how each measure will be calculated, or you can create 1 generic dimension that will be the base for 3 role-playing  (like generic Date dim and creationDate dim, deleteDatedim ..) and a mapping column dimension, which will be used to select mapping column value, so you can use LinkMember functionality to 'jump' between the dims . 

Relations between each role-playing dimension will be regular .

Hi Rakesh, 

As far as I understood the question, you are getting the MUCH harder path to achieve your goal. 

If I've got it correctly, it'd be enough to create a SSAS dimension using table "d1" and a fact table using table "M1", and then creating a regular relationship (in the "Dimension Usage" tab in SSDT BI cube designer). 

If you need more than 1 relationship between both tables you can use role playing dimensions as David suggested. 

Regards. 

Pau.

• Marked as answer by Rakesh Dhar 16 minutes ago

Thankssss sooo much friends ....

Created One more dimension d2 and a Fact table using the named Query and joined the FACT with teh named Query after verifying the Query Output from SSMS .