4

My fact table has a "Date" column that I have used as a foreign key to the Time Dimension table I used SSAS to create for me in the datasource. However when I deploy the cube and browse it, adding the time dimension attributes or any of its hierarchies do not filter anything on the measures. Its like there is a disconnect between the time information in my fact table and the time dimension table SSAS created for me.

Am I missing something here ? How do I link them such that I can use the generated time dimension to apply slice and dice analytics on my cube based on the date values in my fact table ?

skaffman
  • 398,947
  • 96
  • 818
  • 769
Cranialsurge
  • 6,104
  • 7
  • 40
  • 39

1 Answers1

5
  • Is the date column the primary key column of the dimension table?
  • Is that referenced by a foreign key from the fact table?
  • Are the primary key and the foreign key relationship shown in the Analysis Services DSV?
  • In the Cube editor, do you see a filled white area in the intersection of the measure group and the time dimension showing the correct key name, and not an empty gray area?
  • Does it really contain dates or datetimes?

A pattern used often is for a time dimension in OLAP applications when you have a granularity of days is to have an integer primary key of the structure yyyymmdd (i. e. year * 10000 + month * 100 + day_of_month. This makes the key human readable, but still is an integer which is slightly more efficient for Analysis Services.

leemicw
  • 751
  • 8
  • 15
Frank
  • 2,628
  • 15
  • 14
  • Yea the ssas generated time dimension's primary key is a datetime column and it relates to the "Date" column in my fact table as its foreign key. The time dimensions displays fine in the cube editor ... I just reverted to using named calculations for month and year in the fact table iteself – Cranialsurge Aug 18 '10 at 18:39
  • Then everything seems to be ok on the first view. How and in which application did you do the filtering? – Frank Aug 18 '10 at 20:14
  • how i can get yyyymmdd ID with BIDS Generate Relational Schema wizard ? – Amr Ellafy Jan 16 '12 at 14:42
  • For the dot point that refers to the Cube Editor you have to go to the Dimension Usage tab to see what he's referring to. – Jeff Jan 30 '18 at 23:03