CODEWITHSUNDEEP
mdxolapolap-cubessas-2012
Ricardo Castro
Ricardo Castro

Reputation: 157

SSAS MDX Get Dimension Attribute base on other attribute dimensions and a dynamic measure

I am currently experiencing a problem because I need to dynamically associate an attribute of a dimension in my multidimensional cube based on a calculation performed on the cube itself.

Based on the date selected by the user I have to calculate the number of days that a certain type of material is in stock. Then based on the return value and the material type and the group of the same, associate an attribute of the rotation dimension. That is, I have a Dimension Type of Material, a Dimension Group, and a Rotation dimension. Then based on the calculation of the number of days in stock (this calculation is already done and working properly), the type of material and the group associated a certain attribute.

Example:

Product Prod_Typ    Group
PrdA    ZMA1        SAID

If my measure "number of days" is 89 I have to associate the ID "4" (based on the follow table) because my measure returns a value between 0(INI) and 90(END) :

ID  MATTYP  GRP   COD_ID    INI  END        DSC
1   ZMA     CONS    1       0    90         TextA
2   ZMA     CONS    2       91   180        TextB
3   ZMA     CONS    3       181  99999      TextC
4   ZMA1    SAID    1       0    90         TextD
5   ZMA1    SAID    2       91   180        TextE
6   ZMA1    SAID    3       181  99999      TextF

Upvotes: 0

Views: 507

Answers (1)

MoazRub
MoazRub

Reputation: 2911

You need to do the following.

  1. Make the mapping table that you have shown a Fact. Lets say Range_Fact
  2. Connect Range_Fact to Prod_Typ and Group. This will be simple as they are already present as dimensions.
  3. In Fact_Range create two measure min(INI) and max(end).

  4. Now In your query you need to filter based on the calculation of number of days being between min(ini) and Max(end). Your query would be like

    select [Measures].[DayCount] on columns,

    filter ( ([DimProduct].[PrdA].[Prda],[DimProductType].[Pod_Typ].[Pod_Typ],[DimGroup].[Group].[Group]), [Measures].[DayCount]>=[Measures].[minINI] and [Measures].[DayCount]<=[Measures].[maxEnd] ) from [MyCube]

Upvotes: 0

Related Questions