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]
GO
ALTER TABLE dbo.[PartitionList]
ADD CONSTRAINT [DF_PartitionList_CreatedDate] DEFAULT(GETDATE()) FOR [CreatedDate]
GO
ALTER TABLE dbo.[PartitionList]
ADD CONSTRAINT [DF_PartitionList_Active] DEFAULT((0)) FOR [Active]
GO
–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]
AS
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)= ‘
<Alter AllowCreate=”true” ObjectExpansion=”ExpandFull” xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine”>
‘+@DatabaseID+’
‘+@CubeID+’
‘+@MeasureGroupID+’
‘+@PartitionName+’
<ObjectDefinition>
<Partition xmlns:xsd=”http://www.w3.org/2001/XMLSchema” xmlns:xsi=”http://www.w3.org/2001/XMLSchema-instance” xmlns:ddl2=”http://schemas.microsoft.com/analysisservices/2003/engine/2″ xmlns:ddl2_2=”http://schemas.microsoft.com/analysisservices/2003/engine/2/2″ xmlns:ddl100_100=”http://schemas.microsoft.com/analysisservices/2008/engine/100/100″ xmlns:ddl200=”http://schemas.microsoft.com/analysisservices/2010/engine/200″ xmlns:ddl200_200=”http://schemas.microsoft.com/analysisservices/2010/engine/200/200″ xmlns:ddl300=”http://schemas.microsoft.com/analysisservices/2011/engine/300″ xmlns:ddl300_300=”http://schemas.microsoft.com/analysisservices/2011/engine/300/300″ xmlns:ddl400=”http://schemas.microsoft.com/analysisservices/2012/engine/400″ xmlns:ddl400_400=”http://schemas.microsoft.com/analysisservices/2012/engine/400/400″>
<ID>’+@PartitionName+'</ID>
<Name>’+@PartitionName+'</Name>
<Source xsi:type=”QueryBinding”>
<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
Annie Xu 