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…

Read more