# 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 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