SSIS – Getting Partition Names for the past 12 months

I had a situation where I was using a conditional split, so that when data was coming in from my source tables it would dynamically go into the correct partitioned table.

 

Below is the script that I used, as well as for reference the conditional split

 

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

— What we are doing here is to create a Proc so that we can get the PartitionNames going back 12 months.

— While for the first time this is a manual process this can be used for other Parition Schemes going foward.

— The only thing that will need to change if the Partition Naming convention is the same is the Partition name

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

 

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

— 1. Here we are getting our current partition naming scheme into a variable.

— Example of partition scheme: Mart_TF_InternetSales_2014_02

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

Declare@PartitionNameasvarchar(30)

Set@PartitionName=

       (

              Select‘Mart_TF_InternetSales_’

       )

 

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

— 2. Here we are getting our Current Month Details.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

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

Declare@CurrentMonthNameasvarchar(8)

Set@CurrentMonthName=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-0, 0)))

       )

 

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

— 3. Here we are getting our 1 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

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

Declare@OneMonthBackasvarchar(8)

Set@OneMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-1, 0)))

       )

 

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

— 4. Here we are getting our 2 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

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

Declare@TwoMonthBackasvarchar(8)

Set@TwoMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-2, 0)))

       )

 

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

— 4. Here we are getting our 3 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

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

Declare@ThreeMonthBackasvarchar(8)

Set@ThreeMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-3, 0)))

       )

 

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

— 4. Here we are getting our 4 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

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

Declare@FourMonthBackasvarchar(8)

Set@FourMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-4, 0)))

       )

 

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

— 4. Here we are getting our 5 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

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

Declare@FiveMonthBackasvarchar(8)

Set@FiveMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-5, 0)))

       )

 

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

— 4. Here we are getting our 6 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

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

Declare@SixMonthBackasvarchar(8)

Set@SixMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-6, 0)))

       )

 

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

— 4. Here we are getting our 7 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

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

Declare@SevenMonthBackasvarchar(8)

Set@SevenMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-7, 0)))

       )

 

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

— 4. Here we are getting our 8 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

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

Declare@EightMonthBackasvarchar(8)

Set@EightMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-8, 0)))

       )

 

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

— 4. Here we are getting our 9 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

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

Declare@NineMonthBackasvarchar(8)

Set@NineMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-9, 0)))

       )

 

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

— 4. Here we are getting our 10 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

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

Declare@TenMonthBackasvarchar(8)

Set@TenMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-10, 0)))

       )

 

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

— 4. Here we are getting our 11 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

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

Declare@ElevenMonthBackasvarchar(8)

Set@ElevenMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-11, 0)))

       )

 

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

— 4. Here we are getting our 12 Month Back.

— NOTE: We are using our Date Dimension to get our Year and Month, we are then replacing the dash “-” with an

—       underscore “_” as per our Parition Naming Scheme.

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

Declare@TwelveMonthBackasvarchar(8)

Set@TwelveMonthBack=

       (

              SelectReplace(YearMonth,‘-‘,‘_’)

                                                       fromMart_TD_Datewith (nolock)

                                                       whereFullDate=convert(date,dateadd(ms,+0,DATEADD(mm,DATEDIFF(m,0,getdate()  )-12, 0)))

       )

Select

               @PartitionName+@CurrentMonthNameasCurrentMonthParititionName

              ,@PartitionName+@OneMonthBackasOneMonthBackPartitionName

              ,@PartitionName+@TwoMonthBackasTwoMonthBackPartitionName

              ,@PartitionName+@ThreeMonthBackasThreeMonthBackPartitionName

              ,@PartitionName+@FourMonthBackasFourMonthBackPartitionName

              ,@PartitionName+@FiveMonthBackasFiveMonthBackPartitionName

              ,@PartitionName+@SixMonthBackasSixMonthBackPartitionName

              ,@PartitionName+@SevenMonthBackasSevenMonthBackPartitionName

              ,@PartitionName+@EightMonthBackasEightMonthBackPartitionName

              ,@PartitionName+@NineMonthBackasNineMonthBackPartitionName

              ,@PartitionName+@TenMonthBackasTenMonthBackPartitionName

              ,@PartitionName+@ElevenMonthBackasElevenMonthBackPartitionName

              ,@PartitionName+@TwelveMonthBackasTwelveMonthBackPartitionName

 

And here is the conditional split

NOTE: The reason that it is in RED is because I changed the actual datetime column that I have in my dataset.

NOTE 2: Normally this would be black to indicate that there are no conditional errors

 

clip_image002

SharePoint 2013 – Creating a data source to an SQL Server Analysis Services (SSAS) OLAP Cube

Below are the details when using SharePoint 2013 when you want to collect to an SQL Server Analysis Services (SSAS) OLAP cube, instead of only being able to use the SSAS Tabular.

 

Example:

·         In our example we are going to connect to our Adventure Works DW 2012 SSAS

 

1.       Go into your SharePoint 2013 website and then click on Files, then New Document and then select Report Data Source.

a.        clip_image002

2.       This will then open the Data Source Properties window where you will configure it with the following explained below.

a.        Where it says Name put in a name so that you know it is your connection file.

b.       clip_image004

c.        Next where it says Data Source Type click on the drop down and select the following:

d.       clip_image006

e.       Now where it says connection string below is the syntax that you have to use with an explanation below:

Data Source=Server\InstanceName;initial catalog=AdventureWorksDW2012;cube=’Adventure Works’

                                                                                       i.      From the syntax above the part highlighted in RED is to specify the SSAS OLAP Name and if required instance name.

1.       As with our example it was called:

a.        Server\InstanceName

                                                                                      ii.      The next section highlighted in GREEN is your SSAS OLAP Cube database name.

1.       As with our example is:

a.        initial catalog=AdventureWorksDW2012

                                                                                    iii.      The final part which you have to specify is the actual cube name, which is highlighted in PURPLE

1.       As with our example it is called:

a.        ;cube=’Adventure Works’

                                                                                    iv.      clip_image008

f.         In the next section which is the Credentials this is what will be used when running the data source.

                                                                                       i.      What we have setup in our environment is we have a static domain account where the password does not change. This is so that we can then use this account and grant it permissions to the required cubes as well as roles.

                                                                                      ii.      NOTE: We also did this because it is a lot simpler to configure the security to our SSAS OLAP cube via the standard roles.

                                                                                    iii.      So with our example we select Stored Credentials and put in our User Name and Password:

1.       clip_image010

2.       NOTE: We selected Use as Windows credentials so that it knows it is a Windows Domain account.

                                                                                    iv.      Then click on Test Connection to make sure the connection to the cube is correct as well as the credentials.

1.       clip_image012

3.       Then click Ok to create the Report Data Source.

4.       NOTE: If you are concerned about security for people using this data source to see specific data you can do one of two things:

a.        ONE: Change the above credentials to Windows Authentication.

b.       TWO: You can also limit who has access to the actual Report Data Source, Power View Report as well as the Folder that stores all the information and set unique permissions.

5.       Now as you can see below we created a quick and easy Power View Report on our SSAS OLAP Instance.

6.       clip_image014

SSAS – Configuring the Slice Partition in your cube.

After recently going through an online video, I realized that I had not set the Slice option on the partition on a larger cube that I had. And by putting in the slice details this would then enable me to get faster queries when people were accessing the data.

 

Below are the steps that I did in order to configure the Slice details for a partition on your cube.

 

Example:

·         Configuring the slice for the Partition in our Adventure Works DW 2012, for each year that we have created a partition.

 

 

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

 

1.       The first thing that I had to ensure that was for each Partition in my cube, the corresponding table only had the correct date values.

a.        NOTE: In my actual data for some reason this was not 100% correct, so I ensured that each underlying SQL Partition had the valid dates in each configured partition.

2.       Next open up SQL Server Data Tools (SSDT) or in SQL Server Management Studio (SSMS).

3.       Then go to your Partition Window and click on a specific Partition

a.        In our example we went into our Partition called:

                                                               i.      clip_image002

b.       Then you can either right click to go into the Properties or double click.

4.       Next you are looking for the section that says Slice

a.        clip_image004

b.       Click on the Ellipses to go into the Partition Slice – Total_Sales_2005

5.       Next in order to configure our slice, we need to see how the data has been partitioned.

a.        As with our example it is partitioned by Year.

b.       So from our Metadata we can go down to Date click on the Date, then Calendar, then the Calendar Hierarchy, then Calendar Year.

                                                               i.      Now select CY 2005 and drag this into the Expression.

c.        It will now look like the following:

d.       clip_image006

e.       Click on Check to ensure that your syntax is correct.

6.       Click Ok twice to go back to your SSDT or SSMS

7.       Now Process the Partition

8.       It should now come back with the following:

a.        clip_image008

9.       Now when you run your MDX query you should see the following when querying data from 2005

a.        This is the query I ran

Selectnonempty {[Measures].[Sales Amount]} on 0

,nonempty {

[Date].[Date].&[20050701]:[Date].[Date].&[20050731]

 

} on 1

from [Adventure Works]

b.        

10.    And this was the result from Profiler

a.        clip_image010

Which we can now see only used the Total_Sales_2005 Partition