September 27, 2013 at 7:03 am
Hi,
How can I find date from SQL Table in Analysis Services. Like I have Thousand of date in a table 2013-08-13 10:38:39.003, 2012-12-01 45:30:01.009....... and more, How can I add only Years, Months and Dates from those data?
Regards,
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 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply