I had a requirement where I wanted to find out and keep a constant record of exactly how my partitions were set up and created within SQL Server Analysis Services (SSAS). So below is a script that I found somewhere (If I find the source I will put it in hereJ) and how I inserted into a SQL Server Table so that I can use it for the creation and dropping of SSAS Partitions.

 

Example: We are going to be getting back all our SSAS Partition information from our AdventureWorksDW2012 cube.

 

You can get a copy of this here: http://msftdbprodsamples.codeplex.com/releases/view/55330

·         I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012

 

Installation of ASSP for Analysis Services

1.       If you have not got this installed already you would first need to install the ASSP for Analysis services.

2.       You can get the files from here:

https://asstoredprocedures.codeplex.com/releases/view/79180

3.       And then in order to install you can follow these easy to install instructions:

https://asstoredprocedures.codeplex.com/wikipage?title=Installation%20Instructions

 

Configuration andgetting down partitioned data

 

1.       The first thing that we did was to check to ensure that when running our MDX query it would return the required results from our cube.

2.       In SQL Server Management Studio (SSMS) we went into our Analysis Services, then went to our AdventureWorksDW2012 cube.

a.       Then right click, select New Query and then MDX

b.      clip_image001

c.       Once this opens run the following query:

call assp.DiscoverXmlMetadata(“Partition”)

d.      Now execute the query and you should see the following as shown below:

e.      clip_image002

f.        NOTE: There are a whole host more columns with a lot of valuable information.

                                                               i.      Below is often what I am most interested in, and the columns are on the right hand side

g.       clip_image003

3.       Then in order to store this information we are then going to use SSIS to Insert data from an MDX query into a SQL Server table.

a.       You can use this blog post below

b.      SSIS 2012 – INSERTING DATA INTO A SQL SERVER TABLE FROM AN MDX QUERY

4.       Then we created our table with the following syntax in order to get the data into our SQL Server Table as our destination.

CREATETABLE [Mart].[TD_SSAS_PartitionDetails](

       [Name] [varchar](300)NULL,

       [ID] [varchar](300)NULL,

       [CreatedTimeStamp] [varchar](300)NULL,

       [LastSchemaUpdate] [varchar](300)NULL,

       [Description] [varchar](300)NULL,

       [LastProcessed] [varchar](300)NULL,

       [State] [varchar](300)NULL,

       [Type] [varchar](300)NULL,

       [AggregationPrefix] [varchar](300)NULL,

       [StorageMode] [varchar](300)NULL,

       [CurrentStorageMode] [varchar](300)NULL,

       [StringStoresCompatibilityLevel] [varchar](300)NULL,

       [CurrentStringStoresCompatibilityLevel] [varchar](300)NULL,

       [ProcessingMode] [varchar](300)NULL,

       [ProcessingPriority] [varchar](300)NULL,

       [StorageLocation] [varchar](300)NULL,

       [RemoteDataSourceID] [varchar](300)NULL,

       [Slice] [varchar](300)NULL,

       [EstimatedRows] [varchar](300)NULL,

       [AggregationDesignID] [varchar](300)NULL,

       [EstimatedSize] [varchar](300)NULL,

       [Parent_MeasureGroupID] [varchar](300)NULL,

       [Parent_CubeID] [varchar](300)NULL,

       [Parent_DatabaseID] [varchar](300)NULL

)ON [PRIMARY]

 

GO

a.        Once completed our SSIS Data Flow task looked like the following:

b.       clip_image004

5.       Now once we run the above SSIS package we see our data in our SQL Server table from step 4 above.

6.       NOTE: If you want to keep a historical record you could then take this data and put it into a Fact style table.

a.        But for our purposes we just truncated the above table and inserted our data again daily.

 

Usage for SSAS Partition Details

·         The main reason for us getting our SSAS Partition details into a SQL Server table, is so that we could then get a list of our current partitions for our SSAS database and cube.

·         We could then use our Partition details to find out or Max and Min Partitions, and also how many partitions we have.

·         Another use is based on our Max Partition is to ensure that we can create partitions for our data that we are going to load going forward.

·         Likewise we could also find out our Min Partition to drop older partitions of data.

·         And finally because all our data is stored in a SQL Server Table, we could then use this data within SSIS using XMLA to dynamically create partitions.

o    You can use this blog post below as a reference:

SSIS – CREATING NEW PARTITIONS IN SQL SERVER ANALYSIS SERVICES (SSAS) WITH XMLA AND ANALYSIS SERVICES DDL