"Join" facts and dimensions in olap cube

1
votes

I have to calculate / present number of team trough years in olap cube

My team fact is structured this way:

TeamId DateFrom DateTo (FactTeams)
1       2012     2015
2       2012     2015
3       2012     2015
4       2015     2018
1       2018     2019

Cube must be able to answer, for example, how many teams have been active in year 2012 (3 teams)

I have prepared another helper fact table that contains all combination of teams id and their dates. 

TeamId  DateRange (FactTeamDates)

1       2012
1       2013
1       2014
1       2015
1       2018
1       2019
2       2012
2       2013
2       2014
2       2015
...and so on ...

I have created two facts one FactTeams and another FactTeamDates. I have also standard date dimension. Here is my data source view:

https://www.dropbox.com/s/5d2gzumxv5fejdq/teams.jpg

FactTeams.TeamId is linked to FactTeamDates.TeamId and FactTeamDates.DateRange is linked to DimDates.DateKey.

I have measure “Team Number” that is distinct count of FactTeams on column TeamId. My desired MDX query output for measure Team Number on COLUMNS and Years ON ROWS is:

           Team Number  
Year 2012  3
Year 2013  3
Year 2014  3
Year 2015  4

My question: How to organize my fact and set dimension usage in my cube to get desired output?

SQL query that produce desired output:

   SELECT 
       d.CalendarYear
      ,COUNT(DISTINCT TeamId) 
  FROM  FactTeams zt 
  INNER JOIN FactTeamsDates td ON zt.TeamId = td.TeamId
  INNER JOIN DimDates d ON d.DateKey = td.DateRange
  GROUP BY d.CalendarYear ORDER BY 1

Note that I know that I can create data view based on the above sql query (with joins) and then have one joined fact table, but I want to have some kind of join between my cubes dimensions and facts – to have joins in cube (olap) level only, not in sql (database, or cube data view)

Thanks in advance

1
joinmdxolapdimension

1 Answers

0
votes

You can create a distinct count measure and this should solve your problem