Chapter 15: Analyzing Cubes using Office Client Components

We spent a good deal of time in this book exploring design and implementation options, but we haven't spent much time on the end-user experience — until now. In this chapter you learn about the many ways your aggregated data can be presented to the end user for their analysis. It is popular to use knife-wielding metaphors to represent the process of extracting information from a UDM; words like slice and dice are often applied. To slice cube data means to look at the data across some axis, such as a dimension member, and to dice data means to drill down on data by breaking it into smaller and smaller cubes. To put this all in more concrete terms, if you cut a potato longitudinally to make French fries, you are "slicing" the potato. Should you cut those strips into small cubes, you are "dicing" the potato (which, by the way, should fry up quite nicely with eggs sunny side up). Perhaps the word chiffonade, which is what one does to cabbage to make coleslaw, should be applied as well. (If you can think of what the chiffonade cut should mean in the context of business analysis, please alert the authors.)

In the sections that follow, typical usage scenarios in Excel using pivot tables are discussed. Not only will you find that you can connect to Analysis Services directly from Excel, you can even create offline cubes with any data in your spreadsheet which can be used for analysis without interaction with Analysis Services. When you have the feature set of Analysis Services complemented by the analytical tools in Excel, it makes for a great combination. With this feature set, you can analyze data; you can build reports off the data; not to mention build charts and graphs from any view you formulate. As a developer of business intelligence solutions, you will likely do much of your proof-of-concept browsing within BIDS, but the real consumers of your work will likely be browsing using Excel or Office Web Components, both of which include pivot table browsing functionality. All the examples mentioned in this Chapter use Office 2003 and Data Analyzer which is a client tool to analyze data from Analysis Services.

Microsoft Excel Pivot Tables

If you have been using Excel you might be familiar with the pivot table feature, especially considering that pivot tables date back to Excel Version 5. The pivot table feature in Excel is used to create reports for Excel users, which help them analyze data with ease. The pivot table feature can work on data stored in Excel or some other data source that can be accessed by Excel. The only requirement to use the pivot table in Excel with Analysis Services is that Excel should connect the Analysis Services instance. In such a case, Analysis Services becomes a data source for Excel. However there is a tight integration between Excel and Analysis Services. Excel is well aware of the Analysis Services models and objects and presents them effectively to the end users. As you might expect, creating a pivot table can be accomplished through the use of a wizard. The wizard has some smarts to it and even creates what it calculates to be the best resulting layout.

As for the capabilities of a pivot table, they are similar in nature to the cube browser seen in BIDS where you can drag and drop dimensions and measures to analyze the data. You can analyze data using Excel pivot tables in a similar fashion. Not only can you arrange data to best surface the information contained in it, but also the pivot table technology will sum the appropriate columns for you automatically. It is quite common for people to construct pivot tables to some planned configuration that is suited to act as a foundation for building charts and graphs. Charting and graphing capability comes with Excel off the shelf, so you don't need to buy any additional software to exploit a pivot table in this way.


Why is it called a pivot table? Why not call it what it really is, a digital fulcrum for tabulated compilations? Let's deconstruct the name: first, to "pivot" means to swivel and second, "table" refers to the form of representation for the data, in this case tabular with rows and columns. Therefore, the name suggests the user can swivel or pivot on data that is tabular in nature. In simplest possible terms, it is a way to display data such that seeing how different columns interact with each other is very easy to arrange and view.

Creating a Pivot Table against Analysis Services Data

As you can see, the pivot table helps you to view multidimensional data in a two-dimensional nested tabular form. Normally, you will find that users only pivot on two or three dimensions at a time while engaged in data analysis. The reason for this is simple: if you add fourth and fifth dimensions to a pivot table, the results become so complex as to hinder understanding. The pivot table client component helps you visualize the interactions between dimensions, and if used properly will, in fact, facilitate insights about the nature of the results being analyzed. To create a pivot table using Analysis Services data as the source to populate the table, work through the following steps.

  1. Deploy the Adventure Works sample project shipped along with Analysis Services 2005. It is available for install with SQL Server 2005 if you don't have it loaded already.

  2. Launch Excel and create a new worksheet. Click the Data menu item and select Pivot table and Pivot Chart Report You will now see the Pivot Table and Pivot Chart Wizard, as shown in Figure 15-1.

    image from book
    Figure 15-1

You can create pivot tables from different data sources including data within an Excel sheet. In this example you will retrieve data from Analysis Services 2005. To specify the Analysis Server, select External data source and click Next.

  1. To specify the Analysis Server, click the Get Data button as shown in Figure 15-2.

    image from book
    Figure 15-2

  2. You are now in the Choose Data Source dialog. Click the OLAP Cubes tab as shown in Figure 15-3. If you have established connections to Analysis Services you will see those connections here. Double click on <New Data Source>.

    image from book
    Figure 15-3

  3. At the Create New Data Source dialog shown in Figure 15-4, provide a name for the data source you will establish (we recommend "AdventureWorks"). This is not the actual server name but a name that will be shown under OLAP Cubes tab. In the drop down list 2, select Microsoft OLE DB Provider for Analysis Services 9.0. Finally, click the Connect button.

    image from book
    Figure 15-4

  4. In the Multidimensional Connection 9.0 dialog you can either connect to an Analysis Services instance or connect to a file that contains data for a specific cube. You learn to create cube files later in this chapter. Provide the name of the Analysis Services as shown in Figure 15-5 and click Next.s

    image from book
    Figure 15-5

  5. You will now see the list of databases available on the Analysis Services instance. Select the Adventure Works DW database as shown in Figure 15-6 and click Finish.

    image from book
    Figure 15-6

  6. The wizard establishes a connection to the Analysis Services instance, retrieves the list of cubes available within the chosen database, and makes them available in the drop-down list box of the New Data Source dialog (see Figure 15-7). Select the Direct Sales cube and click OK.

    image from book
    Figure 15-7

  7. At this point the connection details for the database and cube are stored in a file named AdventureWorks.oqy, which is located under <DefaultDrive>:\Documents and Settings\<UserName>\Application Data\Microsoft\Queries. You can check out that file, just substitute the correct information for Default Drive and UserName appropriately for your configuration. If you open the .oqy file you find the connection details to the Analysis Server. The AdventureWorks.oqy file contains the following details:

         QueryType=OLEDB     Version=1     CommandType=Cube     Connection=Provider=MSOLAP.3;Cache Authentication=False;Integrated     Security=SSPI;User ID="";Initial Catalog=Adventure Works DW;Data     Source=localhost;Impersonation     Level=Impersonate;Location=localhost;Mode=ReadWrite;Protection Level=Pkt     Privacy;Auto Synch Period=20000;Default Isolation Mode=0;Default MDX Visual     Mode=0;MDX Compatibility=0;MDX Unique Name Style=0;Non Empty     Threshold=0;SQLQueryMode=Calculated;Safety Options=1;Secured Cell Value=0;SQL     Compatibility=0;Compression Level=0;Real Time Olap=False;Packet Size=4096     CommandText=Direct Sales 

    The details of the connection to Analysis Server are represented by the name Connection= and the CommandText contains the name of the cube or perspective you chose.

  8. Click OK to dismiss Choose Data Source and then Next on the resulting page. What appears is the final step of the Pivot Table Wizard. You need to specify if you want the pivot table to be formed in a new worksheet or existing working sheet (the default value) at a specific location. Select the default value as shown in Figure 15-8 and click Finish.

image from book
Figure 15-8

You will now see the pivot table created within the existing worksheet as shown in Figure 15-9. There is a window called the Pivot Table that contains the various options that can be set for the pivot table. Another window called PivotTable Field List shows the list of hierarchies and measures from the selected cube, all of which is the data that can be analyzed. Finally you have the main pivot table, which contains an area for holding row fields, column fields, page fields, and data items. As with the cube browser, the data items can only hold the measure values. The row, column, and page area can only hold members of hierarchies.

image from book
Figure 15-9

Each object shown in the PivotTable Field List that is collapsible or expandable (shown with a + sign) represents a hierarchy in the cube. If you expand the object you will be able to find out if it is an attribute hierarchy or a multilevel hierarchy. If you create a pivot table against Analysis Services 2000 data, you will see each dimension object is represented as an item in the pivot table list; by expanding the dimension you will see the level. There was exactly a one-to-one correspondence between the objects shown in PivotTable Field List to the dimensions in the cube if you used Analysis Services 2000. Analysis Services 2005, on the other hand, has introduced the notion of hierarchies (attribute and multilevel) within a dimension. Excel 2003 does not understand the notion of multiple hierarchies within a single dimension. It would have certainly been much easier for the user if the hierarchies grouped within the dimension were similar to the metadata pane in the cube browser. However, it does allow you to browse each of the hierarchies. In the cube browser you see the measures grouped based on the display folders. Excel 2003 does not understand display folders because this is a new feature, and hence shows all the measures available in the cube. The next version of Microsoft Office (Excel in particular) is expected to have improvements that will present the dimensions in Analysis Services 2005 in an efficient way. The new pivot table will allow users to more easily browse the dimensions and measures of Analysis Services 2005 cubes.

Figure 15-10 shows the two types of hierarchies in the Customer dimension; one is a multilevel hierarchy and another is an attribute hierarchy, which have been expanded in the list.

image from book
Figure 15-10

You can see the levels for each of these hierarchies. Attribute hierarchies can be easily identified because they are one level deep, whereas multilevels are almost always several levels deep. If there is a multilevel hierarchy with a single level, you can distinguish them easily because the name of the hierarchy and level are not the same. For example, an attribute hierarchy Customer will have the name of the hierarchy shown as Customer with the same level name, as shown in Figure 15-10. If there is a multilevel hierarchy created with a level called Customer, the name of the hierarchy itself could not therefore be called Customer.

One of the drawbacks of analyzing Analysis Services 2005 cube data using Excel 2003 is the inability to distinguish hierarchies in dimensions that have the same name. For example, if you have the dimensions employee and customer, each of these dimensions can have hierarchies with the name Name or Geography. If you are designing a cube for users analyzing data using Excel 2003, we recommend you take this into consideration and create names that have full qualification so that your users are not confused as to which hierarchy to select from the Pivot Table Field List.

Analyzing Data using Pivot Tables

Having created a pivot table against Analysis Services cube data, the next logical question is how is the pivot table helpful in analyzing this data? Assume you are interested in looking at the Sales of products to customers in various countries over time. The first step for analysis is to drag and drop the hierarchies and measures of interest to the appropriate rows, columns, pages, and data areas. To start, drag and drop the measure Internet Sales Amount to the field containing the instructions "Drop Data Items Here," which you'll see if you look back at Figure 15-9. The value that you'll see in cell C4, as shown in Figure 15-11, is the total Internet Sales Amount across all dimensions. The pivot table allows you to analyze data on a two-dimensional view based on rows and columns. When there are no hierarchy members specified in the row and column field's area, the default is to show the total value.

image from book
Figure 15-11

Let's start fresh, go ahead and remove the Internet Sales Amount by dragging and dropping back to the Pivot Table List. To analyze the product Sales based on customer's countries and various years drag and drop the Customer Geography hierarchy to the "Drop Column Fields Here" area and the Date.Fiscal hierarchy to the "Drop Row Fields Here" area. And finally, drop Internet Sales Amount back on the Drop Data Items Here area. You will now see the sales amounts for various fiscal years and customer countries as shown in Figure 15-12.

image from book
Figure 15-12

The Pivot table provides you with an additional column and row that provide the subtotal along that specific row or column. To retrieve the data from Analysis Services, Excel sends the following MDX query:

     SELECT NON EMPTY HIERARCHIZE (                 AddCalculatedMembers ({                     DrillDownLevel ({[Customer].[Customer Geography].[All Customers]})}))                 DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,     NON EMPTY HIERARCHIZE (                 AddCalculatedMembers ({                     DrillDownLevel ({[Date].[Fiscal].[All Periods]})}))                 DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS FROM [Direct Sales]     WHERE ([Measures].[Internet Sales Amount]) 

Send the above query to Analysis Service instance using the MDX query editor in SQL Server Management Studio. When you execute the query you will see the results in the result pane of SSMS as shown in the following table.

All Customers





United Kingdom

United States

All Periods








FY 2002








FY 2003








FY 2004








FY 2005








You can see that the results shown in SQL Server Management Studio are identical to the results you see in the Excel Pivot table, but the order in which the results are presented differ. The first row and first column of the results shown in the following table are the final rows and columns you see in the Pivot table, which correspond to the aggregated data corresponding to that specific member in the dimension based on the aggregation function specified for the measure. The members All Customers and All Periods shown in the table are actually the names of the All member specified in the respective dimensions.

Having seen the MDX generated by the pivot table, we are sure you are keen on understanding the query. Similar to the MDX queries you have learned so far, Excel generates a two-dimensional MDX query that requests data on the axes columns and rows in this example. For the dimension dropped on the columns of the Pivot table (Customer Geography), the following MDX expression is specified:

     NON EMPTY        HIERARCHIZE (                     AddCalculatedMembers (                     {DrillDownLevel ({[Customer].[Customer Geography].[All Customers]})}                                         )                     ) 

Let's start with the innermost function and work our way out. Whenever you drag and drop a specific hierarchy to the Pivot table, the member in the topmost level of that hierarchy is identified and used in the query. In the previous example, the All member in the Customer Geography hierarchy, All Customers, is used. The DrillDownLevel MDX function is a function that can take multiple parameters. The Syntax of the DrillDownLevel function is as follows:

     DrillDownLevel (<Set>,{<Level>,<Index>}]) 

The first argument to the DrillDownLevel function is a Set. The second and third arguments are optional. The DrillDownLevel function returns the members of the specified Set (provided as the first argument) that are one level lower than the level of the members specified in the set. If the optional Level parameter is specified, the function returns members one level below the specified level. If the set contains tuples, the index is used to reference the dimension for which the drill down has to be applied. In the query we are currently examining, the members at the Country level of the hierarchy Customer Geography is returned. You could have retrieved the members by specifying the following MDX expression, but the DrilldownLevel function provides you with more options and is useful while drilling down to multiple levels, which is why Excel uses this function. We talk about drilling down to multiple levels later in this section.

     {[Customer].[Customer Geography].[All Customers], [Customer].[Customer     Geography].[All Customers].children} 

The result of the DrillDownLevel function is a set of members. By default during MDX evaluation the calculated members typically do not get included in the set. You would have to explicitly include them. While browsing the Pivot table Excel generates calculated members based on the user selections in the Pivot table. To make sure appropriate calculated members do get included in the result, we use the MDX function AddCalculatedMembers. Following is a simple illustration using the Measures dimension for you to understand the behavior of AddCalculatedMembers (you have learned that Measures is a special dimension within the cube).

     select measures.[Sales Amount] on 0     from [Adventure Works]                //Calculated measures not returned     select AddCalculatedMembers ( measures.[Sales Amount]) on 0     from [Adventure Works]                //Calculated measures are returned in result 

In the code illustration the first MDX query only returns the result for the Sales Amount measure; however, the second MDX query returns the Sales Amount measure and all the calculated members in the measures dimension.

Finally, the result of the AddCalculateMembers function, a Set of members, is passed as an argument to the Hierarchize function. Hierarchize is an MDX function used for sorting the members in a set. The syntax is as follows:

     Hierarchize (Set, [POST]) 

The Hierarchize function takes a Set and returns the members in the set after a sort. If the second parameter POST is not specified, the default sort ordering of the hierarchy is used to sort the members in the set. If the parameter POST is specified, the members are sorted based on the default ordering but the parent member will be at the end. This is illustrated by the following two MDX queries:

     select Hierarchize (AddCalculatedMembers (     {DrillDownLevel ({[Customer].[Customer Geography].[Country]})})) on 0,     Measures.[Internet Sales Amount] on 1     from [Adventure Works]            //MDX Query 1 - Default sort ordering     select Hierarchize (AddCalculatedMembers (     {DrillDownLevel ({[Customer].[Customer Geography].[Country]})}),POST) on 0,     Measures.[Internet Sales Amount] on 1     from [Adventure Works]            //MDX Query 2 - Sort based on POST 

The first MDX query uses the default sort ordering of the hierarchy and returns the members in Country and State levels in the format {<Country Member 1>, <State members for Country Member 1>, <Country Member 2>, <State members for Country Member 2> }. However, the second query that specified POST sorts the results as {<State members for Country Member 1>, <Country Member 1>, <State members for Country Member 2>,<Country Member 2>, }.

Hierarchize does not eliminate duplicate members.

You have learned about the first part of the MDX query and why these functions are used to retrieve the results. The keyword NON EMPTY is used to ensure empty values are eliminated in the results. To refresh your memory, here is the MDX query that is generated by Excel:

     SELECT NON EMPTY HIERARCHIZE (                         AddCalculatedMembers (                         {DrillDownLevel ({[Customer].[Customer Geography].[All Customers]})}                           )                           )     DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,     NON EMPTY HIERARCHIZE (                 AddCalculatedMembers (                         {DrillDownLevel ({[Date].[Fiscal].[All Periods]})}                 )                 )     DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS     FROM [Direct Sales] WHERE ([Measures].[Sales Amount]) 

The members being retrieved for COLUMNS and the members retrieved for ROWS use the same approach to retrieval as members in the Date-Fiscal hierarchy. As you learned earlier in Chapter 5 the member properties of dimensions members can be retrieved using Dimension Properties option on an axis. The MDX expression "Dimension Properties PARENT_UNIQUE_NAME" is used to retrieve the member property PARENT_UNIQUE_NAME; which, in turn, is returned for each member that can be used in subsequent MDX queries based on the user's actions in the Pivot table. Finally, the WHERE clause is used for not only retrieving the right measure used in the Pivot table but also restricting the multi-dimensional coordinate space so that the right values are being returned. Excel appropriately displays the result from the query within the Pivot table. Excel identifies the values for All members of each hierarchy and uses those values for subtotals along the row or column.

You have so far successfully created a pivot table and were able to analyze the Sales data for customers in various countries for various fiscal years. Assume you want to analyze the data for the United States. Because you are interested in the Sales data for customers in the United States, you are not interested in the customers from other countries. The most obvious thing is to restrict the data. To restrict the data for United States customers, click the drop-down arrow next to the hierarchy Country. You will see all the members of the Country hierarchy in Customers dimensions. The boxes next to each member act as toggle switches. If a tick mark is within the box that means the specific member is selected. To select or deselect all members, Excel provides you with a check box for (Show All). Click the box next to (Show All) to deselect all the members and then select the member United States, as shown in Figure 15-13. Click OK. You will now see the Sales data for United States for various years.

image from book
Figure 15-13

Excel generates the following MDX query when you restrict the data analysis to United States:

     SELECT NON EMPTY HIERARCHIZE (     Except ({AddCalculatedMembers (     Except (       {AddCalculatedMembers (       DrillDownLevel ({[Customer].[Customer Geography].[All Customers]}                     )                     )       },       {[Customer].[Customer Geography].[Country].&[United Kingdom],       [Customer].[Customer Geography].[Country].&[Germany],       [Customer].[Customer Geography].[Country].&[France],       [Customer].[Customer Geography].[Country].&[Canada],       [Customer].[Customer Geography].[Country].&[Australia]}       )       )},       {[Customer].[Customer Geography].[Country].&[United Kingdom],       [Customer].[Customer Geography].[Country].&[Germany],       [Customer].[Customer Geography].[Country].&[France],       [Customer].[Customer Geography].[Country].&[Canada],       [Customer].[Customer Geography].[Country].&[Australia]}))     DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,     NON EMPTY HIERARCHIZE (     AddCalculatedMembers ({DrillDownLevel ({     [Date].[Fiscal].[All Periods]})}))     DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS FROM [Direct Sales]     WHERE ([Measures].[Internet Sales Amount]) 

In this MDX query the MDX function Except is used to restrict the data to United States. The Except function takes two arguments, Set1 and Set2, and eliminates all the members in Set1 that are found in Set2. In the preceding example the AddCalculatedMembers function returns the members in the country level for Customer Geography hierarchy. This set is passed as the first parameter to the Except function. The second set is generated based on the user's de-selection of country members in the Pivot table (set containing members United Kingdom, Germany, France, Canada, and Australia). The Except function eliminates the members specified in the second set and returns only United States and the All Customers members in the result. You can view the Except function directly translating to a business question where the question would be "Show me the Sales amount for various years for customers in different countries except Australia, United Kingdom, Germany, France, and Canada." Except is one of the several MDX functions where the meaning of the word reflects its actions, so you will know exactly what the results of the function might be when it's performed. The Except function by default eliminates duplicate members in both sets before performing the operation. A third optional parameter can be used if you want to include the duplicates. You need to pass the value ALL as the third parameter if you want to retain the duplicate members in the result set. Figure 15-14 shows the results in the Pivot after you select the member United States.

image from book
Figure 15-14

In Figure 15-14 you can see that the Sales Amount shown in Grand Total is identical to the value shown for the United States member. You know the Grand Total for each year should be the aggregate of the sales amount for all of the countries. Then how is Excel able to display the Grand Total value same as that for the member United States? By default Excel sets a specific connection property called VisualTotals while connecting to Analysis Services; this results in a request for Analysis Services to send the Grand Total reflecting aggregated data for only the members currently visible (here it is just the United States). You can change this property if you want to see the Grand Total reflect the Total Sales for a specific year. In the Pivot Table window click the icon "Include Hidden Items in Totals" as shown in Figure 15-15. You will now see the Grand Total value is the aggregate of all the countries as seen earlier in Figure 15-12.

image from book
Figure 15-15

You have so far been analyzing the Sales data based on customers' geographic location and fiscal years. If you now want to analyze the data based on a certain class of products, you have two ways of doing this. Drag and drop the Product Categories hierarchy to the "Drop Page Field Here" area. Assume you want to analyze the sales data for bikes and bike accessories. Click the drop-down list next to Product Categories as shown in Figure 15-16. Click the Select multiple items check box and then select Accessories and Bikes. Click the OK button. You will now see the Sales amount in the pivot table reflecting the sales of only accessories and bikes. If you want to analyze individual members you can make the appropriate selections in the Product categories.

image from book
Figure 15-16

The second option to analyze the data for Accessories and Bikes is to drag and drop the Product Categories hierarchy to the rows adjacent to the Fiscal Year. When you had Product categories in the Page Fields area the pivot table showed the "All" members of the Product Categories hierarchy. However, while dropping the product categories on rows you will see the first level of the Product Categories hierarchy and its members will be visible in the pivot table. Figure 15-17 shows the results of the Pivot table.

image from book
Figure 15-17

Whenever there are multiple hierarchies in rows or columns, the Pivot table creates an automatic grouping for each member in the hierarchy and creates a subtotal. In Figure 15-17 you can see a row showing the subtotal for each year. Because you wanted to analyze the data for Accessories and Bikes, you need to restrict the visible members. Select the drop-down list next to category, deselect the Show All checkbox, and then select the members Accessories and Bikes. You will now see the pivot table only showing the results of Accessories and Bikes as shown in Figure 15-18.

image from book
Figure 15-18

The MDX query generated by Excel to retrieve the results shown in Figure 15-18 is shown below.

     SELECT NON EMPTY HIERARCHIZE (         Except ({         AddCalculatedMembers (             Except ({                 AddCalculatedMembers (                 DrillDownLevel ({[Customer].[Customer Geography].[All     Customers]}))},             {[Customer].[Customer Geography].[Country].&[United Kingdom],             [Customer].[Customer Geography].[Country].&[Germany],             [Customer].[Customer Geography].[Country].&[France],             [Customer].[Customer Geography].[Country].&[Canada],             [Customer].[Customer Geography].[Country].&[Australia]}))},     {[Customer].[Customer Geography].[Country].&[United Kingdom],         [Customer].[Customer Geography].[Country].&[Germany],         [Customer].[Customer Geography].[Country].&[France],         [Customer].[Customer Geography].[Country].&[Canada],         [Customer].[Customer Geography].[Country].&[Australia]}))     DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,     NON EMPTY CROSSJOIN (       HIERARCHIZE (           AddCalculatedMembers ({              DrillDownLevel ({[Date].[Fiscal].[All Periods]})})),                 HIERARCHIZE (                     Except ({AddCalculatedMembers (                     Except ({AddCalculatedMembers (                         DrillDownLevel ({[Product].[Product Categories].[All Products]}))}                         ,                         {[Product].[Product Categories].[Category].&[2],                         [Product].[Product Categories].[Category].&[3]}))},                     {[Product].[Product Categories].[Category].&[2],                     [Product].[Product Categories].[Category].&[3]})))     DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS     FROM [Direct Sales]     WHERE ([Measures].[Internet Sales Amount]) 

Excel generates an MDX query that uses Hierarchize, Except, and AddCalculatedMembers to restrict the members on the Product Categories Hierarchy. The MDX function CROSSJOIN is used to obtain a set containing a crossjoin of the members of the hierarchies Fiscal Year and Product Categories, which are subsequently displayed on rows.

Drilling Down to Detailed Data

So far you have used the pivot table to analyze data using the axes rows, columns, and pages; you have seen how to pivot on select members and view corresponding measure values. One of the key aspects of analyzing OLAP data is not only viewing the aggregated data, but also to drill down or up to view member details as needed. Follow the steps below to drill down to detail data in pivot tables.

  1. Move the product categories hierarchy from the row axes to the page axes.

  2. To drill down to details of the customer sales within the United States, double-click the United States member. You will now see that the level State-Province is shown in the column axes and you see all the states within United States as shown in Figure 15-19. You can also achieve drill down by selecting the United States member and then clicking on the Show Detail icon in the Pivot table toolbar as shown in Figure 15-20.

    image from book
    Figure 15-19

    image from book
    Figure 15-20

  3. If you want to drill up from the current level you can once again double-click the member United States or click the member and then click the Hide Detail icon in the Pivot Table toolbar. You can choose certain members after the drill down in the pivot table. Select the drop-down list next to the Country level and select the members Alabama, Arizona, and California. You can drill down on members on each axes. On the row axes, drill down on Fiscal Year up to the Month level by double-clicking the first member in each level. Your Pivot table will show the sales amount for three states within the United States for various fiscal years, with detailed data for the months in the first quarter of fiscal year 2002 as shown in Figure 15-21.

    image from book
    Figure 15-21

  4. Click the drop-down list next to the fiscal year. You will see the members of the Fiscal hierarchy as shown in Figure 15-22. The checkboxes act to enable or disable members. In addition, you can see a third option that contains two tick marks. These tick marks indicate that the member has not only been selected but the user has also drilled down to see the details of its children. If at least one of the children of a member in a hierarchy is made visible, you will see the double tick mark for that member in this dialog. You can also use this dialog to drill down to the details of a member since the check box next to a member is actually a tri-stated option. The three options indicated by the check box are:

    • Member not visible (no tick mark)

    • Member is visible (single tick mark)

    • Member is visible along with at least one of its children (two tick marks)

image from book
Figure 15-22

The MDX query generated to see the results shown in Figure 15-21 is quite long because the query needs to exclude all the states in the United States except Arizona, Alabama, and California. In order to understand the type of MDX query sent by Excel while drilling down to member details, we will use an example where you select all the countries in the column axes and apply drill down only for the first fiscal quarter of 2002. The following MDX query is sent to the Analysis Services to retrieve the results shown in Figure 15-21.

     SELECT NON EMPTY HIERARCHIZE (         AddCalculatedMembers ({             DrillDownLevel ({[Customer].[Customer Geography].[All Customers]})}                 ) // AddCalculatedMembers             ) //Hierarchize     DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS ,     NON EMPTY HIERARCHIZE (         AddCalculatedMembers (             {DrillDownMember ({{                 DrillDownMember ({                     DrillDownLevel ({[Date].[Fiscal].[All Periods]}                                   ) //DrillDownLevel                                   },                           {[Date].[Fiscal].[Fiscal Year].&[2002]}) //DrillDownMember                                 }},                         {[Date].[Fiscal].[Fiscal Semester].&[2002]&[1]}                         ) //DrillDownMember                         }                      ) //AddCalculatedMembers                 ) //Hierarchize      DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON ROWS     FROM [Direct Sales]     WHERE ([Measures].[Internet Sales Amount],     [Product].[Product Categories].[All Products]) 

There is a MDX function in the query called DrillDownMember. The function DrillDownMember takes two sets — Set1 and Set2 — as parameters and returns a set that contains the drill down of members in Set1 that are included in Set2. You can see that the innermost DrillDownMember is called with the sets DrillDownLevel ({[Date].[Fiscal].[All Periods]})} and {[Date].[Fiscal].Fiscal Year].&[2002]}. The first parameter is another MDX function that returns all the members in the Year level. [Date].[Fiscal].Fiscal Year].&[2002] is one of the members in the first set and hence a drill down on this member will result in the members "H1 FY 2002" and "H2 FY 2002." Subsequent DrillDownMember functions drill down on the first half of fiscal year 2002 and the first quarter of fiscal year 2002. There is an optional third parameter for the DrillDownLevel MDX function which takes the flag RECURSIVE. This prompts a recursive drill down on members in Set1 based on the members in Set2. You can understand the behavior of the RECURSIVE flag by looking at the results of the following MDX queries:

     select DrillDownMember ({                            DrillDownLevel ( {[Date].[Fiscal].[All Periods]})                           },                            {[Date].[Fiscal].[Fiscal Year].&[2002],                             [Date].[Fiscal].[Fiscal Semester].&[2002]&[1],                             [Date].[Fiscal].[Fiscal Quarter].&[2002]&[1]}                             ,RECURSIVE) on 0     FROM [Sales Summary]     WHERE ([Measures].[Sales Amount], [Product].[Product Categories].[All])     select DrillDownMember ({                           DrillDownLevel ( {[Date].[Fiscal].[All Periods]})                          },                           {[Date].[Fiscal].[Fiscal Year].&[2002],                            [Date].[Fiscal].[Fiscal Semester].&[2002]&[1],                            [Date].[Fiscal].[Fiscal Quarter].&[2002]&[1]}                          ) on 0     FROM [Sales Summary]     WHERE ([Measures].[Sales Amount], [Product].[Product Categories].[All]) 

Execute the above queries in SSMS to see the results of the queries. The first query returns all the months of the first quarter of fiscal year 2002, but the second query only returns the members in the semester level for the fiscal year 2002. You have now successfully learned to drill down to details in your pivot table report.

Viewing Multiple Measures with Your Pivot Table

So far you have been analyzing a single measure within your pivot table. As an executive making financial decisions, a user might need information concerning additional measures — for example, to analyze sales along with the initial targets or to analyze the budgeted cost versus actual cost. In the cube browser within BIDS you were able to select multiple measures. However, within a pivot table you cannot have more than one measure in the data area. In this section, you learn how to analyze multiple measures within a Pivot table.

Drill up to the fiscal year level on the fiscal hierarchy from the last example. Assume you want to see the quantity ordered along with the sales amount in your Pivot table. Drag and drop the measure Internet Order Count from the Pivot table field list into the data area. The pivot table now creates a new hierarchy called "Data" on the row axes, and adds the two measures Sales Amount and Order Count as members in this hierarchy. Corresponding values in the data area show the values for these measures. This is shown in Figure 15-23. You can select or deselect the members within the Data hierarchy. The Pivot table creates two Total rows along the row axes corresponding to the two measures Sales Amount and Order Count.

image from book
Figure 15-23

Custom Grouping within a Pivot Table

If any word in the English language is overused, it is the word "cool" — but custom groupings are cool and must be described as such! When you analyze the results within Pivot table you might want to group certain members and analyze the data for those specific members. For example, if you have sales data for all the countries you might want to analyze the sales based on continents. If continent is not a level in the hierarchy then you might have to modify the cube design and add this information. Instead Pivot table helps you to group members and provide a name. It is important to know that Grouping functionality in Pivot Table is supported in SQL Server 2005 Service Pack 1 and beyond. Follow the steps below to create custom groups within pivot tables.

  1. Create a Pivot Table by connecting to the Direct Sales cube in Adventure Works DW database.

  2. Drag and drop Internet Sales Amount to the Data area, Customer Geography hierarchy to Rows, and Ship Date Calendar hierarchy to Columns.

  3. You will see all the countries in the Customer Geography hierarchy in your pivot table. Assume you want to group the countries within the same continent. Multi-select France, Germany, and United Kingdom countries by holding the Ctrl key and selecting the members with the mouse. In the Pivot Table window select Pivot TableGroup and Show DetailGroup as shown in Figure 15-24. You can also group the members on a hierarchy by selecting the members, right click, and then select Group and Show DetailGroup.

    image from book
    Figure 15-24

  4. The members France, Germany, and United Kingdom will be grouped together under a new member called Group 1 as shown in Figure 15-25. Sub Total for the member Group 1 is also created within the pivot table. Select members Canada and United States and Group them. You will now see countries Canada and United States are grouped under a member called Group 2. Australia is under a member called Other.

    image from book
    Figure 15-25

  5. Click on the member Group 1 and rename it as Europe. Similarly rename Group 2 as North America and Other as Australia. Your pivot table should look like Figure 15-26. You can see that the totals for the groups are qualified by the new name you have provided.

image from book
Figure 15-26

Thus the grouping feature in Excel helps you to group members in a hierarchy and define a group name that makes it easy to analyze the data. Since the totals are created for the groups for the end user the group name will appear to be a level in the hierarchy which they can drill down or drill up. If you click on the drop-down list for Customer Geography you can see the new members created due to custom grouping along with the members in the Country level as shown in Figure 15-27. If you double-click on the group member you will see that the double-click acts as a toggle switch to hide or show details in the next level. For example if you double-click on Europe you will see the members France, Germany, and United Kingdom are hidden if they are visible and vice versa.

image from book
Figure 15-27

You can create a custom group of existing groups. For example you can group Europe and North America as Northern Hemisphere. Hence Grouping in Excel provides you the flexibility to do multiple levels of grouping which is really useful when there are several members in a hierarchy and you want to perform data analysis by grouping the members into several groups. You can ungroup a group of members by right-clicking on the group name and selecting Group and Show DetailUngroup.

It is important to know the limitations of grouping hierarchy members in Excel Pivot tables. You cannot group members of a parent-child hierarchy and hierarchies of ROLAP dimension using Excel Pivot tables. Having learnt to use various options in the pivot table for data analysis you will learn to present the data to end users as formatted reports in the next section.

Formatting Your Pivot Table Report

The Pivot table is one of the report formats in which data is shown to the end users. As with any reporting tool, you need the ability to format the report based on end-users' needs. The Pivot table provides you several formatting options; principally, you have the ability to format individual cell values in the data, row, or column areas because these are just cells in an Excel spread sheet. You have been analyzing the sales amount and you have not seen any kind of currency associated with the numbers in the data area. You can select all the cells in the data area, right-click, select Format Cells, and choose the currency formatting. Alternatively, you can right-click the cell Internet Sales Amount, select Format Cells, and select the Currency option in the Category list; any formatting changes here will be applied to all the cells in the data area. The formatting options available to any cell in Excel such as alignment, font, border, and patterns can be applied to the cells in the pivot table.

The Pivot table provides you additional formatting options as shown in Figure 15-28. To launch the Pivot Table Options page, click the Pivot table drop-down in the Pivot table toolbar and select Table Options. Most of these options are self-explanatory. You can enable or disable totals for rows and columns, center the labels of the members, and show different values in the report whenever empty cells are retrieved from Analysis Services. We leave the exercise of exploring these options and the impact on the report as an exercise for the reader.

image from book
Figure 15-28

Excel Pivot tables provide you with various kinds of layouts for your report. Certain standard templates can be applied to your pivot table report that will enhance the look and feel for your end users. To select a specific template, click the drop-down Pivot table in the Pivot table toolbar and select Format Report. You will see the Auto format page shown in Figure 15-29.

image from book
Figure 15-29

You can browse through the options in the auto format templates that are made available to you within Excel. If you choose a specific template, your existing Pivot table report is automatically formatted to the style chosen. Explore the various report and table options available in the auto format dialog so that you can present cool reports at no additional cost to your end users and look like a hero to your boss!

Creating Pivot Chart Reports

Reports generated through the Pivot table present the information in a way that is easily comprehensible to end users. However, there is an even better way to represent certain types of data to end users by using the Pivot charts feature. Charts are a good way to convey information to users, especially while comparing and analyzing data. Pivot charts allow you to represent the data within a pivot table in a graphical way to the end users with or without the actual numeric values. Pivot charts are always connected to a specific pivot table and the values within the pivot table. The following instructions show you how to create a pivot chart.

  1. Create a pivot table to Adventure Works cube of Adventure Works DW database by creating a new connection. Drag and Drop Date.Fiscal on the row axes, the Customer Geography hierarchy on the column axes, the Product Categories hierarchy on the Page axes, and the Internet Sales Amount on the data area.

  2. Click the drop-down Pivot table in the Pivot table toolbar and select Pivot Chart or click the chart icon in the Pivot table toolbar. A new data sheet is created in Excel that represents the data shown in the pivot table, as shown in Figure 15-30. You do have the pivot table toolbar available within the pivot chart. The hierarchy on rows is represented on the X-axis of the chart. The sales amount corresponding to a specific year is shown as a bar graph. The members of the customer geography hierarchy are represented as a legend, and the sales amount for various years for a specific country are shown in the same color. The Internet Sales Amount representing a specific year is shown in different colors proportional to the value for various countries. The total Internet Sales Amount indicated as a bar is the total Internet Sales Amount for a specific year. Similar to the drop-down for hierarchies on page axes in a pivot table, you can select specific product categories to analyze the Internet Sales Amount for a specific product or products. If you want to see the Internet Sales Amount based on countries rather than Fiscal Years, you can interchange the levels of the Country and Fiscal Year of the hierarchies Customer Geography and Date.Fiscal respectively. To do so you need to drag and drop Country from legend to X-axis and Fiscal Year from X-axis to legend. Any changes made within the pivot chart are automatically reflected in the pivot table.

    image from book
    Figure 15-30

  3. If you want to drill down to details for a specific year, you need to click the Fiscal Year level and then click the show detail icon in the pivot table toolbar. You will now see the drill down data for the second level of the Date.Fiscal hierarchy as shown in Figure 15-31.

    image from book
    Figure 15-31

  4. Pivot charts facilitate visualization of data in three dimensions. Click the chart wizard icon in the chart pivot table to select the chart type. Select the three-dimensional bar chart. You will now see the sales data in a three-dimensional view. Click Next in the Chart Wizard.

  5. Some end users prefer to see the graphical representation of the data in the chart along with the numerical data embedded within the same report. The Pivot chart allows you to display the data within a table beneath the chart. In the Chart Wizard - Chart Options click the Data Table tab as shown in Figure 15-32 and select the option to "show the data table" to display the numeric values of the pivot table with the pivot chart; then click Finish.

    image from book
    Figure 15-32

Typically, end users print the charts for meetings or use them in power presentations. In order to remove the axis information, click the Pivot table toolbar and under the Pivot Chart drop-down select menu item "Hide PivotChart Field Buttons." You will see the pivot chart containing a three-dimensional view of the sales data along with the pivot table as shown in Figure 15-33.

image from book
Figure 15-33

Professional SQL Server Analysis Services 2005 with MDX
Professional SQL Server Analysis Services 2005 with MDX (Programmer to Programmer)
ISBN: 0764579185
EAN: 2147483647
Year: 2004
Pages: 176

Similar book on Amazon © 2008-2017.
If you may any questions please contact us: