SSAS Tabular - Merging Paritions Dynamically (Network Steve Forum)

SSAS Tabular - Merging Paritions Dynamically

Hi,

I have a solution where, each day application creates a new parition. What I want is to dynamically merge all these partitions at the end of the week. Is it possible to do it dynamically via Script/SSIS and not using SSMS.

Thanks,

Vikas

February 22nd, 2015 2:42pm

Hi Vikas,

You can do it manually on SSMS.

Or you can use the .NET AMO library from anywhere that talks .NET (Ex. Script Task, Powershell, C# or a VB.NET program)

Or you can send an XMLA command using SSIS Analysis Services DDL Component or the ascmd application.

<MergePartitions xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Sources>
    <Source>
      <DatabaseID>Adventure Works DW Multidimensional 2012</DatabaseID>
      <CubeID>Adventure Works DW</CubeID>
      <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
      <PartitionID>Internet_Sales_2001</PartitionID>
    </Source>
    <Source>
      <DatabaseID>Adventure Works DW Multidimensional 2012</DatabaseID>
      <CubeID>Adventure Works DW</CubeID>
      <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
      <PartitionID>Internet_Sales_2002</PartitionID>
    </Source>
    <Source>
      <DatabaseID>Adventure Works DW Multidimensional 2012</DatabaseID>
      <CubeID>Adventure Works DW</CubeID>
      <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
      <PartitionID>Internet_Sales_2003</PartitionID>
    </Source>
  </Sources>
  <Target>
    <DatabaseID>Adventure Works DW Multidimensional 2012</DatabaseID>
    <CubeID>Adventure Works DW</CubeID>
    <MeasureGroupID>Fact Internet Sales 1</MeasureGroupID>
    <PartitionID>Internet_Sales_2004</PartitionID>
  </Target>
</MergePartitions>

Hope this helps!

Free Windows Admin Tool Kit Click here and download it now

February 23rd, 2015 4:28am

Hi Vikas,

Steps:-

SQL TABLE:- Below table should have all the data, which daily basis can be insert one row before process on cube starts.

Conf table

Date	Day	Week	Keep Partition	Partition Query	Week Start Date
1/1/2015	1	Week-1	0	Select * from Fact where date=CurrentDate	1/1/2015
2/1/2015	2	Week-1	0	Select * from Fact where date=CurrentDate	1/1/2015
3/1/2015	3	Week-1	0	Select * from Fact where date=CurrentDate	1/1/2015
4/1/2015	4	Week-1	0	Select * from Fact where date=CurrentDate	1/1/2015
5/1/2015	5	Week-1	0	Select * from Fact where date=CurrentDate	1/1/2015
6/1/2015	6	Week-1	0	Select * from Fact where date = CurrentDate	1/1/2015
7/1/2015	7	Week-1	1	Select * from Fact where date between WeekStartDate and CurrentDate	1/1/2015
8/1/2015	8	Week-2	0	Select * from Fact where date=CurrentDate	8/1/2015
9/1/2015	9	Week-2	0	Select * from Fact where date=CurrentDate	8/1/2015

SSIS Script Task:-

AMO objects:-

Creating Dynamic Partition, based on Partition Query in above Table.

---http://www.mssqltips.com/sqlservertip/2796/create-sql-server-analysis-services-partitions-using-amo/

Delete the partition , using conf table where KeepPartition=0 and datediff(day,Current date,WeekStartDate)=6

So when 7<sup>th</sup> day partition process, you drop all partition and create one which has 7 days of data. As we have different partition query.

Above steps will help you to do the implementation.

February 23rd, 2015 4:33am

This topic is archived. No further replies will be accepted.