I have a datetime field in my fact table, and I want to group and filter by it, so I created a time dimension and ssas generated a table for it that has a date without time primary key. What is the right way to connect this generated table to my field, create a view and calculate there additional date with empty time? or there are any other more simple way, maybe using some hierarchies or something? Sorry for probably very simple question its just my second day with analysis servises
How do you fill your fact table. The usual way to this is in the ETL process.
You have a time dimension DIM_Date
With the columns Date_ID
, Year
, Month
, Weekday
Then you should have a fact table FACT_Sales
With the columns Date_ID
, Product_ID
, Customer_ID
, value
When filling the fact table with your ETL-Tool. Let's assume it is SSIS. You do a lookup in the date dimension and set the surrogate key in the facts (Date_ID
) correspondingly.
If the tables are allready there. You can use a computed column in the SSAS Data Source View and set your foreign key relationship via drag and drop.