Find Date from SQL Table in Analysis Services

  • 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,

  • 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

  • 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?

  • 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

  • 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