Processing monthly Partition from SQL Job using Stored Procedure

PANDEY Prashant 125 Reputation points
2024-09-23T06:43:51.1533333+00:00

Hello Expert,

I have a Tabular model which is having a fact table partitioned monthly on date field.

e,g. Partition_July2024, Partition_Aug_2024, Partiton_Sept2024 etc..

I want to create a single SQL job to process these monthly partitions which should detect the running month and based on that the below TMSL statement should be updated with respective monthly partition to process?

{

"refresh": {

"type": "automatic",

"objects": [

  {

    "database": "dev-ssas",

    "table": "fact_viewing_session_member",

    "partition": "Partition_Sept2024"

  }

]
```  }

}

I understand this can be done using stored procedure, I am not well versed with creating SP, please help to make a dynamic SP to process the monthly tabular model partition. 

Thanks 

Prashant Pandey

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,785 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,282 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,637 questions
0 comments No comments
{count} votes

Accepted answer
  1. MikeyQiaoMSFT-0444 3,025 Reputation points
    2024-09-24T05:53:19.57+00:00

    Hi, PANDEY Prashant

    This is the stored procedure I created based on your description to refresh the specific area with the current date.

    CREATE PROCEDURE ProcessMonthlyPartition
    AS
    BEGIN
        DECLARE @CurrentMonth NVARCHAR(20)
        DECLARE @PartitionName NVARCHAR(100)
        DECLARE @JsonCommand NVARCHAR(MAX)
        DECLARE @CurrentYear NVARCHAR(4)
        -- Get current month and year 
        SET @CurrentMonth = DATENAME(MONTH, GETDATE()) 
        SET @CurrentYear = DATEPART(YEAR, GETDATE()) 
        -- Build partition name 
        SET @PartitionName = 'Partition_' + @CurrentMonth + @CurrentYear 
        -- Build TMSL command
        SET @JsonCommand = 
        '{
            "refresh": {
                "type": "automatic",
                "objects": [
                    {
                        "database": "dev-ssas",
                        "table": "fact_viewing_session_member",
                        "partition": "' + @PartitionName + '"
                    }
                ]
            }
        }'
    
    
    

    Regards

    Mikey Qiao


    If you're satisfied with the answer, don't forget to "Accept it," as this will help others who have similar questions to yours.

    1 person found this answer helpful.

0 additional answers

Sort by: Most helpful

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.