Processing monthly Partition from SQL Job using Stored Procedure

Question

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

Answer 1

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.