Microsoft Business Intelligence Tools for Excel Analysts (2014)
PART II: Leveraging SQL for Business Intelligence
Chapter 13: Browsing Analysis Services OLAP Cubes with Excel
In This Chapter
· Understanding Analysis Services OLAP cubes
· Connecting to an OLAP data source
· Creating offline cubes
· Using cube functions
· Adding calculations to your OLAP PivotTables
Since the release of SQL Server OLAP 7.0, Microsoft Analysis Services has proven to be an analytics workhorse that has taken and maintained the lead position in the market for OLAP (online analytical processing) databases. Many of the leading analytics and data visualization tools (including Excel) provide native connectivity with Analysis Services, providing interaction with a very rich and responsive data store via end-user data tools. Most importantly, there is a significant number of Analysis Services installations across businesses that are handling reporting, analytics, budgeting, and planning on a day-to-day basis.
But with all its capabilities, Analysis Services is complex and can present you with a significant learning curve. Though it serves as a back-end data platform for dashboards and PivotTables, Analysis Services is not well understood by many end users, remaining a platform and set of tools best understood by database developers. Even though these tools are mostly used by database developers, you can benefit from learning how they work.
This chapter provides a solid conceptual understanding of what Analysis Services is capable of. This knowledge not only enhances your abilities when it comes to the ongoing, daily analytics tasks you perform, but just as important, it significantly enhances your abilities as a team member or leader of a team focused on business analytics.
You can find the example file for this chapter on this book’s companion Web site at www.wiley.com/go/bitools in the workbook named Chapter 13 Samples.xlsx.
What Is an OLAP Database and What Can It Do?
The dominant database type in most organizations is the OLTP (online transaction processing) database. Indeed, most of you are probably working some form of an OLTP database. This type of database typically contains many tables, each table usually contains multiple relationships with other tables, and records within any given table can be routinely added, deleted, or updated.
Although OLTP databases are effective in gathering and managing data, they typically don’t make for effective data sources for reporting. There are three main reasons for this:
· Complexity: The large number of tables and relationships that can exist in an OLTP database can leave you wondering exactly which tables to join and how the tables relate to each other.
· Volume: OLTP databases normally contain individual records; lots of them, too. In order to create any number of aggregate reports and views, you would have to run views that group, aggregate, and sort records on-the-fly. The sheer volume of data in the database could very well inundate you with painfully slow reporting.
· Consistency: By its very nature, the records in a transactional database are ever-changing. Building a reporting solution on top of this type of database will inevitably lead to inconsistent results from month to month, or even from day to day.
Some organizations avoid these woes by building their reporting solutions on top of OLAP databases. OLAP databases are data islands that are isolated from the hustle and bustle of transactional databases. An OLAP database can help alleviate these problems in the following ways:
· Structured data: In an OLAP database, all of the relationships between the various data points have been predefined and stored in cubes. These cubes contain the hierarchical structures that allow for the easy navigation of available data dimensions and measures. With this configuration, you no longer have to create joins or try to guess how one data table relates to another. All of that complexity is taken care of behind the scenes, leaving you free to develop the reports you need.
· Predefined aggregations: The data in an OLAP database is not only organized, but it is aggregated. This means that grouping, sorting, and aggregations are all predefined in OLAP databases. In addition, OLAP databases make heavy use of indexes; a technique that allows a database to search for records more efficiently. All of this amounts to reporting solutions that are optimized to provided the reports you need as quickly as possible.
· Consistent results: OLAP databases contain only snapshots of data. That is, the data in an OLAP database is typically historical data that is read-only, stored solely for reporting purposes. New data is typically appended to the OLAP database on a regular basis, but the existing data is rarely edited or deleted. This allows you to retrieve consistent results when building your reporting solutions.
Understanding OLAP Cubes
The backbone of an Analysis Services database is the OLAP cube. A cube can be thought of as an analytical matrix consisting of dimensional coordinates where each coordinate contains a calculation for every unique intersection. Think of a PivotTable where the data fields in the Rows area and Columns area intersect to calculate the figures in the Values area. In an OLAP cube, the data fields are called dimensions and the calculated values are called measures.
Understanding dimensions and measures
For an analytic engine like Analysis Services to be useful, it must work with source data that can be measured or quantified in some way, possibly in many different ways. This means that the source data must contain numeric fields that can be summed or otherwise computed using a mathematical aggregate function such as Sum(), Count(), Min(), or Max(). Quantitative columns are measures, and there are countless examples of measures in the real world: revenue, cost, quantity sold, and employee count, to name a few.
Descriptive data provides context, meaning, and structure to quantifiable data. Descriptive fields are dimensions. There are countless examples of dimensions, but here are a few: date, territory, product, customer, and sales rep.
Dimensions can get more complicated than measures. This is because dimensions not only provide context and meaning to measures, but also behave like glue in terms of making the analytics system feasible. For example, dimensions can relate to measures, to other dimensions, or to themselves.
Understanding hierarchies and dimension parts
While relational databases are primarily concerned with relationships for data integrity, storage, and retrieval purposes, Analysis Services uses the relationships as a set of instructions that indicate how each node in a dimension relates to other nodes in order to form a whole structure. Put another way, Analysis Services uses data and data relationships to build hierarchies.
For example, in a Date dimension, a set of dates falls under a set of months, which falls under a set of years. Analysis Services understands the hierarchy between these nodes from a parent-child perspective, as well as the ordinal positioning of nodes.
Within a hierarchy, each data value serves as a dimension part. Dimension parts are common terms that are applied based on the context of the analysis being performed:
· Member: A member is any value within a particular dimension. In the example of a Date dimension, some members would be 2012, 2013, January, and February.
· Parent: A parent is a member that has immediate hierarchical precedence over another member; that is, it is the “hierarchical parent” of another member. For example, 2013 would be the parent of January and February. Likewise, January is shown as the parent of 1/1/2013 and 1/2/2013.
· Child: A child is the inverse of a parent.
· Level: A level is a grouping of members that falls under the same parent. In the example of the Date dimension, there is one level for Years, another level for Months, and another level for Dates.
· Ancestor(s): An ancestor has eventual (not immediate) hierarchical precedence over another member; that is, it is the “hierarchical ancestor” of another member. For example, 2013 is shown as the ancestor of 1/1/2013.
· Descendant(s): A descendant is the inverse of an ancestor.
Figure 13-1 illustrates the basic structure of a typical OLAP cube.
Figure 13-1: The basic structure of an OLAP cube.
Connecting to an OLAP Data Source
Before you can browse OLAP data, you must establish a connection to an OLAP cube. From the Data tab, select From Analysis Services from the From Other Sources drop-down menu.
The Data Connection Wizard starts, shown in Figure 13-2. This wizard allows you to configure your connection settings so Excel can establish a link to the server.
Figure 13-2: Enter your authentication information and then click Next.
The examples you see in this chapter have been created using the Analysis Services Tutorial cube that comes with SQL Server Analysis Services 2012.
Follow these steps to connect to an OLAP cube:
1. Enter the name of your server as well as your username and password, as shown in Figure 13-2. Then click Next.
If you typically authenticate via Windows authentication, select the Use Windows Authentication option before clicking Next.
2. Select the database with which you are working from the drop-down menu.
As shown in Figure 13-3, the Analysis Services Tutorial database is selected for these steps.
Selecting a database causes all the available OLAP cubes to appear in the list of objects below the drop-down menu.
3. Select the cube you want to analyze and then click Next.
4. In the next screen, shown in Figure 13-4, enter descriptive information about the connection you've just created.
Figure 13-3: Specify your database and then select the OLAP cube you want to analyze.
All the fields in the screen shown in Figure 13-4 are optional. You can bypass this screen without doing anything, and your connection will work fine.
Figure 13-4: Enter descriptive information for your connection.
5. Click Finish to finalize your connection settings.
The Import Data dialog box opens, as shown in Figure 13-5.
6. Select PivotTable Report and then click OK to build your PivotTable.
Figure 13-5: When your connection is finalized, you build your PivotTable.
After building your PivotTable, you see measures (represented by the Sigma icon), dimensions (represented by a table icon), hierarchies, and levels. Figure 13-6 illustrates what the PivotTable Fields list for an OLAP PivotTable might look like.
Figure 13-6: You can navigate through the OLAP cube with ease using your PivotTable Fields list.
Understanding the Limitations of OLAP PivotTables
For the most part, PivotTables that are based on OLAP data sources look, feel, and act like standard PivotTables.
An OLAP data source is ultimately controlled by the database administrator, who is responsible for maintaining the Analysis Services server. That control encompasses every aspect of the OLAP cube’s behavior, from the dimensions and measures included in the cube to the ability to drill into the details of a dimension. As the consumer of the OLAP data source, you have limited control on how the OLAP cube ultimately looks and feels.
This limited control translates into some limitations to the actions you can take with your OLAP-based PivotTables. You should take these limitations into account before moving forward with an OLAP-based reporting solution.
When your PivotTable report is based on an OLAP data source
· You cannot place any field other than measures into the Values area of the PivotTable.
· You cannot change the function used to summarize a data field.
· The Show Report Filter Pages command is disabled.
· The Show Items with No Data option is disabled.
· The Subtotal Hidden Page Items setting is disabled.
· The Background Query option is not available.
· Double-clicking in the Values field returns only the first 1,000 records of the pivot cache.
· The Optimize Memory check box in the PivotTable Options dialog box is disabled.
Creating Offline Cubes
With a standard PivotTable, the source data is typically stored on your local drive. This way, you can work with and analyze your data while disconnected from the network. However, this is not the case with OLAP PivotTables. With an OLAP PivotTable, the pivot cache is never brought to your local drive. This means that while you're disconnected from the network, your PivotTable is out of commission. You can't even move a field while disconnected.
Offline cubes are files that locally store portions of the source data found in an OLAP data source for browsing while you're disconnected from the network. These types of cubes are useful when you need to distribute reporting solutions to clients who do not have access to your network, or clients for whom network access is extremely slow.
To create an offline cube, start with an OLAP-based PivotTable and follow these steps:
1. Position your cursor anywhere inside the PivotTable and, from the OLAP Tools drop-down menu on the PivotTable Tools Analyze tab, select Offline OLAP.
2. From the Offline OLAP Settings dialog box, click the Create Offline Data File button.
3. In the Create Cube File Wizard, click Next.
4. Select the dimensions and levels you want included in your offline cube; see Figure 13-7. Then click Next.
This is the data you want to import from the OLAP database. Select only the dimensions that you need available to you while disconnected from the server.
The more dimensions you select, the more disk space your offline cube file takes up.
Figure 13-7: Select the dimensions and level you want included in your offline cube.
5. Filter any members or data items you do not want to include; see Figure 13-8. Then click Next.
For example, the Extended Amount measure is not needed, so deselect that check box. This ensures that this measure will not be imported and will not take up unnecessary disk space.
Figure 13-8: Deselect any members you do not need to see offline.
6. Specify a name and location for your cube file. Click Finish when you’re done.
The file extension for all offline cubes is .cub. In Figure 13-9, the cube file is named MyOfflineCube.cub, and it’s placed in your chosen directory.
Figure 13-9: Specify a name and location for your cube file.
After a few moments of crunching, Excel outputs your offline cube file to your chosen directory. Double-click the file and the Excel workbook opens that is linked to the offline cube via a PivotTable.
After your offline cube file has been created, you can distribute it to others and use it while you're disconnected from the network.
When you're connected to the network, you can open your offline cube file and refresh the PivotTable to get updated data.
Using Cube Functions
Cube functions are Excel functions that can be used to access OLAP data outside a PivotTable object. In pre-2010 versions of Excel, you could find cube functions only if you installed the Analysis Services Add-In. In Excel 2010, cube functions were brought into the native Excel environment.
One of the easiest ways to start exploring cube functions is to allow Excel to convert your OLAP-based PivotTable into cube formulas. Converting a PivotTable to cube formulas is an easy way to create a few cube formulas without doing any of the work yourself. Excel replaces all the cells in the PivotTable with a formula that connects back to the OLAP database. Figure 13-10 shows a PivotTable connected to an OLAP database.
Figure 13-10: A normal OLAP PivotTable.
With just a few clicks, you can convert any OLAP PivotTable into a series of cube formulas. Place the cursor anywhere inside the PivotTable and select Convert to Formulas from the OLAP Tools drop-down menu on the Analyze tab.
If your PivotTable contains a report filter field, the message box in Figure 13-11 appears. This dialog box gives you the option of converting your filter drop-down selectors to cube formulas. If you select the Convert Report Filters check box, the drop-down selectors are removed, leaving a static formula. If you need to have your filter drop-down selectors intact so that you can continue to interactively change the selections in the filter field, deselect the Convert Report Filters check box.
If you're working with a PivotTable in compatibility mode, Excel automatically converts the filter fields to formulas.
Figure 13-11: Excel gives you the option of converting your filter fields.
After a second or two, the cells that used to house a PivotTable are now homes for cube formulas. Note that, as shown in Figure 13-12, any styles you may have applied are removed. The formula bar tips you off that these cells are now cube formulas: the formulas start with =CUBEVALUE.
Figure 13-12: The formula bar shows these cells are now a series of cube formulas.
So why is this capability useful? Well, now that the values you see are no longer part of a PivotTable object, you can insert rows and columns, add your own calculations, combine the data with other external data, and modify the report in all sorts of ways by simply moving the formulas around.
Adding Calculations to Your OLAP PivotTables
In previous versions of Excel, OLAP PivotTables were limited in that you couldn’t build your own calculations with the OLAP data. This means you could not add that extra layer of analysis like you could with the calculated fields and calculated items functionality found in standard PivotTables.
Excel 2013 changes that with the introduction of the new OLAP tools: calculated measures and calculated members. With these two new tools, you can add your own analysis by building your own calculations.
In this section you explore how to build your own calculated measures and calculated members.
A word about MDX
When you are using a PivotTable with an OLAP cube, you’re sending the OLAP database MDX (multidimensional expressions) queries. MDX is an expression language that is used to return data from multidimensional data sources (that is, OLAP cubes).
As your OLAP PivotTable is refreshed or changed, subsequent MDX queries are passed to the OLAP database. The results of the query are sent back to Excel and displayed through the PivotTable. This is how you can work with OLAP data without a local copy of a pivot cache.
When building calculated measures and calculated members, you need to utilize MDX syntax. This is the only way the PivotTable can communicate your calculation to the back-end OLAP database.
The examples in this chapter use basic MDX constructs to demonstrate the functionality found in Excel 2013. If you need to create complex calculated measures and calculated members, you need to learn MDX.
That said, the topic of MDX is robust and beyond the scope of this book. If after reading this section, you want to learn more about MDX, consider picking up MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase by George Spofford (John Wiley & Sons); an excellent guide to MDX.
Creating calculated measures
A calculated measure is essentially the OLAP version of a calculated field. A calculated measure creates a new data field based on some mathematical operation that uses the existing OLAP fields.
Figure 13-13 shows an OLAP PivotTable containing products along with their respective quantities and revenues. It needs a measure that calculates average sales price per unit.
Figure 13-13: To show average sales price per unit, you need a calculated measure.
Place your cursor anywhere in the PivotTable and follow these steps:
1. Select MDX Calculated Measure from the OLAP Tools drop-down menu on the Analyze tab.
The New Calculated Measure dialog box opens, as shown in Figure 13-14.
Figure 13-14: Use the New Calculated Measure dialog box to build your calculated measure.
2. Give your calculated measure a name by entering it in the Name text box.
3. Click the Measure Group drop-down menu and select the group you want to place your calculated measure in.
If you don’t choose one, Excel automatically places your measure in the first available measure group.
4. Enter the MDX syntax for your calculation in the MDX text box.
To save time, you can use the list on the left to select the existing measures you need for your calculation. Double-click the measures needed, and Excel enters them in the MDX text box. In this example, the calculation for the average sales price isIIF([Measures].[Order Quantity] = 0,NULL,[Measures].[Sales Amount]/[Measures].[Order Quantity]).
Click the Test MDX button to check if your MDX is well formed. Excel lets you know via a message box if there is an error in your syntax.
5. Click OK.
Excel builds your defined measure and adds it to the PivotTable Fields list.
6. Select your newly created calculation from the PivotTable Fields list (see Figure 13-15).
Figure 13-15: Add your newly created calculation to your PivotTable via the PivotTable Fields list.
Your calculated measure adds a meaningful layer of analysis to the PivotTable (see Figure 13-16).
Figure 13-16: Your PivotTable now contains your calculated measure.
When you create a calculated measure, it exists in your workbook only. You're not building your calculation directly in the OLAP cube on the server. This means no one else connected to the OLAP cube can see your calculations unless you share or distribute your workbook.
Creating calculated members
A calculated member is essentially the OLAP version of a calculated item. A calculated member creates a new data item based on some mathematical operation that uses the existing OLAP members.
Figure 13-17 shows an OLAP PivotTable containing sales information for each quarter of the year. Imagine you want to aggregate quarters 1 and 2 into a new data item called First Half of Year. You also want to aggregate quarters 3 and 4 into a new data item called Second Half of Year.
Figure 13-17: You want to add new calculated members to aggregate the four quarters into First Half of Year and Second Half of Year.
Place your cursor anywhere in the PivotTable and follow these steps:
1. From the Analyze tab, select MDX Calculated Member from the OLAP Tools drop-down menu.
The New Calculated Member dialog box opens, as shown in Figure 13-18.
Figure 13-18: Use the New Calculated Member dialog box to build your calculated member.
2. Give your calculated member a name by entering it in the Name text box.
3. Click the Parent Hierarchy drop-down to select the hierarchy for which you are creating new members.
Be sure to leave the Parent Member set to All. This ensures that Excel takes into account all members in the parent hierarchy when evaluating your calculation.
4. Enter the MDX syntax for your calculation in the MDX text box.
To save time, you can use the list on the left to select the existing members you need for your calculation. Double-click the member needed, and Excel enters them into the MDX text box. In the example shown in Figure 13-18, the MDX is [Ship Date].[Calendar Quarter].[1] + [Ship Date].[Calendar Quarter].[2].
Click the Test MDX button to check if your MDX is well formed. Excel lets you know via a message box if there is an error in your syntax.
5. Click OK.
As soon as you click OK, Excel shows your newly created calculated member in the PivotTable. As shown in Figure 13-19, the calculated member is included with the other original members of the pivot field.
Figure 13-19: Excel immediately adds your calculated member to your pivot field.
Figure 13-20 shows the calculated members for the Second Half of Year.
Figure 13-20: Repeat the process for any additional calculated members.
Figure 13-21 shows the result. Excel makes no attempt at removing any of the original members. In this case, you see that quarters 1, 2, and 4 are still in the PivotTable. This may be fine for your situation, but in most scenarios, you’ll probably hide these members to avoid confusion.
Figure 13-21: Excel shows your final calculated members along with the original members.
Remember that your calculated member exists in your workbook only. No one else connected to the OLAP cube can see your calculations unless you share or distribute your workbook.
If the parent hierarchy or parent member is changed in the OLAP cube, your calculated member ceases to function. You’ll have to re-create the calculated member.
Managing your OLAP calculations
Excel provides an interface to manage the calculated measures and calculated members in your OLAP PivotTable.
Place your cursor anywhere in the PivotTable and select Manage Calculation from the OLAP Tools drop-down menu found on the Analyze tab.
In the Manage Calculations dialog box shown in Figure 13-22, you see three buttons:
· New: Create a new calculated measure or calculated member.
· Edit: Edit the selected calculation.
· Delete: Permanently delete the selected calculation.
Performing what-if analysis with OLAP data
One final piece of functionality Excel 2013 offers is the ability to perform what-if analysis with the data in OLAP PivotTables. With this new functionality, you can actually edit the values in the PivotTable and recalculate your measures and members based on your changes. You can even publish your changes back to the OLAP cube.
To use the what-if analysis functionality, create an OLAP PivotTable, then on the Analyze tab, choose What-If Analysis → Enable What-If Analysis from the OLAP Tools drop-down menu.
Figure 13-22: The Manage Calculations dialog box allows you to create a new calculation, edit an existing calculation, or delete an existing calculation.
Now you can edit the values in your PivotTable. After you have made your changes, right-click any of the changed values and select Calculate PivotTable with Change (see Figure 13-23). This forces Excel to reevaluate all the calculations in the PivotTable based on your edits; including your calculated members and measures.
Figure 13-23: Select Calculate PivotTable with Change to reevaluate all your calculations.
The edits you make to your PivotTable while in what-if analysis mode are, by default, local edits only. If you want to actually make the changes on the OLAP server, you have to tell Excel to publish your changes.
From the Analyze tab choose What-If Analysis → Publish Changes from the OLAP Tools drop-down menu. This triggers a write back to the OLAP server, meaning the edited values are sent to the source OLAP cube.
You need adequate server permissions to publish changes to the OLAP server. Your database administrator can guide you through the process of getting write access to your OLAP database.