none
Referencing two fact keys from one dimension using Dim.Key = Fact.Key1 OR Fact.Key2 possible ?

    Question

  • Hi all

    I need your help for an approach to the following problem. Lets say that i have the following dataset

    AccountMonth

    ActivityMonth

    Amount

    201307

    201301

    2.500

    201307

    201304

    600

    201307

    201305

    900

    201307

    201306

    4.000

    201307

    201307

    500.000

    201308

    201305

    500

    201308

    201307

    400

    201308

    201306

    300

    201308

    201308

    400.000

    201309

    201307

    300

    201309

    201302

    500

    201309

    201309

    100.000

    201310

    201305

    400

    201310

    201309

    50.000

    201310

    201310

    200.000

    In my cube i want the user to select the Accountmonth from one time dimension, but i want some logic to also apply for the ActivityMonth. For one measure if I for example select 201307 i'll get this amount

    AccountMonth

    ActivityMonth

    Amount

    201307

    201301

    2.500

    201307

    201304

    600

    201307

    201305

    900

    201307

    201306

    4.000

    201307

    201307

    500.000

    This is easy - Accountmonth dimension is related to AccountMonth column in the fact table. Now i also need another measure, where the month selected needs to be meet for both accounting and activity month. This is also easy, as i can just reference both columns in the fact table. Result in this case is then only one row:

    AccountMonth

    ActivityMonth

    Amount

    201307

    201307

    500.000

    Now the tricky part comes when I start to select more months, because the amount i need is where month combinations are shared, so if i for example choose 201307, 201308 and 201309 i don't just want

    AccountMonth

    ActivityMonth

    Amount

    201307

    201307

    500.000

    201308

    201308

    400.000

    201309

    201309

    100.000

    I actually want the amount from which the months is in either accounting or activity, like this

    AccountMonth

    ActivityMonth

    Amount

    201307

    201307

    500.000

    201308

    201307

    400

    201308

    201308

    400.000

    201309

    201307

    300

    201309

    201309

    100.000

    I know how to solve it either by having two time dimensions or using MDX, but user should only have to select from one time dimension. So far, solving it using Non-empty/existing in MDX is not performing fast enough. Feedback will be appreaciated if you have an idea of how to solve this.


    • Edited by Jam281 Thursday, June 19, 2014 2:45 PM better subject
    Wednesday, June 18, 2014 10:36 PM

All replies

  • So far, solving it using Non-empty/existing in MDX is not performing fast enough. Feedback will be appreaciated if you have an idea of how to solve this.
    here are some links about performance tuning
    http://www.mssqltips.com/sqlservertip/2565/ssas--best-practices-and-performance-optimization--part-1-of-4/
    http://technet.microsoft.com/en-us/library/cc966527.aspx
    Friday, June 20, 2014 9:14 AM