Learn for Life!

SSAS Cubes – Dynamic generation of partition


There is no easy way to generate partition for SSAS Cubes by default. So you have to do a SSIS-Package  (here is a way you can do it dynamic-cube-partitioning-in-ssas-2008) or write a SQL Script which generates XMLA executables.

As shown in the Statement, you can dynamically generate month partitions. The trick is, to create a linked server, that you can execute XMLA statements with relational T-SQL, so i created a Linked Server first.

Thanks for any advice or improvements in the comment section:
    EXEC master.dbo.sp_addlinkedserver
	      @server = ‘SSAS’
      , @srvproduct = ”
      , @provider = ‘MSOLAP’
     , @datasrc = ‘[DB-NAME]’;

    EXEC master.dbo.sp_serveroption @server=’SSAS’, @optname=’rpc out’, @optvalue=’true’;

  CREATE SCHEMA ssas;


    CREATE PROCEDURE ssas.usp_Execute (
	      @XMLA XML
    )
	AS
	DECLARE… Read more

There is no easy way to generate partition for SSAS Cubes by default. So you have to do a SSIS-Package  (here is a way you can do it dynamic-cube-partitioning-in-ssas-2008) or write a SQL Script which generates XMLA executables.

As shown in the Statement, you can dynamically generate month partitions. The trick is, to create a linked server, that you can execute XMLA statements with relational T-SQL, so i created a Linked Server first.

Thanks for any advice or improvements in the comment section:

    EXEC master.dbo.sp_addlinkedserver
	      @server = 'SSAS'
      , @srvproduct = ''
      , @provider = 'MSOLAP'
     , @datasrc = '[DB-NAME]';

    EXEC master.dbo.sp_serveroption @server='SSAS', @optname='rpc out', @optvalue='true';

  CREATE SCHEMA ssas;


    CREATE PROCEDURE ssas.usp_Execute (
	      @XMLA XML
    )
	AS
	DECLARE @Command VARCHAR(MAX) = CONVERT(VARCHAR(MAX), @XMLA);
	EXEC (@Command) AT SSAS;
	


CREATE PROCEDURE ssas.usp_ProcessDatabase (
 @CubeName NVARCHAR(50) 
, @CubeID NVARCHAR(50) 
, @MeasureGroupID NVARCHAR(50) 
, @DataSourceID NVARCHAR(50) 
, @StartDT DATETIME --'YYYY-MM-DD' 
, @EndDT DATETIME 
, @PartitionPreFix NVARCHAR(20) 
, @SqlQuery nvarchar(max) ='' /*'SELECT FK_AS_Produkt_ID, FK_AS_Produkt_productId_BK, FK_AS_Vertrag_ID, FK_AS_Vertrag_bookingId_BK, FK_AS_Inserent_ID, FK_AS_Inserent_accountId_BK, FK_AS_Objekt_ID, FK_AS_Objekt_vehicleId_BK, 
 FK_CD_Zeit_Zeit_ID, FK_CD_Zeit_Zeit_DIM_ZEIT_KEY_BK, [Datum Start], [Datum Ende], [Datum Start Booking], [Status ID], [Anzahl Plätze], [Anzahl verwendete Plätze], Preis, MwSt, 
 privateInserters_distict_cnt_PD, privateInsertersReturners1_distict_cnt, privateInsertersReturners12_distict_cnt, privateInsertersReturners48_distict_cnt, soldMemberSlotsFixdate_sum_PD, 
 privateInsertersMoto_sum_PD, vehiclesPrivatInsertersAuto_sum_PD, revenuePrivateInsertersListingsAuto_sum_PD, revenuePrivateInsertersAdditionalsAuto_sum_PD, vehiclesPrivatInsertersMoto_sum_PD, 
 revenuePrivateInsertersAdditionalsMoto_sum_PD, SA_SourceSystem_ID, SA_Load_ID, CA_Load_ID, DW_Load_ID, SCD_TransactionDate, DS_Load_ID
FROM DM.AS_BookingStats
WHERE FK_CD_Zeit_Zeit_ID BETWEEN <#=dateFrom#> AND <#=dateTo#>'*/
)
AS

DECLARE @myXMLA XML, @myXMLA_before XML, @myXMLA_delete XML
declare @value nvarchar(30),@dateFrom varchar(10),@dateTo varchar(10),@year nvarchar(4)
declare @replSqlQuery nvarchar(max)

set @dateFrom=FORMAT(DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDT), 0),'yyyyMMdd') --First day of month
select @value=@PartitionPreFix+FORMAT (@StartDT, 'yyyyMM')+'_before' 
SELECT @replSqlQuery= REPLACE(@SqlQuery,'WHERE FK_CD_Zeit_Zeit_ID BETWEEN <#=dateFrom#> AND <#=dateTo#>', 'WHERE FK_CD_Zeit_Zeit_ID &lt; ' + @dateFrom) 



 --delete standard
 set @myXMLA_delete = N'<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <Object>
 <DatabaseID>'+@CubeName+'</DatabaseID>
 <CubeID>'+@CubeID+'</CubeID>
 <MeasureGroupID>'+@MeasureGroupID+'</MeasureGroupID>
 <PartitionID>'+@MeasureGroupID+'</PartitionID>
 </Object>
 </Delete>'

 PRINT CONVERT(nvarchar(max),@myXMLA_delete)
 EXEC ssas.usp_Execute @myXMLA_delete;
 PRINT ' '

 --delete first partition before
 set @myXMLA_delete = N'<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <Object>
 <DatabaseID>'+@CubeName+'</DatabaseID>
 <CubeID>'+@CubeID+'</CubeID>
 <MeasureGroupID>'+@MeasureGroupID+'</MeasureGroupID>
 <PartitionID>'+@value+'</PartitionID>
 </Object>
 </Delete>'

 PRINT CONVERT(nvarchar(max),@myXMLA_delete)
 EXEC ssas.usp_Execute @myXMLA_delete;
 PRINT ' '

 Set @myXMLA =N'<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <ParentObject>
 <DatabaseID>'+@CubeName+'</DatabaseID>
 <CubeID>'+@CubeID+'</CubeID>
 <MeasureGroupID>'+@MeasureGroupID+'</MeasureGroupID>
 </ParentObject>
 <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>'+@value+'</ID>
 <Name>'+@value+'</Name>
 <Source xsi:type="QueryBinding">
 <DataSourceID>'+@DataSourceID+'</DataSourceID>
 <QueryDefinition>
 '+ @replSqlQuery +'
 </QueryDefinition>
 </Source>
 <StorageMode>Molap</StorageMode>
 <ProcessingMode>Regular</ProcessingMode>
 <ProactiveCaching>
 <SilenceInterval>-PT1S</SilenceInterval>
 <Latency>-PT1S</Latency>
 <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
 <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
 <Source xsi:type="ProactiveCachingInheritedBinding" />
 </ProactiveCaching>
 </Partition>
 </ObjectDefinition>
 </Create>'

 
 PRINT CONVERT(nvarchar(max),@myXMLA)
 EXEC ssas.usp_Execute @myXMLA;
 PRINT ' '


WHILE @StartDT < @EndDT
BEGIN

 
 set @dateFrom=FORMAT(DATEADD(MONTH, DATEDIFF(MONTH, 0, @StartDT), 0),'yyyyMMdd') --First day of month
 set @dateTo =FORMAT(DATEADD(MONTH, DATEDIFF(MONTH, -1, @StartDT), -1),'yyyyMMdd') --Last Day of month 
 select @value=@PartitionPreFix+FORMAT (@StartDT, 'yyyyMM') 

 SELECT @replSqlQuery= REPLACE(REPLACE(@SqlQuery,'<#=dateTo#>', @dateTo), '<#=dateFrom#>', @dateFrom) 

 set @myXMLA_delete = N'<Delete xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <Object>
 <DatabaseID>'+@CubeName+'</DatabaseID>
 <CubeID>'+@CubeID+'</CubeID>
 <MeasureGroupID>'+@MeasureGroupID+'</MeasureGroupID>
 <PartitionID>'+@value+'</PartitionID>
 </Object>
 </Delete>'

 PRINT CONVERT(nvarchar(max),@myXMLA_delete)
 EXEC ssas.usp_Execute @myXMLA_delete;
 PRINT ' '

 Set @myXMLA =N'<Create xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
 <ParentObject>
 <DatabaseID>'+@CubeName+'</DatabaseID>
 <CubeID>'+@CubeID+'</CubeID>
 <MeasureGroupID>'+@MeasureGroupID+'</MeasureGroupID>
 </ParentObject>
 <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>'+@value+'</ID>
 <Name>'+@value+'</Name>
 <Source xsi:type="QueryBinding">
 <DataSourceID>'+@DataSourceID+'</DataSourceID>
 <QueryDefinition>
 '+ @replSqlQuery +'
 </QueryDefinition>
 </Source>
 <StorageMode>Molap</StorageMode>
 <ProcessingMode>Regular</ProcessingMode>
 <ProactiveCaching>
 <SilenceInterval>-PT1S</SilenceInterval>
 <Latency>-PT1S</Latency>
 <SilenceOverrideInterval>-PT1S</SilenceOverrideInterval>
 <ForceRebuildInterval>-PT1S</ForceRebuildInterval>
 <Source xsi:type="ProactiveCachingInheritedBinding" />
 </ProactiveCaching>
 </Partition>
 </ObjectDefinition>
 </Create>'

 
 PRINT CONVERT(nvarchar(max),@myXMLA)
 EXEC ssas.usp_Execute @myXMLA;
 PRINT ' '
 SET @StartDT = DATEADD(MONTH,1,@StartDT)
END

GO

 

 

 

1,631 total views, 1 views today


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.