Book Review – Expert Cube Development with SSAS Multidimensional Models

http://www.packtpub.com/expert-cube-development-with-ssas-multidimensional-models/book

This book is a must read if you are looking for some useful and practical information in your SSAS Multidimensional models. But it must be noted that this is not an introduction into SSAS Multidimensional modelling, rather practical real world examples.

I found that the book is a wealth of information that can be applied to your working environment. There are a lot of real world examples that I have had a look at and made me check and update my SSAS Multidimensional models so that they can be that much faster and quicker.

It is also a great reference book, for when you are looking for a specific issue, where you can find the requirement and how to solve your particular issue.

I also really enjoyed the book, because it got straight into the requirement and then explained how to potentially solve or improve your requirement. Along with this I found that it was great where they applied their working experience in what they encountered and how they overcome an issue.

It is written by the guys who I consider to be some of the leaders in SSAS Multidimensional modelling. And you can see by the context of the detail in the book, as well as their examples that this happens often in the working environment, and how to get the best performance from your SSAS Multidimensional models.

A great read and I would recommend this book to anyone who is looking for a wealth of information as well as some great insight into the inner workings of SSAS Multidimensional models.

SSIS (SQL Server Integration Services) – Using the Lookup Transformation and cache and how to handle NULL Values

I had a situation where I was using the Lookup transformation and then loading this into the SSIS Cache, but I wanted all rows to be inserted using the SSIS Cache, even if there was a NULL Value. Below explains how I overcame this.

 

Example:

·         In our Source data we have Products, but they might not be in our Product Dimension table, due to the source systems not having the required data.

o    NOTE: While this is not ideal and we either should fail the component or redirect it, for this example this is fine.

·         We have created a row in our Products Table for Products that we do not find.

o    EG:

§  ProductsSK ‘-1’

§  ProductsName ‘Product not Found’

·         So when we insert data we want to ensure that even when products are not found, they will still exist in the Lookup Transformation and cache.

 

1.       I created a Lookup Transformation in SSIS which I then configured as the following explained below.

2.       On the General Tab I configured it with the following as shown below:

a.        clip_image002

b.       NOTE: The reason that I selected the Redirect rows to no match output in the Specify how to handle rows with no matching entries, is because due to the configuration of our Lookup transformation all the rows WILL find a match.

3.       Next click on the Connection on the left hand side.

a.        clip_image004

b.       Now you will select your OLE DB connection to your database where your Product Dimension is located.

c.        Then select Use results of an SQL Query.

d.       Then what we did was to put in the following SQL Query which we will explain why we did this after the query

  SELECT[ProductsSK],[ProductsSK]asProductsSKToUse      

  FROM[dbo].[Mart_TD_Products]with (nolock)

 

  UnionAll

 

  Selectnullas[ProductsSK],1 asProductsSKToUse

                                                               i.      Now as you can see above we have create a duplicate column names from our Dimension Table.

                                                              ii.      The reason for this is in the second part of the query with the Union All

                                                            iii.      Now you can see that we have actually put in a NULL value, and then given this NULL value a value of -1

1.       NOTE: This corresponds to the data that we already have in our Products Dimension table.

                                                            iv.      So now when you load your query into the SSIS Cache it will also have a value of the following:

1.       clip_image006

e.       Now when the lookup is running in SSIS, when it finds a NULL value it will assign it a value of ‘-1’ to the ProductsSKToUse

4.       Then click on Columns and create the lookup

                                                               i.      clip_image008

                                                              ii.      As you can see above we dragged the ProductsSK to our ProductsSK in our Available Lookup Columns

                                                            iii.      And then below you can see that our Output Alias is ProductsSKToUse

5.       This will then be used in our insert into our Fact Table.

6.       And in this way when there is a NULL Value it will still be matched.

a.        So as you can see for our 2 rows, we had 2 matches even when 1 row was NULL

b.       clip_image010

SSAS (SQL Server Analysis Services) – Securing Measures in Measure Group for specific Roles

What I had to do today for the first time is to only show specific measures in the measure group for particular Roles (Users or AD Groups)

·         The reason that this was required is because we had sensitive information that was not for everyone’s eyes.

 

NOTE: From my recent review and read through from the following book, http://www.packtpub.com/expert-cube-development-with-ssas-multidimensional-models/book that the least restrictive will apply for a particular User.

·         So if User (BOB) belonged to one SSAS Role which only allowed access to the count of rows. And User (BOB) also belonged to an SSAS Role which has access to the financials he would be granted access to both the count and the financials.

·         Another thing to remember is that your calculated measures will be shown regardless of the Role permissions.

 

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

 

Example:

·         We are going to create a Sales Role, which will only be able to see the Sales Orders Measures group and associated Measures for the Adventure Works Cube.

·         We will also create a Financial Reporting Role, which will be able to only see the Financial Reporting Measures group and associated Measures for the Adventure Works Cube.

 

Creating the Roles

1.       The first thing that we are going to do is to create the 2 roles.

2.       Right click on Roles and New Role

a.        clip_image002

3.       It will default to Role.Role.

a.        Right click, select Rename and change it to Sales Orders.Role

b.       It will prompt you if you want to change the Object Name also, click Yes.

c.        clip_image004

4.       Next we will create our Financial Reporting Role.

a.        You can follow steps 2 and 3 above so that you will then see your Financial Reporting.Role.

b.       clip_image006

 

Adding Permissions and configuration to see particular measures

In this next section we are going to configure each of our Roles, and then configure which measures they can see.

 

1.       We will start with the Sales Orders.Role.

a.        In the General Tab, make sure you set the database permission for this role to:

                                                               i.      Read definition

                                                              ii.      clip_image008

b.       Click on the Membership tab and put in your domain users, or ideally your Domain Groups who are part of the Sales team.

c.        Then click on Cubes.

                                                               i.      Then next to the Cube Name of Adventure Works, make sure that you change the access from None to Read as shown below:

                                                              ii.      clip_image010

                                                            iii.      After allowing Read access click on the Save button, it will then prompt you saying that the following objects will also be saved:

1.       clip_image012

2.       Click Ok.

                                                            iv.      NOTE: You have to first save the access to the cube, if you do not do this in the next steps under Dimension Data you will NOT see the Measure Groups in order to change the permissions.

d.       Next click on Dimension Data.

                                                               i.      Where it says Dimension click on the drop and select the following as shown below:

1.       clip_image014

2.       Then click Ok.

3.       NOTE: You will see all of the Measure Group values.

a.        So you will need to know which measures are associated to your Measure Group.

b.       In our example for Sales Orders it was the following:

c.        clip_image016      

                                                              ii.      Now click on the Deselect all Members, this is so that we only need to select the Members that we WANT to enable.

1.       clip_image018

2.       Now scroll through the list until you find Order Count.

3.       And as shown below put a tick next to Order Count.

a.        clip_image020

                                                            iii.      Now save your Sales Order.Role.

2.       Now open your Financial Reporting.Role

a.        In the General Tab, make sure you set the database permission for this role to:

                                                               i.      Read definition

                                                              ii.      clip_image008[1]

b.       Click on the Membership tab and put in your domain users, or ideally your Domain Groups who are part of the Sales team.

c.        Then click on Cubes.

                                                               i.      Then next to the Cube Name of Adventure Works, make sure that you change the access from None to Read as shown below:

                                                              ii.      clip_image021

                                                            iii.      After allowing Read access click on the Save button, it will then prompt you saying that the following objects will also be saved:

1.       clip_image022

2.       Click Ok.

                                                            iv.      NOTE: You have to first save the access to the cube, if you do not do this in the next steps under Dimension Data you will NOT see the Measure Groups in order to change the permissions.

d.       Next click on Dimension Data.

                                                               i.      Where it says Dimension click on the drop and select the following as shown below:

1.       clip_image014[1]

2.       Then click Ok.

3.       NOTE: You will see all of the Measure Group values.

a.        So you will need to know which measures are associated to your Measure Group.

b.       In our example for Financial Reporting it was the following:

c.        clip_image024  

                                                              ii.      Now click on the Deselect all Members, this is so that we only need to select the Members that we WANT to enable.

1.       clip_image025

2.       Now scroll through the list until you find Amount

3.       And as shown below put a tick next to Amount.

a.        clip_image027

                                                            iii.      Now save your Financial Reporting.Role

 

Updating the cube with the Roles and testing.

The final step is to update the cube with the new roles and to test.

 

1.       What I did is I did a process Update on the Products dimension.

a.        NOTE: This just enables me to push the changes to the SSAS cube.

2.       Next I opened up the Adventure Works Cube and then clicked on the Browser Tab.

3.       Now where it says Change User click on the Icon.

a.        clip_image029

b.       This will then open the Security Context – Adventure Works window.

c.        Then click on Roles and click on the drop down and select Sales Orders

                                                               i.      clip_image031

d.       Then click Ok.

4.       Now when we browse the cube under Measures we only see the following:

a.        clip_image033

5.       And if you had to follow steps 3 above and change it to Financial Reporting you would see the following in the browser.

a.        clip_image035

Excel – User Input for Custom MDX query using VBA and Pivots

I had a requirement where I wanted to do the following for a particular Power User

·         Give them the ability to put in a Date as a parameter in an Excel Workbook.

·         Once I have this date, is to the have a custom MDX query, which will then go and get the required data for the date.

o    NOTE: The reason for the custom MDX query was due to the volume of data returned I wanted to limit the data and query, thereby making the results return as quickly as possible.

·         This must then be queried and returned to Excel, where the results could then be pivoted.

·         After which the user could then create his Pivot and chart based on his date.

·         And essentially for the user to just input his date and click a button.

 

Working Example

·         I wanted the user to put in his Date (01 June 2008) which will then return all the Products and Product Lines for that date inputted.

·         The outcome will be in the same sheet once the user has clicked on the button Click to Refresh clip_image002[4] their Pivot and Chart will then be updated.

 

NOTE:

·         VBA is new to me, so I am aware that there is no error checking in place.

·         I also do not validated that the date is in the correct format for the user input. I am going to assume that I have explained to my user what date format to use.

 

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 the VBA Function within Excel

The first part is to hard code the VBA function to ensure that we can get our data returned correctly to our Sheet in Excel.

 

NOTE: You might have to enable the Developer Ribbon, which you can reference here: http://office.microsoft.com/en-za/excel-help/show-the-developer-tab-HA101819080.aspx

 

1.       Click on the Developer Ribbon and then click on Macros

a.        clip_image004[4]

2.       This will then open the Macro Window

3.       Where it says Macro Name, put in a name for the Macro you are going to create.

a.        As with our Example I put the following Macro Name:

b.       clip_image006[4]

c.        Then click on the Create Button

4.       This then opens the Microsoft Visual Basic for Applications Window

5.       You will now see on the left hand side under Modules, it has Module 1

a.        clip_image008[4]

6.       On the right hand side is where we will be putting in our VBA code in our AW_ProductsByDate

a.        clip_image010[4]

7.       The next that you HAVE to do is to add the Microsoft ActiveX Data Objects 2.8 Library to your VBA function otherwise it will not recognize some of the coding later.

a.        To do this click on Tools and then Reference

                                                               i.      clip_image012[4]

b.       Once this opens the References – VBAProject, you are going to have to scroll down quite a bit until you find the following:

                                                               i.      clip_image014[4]

                                                              ii.      As shown above put a tick next to Microsoft ActiveX Data Objects 2.8 Library

                                                            iii.      Then click Ok.

c.        Now if you want you can save your workbook.

d.       NOTE: when saving your workbook you have to save it as an Excel Macro-Enabled Workbook as shown below. If this is not done then your Macro will not run.

                                                               i.      clip_image016[4]

8.       Now in the section below I will explain each section, while at the end I will have the entire VBA function so that you can copy and paste it.

9.       In the first section is where we are actually just declaring our Variables to use later.

‘Declare variables

Set objMyConn = New ADODB.Connection

Set objMyCmd = New ADODB.Command

Set objMyRecordset = New ADODB.Recordset

Dim Input1 As String

‘Input1 = ActiveWorkbook.Sheets(“Sheet1”).Range(“A2”)

a.        What we are doing above is setting our connections.

b.       Then where we have the DIM Input1 this is where we are creating our input variable

                                                               i.      NOTE: This has been commented out and will be explained in the later steps how to get it working.

                                                              ii.      Dim Input1 As String

                                                            iii.      Then we are setting the location of our Input1 variable

ActiveWorkbook.Sheets(“Sheet1”).Range(“A2”)

1.       NOTE: The above means on our Active Workbook, on the sheet namedSheet1look in the columnA2

                                                            iv.      We will only be using the input later once we have got everything configured.

10.    In the next section is where we open and create our connection to our SQL Server Analysis Services Database

‘Open Connection

objMyConn.ConnectionString = “Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog= AdventureWorksDW2012;Data Source=ServerName\OLAP;MDX Compatibility=1;Safety Options=2;Protocol Format=XML;MDX Missing Member Mode=Error;Packet Size=32767″

objMyConn.Open

a.        NOTE: In the above connection string you will have to modify the following for your environment.

b.       For Initial Catalog highlighted in RED you will have to change this to the name of your SSAS Database.

                                                               i.      In our example it was:

1.       Initial Catalog= AdventureWorksDW2012

2.       NOTE: Ensure to get the Database ID, and not the name to ensure that you can connect to the database correctly.

c.        For the Data Source, this is your Server name, and in our example it is highlighted in BLUE

                                                               i.      Data Source=ServerName\OLAP

d.       The other settings are optional settings for if data is going across a slower network and to potentially speed up queries.

11.    In the next section is where we actually put our MDX query

‘Set and Excecute SQL Command

Set objMyCmd.ActiveConnection = objMyConn

objMyCmd.CommandText = “Select ‘ {[Measures].[Internet Sales Amount]} on 0 ” & _

                        “,’ {( ” & _

                                    “[Date].[Date].&[20080601], ” & _

                                    “[Product].[Product Line].Children, ” & _

                                    “[Product].[Product].Children ” & _

                                    “)} on 1” & _

                        “from [Adventure Works]”

objMyCmd.CommandType = adCmdText

objMyCmd.Execute

a.        As you can see above the section that you need to change is after the objMyCmd.CommandText

b.       A thing to note is that you have to start your query with the double quotes

                                                               i.     

c.        And if you are going to continue it on another line and not have it all on one like then at the end of each line you need to put the following:

                                                               i.      ” & _A

d.       With our query above you can see for now that we have hardcoded our query.

e.       NOTE: We will come back later and update it to get the details from the Input1 variable we created in step 9 above.

f.         The rest of the syntax just executes the MDX query.

12.    The next section just opens the recordset

‘Open Recordset

Set objMyRecordset.ActiveConnection = objMyConn

objMyRecordset.Open objMyCmd

13.    Now in this next section we are going to copy the data to Excel

‘Copy Data to Excel

ActiveWorkbook.Sheets(“Dataset”).Range(“A2”).CopyFromRecordset (objMyRecordset)

‘ActiveWorkbook.Sheets(“Dataset1”).Range(“DownTimeTable”).CopyFromRecordset (objMyRecordset)

a.        As you can see above what we are doing is to copy from the recordset to a sheet called “Dataset” and then insert it starting at “A2

b.       NOTE: You will see that there is a section commented out below. We will come back and change this in further steps.

c.        What we now need to do is to create our sheet that is referenced above calledDataSet

d.       If you click on your Excel Workbook, and the plus sign next to Sheet 1, it will create Sheet 2

                                                               i.      clip_image018[4]

e.       Right click on Sheet2 and select Rename

                                                               i.      Rename it to DataSet.

f.         Now we have created our Sheet which is referenced above.

14.    Now in the next section is where we will refresh our Power Pivot model which we will create in further steps.

‘This is to refresh the Power Pivot Model

ActiveWorkbook.RefreshAll

15.    The final part is to close the connection and end our code.

‘Close Connection

objMyConn.Close

End Sub

16.    Here is the entire code all in one block.

a.        NOTE: We will explain the Delete section in step 21 below

Sub AW_ProductsByDate()

‘Delete all data out of Table before inserting new data

With Sheet2.ListObjects(“ProductsByDate”)

        If Not .DataBodyRange Is Nothing Then

            .DataBodyRange.ClearContents

            .DataBodyRange.Delete

        End If

    End With

 

‘Declare variables

Set objMyConn = New ADODB.Connection

Set objMyCmd = New ADODB.Command

Set objMyRecordset = New ADODB.Recordset

‘Dim Input1 As String

‘Input1 = ActiveWorkbook.Sheets(“Sheet1”).Range(“A2”)

 

‘Open Connection

objMyConn.ConnectionString = “Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=AdventureWorksDW2012;Data Source=ServerName\OLAP;MDX Compatibility=1;Safety Options=2;Protocol Format=XML;MDX Missing Member Mode=Error;Packet Size=32767”

objMyConn.Open

 

‘Set and Excecute SQL Command

Set objMyCmd.ActiveConnection = objMyConn

objMyCmd.CommandText = “Select ‘ {[Measures].[Internet Sales Amount]} on 0 ” & _

                        “,’ {( ” & _

                                    “[Date].[Date].&[20080601], ” & _

                                    “[Product].[Product Line].Children, ” & _

                                    “[Product].[Product].Children ” & _

                                    “)} on 1” & _

                        “from [Adventure Works]”

objMyCmd.CommandType = adCmdText

objMyCmd.Execute

 

‘Open Recordset

Set objMyRecordset.ActiveConnection = objMyConn

objMyRecordset.Open objMyCmd

 

‘Copy Data to Excel

ActiveWorkbook.Sheets(“Dataset”).Range(“A2”).CopyFromRecordset (objMyRecordset)

‘ActiveWorkbook.Sheets(“Dataset”).Range(“ProductsByDate”).CopyFromRecordset (objMyRecordset)

 

‘This is to refresh the Power Pivot Model

ActiveWorkbook.RefreshAll

 

‘Close Connection

objMyConn.Close

End Sub

17.    Now if you click on the Run sub/UserForm as shown below your query should now run.

a.        clip_image020[6]

b.       Then on your Dataset Sheet you should see the output of your query.

c.        NOTE: If the query only returns one row, ensure that your query is not set to only show non empty results.

d.       clip_image022[4]

18.    In the next steps we are going to give our results column names and convert it to a table.

19.    So on the sheetDataset” at the top we put in the following column names:

a.        clip_image024[4]

b.       Then what we did is to select the entire range and clicked on Format as Table and formatted it as a table.

                                                               i.      clip_image026[4]

                                                              ii.      NOTE: Ensure to select My Table has headers

1.       clip_image028[4]

                                                            iii.      Click Ok

c.        You should now see this below:

d.       clip_image030[4]

e.       The final thing is on the top left hand side it will have Table Name, change this from Table1 to ProductsByDate

                                                               i.      clip_image032[4]

20.    Now the final step is to go back into your VBA Macro and change the following code:

‘Copy Data to Excel

‘ActiveWorkbook.Sheets(“Dataset”).Range(“A2”).CopyFromRecordset (objMyRecordset)

ActiveWorkbook.Sheets(“Dataset”).Range(“ProductsByDate”).CopyFromRecordset (objMyRecordset)

a.        You can see from above that we have now changed our dataset that is returned to go into our Table name from step 19 above.

                                                               i.      NOTE: This is so that when the number of rows changes we will always get back all the rows.

21.    What we are doing in the last part is to delete all the data in our table each time it runs.

a.        This is to ensure that there is no left over or incorrect data

b.       This will go at the top of your codeso that it will run first

‘Delete all data out of Table before inserting new data

With Sheet2.ListObjects(“ProductsByDate”)

        If Not .DataBodyRange Is Nothing Then

            .DataBodyRange.ClearContents

            .DataBodyRange.Delete

        End If

    End With

 

Taking the output from our VBA function creating our Pivots with a Chart.

In the next section we are going to take the output from our VBA function which was inserted into a table and then create our Pivot and Pivot Chart on our First sheet.

 

1.       Click on your table that you created in your sheet called “Dataset”

2.       Then click on the Insert ribbon, and then click on Pivot Table

a.        clip_image034[4]

3.       This will then open the Create Pivot Table Window, which you will configure with the following:

a.        Where it says Choose the data that you want to analyze leave the default which is shown below:

                                                               i.      clip_image036[4]

b.       Then where it says Choose where you want the PivotTable report to be placed, click on Existing Worksheet

                                                               i.      Then were it says location click on the box to select your location.

                                                              ii.      Now click on your Sheet 1, and click on Cell A6

                                                            iii.      So it will look like the following:

1.       clip_image038[4]

c.        Finally where it says choose whether you want to analyze multiple tables, click the tick box which is shown below:

                                                               i.      clip_image040[4]

                                                              ii.      NOTE: The reason that we add it to the Data Model is so that it actually puts in into Power Pivot.

1.       You can validate it, if you had to click on your Power Pivot ribbon and click on Manage you would see your data.

d.       So this is what the entire Window looks like:

                                                               i.      clip_image042[4]

e.       Then click Ok.

4.       Now we can create our Pivot and our Pivot chart.

5.       I created the following below:

a.        clip_image044[4]

b.       NOTE: While this does look quite simple it is used for our example.

c.        And the actual Pivot looks like this:

d.       clip_image046[4]

 

Creating the variable and button for the users input

Now in the final part what we are going to do is the following:

·         Define where we will get the users data from.

o    Convert this to the correct format which we need for our MDX query

·         Update our VBA code so that we can use this within our code dynamically.

·         Put in a button which will refresh everything based on the user input.

 

1.       Here we will first create our column and format it as required from the users Input

a.        So what we did is to put in some text so that the user knows what to do

b.       clip_image048[4]

c.        Next we put in a date in A2

                                                               i.      EG: 2008-06-04

                                                              ii.      NOTE: What we need to do though is to ensure that we format the A2 column as Text.

1.       The reason for this is we are going to substitute the date to the correct format which we require for our MDX query

                                                            iii.      clip_image050[4]

d.       Next in column B2 I put the following:

=SUBSTITUTE(A2;”-“;””)

                                                               i.      And you will then see that the column now has the following

                                                              ii.      clip_image052[4]

                                                            iii.      Now what I did is I moved the Chart from above to the side.

                                                            iv.      I then cut and pasted the above excel function and put it in cell D11

                                                              v.      clip_image054[4]

                                                            vi.      NOTE: The reason that I did this is so that the cell value would be hidden from the user. But we did NOT have to hide a cell.

e.       I then moved the chart and slicer back to where it belongs.

2.       Next we are going to go back into our VBA Code and update the Variable so that it will get the data from cell D11

a.        So you should still have your VBA window open.

b.       Under the Declare Variables section uncomment the two lines so that they look like the following below:

Dim Input1 As String

Input1 = ActiveWorkbook.Sheets(“Sheet2”).Range(“A2”)

c.        Now we are going to change the location of where the cell is from the above details to the following:

Input1 = ActiveWorkbook.Sheets(“Sheet1”).Range(“D11”)

d.       The next thing that we need to do is to change our MDX query from being hard coded to use the variable Input1

e.       So under the section ‘Set and Execute SQL command, go to the following line:

“[Date].[Date].&[20080604], ” & _

f.         Now we are going to change it to the following below:

“[Date].[Date].&[” & Input1 & “], ” & _

                                                               i.      What we have done above is in order to use the variable we have to encapsulate it with the following:

“& VariableName &”

c.        Now what you can do to test this is to click on the Run sub/UserForm as shown below your query should now run.

                                                              ii.      clip_image020[7]

g.        Now it should run through correctly and you should see your data updated in Sheet1.

3.       Now the final step is to add the button, which the user can press after they have put in their date.

a.        Go back into your Excel spreadsheet and onto Sheet1

b.       Click on the Developer Tab

c.        Then under the Controls section, click on Insert and then select Button

                                                               i.      clip_image056[4]

d.       Next you will need to drag your button where you want it to be.

                                                               i.      You could do this anywhere initially.

e.       After you have dragged in your button it will then open up the Assign Macro Window.

                                                               i.      From this Window select your VBA that you created.

                                                              ii.      clip_image058[4]

f.         Then click Ok.

g.        You will now see the following:

h.       clip_image060[4]

i.         Now in order to change the text on the button do the following:

                                                               i.      Right click on the Button 1 and select Edit Text

                                                              ii.      clip_image062[4]

j.         I changed it to the following:

Click to refresh data

                                                               i.      NOTE: I had to drag the button to make the text fit.

1.       Also whilst you are editing the text you could also change the font and size.

k.        I then moved it so it looked like the following:

l.         clip_image064[4]

4.       Now to finally test that it works I changed the date to:

a.        2008-06-05 and then clicked the button “Click to refresh data”

b.       Now after the data refreshed I saw my pivot change to the following:

c.        clip_image066[4]

 

Now you can save your Excel worksheet and send it to the required people

NOTE: Ensure that the users have the required access to the cube where the MDX will be running.

 

You can get a copy of the workbook here: http://1drv.ms/1dfKry4 

SSAS – Error – Value cannot be null. Parameter name: rootComponent

I got the following error as shown below, when I was adding rows into the XML file for the Data Source View.

NOTE: The reason that I was using the XML file was because I had added a whole lot of partitions to my Data Source View, so I did not want to create all the relationships manually but rather wanted to copy and paste them into the XML file. This enabled me to complete the task a lot quicker.

 

clip_image002

 

clip_image004

 

Once I had saved the XML for the Data Source View it would not render the document any more.

 

This is what I did to resolve the issue:

1.       When getting the error below I had to click Ok multiple times for the Window to go away

a.        clip_image005

2.       Once that was completed I then saved the XML file.

3.       I then closed down the entire project.

4.       I then opened up the project and once it had opened everything appeared as it should with the new relationships created.

SSAS – Using the SUM Function within a Measure Group to display a Distinct Count with SSAS (SQL Server Analysis Services)

Whilst completing my review on the Expert Cube Development with SSAS Multidimensional Models, I came across the Distinct Count Measure within SSAS and how this can affect query performance as well as processing performance.

 

You can find the reference on page 114

And here is a link to the actual book that I am reviewing:

http://www.packtpub.com/expert-cube-development-with-ssas-multidimensional-models/book

What I wanted to do is instead of using the Distinct Count Function which we can use within SQL Server Analysis Services (SSAS), (which as we know has some performance issues along with creating its own measure group), I wanted to find a way where I could use the SUM Function in our Measure group but still return the distinct count.

 

And then use this distinct count using a dimension to slice by

 

In our example below we want to extract from the AdventureWorksDW2012 Analysis Services Database

 

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

 

Example reference:

·         We are going to get the distinct number of Customers (CustomerKey) from the [dbo].[FactInternetSales]

o    So this will enable us to get a distinct count based on any of the attributes from our Customer Dimension

·         NOTE: The thing to note is that you will always want to create your distinct calculation on your lowest granularity.

·         As with our example in our [dbo].[FactInternetSales] the lowest level was the Date or OrderDateKey.

·         In order to follow how this works, we will be using the Order date of 03 March 2008.

o    The distinct number of Customers on 03 March 2008 is 51 Customers.

 

Creating the Distinct Number of Customers Calculation

1.       The first thing is we will be creating our distinct calculation and then insert this into a staging table.

a.        NOTE: What you could do is to use your staging table later as part of the loading into your Fact Table.

                                                               i.      But with our example we are going to update our Fact Table with our calculations.

                                                              ii.      We also insert the data into a Staging table so that we can use it as part of our Update Statement.

2.       Next below is the TSQL syntax that we used to create our distinct number of Customers Calculation, with an explanation afterwards

Select

                      convert(float,Count(Distinct(CustomerKey)))/ Count(1)asDistinctCustomerKey

                     ,OrderDateKey

  FROM[AdventureWorksDW2012].[dbo].[FactInternetSales]with (nolock)

         GroupbyOrderDateKey

         OrderbyOrderDateKey

a.        The only part that we are going to concentrate on is the actual calculation.

b.       We will start the explanation from the inside out, due to this being the logical way that I built this up.

                                                               i.      The part highlighted in RED is where we first are selecting the Distinct CustomerKey

1.       Distinct(CustomerKey)

                                                              ii.      The next section highlighted in PURPLE is where we are doing a count of the Distinct CustomerKey

1.       Count(Distinct(CustomerKey))

                                                            iii.      The next section highlighted in GREEN is where we are converting our values to a Float

1.       convert(float,Count(Distinct(CustomerKey)))

                                                            iv.      The final part of the calculation is highlighted in BLUE, where we are dividing our distinct count by the number of rows.

1.       / Count(1)

2.       This is so that we can then get the correct calculation.

                                                              v.      We also have the OrderDateKey, because this is our lowest level of granularity, we need to group by this so that the number of rows is grouped per day to work out the calculation correctly.

                                                            vi.      NOTE: The reason that we are converting this to a float is so that later when we sum the row details it will sum back up to the correct number.

3.       For the simplicity to understand below is the complete TSQL Syntax where we will be truncating and inserting our data into our Staging table in our AdventureWorksDW2012 database

TruncateTabledbo.Staging_tb_FactInterNetSales_DistinctCustomerKey

Insertintodbo.Staging_tb_FactInterNetSales_DistinctCustomerKey

Select

                      convert(float,Count(Distinct(CustomerKey)))/Count(1)asDistinctCustomerKey

                     ,OrderDateKey

                    

  FROM[AdventureWorksDW2012].[dbo].[FactInternetSales]with (nolock)

         GroupbyOrderDateKey

         OrderbyOrderDateKey

a.        As you can see above we are also grouping the calculation by OrderDateKey so that we can use this to insert the calculation for multiple days.

4.       With our example explained above if we had to look at our calculation for our Distinct Customers on the 03 March 2008 it would be the following:

a.        clip_image002[4]

 

Updating our Fact Table with our Distinct Customer calculation

1.       The first thing is that you need to ensure that you have your column created in your Fact Table.

a.        I manually created the following column in the dbo.FactInternetSales Table:

Altertable[dbo].[FactInternetSales]

  AddDistinctCustomersFloatNULL

2.       Next is our Update TSQL Statement where we are updating the column we created in Step 1 above, with an explanation below:

Update[dbo].[FactInternetSales]

SetDistinctCustomers=DistinctCustomerKey

From[dbo].[FactInternetSales]asFwith (nolock)

       Innerjoindbo.Staging_tb_FactInterNetSales_DistinctCustomerKeyasSwith (nolock)

              onF.OrderDateKey=S.OrderDateKey

a.        From above you can see that we are joining from our Fact table to our Staging table and using the OrderDateKey in our join.

                                                               i.      NOTE: Once again this is our lowest level of granularity

3.       As with our example if we now have a look at our Fact Table for 03 March 2008 we will see the following for our DistinctCustomers column.

a.        clip_image004[4]

 

Adding the new Measure to your SSAS Cube

1.       Open up your SSAS Project in SQL Server Data Tools (SSDT)

2.       Next open the Data Source View and refresh it.

a.        As our example we opened up the Adventure Works DW.dsv

b.       Once open we clicked Refresh, which you will then see the following:

                                                               i.      clip_image006[4]

c.        Click Ok.

3.       Next open your cube where you want to add your new Distinct Measure to.

a.        As with our example we opened the Adventure Works cube

                                                               i.      clip_image008[4]

4.       Under Measures if you click on the plus sign, and next to Internet Sales, right click and select New Measure

a.        clip_image010[3]

5.       This will open the New Measure Window

a.        Now as shown below under Usage: Sum

b.       Source Table: Internet Sales Facts

c.        Source Column: DistinctCustomers

d.       clip_image012[3]

e.       Then click Ok.

6.       You will now see your measure under the Internet Sales Measure Group

a.        clip_image014[3]

b.       Now we are going to rename the Distinct Customers measure so that we can use this name in our calculation member which will be explained in the next steps.

                                                               i.      NOTE: The reason we are creating a calculation is because we need to round up our values from our database in order to make the distinct count a whole number.

c.        Right click on the Distinct Customers Measure and select rename

                                                               i.      clip_image016[3]

d.       We are renaming it to Distinct Customers – Calc

                                                               i.      NOTE: The reason that we give it this name is so that we know it is used as part of a calculation.

e.       The final thing to do is to right click on our Distinct Customers – Calc and select properties.

                                                               i.      Then change the Visible Property to False

                                                              ii.      clip_image018[3]

f.         Then save your cube.

 

Creating a calculation in SSAS and rounding the value so that it will be shown as a whole number

1.       The final step is to create our calculated member so that when we display the values to the client tool that the numbers look correct.

2.       Click on the Calculations Tab.

3.       Then click on New Calculated Member

a.        clip_image020[3]

4.       We then configured it with the following as shown below:

a.        Name: [Distinct Customers]

b.       Parent Hierarchy: Measures

c.  Expression: Round([Measures].[Distinct Customers – Calc],0)

                                                               i.      NOTE: Here we are using the Round function and setting it to not keep any decimal points by specifying the zero (0).

1.       This will also enable if the value is higher than 0.5 to round up to 1.

2.       This is so that when the Calculated Measure is displayed it will always be a whole number.

d.       Format String: Standard

e.       Visible: True

f.         Non-empty behavior: Distinct Customers – Calc

g.        Associated Measure Group: Internet Sales

h.       clip_image022[3]

5.       Now finally process your cube.

a.        NOTE: If you are using the Adventure Works MultiDimensional project in SSDT, they are by default set to Query binding for the partitions and due to this you will have to add our new column to the following Measure Groups:

                                                               i.      Part to Add:

,[dbo].[FactInternetSales].[DistinctCustomers]

                                                              ii.      Measure Group partitions:

1.       clip_image024[3]

                                                            iii.      If you do not add the column name in there the processing will fail.

 

Viewing Distinct Customer Counts

1.       Finally we now can view our new Distinct Count Measure that we created.

2.       As with our example I am expecting to see for 03 March 2008 the Distinct Customers to be 51

a.        clip_image026[3]

3.       And this was the goal of this exercise to enable to get a Distinct Count that can be used on our Dimensions using the SUM Function

 

Getting a distinct count for another dimension

If you wanted to get a distinct count for another dimension, you would then need to create another column as well as all of the steps above.

 

1.       For our above example if we wanted to get the distinct count of Currency (CurrencyKey) we would need to modify our query to include the following:

TruncateTabledbo.Staging_tb_FactInterNetSales_DistinctCustomerKey

Insertintodbo.Staging_tb_FactInterNetSales_DistinctCustomerKey

Select

                      convert(float,Count(Distinct(CustomerKey)))/Count(1)asDistinctCurrencyKey

                      ,CurrencyKey

                     ,OrderDateKey

                    

  FROM[AdventureWorksDW2012].[dbo].[FactInternetSales]with (nolock)

         GroupbyOrderDateKey,CurrencyKey

         OrderbyOrderDateKey

a.         

2.       And then we would create all the column names and details in SSAS as detailed above.

Win Free Ecopy of new book on SSAS

Readers would be pleased to know that I have teamed up with Packt Publishing to organize a Giveaway of the Expert Cube Development with SSAS Multidimensional Models

 

And two lucky winners stand a chance to win ecopy of their new book. Keep reading to find out how you can be one of the Lucky Winners.

Overview:

                              clip_image001 

 

·         Build a data mart suitable for use with Analysis Services

·         Work with a thread pool effectively

·         Create and configure an Analysis Services project in SQL Server Data Tools

·         Use the Dimension Wizard and the Dimension Editor to build dimensions

·         Create measure groups and associate them with dimensions

·         Design cubes and dimensions and also implement common calculations in MDX

·         Explore the security model, including dimension security and cell security, and implement dynamic security

·         Tune queries to get the best possible performance

·         Automate processing and partition creation

·         Monitor your cube to see who’s actually using it

 

How to Enter?

All you need to do is head on over to the book page (Expert Cube Development with SSAS Multidimensional Models) and look through the product description of the book and drop a line via the comments below this post to let us know what interests you the most about this book. It’s that simple.

Deadline

 

The contest will close on 19 March 2014. Winners will be contacted by email, so be sure to use your real email address when you comment!