locked
Date and Time splitting to reduce Cardinality in SSAS RRS feed

  • Question

  • Hi All,

    I have a DATETIME column in Oracle db. Using the same in SSAS. As I understood from blogs, we should split Date and Time into two columns to reduce the cardinality.

    I have done that in SSDT. But what if my requirement is to show the date with time stamp in the same column in Reporting side.

    Regards,

    Akash

    Wednesday, March 21, 2018 6:19 AM

All replies

  • For requirements like that SSAS is not the best tool

    Displaying data at point in time is more like operational reporting need which is best dealt with reporting tools like SSRS

    SSAS is more on aggregated data and dynamic slicing dicing based on multiple factors one of which can be date. In most cases it makes sense to go until day level or even hour level rather than analyzing to a point in time

    Even if you want point in time you can still go ahead an add timestamp level data in SSAS but it will make time dimension huge causing it to spawn enormous number of aggregation combinations causing cube performance to suffer.

    So from analysis perspective its better to keep time based analysis at day level or may be hour. You may still add it as a measure if you want to find out when a transaction occured etc rather than using it as a slicer/dicer attribute

    see

    https://biwithjb.wordpress.com/2011/05/30/date-as-a-measure/


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Wednesday, March 21, 2018 6:26 AM
  • I am talking about the following:

    https://www.sqlbi.com/articles/optimizing-high-cardinality-columns-in-vertipaq/

    DATETIME Columns Section.

    Wednesday, March 21, 2018 7:01 AM
  • Having a datatime data type for a column on a detail report is different than having one in an Analysis Services (SSAS) database.

    Line Item reporting is not suggested in Analysis Services (SSAS). The aggregations in SSAS for a date dimension are columns like Year or Month or Day. The Time dimension would be Hour or Minutes of a day. That is why SQLBI.com's suggestion is to split those into 2 columns in the data imported into SSAS.

    In the SQL Server world, we would create a view of the source table and use the logic like the article you show. Not sure how to use that in Oracle.

    If you cannot live without the DateTime column in SSAS database, then create 2 new columns in the SQL query for Date and one for Time PLUS keep the existing column for data datetime. Just do not related the DateTime column to any Time Intelligence functions.

    Wednesday, March 21, 2018 4:34 PM
  • Hi Akash,

    Thanks for your question,

    I agree with Visakh16 and Thomas,requirements like that SSAS is not the best tool. As i suggested in the other thread posted by you, For report with high cardinality data, it's much better use T-SQL to achieve the same thing for report with high cardinality data.

    Please refer to below article to better understand granularity for your SSAS model:
    https://dwbi.org/data-modelling/dimensional-model/1-dimensional-modeling-guide


    Best Regards
    Willson Yuan
    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, March 22, 2018 2:18 AM