Automatically Create Model Partitions

Creating Tabluar model partitions through visual studio may be a headache task. If you manually typed wrong query in any partition, you may screw up the processing.

In this post, I will share some code I used before to help you automatically create SSAS model partitions.

Step 1: Using SQL management studuo, we need to create a table called PartitionList to provide some information about the partition including PartitionID, the dates which will be used in the partition query and etc.

IF OBJECT_ID(‘dbo.PartitionList’) IS NOT NULL

BEGIN

DROP TABLE dbo.PartitionList

END

CREATE TABLE dbo.[PartitionList]

([PartitionID] [INT] IDENTITY(1, 1)

NOT NULL,

[CreatedDate] [DATETIME] NOT NULL,

[Active] [INT] NOT NULL,

[PartitionNameSuffix] [VARCHAR](255) NOT NULL,

[PartitionStartDate] [DATE] NOT NULL,

[PartitionEndDate] [DATE] NOT NULL,

[PartitionStartDateID] AS (CONVERT( [INT], CONVERT([CHAR](8), [PartitionStartDate], (112)))) PERSISTED,

[PartitionEndDateID] AS (CONVERT( [INT], CONVERT([CHAR](8), [PartitionEndDate], (112)))

)

CONSTRAINT [PK_PartitionList] PRIMARY KEY CLUSTERED

(

[PartitionID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

ALTER TABLE dbo.[PartitionList]

ADD CONSTRAINT [DF_PartitionList_CreatedDate] DEFAULT(GETDATE()) FOR [CreatedDate]

ALTER TABLE dbo.[PartitionList]

ADD CONSTRAINT [DF_PartitionList_Active] DEFAULT((0)) FOR [Active]

–DELETE dbo.[PartitionList]

–SELECT *

–FROM dbo.[PartitionList]

Step 2: After created the Partition List Table, we need to populate the list with data. Assuming you need to create weekly partitions for year 2016. And we will need a calendar table(most organization will have calendar table, and if you don’t have, you can find plenty of sources online. So here I assume you have one).

DECLARE @dynamicSQL VARCHAR(MAX)

SET @dynamicSQL = ”

SELECT @dynamicSQL = @dynamicSQL+’INSERT INTO dbo.[PartitionList] (

[PartitionNameSuffix]

,[PartitionStartDate]

,[PartitionEndDate])

VALUES (”’+CAST(CalendarYear AS CHAR(4))+’W’+RIGHT(‘0’+CAST(CalendarWeekNumber AS VARCHAR(2)), 2)+”’

, ”’+CONVERT(VARCHAR(50), [Date])+”’

, ”’+CONVERT(VARCHAR(50), DATEADD(day, 6, [Date]))+”’)’

FROM Ancillary.DBO.V_Calendar x WITH (NOLOCK)

WHERE [Date] >= ‘2016-01-01’

AND [Date] < ‘2017-01-01’

AND ([Day Number Of Week] = 1

OR [Date] = ‘2016-01-01’)

ORDER BY x.[Date]

EXEC (@dynamicSQL)

UPDATE PartitionList

SET

active = 1

WHERE PartitionID = 1

UPDATE PartitionList

SET

PartitionEndDate = ‘2016-01-02’

WHERE PartitionID = 1

UPDATE PartitionList

SET

active = 1

WHERE PartitionID = 1

SELECT *

FROM PartitionList

Step 3: Create a table called Modelinfo which provide the information about the model and the table in the model you are partitioning at.

IF OBJECT_ID(‘dbo.ModelInfo’) IS NOT NULL

BEGIN

DROP TABLE dbo.ModelInfo

END

CREATE TABLE dbo.ModelInfo

(ModelInfoID INT IDENTITY(1, 1)

NOT NULL,

DatabaseID VARCHAR(200),

CubeID VARCHAR(200),

MeasureID VARCHAR(200),

DatasourceID VARCHAR(200),

TableName VARCHAR(200),

PartitionField VARCHAR(200),

ServerName VARCHAR(200)

)

DELETE dbo.modelinfo

INSERT INTO dbo.ModelInfo

VALUES

(‘(modelName)‘,

‘(Usually you just need to put – Model)‘,

‘(ModelTableId which can be found in partition processing script)‘,

‘(DatasourceID which can be found when you trying to script on creating an new partition through management studio)‘,

‘(the table name in the database which your model table is trying to query at)‘,

‘(the field to partition at)‘,

‘(The server which the model is located at)‘

)

Step 3: Create a table called TableList which provide the information about partition id and queries on the targeted model table. Assume in our table has a field called [System] and we need to partition the table by system and by week

IF OBJECT_ID(‘dbo.TableList’) IS NOT NULL

BEGIN

DROP TABLE dbo.TableList

END

CREATE TABLE dbo.TableList

([PartitionID] [INT] NOT NULL,

[System] VARCHAR(255) NOT NULL,

[PartitionName] VARCHAR(255) NOT NULL,

[TableName] [VARCHAR](255) NOT NULL,

[PartitionQuery] [VARCHAR](MAX) NULL,

[Active] [INT] NOT NULL

)

ON [PRIMARY]

ALTER TABLE dbo.[TableList]

ADD CONSTRAINT [DF_Table_Active] DEFAULT((0)) FOR [Active]

Step 4: Populate the TableList with data regarding the tables partition queries

IF OBJECT_ID(‘TEMPDB..#TempModelInfo’) IS NOT NULL

BEGIN

DROP TABLE #TempModelInfo

END

SELECT *

INTO #TempModelInfo

FROM dbo.ModelInfo

WHILE(

(

SELECT COUNT(*)

FROM #TempModelInfo

) > 0)

BEGIN

DECLARE @ModelInfoID INT=

(

SELECT MIN(ModelInfoID)

FROM #TempModelInfo

)

DECLARE @TableName VARCHAR(200)=

(

SELECT TableName

FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

)

DECLARE @PartitionField VARCHAR(200)=

(

SELECT PartitionField

FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

)

DECLARE @int INT= 1

WHILE(@int < 54)

BEGIN

IF OBJECT_ID(‘TEMPDB..#TempSystems’) IS NOT NULL

BEGIN

DROP TABLE #TempSystems

END

SELECT ‘SystemA’ AS SystemName

INTO #TempSystems

UNION

SELECT ‘SystemB’

UNION

SELECT ‘SystemC’

UNION

SELECT ‘SystemD’

WHILE(

(

SELECT COUNT(*)

FROM #TempSystems

) > 0)

BEGIN

DECLARE @System VARCHAR(50)=

(

SELECT MIN(SystemName)

FROM #TempSystems

)

DECLARE @FilterStart VARCHAR(50)= CAST(

(

SELECT PartitionStartDate

FROM dbo.PartitionList

WHERE PartitionID = @int

) AS VARCHAR(50))

DECLARE @FilterEnd VARCHAR(50)= CAST(

(

SELECT PartitionEndDate

FROM dbo.PartitionList

WHERE PartitionID = @int

) AS VARCHAR(50))

DECLARE @PartitionName VARCHAR(100)= @System +

(

SELECT PartitionNameSuffix

FROM dbo.PartitionList

WHERE PartitionID = @int

)

INSERT INTO dbo.TableList

(TableName,

PartitionID,

System,

PartitionName,

PartitionQuery

)

VALUES

(@TableName,

@int,

@System,

@PartitionName,

‘SELECT * FROM dbo.’+@TableName+’ WHERE ‘+’LTRIM(RTRIM([System])) =”’+@System+”’ and ‘+@PartitionField+’ between ”’+@FilterStart+”’ AND ”’+@FilterEnd+”’ ‘

)

DELETE FROM #TempSystems

WHERE SystemName = @System

END

SET @int = @int + 1

END

DELETE FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

END

Step 5: Create a store procedure, by excuting the store procedure it will create a job in your SQL server agent. You can double check the properties of the job and make sure everything looks good. By triggering the job, your partitions will created in the model you are targeted at

Create PROC [dbo].[CreatePartitions]

DECLARE @UserName VARCHAR(200)=

(

SELECT CURRENT_USER

)

IF OBJECT_ID(‘TEMPDB..#TempModelInfo’) IS NOT NULL

BEGIN

DROP TABLE #TempModelInfo

END

SELECT *

INTO #TempModelInfo

FROM dbo.ModelInfo

WHILE(

(

SELECT COUNT(*)

FROM #TempModelInfo

) > 0)

BEGIN

DECLARE @ModelInfoID INT=

(

SELECT MIN(ModelInfoID)

FROM #TempModelInfo

)

DECLARE @ServerName VARCHAR(200)=

(

SELECT ServerName

FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

)

DECLARE @DatabaseID VARCHAR(200)=

(

SELECT DatabaseID

FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

)

DECLARE @CubeID VARCHAR(200)=

(

SELECT CubeID

FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

)

DECLARE @MeasureGroupID VARCHAR(200)=

(

SELECT MeasureID

FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

)

DECLARE @DatasourceID VARCHAR(200)=

(

SELECT DatasourceID

FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

)

DECLARE @TableName VARCHAR(200)=

(

SELECT TableName

FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

)

DECLARE @JobName VARCHAR(255)= ‘CreatePartition_’+@DatabaseID+’_’+@TableName

EXEC msdb.dbo.sp_add_job

@job_name = @JobName,

@enabled = 1,

@notify_level_eventlog = 0,

@notify_level_email = 0,

@notify_level_netsend = 0,

@notify_level_page = 0,

@delete_level = 0,

@category_name = N'[Uncategorized (Local)]’,

@owner_login_name = @UserName

IF OBJECT_ID(‘TEMPDB..#TempSystems’) IS NOT NULL

BEGIN

DROP TABLE #TempSystems

END

SELECT ‘System1’ AS SystemName

INTO #TempSystems

UNION

SELECT ‘System2’

UNION

SELECT ‘System3’

UNION

SELECT ‘System4’

WHILE(

(

SELECT COUNT(*)

FROM #TempSystems

) > 0)

BEGIN

DECLARE @int INT= 1

DECLARE @OnSuccess INT= 3

WHILE(@int < 54)

BEGIN

DECLARE @System VARCHAR(50)=

(

SELECT MIN(SystemName)

FROM #TempSystems

)

DECLARE @PartitionQuery VARCHAR(800)=

(

SELECT PartitionQuery

FROM [TableList] WITH (NOLOCK)

WHERE PartitionID = @int

AND [System] = @System

AND [TableName] = @TableName

)

DECLARE @PartitionName VARCHAR(200)=

(

SELECT PartitionName

FROM [TableList] WITH (NOLOCK)

WHERE PartitionID = @int

AND [System] = @System

AND [TableName] = @TableName

)

DECLARE @PartitionCmd VARCHAR(MAX)= ‘

‘+@DatabaseID+’

‘+@CubeID+’

‘+@MeasureGroupID+’

‘+@PartitionName+’

<ID>’+@PartitionName+'</ID>

<Name>’+@PartitionName+'</Name>

<DataSourceID>’+@DataSourceID+'</DataSourceID>

<QueryDefinition>’+@PartitionQuery+'</QueryDefinition>

</Source>

<StorageMode valuens=”ddl200_200″>InMemory</StorageMode>

<ProcessingMode>Regular</ProcessingMode>

<ddl300_300:DirectQueryUsage>InMemoryOnly</ddl300_300:DirectQueryUsage>

</Partition>

</ObjectDefinition>

</Alter>’

IF @int = 53

AND @System = ‘System4’

BEGIN

SET @OnSuccess = 1

END

EXEC msdb.dbo.sp_add_jobstep

@job_name = @JobName,

@step_name = @PartitionName,

@step_id = @int,

@cmdexec_success_code = 0,

@on_success_action = @OnSuccess,

@on_fail_action = 2,

@retry_attempts = 0,

@retry_interval = 0,

@os_run_priority = 0,

@subsystem = N’ANALYSISCOMMAND’,

@command = @PartitionCmd,

@server = @ServerName,

@database_name = N’master’,

@flags = 0,

@proxy_name = N’BAO_SSIS’

SET @int = @int + 1

END

–EXEC msdb.dbo.sp_start_job

— @job_name = @JobName

DELETE FROM #TempSystems

WHERE SystemName = @System

END

EXEC msdb.dbo.sp_update_job

@job_name = @JobName,

@start_step_id = 1

EXEC msdb.dbo.sp_add_jobserver

@job_name = @JobName,

@server_name = N’(put here the server name where your agent job at)‘

DELETE FROM #TempModelInfo

WHERE ModelInfoID = @ModelInfoID

END

Share this:

Like this:

Related

Leave a Reply Cancel reply