September 27, 2013 at 7:23 am
The best approach would be to create a date/time dimension.
Using Date Dimensions for Reporting
Storing the dates as-is in a dimension or fact table is a terrible idea. As you have noticed, it is nearly impossible to do effective analysis on such kind of data. You'll need to split it in a seperate date and time dimension.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
September 27, 2013 at 8:08 am
Thank you for your reply.
I have Schedule table and Date is a column of the table,? which contain Date and time like 2013. 01.10 12:00:13.Now if u plz tell how can I add Year, Month, Day in the? cube and/or Dimesion and also how can I fetch/show those in Excel?
September 29, 2013 at 1:35 pm
You need to create another table which stores all the dates between two certain dates.
For example all days between 1900-01-01 and 2050-12-31. You can add extra columns with year, month, quarter info.
Add a foreign key from your schedule table to this data table.
Do the same for time: store every minute of the day in a seperate table: 00:00 till 23:59. Add yet a surrogate key in your schedule table.
Add those two tables as dimensions in SSAS. You will be able to browse them in Excel.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
October 2, 2013 at 7:40 am
okz! thanks a lot 🙂
Viewing 4 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply