Creating Local Cubes from Excel

Being busy executives, many consumers of business intelligence information want to be able to access their cubes from the most remote of locations, like from business class aboard a Boeing 747. Yes, some people actually find themselves with a laptop and no Internet connection. Once you stop shivering from the very thought of not being connected to the internet, consider this alternative: you can create what are called local cubes for your customers. With these offline cubes you can distribute an analytic environment to someone sans a network connection. In other words, these customers do not need to have access to an Analysis Services instance to see the data. Local cubes can be created directly by sending the DDL of the database or using Excel. In this chapter you will learn to create local cubes from Excel 2003. Typically, local cubes are small sections of a server cube which are distributed to the end users to analyze the data offline. Customers using local cubes can do almost all of the operations associated with online analysis. However there are some restrictions that you need to be aware of while using local cubes.

The user creating a local cube from a server cube should have the ability to drill-through to the source data so that appropriate data can be retrieved from the server cube to form the local cube. Appropriate permissions to see the source-level data is specified using a role that has specific access permissions to the cubes and dimensions in a database. Though local cubes behave similarly to a server cube in functionality, there are certain restrictions. Local cubes in Analysis Services 2005 do not have the ability to execute stored procedures (.NET assemblies as well as COM DLLs). Hence if the server cube has a stored procedure that is called while querying the cube, you would not be able to send such queries against the local cube. Because the local cube is often a section of the server cube, some of the calculations in the MDX scripts might not be able to access tuples because they are not available in the local cube. These are some things for you to consider during creation of local cubes that might be distributed to end users. Creation of local cubes from server cubes is available in SQL Server 2005 RTM version. You need to at least have SQL Server 2005 Service Pack 1 in order to create local cubes through Excel. To create a local (or "offline") cube, follow the steps below.

1. Create a pivot table on the Direct Sales perspective of the sample Analysis Services database Adventure Works DW that includes the hierarchies Customer.[Customer Geography] on columns, Date.Fiscal on rows, Products.[Product Categories] on page, and the measure Internet Sales Amount on the data area. Recall that Page is just another dimension to pivot on.

2. Click the Pivot table drop-down in the pivot table toolbar and select Offline OLAP. You will see the dialog shown in Figure 15-34. Click the Create Offline Data File button.

   
   Figure 15-34

3. Click the Next button in the welcome screen of the Create cube file dialog. You will now be in the level selection page where you choose the list of dimension hierarchies and the levels you want to be included in the offline cube file, as shown in Figure 15-35. In this dialog Excel shows the hierarchies of all the dimensions. By default Excel selects the hierarchies that are included in the pivot table. You can alternately add additional dimensions to your local cubes if you or the end users using the local cube would need them to analyze the data. Select the hierarchies Customer Geography, Date Fiscal, Destination Currency, and Product Categories and click the Next button.

   
   Figure 15-35

4. In the third page of the local cube wizard (see Figure 15-36) you can see the hierarchies that are part of the pivot table. Specific members can be selected for inclusion in the local cube, as opposed to using all of the members in the hierarchy. For example, if different employees are responsible for analyzing the data for various categories of products, you can restrict the data in the local cube for those specific products. At the time we are writing the book we believe there are still some more issues to be fixed in offline cubes. In Figure 15-36 you can notice that the top level of Customer Geography Countries is not available. Expand each level in this dialog and analyze the members selected by the wizard and click Next.

   
   Figure 15-36

5. In the final page of the offline cube wizard shown in Figure 15-37, specify a file name to store the data and click the Finish button. At this point Excel sends a request to the Analysis Services instance to create a local cube using the OLEDB provider for Analysis Services.

Figure 15-37

Shown below is the statement sent to Analysis Services instance to create an offline cube with your selections of measures and hierarchy levels. This statement can also be executed from SQL Server Management Studio.

     CREATE GLOBAL CUBE [Direct Sales] STORAGE 'D:\Direct Sales.cub’ FROM [Direct Sales]     (         MEASURE [Direct Sales].[Internet Sales Amount],         DIMENSION [Direct Sales].[Customer].[Customer Geography]         (                   LEVEL [(All)],                   LEVEL [State-Province],                   LEVEL [City]         ),         DIMENSION [Direct Sales].[Date].[Fiscal]         (                   LEVEL [(All)],                   LEVEL [Fiscal Year]         ),         DIMENSION [Direct Sales].[Product].[Product Categories]         (                   LEVEL [(All)],                   LEVEL [Category]         )     ) 

In the Offline OLAP Settings page (shown in Figure 15-38) select Offline OLAP and click OK. The operations including drill up and drill down, appropriate member selections, and so forth, all of which you did online, can also be performed offline. Once the offline cube (usually a subset of the server cube) is created, it can then be distributed to business decision-makers who do not need real-time access to the server cube.

Figure 15-38

As this Chapter is being written, the offline cubes are not yet completely functional. However, most of the steps mentioned above should be sufficient for you to create offline cubes. If there are any updates or instructions that need to be added to the content in the Offline cube section we will provide them through the download site for the book.