How To Implement Slowly Changing Dimension In SSAS Tabular Model

Palash Aich 21 Reputation points
2021-11-23T10:59:28.693+00:00

Hello there,

I am trying to implement SCD Type 2 in SSAS tabular model. My model will not have a common date dimension for the users as I have many dates. The reason for not having a common date dimension is to avoid confusion from the users of many role-playing dimensions. My date fields will be part of the dimension table itself. If a user selects a particular date or other attributes, my fact/measures should aggregate data accordingly. Also, if a user selects a particular year, and if the year has many entries, it should fetch the last available values for that particular year. This is similar to LASTNONEMPTY in the multi-dimensional cube.

Please can you share details on how to implement the same in SSAS tabular model? I will have one SCD Type 2 dimension in the SQL DB and a fact table with a surrogate key from the dimension for the relationship.

Thanks,
Palash

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,710 questions
SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,244 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,552 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. CarrinWu-MSFT 6,851 Reputation points
    2021-11-24T03:19:56.143+00:00

    Hi @Palash Aich ,

    Thanks for your posting.

    Please refer to below links to get more information:
    Analysis Services Tabular: Displaying History and Slowly Changing Dimensions
    Slowly Changing Dimensions, Part 2
    Implementing Slowly Changing Dimensions (SCDs) in Data Warehouses

    Best regards,
    Carrin

    If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments