Build SSAS Tabular Daliy Partition from SQL Server

Question

Hello Experts,

We are using SQL Server and SSAS Services, have to automate the process of building daily partition on fact table (TMSL code mentioned below). this should be called from Stored Procedure.

I have to code such a way to build daily partitions on table for last 5 years (from 01 Jan 2020).

The Partition name must be dynamic as "Partition_Member_"<date><month><year>.

and same date should be passed in filter criteria

each [calendar_id] = 20250211

{

"createOrReplace": {

"object": {

  "database": "audex-analyse-dev-ssas",

  "table": "audexdw fact_viewing_session_member",

  "partition": "Partition_Member_11Feb2025"

},

"partition": {

  "name": "Partition_Member_11Feb2025",

  "dataView": "full",

  "source": {

    "type": "m",

    "expression": [

      "let",

      "    Source = #\"SQL/audex-analyse-dev-ssdw-rds c6lalk0xulcn eu-west-2 rds amazonaws com;audex-an\",",

      "    audexdw_fact_viewing_session_member = Source{[Schema=\"audexdw\",Item=\"fact_viewing_session_member\"]}[Data],",

      "    #\"Changed Type\" = Table.TransformColumnTypes(audexdw_fact_viewing_session_member,{{\"person_id\", type text}}),",

      "    #\"Filtered Rows\" = Table.SelectRows(#\"Changed Type\", **each [calendar_id] = 20250211)"**,

      "in",

      "    #\"Filtered Rows\""

    ]

  }

}

}

}

Can experts please help to build above code.

Thanks

Prashant Pandey

Answer 1

Hi PANDEY Prashant!

Welcome to Microsoft Q&A!

To automate the process of building daily partitions on your fact table in SSAS Tabular, you can use a combination of TMSL (Tabular Model Scripting Language) and a stored procedure in SQL Server. Below is a detailed approach to achieve this:

Step-by-Step Guide

1. Create a Stored Procedure in SQL Server

First, create a stored procedure that generates the TMSL script for creating partitions dynamically. This stored procedure will loop through the dates from January 1, 2020, to the current date and create partitions accordingly.

CREATE PROCEDURE CreateDailyPartitions
AS
BEGIN
    DECLARE @StartDate DATE = '2020-01-01';
    DECLARE @EndDate DATE = GETDATE();
    DECLARE @CurrentDate DATE = @StartDate;
    DECLARE @TMSL NVARCHAR(MAX);
    WHILE @CurrentDate <= @EndDate
    BEGIN
        DECLARE @PartitionName NVARCHAR(50) = 'Partition_Member_' + FORMAT(@CurrentDate, 'ddMMMyyyy');
        DECLARE @CalendarID INT = CONVERT(INT, FORMAT(@CurrentDate, 'yyyyMMdd'));
        SET @TMSL = '
        {
            "createOrReplace": {
                "object": {
                    "database": "audex-analyse-dev-ssas",
                    "table": "audexdw fact_viewing_session_member",
                    "partition": "' + @PartitionName + '"
                },
                "partition": {
                    "name": "' + @PartitionName + '",
                    "dataView": "full",
                    "source": {
                        "type": "m",
                        "expression": [
                            "let",
                            "    Source = #\"SQL/audex-analyse-dev-ssdw-rds c6lalk0xulcn eu-west-2 rds amazonaws com;audex-an\",",
                            "    audexdw_fact_viewing_session_member = Source{[Schema=\"audexdw\",Item=\"fact_viewing_session_member\"]}[Data],",
                            "    #\"Changed Type\" = Table.TransformColumnTypes(audexdw_fact_viewing_session_member,{{\"person_id\", type text}}),",
                            "    #\"Filtered Rows\" = Table.SelectRows(#\"Changed Type\", each [calendar_id] = ' + CAST(@CalendarID AS NVARCHAR) + ')",
                            "in",
                            "    #\"Filtered Rows\""
                        ]
                    }
                }
            }
        }';
        -- Execute the TMSL script using SSMS or any other tool that supports TMSL execution
        EXEC sp_execute_external_script @TMSL;
        SET @CurrentDate = DATEADD(DAY, 1, @CurrentDate);
    END
END

2. Execute the Stored Procedure

You can execute the stored procedure to create the partitions. This will loop through each date and create a partition with the appropriate name and filter criteria.

EXEC CreateDailyPartitions;

Additional Considerations

• Performance: Ensure that your SSAS server can handle the number of partitions being created. Too many partitions can impact performance.
• Maintenance: Regularly review and maintain the partitions to ensure they are optimized and not causing unnecessary overhead.
• Error Handling: Add error handling in the stored procedure to manage any issues that may arise during the partition creation process.

References

• Partitions in Analysis Services Tabular Models

I hope this helps you automate the process of building daily partitions in SSAS Tabular. If you have any further questions or need additional assistance, feel free to ask. Good luck with your project!

Best regards,
Jonathan

---

Your feedback is very important to us! If this answer resolved your query, please click 'YES'. This helps us continuously improve the quality and relevance of our solutions. Thank you for your cooperation!

Answer 2

Good morning, PANDEY Prashant!

In accordance with the previously provided information, the purpose of this message is to verify the same. If you have any new updates related to this matter, please feel free to respond in this thread.

Sincerely,

Jonathan

---

Your feedback is very important to us! If this answer resolved your query, please click 'Yes'. This helps us continuously improve the quality and relevance of our solutions. Thank you for your cooperation!

Answer 3

Thanks for coming back Jonathan. nothing on this thread left.

Would request you to please also look into the new thread

https://learn.microsoft.com/en-us/answers/questions/2168268/ssas-tabular-delta-schema-changes-and-processing