What I wanted to do is to create a process in SSIS that did not use code to create new SSAS Partitions using XMLA.

 

NOTE: This is quite a long blog post, but it has all the details from start to finish to get this completed.

 

Example:

·         We will be using the AdventureWorks2012DW data warehouse and Cube.

·         We will be creating new Partitions starting from 01 Jan 2010 on our Internet Sales Measure Group.

o    When creating our Partitions we will be using the query binding because our underlying SQL Server Table will be partitioned by DateKey.

§  NOTE: Our SQL Server Table would also have a Clustered Index created on the DateKey.

o    For our query in Analysis Services Partition we will be using the first day and the last day of the month in order to get our required data into our Partition.

o    Along with this when we create our Partition we will also use the Slice Function to further help Analysis Services query the data as quickly as possible.

o    So our Start Datekey will be 20100101

o    And our End DateKey will be 20100131

·         Our Partitioning naming scheme will be the Measure group name and year and month

o    So for our example it will be Internet Sales 2010-01

 

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

 

Creating our Partitioned Table and starting point.

Below are the steps where we will create our table and our starting point for our example above.

 

1.       Below is the script that we will use on our AdventureWorksDW2012 SQL Server database which will be explained below:

— =======================================================================

— 1. This will create our Table

— =======================================================================

CREATETABLE[dbo].[Mart_TD_Max_SSAS_PartitionDetails](

       [YearOfLastPartition][int]NOTNULL,

       [MonthNameOfLastPartition][varchar](30)NOTNULL,

       [YearMonthOfLastPartition][varchar](30)NOTNULL,

       [DateTimeInsertedIntoTable][datetime]NOTNULL,

       [PartitionName][varchar](300)NOTNULL,

       [SSAS_DatabaseName][varchar](300)NOTNULL

)ON[PRIMARY]

 

GO

 

— =======================================================================

— 2. Then we insert our starting point into our Partitioned Table

— =======================================================================

Insertinto[dbo].[Mart_TD_Max_SSAS_PartitionDetails]

Select 2009 as[YearOfLastPartition]

      ,‘December’as[MonthNameOfLastPartition]

      ,‘2009-12’as[YearMonthOfLastPartition]

      ,getdate()as[DateTimeInsertedIntoTable]

      ,‘Internet Sales – 2009-12’[PartitionName]

      ,‘Adventure Works’as[SSAS_DatabaseName]

 

— =======================================================================

— 3. Then we do a select to ensure that the details inserted are correct

— =======================================================================

Select*

from[dbo].[Mart_TD_Max_SSAS_PartitionDetails]

a.        As you can see above in Section 1 is where we are creating our table.

b.       In Section 2 is where we are inserting the data which is easier to explain:

                                                               i.     

                                                              ii.      NOTE: We will be using the above columns to generate our requirements for when creating our next partition.

1.       Which you can view here in section later in the document.

                                                            iii.      As you can see above because we want our next partition to be for Jan 2010, we need to start our details one month earlier. EG; December 2009

 

Getting details from a stored procedure to be used in SSIS to create our next partition

In the steps below we are going to show the stored procedure and explain how we get our details for our next partition.

We will then use these details and in the next step put this all into SSIS to create our partition.

 

1.       Below is the entire script and each section is explained in the comments section

a.        NOTE: A quick overview is that based on the information in the Mart_TD_Max_SSAS_PartitionDetails, we are getting the next month’s values for the following:

                                                               i.      Our Start and End DateKeys, Year Month for the Partition Name, as well as the year and Month name which are used in the proc.

b.      

USE[AdventureWorksDW2012]

GO

 

/****** Object:  StoredProcedure [dbo].[prc_GetNextPartitionValues_ForSSAS_SQL]    Script Date: 2014-04-09 10:46:59 AM ******/

SETANSI_NULLSON

GO

 

SETQUOTED_IDENTIFIERON

GO

 

— ===============================================================================================================

— Disclaimer

— The sample scripts are provided AS IS without warranty of any kind.

— I further disclaims all implied warranties including, without limitation, any implied warranties of

— merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance

— of the sample scripts and documentation remains with you.

— In no event shall I, its authors, or anyone else involved in the creation, production, or delivery of

— the scripts be liable for any damages whatsoever (including, without limitation, damages for loss of business

— profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use

— of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the

— possibility of such damages.

— ===============================================================================================================

 

— ===============================================================================================================

— Author:       Gilbertq

— Create date: 09 Apr 2014

— Description:  This is where we get the data from the Current or Last Partition and then get into variables

—              the next partition values to create the SSAS Partitions and SQL Partitions if required in SSIS

— ===============================================================================================================

CREATEPROCEDURE[dbo].[prc_GetNextPartitionValues_ForSSAS_SQL]

 

AS

BEGIN

 

   SETNOCOUNTON;

 

— ==================================================================================================================

— 1. We are getting the Year Value of the last partition that was created from Mart_TD_Max_SSAS_PartitionDetails

— ==================================================================================================================

Declare@YearOfLastPartitionasInt

Set@YearOfLastPartition=

   (

          SelectYearOfLastPartition

          from[dbo].[Mart_TD_Max_SSAS_PartitionDetails]with (nolock)

   )

 

— ====================================================================================================================

— 2. We are getting the Month Name Value of the last partition that was created from Mart_TD_Max_SSAS_PartitionDetails

— ====================================================================================================================

Declare@MonthNameOfLastPartitionasvarchar(30)

Set@MonthNameOfLastPartition=

   (

          SelectMonthNameOfLastPartition

          from[dbo].[Mart_TD_Max_SSAS_PartitionDetails]with (nolock)

   )

 

— ====================================================================================================================

— 3. Here we are getting the Actual Date from our previous values so that it can be used in the next steps.

—    Below we are always getting the last day of the month for our current values.

— ====================================================================================================================

Declare@MaxDateForCurrentPartitionasDate

Set@MaxDateForCurrentPartition=

   (

          Selecttop 1 [FullDateAlternateKey]

          fromDimDatewith (nolock)

          whereCalendarYear=@YearOfLastPartition

          and[EnglishMonthName]=@MonthNameOfLastPartition

          orderby[DayNumberOfMonth]desc

   )

 

— ====================================================================================================================

— 4. Now here we are getting the next date value, which will be the first day of the Next Month

— ====================================================================================================================

Declare@MaxDateForNextPartitionasint

Set@MaxDateForNextPartition=

   (

          SelectDateKey

          fromDimDatewith (nolock)

          where[FullDateAlternateKey]=DATEADD(Day,1,@MaxDateForCurrentPartition)

   )

 

— ====================================================================================================================

— 5. Next we are getting the Year value for our Next Month’s value

— ====================================================================================================================

Declare@YearForNextPartitionasint

Set@YearForNextPartition=

   (

          SelectCalendarYear

          fromDimDatewith (nolock)

          whereDateKey=@MaxDateForNextPartition

   )

 

— ====================================================================================================================

— 6. Next we are getting the Month Name value for our Next Month’s value

— ====================================================================================================================

Declare@MonthNameForNextPartitionasvarchar(30)

Set@MonthNameForNextPartition=

   (

          Select[EnglishMonthName]

          fromDimDatewith (nolock)

          whereDateKey=@MaxDateForNextPartition

   )

 

— ====================================================================================================================

— 7. Next we are getting the first day of Next Month’s value

— ====================================================================================================================

Declare@StartDateKeyForNextPartitionasInt

Set@StartDateKeyForNextPartition=

   (     

          Selecttop 1 DateKey

          fromDimDatewith (nolock)

          whereCalendarYear=@YearForNextPartition

          and[EnglishMonthName]=@MonthNameForNextPartition

          orderby[DayNumberOfMonth]

   )

 

— ====================================================================================================================

— 8. Next we are getting the last day of Next Month’s value

— ====================================================================================================================

Declare@EndDateKeyForNextPartitionasINt

Set@EndDateKeyForNextPartition=

   (

          Selecttop 1 DateKey

          fromDimDatewith (nolock)

          whereCalendarYear=@YearForNextPartition

          and[EnglishMonthName]=@MonthNameForNextPartition

          orderby[DayNumberOfMonth]desc

   )

 

— ====================================================================================================================

— 9. Next we are getting the Year Month Value for Next Month’s Partition

— ====================================================================================================================

Declare@YearMonthForNextPartitionasvarchar(30)

Set@YearMonthForNextPartition=

   (

          Selecttop 1 convert(Varchar(4),CalendarYear)+‘-‘+RIGHT(REPLICATE(‘0’,5)+CONVERT(VARCHAR(2),MonthnumberofYear),2)

          fromDimDatewith (nolock)

          whereCalendarYear=@YearForNextPartition

          and[EnglishMonthName]=@MonthNameForNextPartition

   )

 

— ====================================================================================================================

— 12. And finally we are getting our 3 values for Next month which we can then use in our SSIS package for multiple

—     loads

— ====================================================================================================================

Select

           @StartDateKeyForNextPartitionasStartDateKeyForNextPartition

          ,@EndDateKeyForNextPartitionasEndDateKeyForNextPartition

          ,@YearMonthForNextPartitionasYearMonthForNextPartition

          ,@YearForNextPartitionasYearForNextPartition

          ,@MonthNameForNextPartitionasMonthNameForNextPartition

 

 

 

         

   SETNOCOUNTOFF;

END

 

GO

2.       Run this against your SQL Server AdventureWorksDW2012 database.

 

SSIS to create new Partitions for SSAS

In the steps below we will use SSIS to create our new Partitions for our SSAS Cube.

 

1.       The first thing to do is to create the following variables which will be used for our entire SSIS Package:

a.       

b.       NOTE: The reason that I set them all to the Data type of string is so that when they are used later in the expressions we do not have to cast the values.

                                                               i.      This is because all our expressions will either be passed to SQL server or Analysis Services which only accepts it in a string format.

                                                              ii.      We also put in defaults so that when we Evaluate the Expressions later we can see values.

2.       Next we are going to get all our variables for our stored procedure above and put them into the above variables.

a.        Drag in your Execute SQL task and as with our example we gave it the following name:

Get Variables for Next Months SSAS Partition for Internet Sales

b.       Next right click and select Properties.

c.        You will have to create your OLE DB connection to your SQL server where you have your AdventureWorksDW2012 database.

                                                               i.     

d.       Then configure the General window with the following as shown below:

                                                               i.     

e.       Then click on the Result Set on the left hand side and configure it with the following to map our variables from our Stored Procedure to our Variables in SSIS

                                                               i.     

                                                              ii.      NOTE: All of the above will be used in different sections throughout the SSIS Package.

f.        Click Ok.

g.        Then right click and select Execute Task, to test that it runs correctly.

                                                               i.      You should see the following below:

                                                              ii.     

3.       Now in the following steps I am going to explain how initially to manually create our Partition so that we can then use this for our SSIS in the next step.

a.        Log into your SSAS Server and go into the Measure Group and then Partition where you want to automate your partition creating.

b.       NOTE: As with our example we are going to create a new Partition on the Internet Sales Measure Group for December 2009

                                                               i.     

c.        Right click on Partitions and select New Partition, this will open the Partition Wizard.

                                                               i.      We selected the Internet Sales Facts table as shown below and clicked Next

1.      

                                                              ii.      On the Restrict Rows we selected Specify a query to restrict rows

1.       NOTE: We are doing this due to wanting to specify and our start and end DateKey

2.      

3.       We then scrolled to the end of the Query and put in the following as per our example

a.       

                                                            iii.      We accepted the defaults until we got to the final window. And put in the details below.

1.      

                                                            iv.      We then clicked Finish.

d.       You will now see the partition we created

                                                               i.     

4.       Next is where we are going to script out our Partition so that we can then use this within SSIS

a.        Right click on the partition we created above and select Script Partition as, Create To, New Query Editor Window.

                                                               i.     

                                                              ii.      You should see the following below which is not the complete script

1.      

b.       Next you will first have to do a find and replace on all the double quotation so that it can be escaped in SSIS.

                                                               i.      Press Control+H and complete the following below:

1.      

                                                              ii.      Then click Replace All.

c.        If you have any singles quotes in your Query Definition you will also have to change them to have 2 single quotes so that when this is parsed by TSQL it will work.

                                                               i.      So as with our above example we also had to change the following from:

1.  + ‘Line ‘  +

2.       To: + ”Line ”  +

d.       Now open a TSQL Query and first put in the following:

Select”asXMLAScript_CreateSSASPartition

                                                               i.      Then take your XMLA script from above and insert it in between the single quotes above.

                                                              ii.      Now run the TSQL Select to ensure that it will run successfully

1.      

                                                            iii.      NOTE: The reason that we parse this in TSQL is so that in SSIS we can then put in our variables as required.

e.       Now take the entire TSQL statement from step 4c above and copy it.

f.         Now go back into SSIS and open your variables.

                                                               i.      Where it has the variable name of XMLAQuery_CreateSSASPartition click on the Ellipses under Expression

                                                              ii.      Then in the Expression Window put in a double quote at the start and insert your TSQL query from step 4d above:

1.      

                                                            iii.      Then scroll right to the end and put in another double quote at the end

1.      

                                                            iv.      Then click on Evaluate Expression to ensure that so far it is all correct.

g.        Now the final part in creating our script is to put in the required variables.

                                                               i.      The first section where we are going to add the variables is for the ID and name of our partition.

                                                              ii.      As with our example we put in the following:

1.       What it looked like before the change:

<ID>Fact Internet Sales 2009-12</ID>

<Name>Fact Internet Sales 2009-12</Name>

2.       And what it was with the variables inserted:

<ID>Fact Internet Sales “+ @[User::YearMonthForNextPartition]  +”</ID>

<Name>Fact Internet Sales “+ @[User::YearMonthForNextPartition]  +”</Name>

                                                            iii.      In the next section we are going to put in our Start and end DateKey’s for our Query Definition

1.       What it looked like before the change:

WHERE orderdatekey between 20091201 and 20091231</QueryDefinition>

2.       And what it looked like after the change:

WHERE orderdatekey between “+ @[User::StartDateKeyForNextPartition]  +” and “+ @[User::EndDateKeyForNextPartition]  +”</QueryDefinition>

                                                            iv.      Now what we are going to add is our Slice Property to our Partition.

1.       So after the section below is where you will put in the Slice Property

<ProcessingMode>Regular</ProcessingMode>

2.       As you can see we have put in the Slice Property with the variables already inserted:

<ProcessingMode>Regular</ProcessingMode>

<Slice>{[Date].[Date].&amp;[“+ @[User::StartDateKeyForNextPartition]  +”],[Date].[Date].&amp;[“+ @[User::EndDateKeyForNextPartition]  +”]}</Slice>

                                                              v.      Now click on Evaluate Expression to ensure that everything is correct.

1.       If you scroll down you should see the following

a.       

b.       NOTE: This was due to us creating the values for our variables in Step 1 above.

5.       Next we are going to be taking our script which has been populated with the details above and put this into a variable which can then be passed to our Analysis Services Execute DDL Task.

a.        Drag in an Execute SQL Task.

b.       We then gave it the following name:

                                                               i.      Get XMLA for SSAS to create new Partition

c.        Next right click and go into the Properties and configure it with the following as shown below:

                                                               i.     

                                                              ii.      NOTE: As you can see above we are using the variable which we populated with our XMLA script in step 4 above.

                                                            iii.      We are also setting the Result Set to a Single row. This is so that we can populate our XMLA Script which will then be passed to Analysis Services.

1.       Just to ensure the understanding is that this Execute SQL Task will populate our XMLAQuery_CreateSSASPartition variable with the details.

2.       After which we want the output to be inserted into our XMLA Script which will then be used in Analysis Services.

d.       Now click on Result Set and configure it with the following below:

                                                               i.       Click On Add and select the following:

                                                              ii.     

e.       Then Click Ok.

6.       Next we will need to configure our Analysis Services Execute DDL Task with the following steps below.

a.        Drag in the Analysis Services Execute DDL Task

b.       Right click and select Properties to go into the Properties.

c.        Under Name we gave it the following name as per our Example:

                                                               i.      Run XMLA Script to create new SSAS Partition

d.       Then click on DDL.

                                                               i.      In the right hand side where it says Connection you are going to have to create a connection to your SSAS, and Database

                                                              ii.      Click on New Connection

                                                            iii.      As with our example we created our connection

1.      

                                                            iv.      Then Click Ok Twice to get back.

                                                              v.      Then next to Source click on the Drop down and select the Variable that we populated in the previous steps.

                                                            vi.      As with our Example we are going to select the variable that we populated in step 5 above.

                                                           vii.      So once complete it will look like the following:

1.      

                                                         viii.      Then Click Ok.

7.       Next we need to create our Update TSQL Statement for our Mart_TD_Max_SSAS_PartitionDetails table so that we can dynamically update this after the partition above has been created.

a.        Go into your variables and click on the ellipses button next to Query_Update_Mart_TD_Max_SSASPartitionDetails

b.       Then we put in the following for our Update Statement in the Expression Window:

“Update [dbo].[Mart_TD_Max_SSAS_PartitionDetails]

 Set         [YearOfLastPartition] = “+ @[User::YearForNextPartition]  +”

                                ,[MonthNameOfLastPartition] = ‘”+ @[User::MonthNameForNextPartition]   +”‘

                                ,[YearMonthOfLastPartition] = ‘”+ @[User::YearMonthForNextPartition]  +”‘

                                ,[DateTimeInsertedIntoTable] = getdate()

                                ,PartitionName = ‘Internet Sales “+ @[User::YearMonthForNextPartition]  +”‘

                                ,SSAS_DatabaseName = ‘Adventure Works'”

                                                               i.      Then click on Evaluate Expression to ensure that it is valid and you should see the following:

1.      

c.        Then click Ok.

8.       The next part is to update our Mart_TD_Max_SSAS_PartitionDetails with our last Partition that was created in the steps above. This is so that we have a starting point for the next time this runs.

a.        Drag in an Execute SQL Task and give it the following name:

                                                               i.      Update Mart_TD_Max_SSAS_PartitionDetails with Last Partitions created

b.       Then configure it with the following:

                                                               i.     

                                                              ii.      NOTE: The variable that we selected is called: Query_Update_Mart_TD_Max_SSASPartitionDetails

c.        Then click Ok

9.       Now the final part is where if the create partition script fails to then send an email so that the person responsible is made aware of the error.

a.        Drag in your Send Mail Task and drag it under your Run XMLA Script to create new SSAS Partition

b.       Drag the precedence constraint to your Send Mail Task and change it from success to failure.

                                                               i.     

c.        Then configure your Send Mail Task as you require for the person responsible.

10.    So once complete it looks like the following:

a.       

11.    Now that it is complete you can run the package.

a.        So when we looked at our Partitions before we ran the package it looked like the following:

                                                               i.     

                                                              ii.      NOTE: The reason for our Fact Internet Sales 2009-12 was so that we could create our script.

b.       Now after running the package it completes as shown below:

                                                               i.     

c.        Now if we look at our Partitions again we will see the following after refreshing it:

                                                               i.     

d.       And then finally if we look at our Mart_TD_Max_SSAS_PartitionDetails it should have the details of our Partition we created above:

                                                               i.