SSIS – Running single SSIS packages in Parallel to get faster performance for multiple data loads

What we are going to show you below, is that by using ONE SSIS package, we can re-use this one SSIS package multiple times to get data from multiple sources, possibly from different databases hosted in different locations around the world. So in effect we are loading our data from one SSIS package in parallel.

 

NOTE: By completing the steps below we have seen our packages perform and complete a lot quicker due to running in parallel.

 

The thing to note with my example below is that all the database have exactly the same structure within the databases, where ever they are hosted.

 

Example Details

·         Databases are hosted in different locations around the world.

o    And there were multiple databases on each database system.

·         All the databases have exactly the same table and schema structures.

·         Within each hosting location we have different IP Addresses and database names.

 

Our requirement was to load the data as quickly as possible from our multiple locations. This is how we achieved this.

 

NOTE: I am not going to go into the details on how we got our connection details to connect to each system. If someone has a requirement for this then I can possibly put this into another blog post.

 

Creating your Control SSIS Package to run in Parallel

What I am going to explain below is how we use a control SSIS package, which controls how we run our SSIS packages in parallel.

 

1.       The first thing that we did was to create the following variable called:

a.        Name:

                                                               i.      LoopQuitParent

b.       Scope:

                                                               i.      PackageName

c.        Data Type:

                                                               i.      Int32

d.       NOTE: The reason that we create this is because of the following:

                                                               i.      This is going to be used in the steps further down in our For Loop container to know when to exit the For Loop.

                                                              ii.      It is also going to be used in the actual SSIS package that runs in parallel, and then it has completed to use a script task to populate this variable from our parent package.

2.       As with normal data warehousing practice we first truncate our staging tables.

a.        clip_image002

3.       Next is where we have our sequence container.

a.        It is within this sequence container that we put how many packages we want to run in parallel.

4.       So within our Sequence container we put the following

a.        First we dragged in a For Loop container inside our Sequence Container.

b.       We then configured our For Loop container with the following:

                                                               i.      clip_image004

                                                              ii.      What we are saying above is when our variable from step 1 @LoopQuitparent not equals zero then finish or complete.

c.        Next inside our For Loop Container we put an Execute Package Task

                                                               i.      NOTE: This is our SSIS Package that is going to run multiple times or in Parallel.

                                                              ii.      As per our example if you look at the Package Window on the left hand side you will see where we have configured our SSIS Package

                                                            iii.      clip_image006

d.       Then all that you need to do is to copy and paste the For Loop Container as many times as you want to run in Parallel.

                                                               i.      In our picture below we wanted to run it in parallel x 4

                                                              ii.      NOTE: We just renamed each For Loop container so that we know how many we have.

                                                            iii.      NOTE 2: It might be good to test how many will be the optimal number for you, as it is dependent on server resources, network interface and bandwidth to your locations of where the data is.

e.       clip_image008

5.       In the next steps we will explain how we configure our one SSIS package so that it can run in parallel as well as know when to exit.

 

Configuring our SSIS Package so that it can get the required details and run in parallel (SSIS-Parallel-LoadStaging.dtsx)

In the next steps I will explain how we get our one SSIS package (SSIS-Parallel-LoadStaging.dtsx) to get the required data into variables and then run in parallel.

 

1.       It is going to be easiest to explain if I can show you an example of what our Source Table looks like below. This is where we store the following which will be explained after the picture for clarity.

a.        clip_image010

b.       SourceSystemSK

                                                               i.      We use this to uniquely identify our Source Systems. This is used throughout our SSIS Packages and is put into our data warehouse and Cube so that we know from which system the data came from.

                                                              ii.      NOTE: This also enables us to get an overview of a particular system when it has more than one database.

c.        IPAddress

                                                               i.      This is the actual IP address that we will pass as an expression in our Connection manager in order to connect to the SQL Instance.

d.       SQLPortNumber

                                                               i.      This is the standard SQL Port number, but if for some reason it was on a different port we would specify it here.

                                                              ii.      NOTE: This can also get passed as an expression in our Connection manager.

e.       IsProcess

                                                               i.      This is used later within our SSIS package (SSIS-Parallel-LoadStaging.dtsx) and what we will explain later, is that when the row has been used and the data processed it changes the value from a 2 to a 1.

f.         DatabaseName

                                                               i.      This is the database name within the SQL Instance system we are connecting to.

                                                              ii.      NOTE: This is gets passed as an expression in our Connection Manager.

g.        Location

                                                               i.      This is used in our dimensions later if we want to know where the actual data is coming from.

h.       SQLInstanceName

                                                               i.      This is the actual SQL Instance name that we are connecting to.

i.         IsActive

                                                               i.      This is part of our process, where we test to see if we can connect to the actual SQL Instance and databasename. If we can the IsActive is set to 1, and if not it is set to 0 (Zero)

                                                              ii.      NOTE: We did not explain how we get the data into this table, as this would make this blog post a whole lot longer.

2.       So what we do in our first step is to get the SourceSystemSK into a variable.

a.        clip_image012

b.       But we need to explain what else we do within our Execute SQL Task.

c.        Below is the actual query with an explanation afterwards

— Section 1

DECLARE@ServerIDINT

 

— Section 2

BEGINTRAN

 

— Section 3

SETROWCOUNT 1

 

— Section 4

SELECT@ServerID=[SourceSystemSK]

FROM[dbo].[Mart_TD_SourceSystems]WITH (TABLOCKX,HOLDLOCK)

WHEREIsProcess= 2

 

— Section 5

UPDATE[dbo].[Mart_TD_SourceSystems]

WITH (TABLOCK)

SETIsProcess= 1

WHERE[SourceSystemSK]=@ServerID

 

— Section 6

SETROWCOUNT 0

 

— Section 7

SELECTISNULL(@ServerID, 0)asSourceSystemSK

 

— Section 8

COMMIT

                                                               i.      — Section 1

                                                              ii.      The first thing that we do is declare our @ServerID

                                                            iii.      — Section 2

                                                            iv.      Next we start a Begin Tran

1.       NOTE: The reason for this is so that we can actually lock the table whilst this transaction is happening.

2.       NOTE 2: This is so that when the SSIS Packages are running in parallel, we know for a fact that each package will only get a valid SourceSystemSK when trying to get another value. If it tries whilst another SSIS Package is busy it will wait until the transaction has been completed.

                                                              v.      — Section 3

                                                            vi.      We then set the RowCount to 1, this is so that we will only get one row back.

1.       NOTE: we are doing this because we only ever want to get back one SourceSystemSK

                                                           vii.      — Section 4

                                                         viii.      Next is our TSQL Select statement

1.       Here we are getting any SourceSystemSK from our table, where the IsProcess is set to 2.

2.       We are then putting this into our @ServerID variable.

                                                            ix.      — Section 5

                                                              x.      Next we are updating our table using the TABLOCK hint so that we can once again lock the entire table, to ensure that for our SourceSystemSK row it gets updated from a 2 to 1.

1.       NOTE: This then means that this data is and has been processed when the next package comes along looking for data to process.

                                                            xi.      — Section 6

                                                           xii.      We then set the RowCount back to 0 (zero)

                                                         xiii.      — Section 7

                                                         xiv.      Then we run a TSQL Select statement so that we can then pass our @ServerID as SourceSystemSK

1.       NOTE: This is because we are using the SourceSystemSK and passing this into a variable in our Execute SQL Task.

2.       NOTE 2: We also set the isNull to 0 (zero) so that when there are no more rows to process it defaults to zero, which is explained later.

                                                           xv.      — Section 8

                                                         xvi.      Finally we are then committing our transaction.

d.       Within our Execute SQL Task we also map the result set to our variable called:

                                                               i.      clip_image014

3.       The next thing that we need to look into is our Exit Loop

a.        clip_image016

b.       NOTE: The reason that we have this is because we need to pass a variable from our child package back to our Parent Package.

c.        So we put in a Script Task using Visual Basic 2010 and configured it with the following:

d.       NOTE: Click on Edit script to open the Script Task editor

PublicSub Main()

       

        ‘ Add your code here

       

        Dts.Variables(“LoopQuitParent”).Value = 1

 

        Dts.TaskResult = ScriptResults.Success

                                                               i.       

                                                              ii.      What the above does is pass the Variable name LoopQuitParent value of 1 back to the Parent package.

                                                            iii.      NOTE: This was configured in our For Loop Container in step 4 from the section above, where it was set to not equal to zero. So when it equals one then exit or complete

4.       Next we creating another Execute SQL Task, and in this task it then uses a Query within a variable to get the IP Address and Database name for our server that we want to connect to.

a.        clip_image018

b.       NOTE: This is based on our SourceSystemSK variable which we populated in step 2 above.

c.        This is what our Variable looks like, where we have configured it as an Expression

                                                               i.      clip_image020

                                                              ii.      As you can see from above when we run the above query, if our SourceSystemSK = 1 we would get the following details back

                                                            iii.      clip_image022

d.       Then in our Result set for our Execute SQL Task we would have the following:

                                                               i.      clip_image024

e.       Now we currently have the following in our variables for our SSIS Package

                                                               i.      SourceSystemSK, IPAddress and Database Name.

5.       Now you need to create a local OLE DB Connection Manager in your SSIS Package.

a.        NOTE: The reason that it must be a local connection, is so that when each package is running in parallel it will only populate the expressions for the current package.

b.       With our example we created a local OLEDB connection with the following name:

                                                               i.      ServerConnection.local-OLE

c.        Next right click and go into the Properties for your ServerConnection.local-OLE.

d.       Where it says Expressions click on the Ellipses button and configure it with the following:

                                                               i.      Property:

1.       InitialCatalog

2.       NOTE: This is the actual DatabaseName

                                                              ii.      Expression:

1.       @[User::DatabaseName]

                                                            iii.      Property:

1.       ServerName

2.       NOTE: This is the actual IPAddress to connect to the server.

                                                            iv.      Expression:

1.       @[User::IPAddress]

                                                              v.      IT will look like the following:

                                                            vi.      clip_image026

e.       Then click Ok.

f.         NOTE: Ensure that your DatabaseName and IPAddress variables have valid default Values in your Variables

                                                               i.      This is because when you put in your expressions above, it attempts to connect to the system to ensure that it is valid.

                                                              ii.      This will save you a lot of time waiting for it to timeout and will ensure that it is indeed valid.

6.       Now the next step is to put in what you want your SSIS Package to complete on each and every server.

a.        clip_image028

b.       With our example we would then get the Max RowID for each system which is stored in a separate table.

c.        Within our table we simply have the Max RowID, SourceSystemSK and the date it got inserted.

d.       Here is an example below:

e.       clip_image030

f.         This would then be in the Result Set

                                                               i.      clip_image032

g.        Then we have our Data Flow task which based on our Max RowID inserts the data into our Staging_tb_SalesInformation

h.       clip_image034

7.       Now the last thing to configure in this SSIS package (SSIS-Parallel-LoadStaging.dtsx) is the Precedence Constraint which can be seen in the picture below

a.        clip_image036

b.       The reason that we have this is so that we know for our package when it has completed going through our entire list of servers (and table with the connection details) and populate our LoopQuitParent variable back to our Parent Package, and let that particular For Loop Container complete.

c.        NOTE: What we did in step 2 above is your will see that we set an IsNull in section 7 and set this to 0 (zero).

d.       This is now used in our Precedence constraint and configured with the following for the “Get the IPAddress  and DatabaseName into Variable based on SourceSystemSK” which is on the left hand side.

                                                               i.      We configured the Precedence Constraint with the following:

                                                              ii.      clip_image038

                                                            iii.      And here what we are saying is if we have any value greater than zero then get our values, and get the data down.

e.       This is now used in our Precedence constraint and configured with the following for the “Exit Loop” which is on the right hand side.

                                                               i.      We configured the Precedence Constraint with the following:

                                                              ii.      clip_image040

                                                            iii.      And here what we are saying is when there are no more values for the SourceSystemSK, which will be set to zero, then exit out and populate the LoopQuitParent so that this can go back to the parent package.

8.       Now we have configured our SSIS Package which can run in parallel.

9.       This is what the entire package looks like

a.        clip_image042

 

Final Notes

·         Now when you run the Control package, and it runs, you will see all your For Loop containers running at once.

·         The thing that you will not essentially see is if you have configured it to run in parallel with 4 SSIS packages, you will only see one running, but actually all 4 are running at once.

·         Once it has all completed you will see your For Loop Container complete with the green arrow.

o    You can then go into your Staging_tb_SalesInformation table and validate it to see that you have got all your required information from your Source Systems.

 

If you have any questions or queries then please do not hesitate to contact me.

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