The Blog is prepared to automate and accelerate SSAS tabular cube Partition creation and maintenance activities required for the most of the MSBI analytics projects. There are definitely better way of doing it and probably easy to do but this is how I tackled it.
Also, I would like to focus on simplifying the automation core concept with basic cube details stored in SQL master partition control table and generating required partition details in Partition Control detail table, can be used effectively for creating cube partitions using PowerShell script.
Step 01: Define Master table with Partition Cubes, Frequency and Partition Name targeted to create
CREATE TABLE [Ctrl].[Cube_Partition_Control]
(
[ID] [int] NOT NULL,
[CubeName] [varchar](100) NULL,
[PartitionTable] [varchar](500) NULL,
[PartitionMethod] [varchar](2) NOT NULL,
[PartitionNamePrefix] [varchar](200) NULL,
[PartitionQuery] [varchar](500) NULL,
[Connection] [varchar](100) NULL,
[MinPartitionPeriodValue] [int] NULL,
[LastPartitionPeriod] [int] NULL,
[IsActive] [varchar](1) NULL,
[LastProcessedDate] [datetime] NULL
)
Here is query to populate the Control table:
INSERT INTO [Sales_Channel].[Cube_Partition_Control] Values (1,’Cube_Name’,’Fact_Table’,’W’,’Fact_ Sales_View_’,’SELECT * FROM dbo.[ Fact_Sales_View] where [Transaction_Week] = ? )’,’DS_Connection’,201644,0,’Y’,’2017-01-01′)
Gives entries based on Partition requirement as shown below:
| ID |
CubeName |
PartitionTable |
PartitionMethod |
PartitionNamePrefix |
PartitionQuery |
Connection |
MinPartitionPeriodValue |
| 1 |
Cube_Name |
Fact_Table |
W |
Fact_Sales_View_ |
SELECT * FROM dbo.[ Fact_Sales_View] where [Transaction_Week] = ? |
DS_Connection |
201611 |
…
We could write T-Sql procedure or SSIS package to fill the detailed table with below entries:
CREATE TABLE [Cube_Partition_ControlDetail]
(
[CubeName] [varchar](100) NULL,
[TableName] [varchar](200) NULL,
[PartitionName] [nvarchar](200) NULL,
[PartitionCriteria] [varchar](20) NULL,
[PartitionYear] [varchar](20) NULL,
[Week_Start_Date] [varchar](20) NULL,
[Week_End_Date] [varchar](20) NULL,
[Partition_Created_Flag] [varchar](5) NULL,
[Partition_Deleted_Flag] [varchar](5) NULL,
[DateInfo] [datetime] NULL,
[PartitionNamePrefix] [nvarchar](200) NULL,
[DataSourceConnection] [varchar](100) NULL,
[QueryPartitionFlag] [varchar](5) NULL,
[PartitionQuery] [nvarchar](500) NULL
)
Step 02: Populate Partition Detail table
The Partition Control Details are Populated from base table Time (Calendar Table) as shown below, also gives partition query, partition name which needs to be dynamic. It helps to avoid manual creation as it will consume more time and easy maintain using PowerShell and SSIS with C# custom scripts.
Partition method shown below for Weekly partition criteria from core calendar table, can be extended for Monthly, Quarterly, Yearly…based on project cube requirements. I have included the comment column below to make it more clear and easy to understand.
We need to create a T-Sql procedure with Parametrized with Cube Name and Partition Table from Master Partition Control as shown below with details highlighted.
if @PartitionMethod = ‘W’
begin
Print ‘Inside Partition Category Week ‘ + @CubeName + ‘ ‘ +@PartitionTable +’ ‘
+cast(@MaxPartitionCriteria as varchar)
–Parameterized by #Cube Name and Fact/Partition Table, Cube can have Multiple Fact tables
Insert into [Cube_Partition_ControlDetail]
(CubeName, TableName, PartitionName, [PartitionCriteria], PartitionYear, Partition_Created_Flag, Partition_Deleted_Flag, DateInfo, PartitionNamePrefix,
DataSourceConnection, [PartitionQuery],[QueryPartitionFlag], ID)
Select CubeName, TableName, PartitionNeedToCreate, Calendar_Week, substring(Calendar_Week, 1,4), ‘N’, ‘N’, getdate(), [PartitionNamePrefix],
[Connection], [PartitionQuery], [QueryPartitionFlag], ID
from
(
select ctrl.CubeName, ctrl.TableName, ctrl.[PartitionNamePrefix], [Connection],
wc.Calendar_Week, ctrl.[PartitionNamePrefix]+’_’+wc.Calendar_Week As ‘PartitionNeedToCreate’,
Replace(PartitionQuery, ‘?’, [Calendar_WeekQ]) as PartitionQuery, [PartitionMethod] as [QueryPartitionFlag] , ID
–Replace above? question mark with dynamic week to build the baseline partition query
from
(
Select Distinct [Calendar_Week], ””+[Calendar_Week]+”” as [Calendar_WeekQ] from [Calendar]
–Get List of Distinct Calendar Weeks to create
Where [Calendar_Week] >=
(Select distinct [MinPartitionPeriodValue] from [Cube_Partition_Control] ctrl Where CubeName = @CubeName and [PartitionTable] = @PartitionTable)
and [Calendar_Week] >
(Select distinct isnull([LastPartitionPeriod],0) from [Cube_Partition_Control] ctrl Where CubeName = @CubeName and [PartitionTable] = @PartitionTable)
—Get Last Partition had been created before
and
[Calendar_Month] <= Year(DATEADD(mm,3,getdate())) * 100 + Month(DATEADD(mm,3,getdate()))
–to make control partition detail #3 months ahead
) wc
,
–Get Cube details, you can find better options to get the details, can be Parametrized
(Select distinct CubeName, [PartitionTable] as TableName,[PartitionNamePrefix] ,[Connection],[MinPartitionPeriodValue],[LastPartitionPeriod], PartitionQuery,
[PartitionMethod], ID
from [Cube_Partition_Control] ctrl Where CubeName = @CubeName and [PartitionTable] = @PartitionTable
) ctrl
) Part
End
Step 03: PowerShell script to create the Cube Partitions
We have Partition control details are being generated with above simplified approach and ready to create the Partitions in Cube using dynamic XMLA scripts using PowerShell or SSIS with custom scripts.
PowerShell is constructed to focus on cube and constructed for multiple fact tables along with required partitions to create, it can fine tuned based on further business requirements…
The basic idea to generate the below XMLA script and execute by connecting the Analysis Server and automate with Predefined schedules.
XMLA to produce by Weekly Partition as targeted and Execute from PowerShell Scripts – Example:
{
“createOrReplace”: {
“object”: {
“database”: “Cube Name”,
“table”: “Partition Fact Table Name”,
“partition”: “Partition_Table_Name_201712”
},
“partition”: {
“name”: ” Partition_Table_Name_201712″,
“dataView”: “full”,
“source”: {
“query”: [
“select * from Fact_Table_Name “,
” where [Week] = ‘201712’”
],
“dataSource”: “DS_Connection_Name”
}
}
}
}
PowerShell script example:
#Make sure to set below variables value used for connecting sqlDB & extract the Partition Details from Control tables as populated from previous session.
$Svr = “SQL Server Name”
$Id = “DB Name”
$Ins = “User Name”
$pwd = “Password**”
$CubeNameP = ”’Cube_Name”’
$querypartition =
“SELECT CubeName, TableName, PartitionName, [PartitionCriteria], PartitionYear, Partition_Created_Flag, Partition_Deleted_Flag, PartitionNamePrefix, DataSourceConnection, [PartitionQuery],[QueryPartitionFlag] FROM [Ctrl].[Cube_Partition_ControlDetail] WHERE Partition_Created_Flag = ‘N’ AND CubeName = $CubeNameP “
#Executing above sql to extract the cube Partition Details
$DBTblDetailspartition = invoke-sqlcmd -ServerInstance $Svr -Database $Ins -Username $Id -Password $pwd -Query $querypartition -ErrorAction Stop
foreach ($items in $DBTblDetailspartition)
{
$CubeName = $items.CubeName
$TableName = $items.TableName
$PartitionName = $items.PartitionName
$PartitionCriteria = $items.PartitionCriteria
$DataSourceConnection = $items.DataSourceConnection
$PartitionQuery = $items.PartitionQuery
##the Most important part to construct the required XMLA
$TMSLpartition =
@”
{
“createOrReplace”: {
“object”:
“@;
$TMSLpartition = $TMSLpartition +
@”
{
“database”: “$CubeName”,
“table”: “$TableName”,
“partition”: “$PartitionName”
},
“partition”: {
“name”: “$PartitionName”,
“dataView”: “full”,
“source”: {
“query”: [
“$PartitionQuery”
],
“dataSource”: “$DataSourceConnection”
}
}
}
}
“@;
$Svr.disconnect
#Initialize
[System.Reflection.Assembly]::LoadWithPartialName(“Microsoft.AnalysisServices”) >$NULL
$server = New-Object Microsoft.AnalysisServices.Server
#Set the Analysis Server to connect
$server.connect(“Analysis Server”)
#Execute the XMLA script has been generated above
Invoke-ASCmd -Server $Server -Query $TMSLpartition
$server.Disconnect()
}
You can also refer below URL to achieve the same using SSIS instead of PowerShell scripts.
http://www.ssas-info.com/analysis-services-scripts/1622-script-to-automate-ssas-partition-management-sql-ssis
https://dataqueen.unlimitedviz.com/2014/05/how-to-automate-ssas-cube-partitioning-in-ssis/
Happy Learning!!
I hope to make it more useful everyone, simplify the approach as much as I could. 😊
Cheers,
12.958692
77.721663
Guna's Blog 