Search code examples
sql-serverdata-warehousebusiness-intelligencessas

One to many Relationship in SQL Server Analysis Services

I have these tables:

  1. DimDate (PK: DateKey, other attributes)

  2. FactActivationCodes (PK: ActivationCode, IssuedDateKey (FK to DimDate)

  3. FactExpirations (PK: ActivationCode + ExpirationType, FK: ActivationCode to FactActivationCodes)

I set up measures that count the number of rows in

  1. Issued Count (count of rows in FactActivationCodes)

  2. Expired Count (count of distinct ActivationCodes in FactExpirations)

The idea is that the FactActivationCodes has one activation code, with a date when it was issued. The activation code can get expired year after year (and then renewed) so it would have a row for expiration in FactExpirations (one each year)

I put some test rows in the tables; I put 3 rows in FactActivationCodes (different IssuedDate for each) , and only 2 in FactExpirations. When I browse the cube, and I am looking at the count of Issued on columns, and the Issued Date (dimension) on rows, it looks like this:


                   Issued Date 
January 2008         1 
February 2008        1 
March 2008           1

But then, when I add the Expired Count, I was hoping to see the 'expired column' count with only the ones that match the 'Activation Code' like so, because of the one to many relationship between the two fact tables:


                    Issued Date   Expired Date 
January 2008            1             1  
February 2008           1             1 
March 2008              1             0

But instead, I a cross join of everything like so, with the totals of expired:



                    Issued Date   Expired Date 
January 2008            1               2 
February 2008           1               2 
March 2008              1               2 
April 2008                              2 
May 2008                                2 
June 2008                               2

And onwards, for every date entry in my Date Dimensions... I guess I'm not doing the relationship correctly... how can I get the expected result?

Solution

  • The answer to use referenced relationship: http://technet.microsoft.com/en-us/library/ms166704.aspx