Cube functions were introduced in Excel 2007. Data cubes are complex, multidimensional sets of data derived from raw information stored in a standard database. Cube functions are a little daunting on a technical level (they require at least Microsoft SQL Server 2005 and Microsoft SQL Server Analysis Services) and also because they include many new terms that may not be familiar.
This book tries to explain complex tasks based on a simple example. The example uses two store groups named North and South, which sold sweets (chocolate and cookies) from 2008 to 2010. Each store group consists of two stores. The sales are saved in a database and can be summarized and evaluated by using common methods. You can use views prepared in Microsoft Access or another database, use Excel data connections, or use Microsoft Query to create the query.
OLAP (online analytical processing) cubes, also known simply as cubes, offer the advantage that the data is already aggregated on the server and linked with the cube to make the information more readily available. This allows an analysis of complex sets of data.
The sales list could look like the one in Figure 2-41. Because the list repeats information for groups, stores, years, and products, it needs to be summarized.
You can prepare the summary with programming tools, such as those in Microsoft Visual Studio 2005, Visual Studio 2008, and Visual Studio 2010. To do this you have to examine the data structure and preprocess the data (see Figure 2-42).
Years, products, and stores become dimensions (cube axes), and the sales become measures. You can extend the measures as required (for example, for gross sales, average values, and other statistical values). The Key Performance Indicators (KPIs) allow you to use additional measures with different properties.
If the cube exists on the server (unlike a table in a database, it is not changed until it is regenerated) and you have access to the server, you have two options for creating the PivotTable:
Select Insert/PivotTable/Use External Data Source and then select a workbook connection.
Select Data/From Other Sources with these options:
From Analysis Services
From Microsoft Query
If you use the second option, you will be asked to insert the PivotTable afterwards. Microsoft Query also allows you to attach to offline cubes. Chapter 14, explains this in more detail.
The field list of the PivotTable in Figure 2-43 reflects the dimensions and configuration of the cube.
It is up to you to display the data you are interested in. You can select report filters and column and row headings and values (see Figure 2-44).
The cube functions can be used in two different ways:
The OLAP tools include the Convert To Formulas option (see Figure 2-45). This command converts the entire PivotTable (with the exception of the report filter). Afterwards you can still format the table as desired, but the table cannot be rearranged further.
You can manually enter formulas into the worksheet to query information from the cube.
Cube functions are not tied to the PivotTable, because a cube connection in the workbook is sufficient. The following formula returns the gross sales for cookies sold in 2008 in the NorthEast store:
=CUBEVALUE("offLine","[Measures].[GrossSales]","[Stores].[Store].[All].[NorthEast]","
[Years].[Year].[All].[2008]","[Products].[Product].[All].[Cookies]")The following formula returns the year with the highest sales for this store:
=CUBERANKEDMEMBER("offLine",CUBESET("offLine","([Stores].[Store].[All].[NorthEast],
[Years].Children)","all sales",2,"[Measures].[Sales]"),1)