none
Dimension 1 OR Dimension 2

    Question

  • I have a cube with a dimension called Country which is connected to FactBilling on CountryKey (DimCountry rolls country to region in a hierarchy). 

    I have the same dimension as a role playing dimension (ReferrerCountry), joined to FactBilling on ReferrerKey. 

    I am looking for a way to allow the user to query across both dimensions, but where the user can say UK from Dimension 1 (Country) and France From Dimension 2 (ReferrerCountry) and the cube returns UK from dim 1 OR France from dim 2. 

    Any ideas appreciated. The end user is querying from local Excel.

    Tuesday, March 28, 2017 10:55 AM

Answers

  • Thanks David, i think i have the solution. 

    FactBilling - join to DimBilling (1-M) - Dim Billing is the fact table converted to a dim.

    Then, join a junction table called FactBillingCountry (BillingKey from DimBilling and CountryKey from Dim Country)

    The keys in this table represent the combination of BillingEntries across the 2 country bassed dims. 

    Then a DimCountry dimension which joins to FactBillingCountry on CountryKey, 

    This seems to work, and provide all the keys from one dim, and relatign keys from another without being mutually exclusive.

    Regards, Dave

    • Marked as answer by rtdh Tuesday, March 28, 2017 1:14 PM
    Tuesday, March 28, 2017 1:14 PM

All replies

  • Hello,

    Add a cube dimension for Referrercountry, it should work.


    Rajkumar

    Tuesday, March 28, 2017 11:33 AM
  • I already tried that, but when i select countries from the ReferrerCountry in Excel, it restricts the countries in the base Country dim.
    Tuesday, March 28, 2017 11:36 AM
  • Hi ,

    I'm not sure when you wrote the cube returns UK from dim 1 OR France from dim 2 .

    Did you try to use the LinkMember function to 'jump' between members in different dimensions with the same structure ?

    https://docs.microsoft.com/en-us/sql/mdx/linkmember-mdx


    Regards, David .

    Tuesday, March 28, 2017 12:01 PM
  • Thanks David, to clarify, i want the end user to select England from dim 1 and France from dim 2, and the cube returns records where dim 1 = "England" OR dim 2 = "France". 


    Tuesday, March 28, 2017 12:53 PM
  • There is no OR/conditioal join  in the dimension usage .

    I can think on a solution where you need to duplictae the relevant measure group, slice them with all dimensions except Dim1 and Dim2 .

    Now slice Dim1 with MG1 and Dim2 with the duplicated MG2 . It is not elegant and should be doubled maitained/processed .



    Regards, David .

    Tuesday, March 28, 2017 1:04 PM
  • I'm afraid it is not possible without some kind of a redesign or a workaround. Once a user has restricted, say, Country to UK, no other members of the Country dimension will be returned even if they have billings with France as the referrer.

    One option would be to add a dedicated dimension or two, unrelated to the fact table, exclusively for users to use as Country and Referrer filters. Then you can use MDX to apply those selections to the base Country and Referrer dimensions.


    Expect me to help you solve your problems, not to solve your problems for you.

    Tuesday, March 28, 2017 1:10 PM
  • Thanks David, i think i have the solution. 

    FactBilling - join to DimBilling (1-M) - Dim Billing is the fact table converted to a dim.

    Then, join a junction table called FactBillingCountry (BillingKey from DimBilling and CountryKey from Dim Country)

    The keys in this table represent the combination of BillingEntries across the 2 country bassed dims. 

    Then a DimCountry dimension which joins to FactBillingCountry on CountryKey, 

    This seems to work, and provide all the keys from one dim, and relatign keys from another without being mutually exclusive.

    Regards, Dave

    • Marked as answer by rtdh Tuesday, March 28, 2017 1:14 PM
    Tuesday, March 28, 2017 1:14 PM
  • Thanks Alexei, i agree - i needed to do a junction/fact table which contained all keys for both dimensions. Then join this table to DimCountry. 


    Tuesday, March 28, 2017 2:24 PM
  • I'm not sure I like your proposed solution. It may work but the approach is not very scalable. I wrote a blog post that suggested an alternative approach as your question represents a more general dimensional modeling problem. Role Playing Dimension or Role Dimension?

    HTH, Martin


    Martin Mason Wordpress Blog

    Sunday, April 02, 2017 7:08 PM