0

Model mapping advanced

Hi, 

I have two different OLAP cubes with two different dimensions for Companies with different hierarchies and different name standard. I would like to use a filter in my Present dashboard from one of the dimension to select a company in the other model as well. I have read and tried the Advanced model mapping but I am not able to make it (I am just a superuser that obviously  not that advanced in MDX). 

 

Model 1 is Finance and this is where I have my current slicer. Model 2 is Sales DW and where I have my Discovery report I want to be affected of the slicer.

I believe I do not get it right with the hierarchy levels. 

 

TargetMember().HierarchyUniqueName() + ".["
 + Case(DataSet(SourceMember().MemberCaption()="C46752"), 

DataSet("X46752 SWE, Alfa Laval Tumba AB"), "")
 + "]"

 

If I do a formula of the selected company in the Unit Source System I get the following string for the company called X46752 SWE, Alfa Laval Tumba AB above; [Unit Source System].[Unit Source System].[Company].&[RE300]&[CL400]&[SR430]&[Swe467]&[X46752]

But even if I include this in the DataSet above I do not succeed. 

 

Would very much appreciate some help here. 

3 replies

null
    • Jason_Picker
    • 3 yrs ago
    • Reported - view

    When working through the Model Mapping on MS OLAP cubes, it is important to understand how MDX members are represented, either through captions or keys.  If you are matching via captions, then the format is [Dimension].[Hierarchy].[Caption] or [Dimension].[Hierarchy].[Level].[Caption] depending on how unique the caption is (i.e. [Unit].[Company Code].[C46752]).   If you are matching via keys, then you will see an ampersand in the naming convention and the value may be dramatically different than the caption [Dimension].[Hierarchy].&[Key] (i.e. [Unit Source System].[Unit Source System].[Company].&[RE300]&[CL400]&[SR430]&[Swe467]&[X46752]).  It really depends on how the cube was built as to which method is easier to match.  In the example you showed, it looks like there is not a simple way to parse the captions to map them and the keys for the Sales DW model are a combination of multiple levels.  So the quickest way to solve the problem would be with a manual mapping of each value in both cubes by using a CASE statement. Obviously this would be difficult if you had a large number of values to map.

    Be aware that the CASE statement in the Model Mapper is not MDX but actually PQL so the syntax is a bit different (see Help file for more detail: LINK ).  Here is an example:

     

    Case(
    criteria(SourceMember().MemberCaption()="Canada"
    ,SourceMember().MemberCaption()="France"
    ,SourceMember().MemberCaption()="Germany"
    ,SourceMember().MemberCaption()="Australia"
    ,SourceMember().MemberCaption()="United States")
    ,criteriaResults("[Organization].[Organization].&[8]"
    ,"[Organization].[Organization].&[11]"
    ,"[Organization].[Organization].&[12]"
    ,"[Organization].[Organization].&[13]"
    ,"[Organization].[Organization].&[7]")
    ,"")

    In my example, I am mapping the Caption from one model to the Key of the other model. There are other ways to write this but it should give you an idea of the basic syntax.

    • Jason_Picker
    • 3 yrs ago
    • Reported - view

    By the way, if you are having trouble constructing the right logic for your mapping, you can take advantage of the Designer to help test the logic before you run it live.

     

    You can pick a sample member from the Source attribute and test it with your logic to see what it produces.  

     

      • Maria_Hagg
      • 3 yrs ago
      • Reported - view

      Jason Picker Thank you very much! I will try it out. 

Content aside

  • Status Answered
  • 3 yrs agoLast active
  • 3Replies
  • 62Views
  • 2 Following