Creating data cubes with Excel and a database

Applies to
Microsoft Data Analyzer 2002

Data Analyzer contains a very powerful feature: flexibility. For example, you can use Data Analyzer in concert with industrial strength tools such as Microsoft SQL Server™ with Analysis Services, and you can also use it in a small home office with Microsoft Access and Microsoft Excel.

Consider this scenario: Say you run a small business. You store customer names and addresses in an Access database, and you use that information in concert with the mail-merge features of Microsoft Word to send flyers to your customers. You then gather response information from your customers and store that data in another set of tables in your database. You can then use Excel to create cubes from that data. From there, you can take a variety of actions, including analyzing the cubes with Data Analyzer and importing their data into PivotTable® reports.

This article explains how to use Excel to create a data cube from data stored in a sample Access database called Foodmart 2000. It also explains how to explore the resulting cube with Data Analyzer and export the data in the cube to a PivotTable report.

Important    If you or your IT department have a copy of SQL Server 2000 with Analysis Services, you have a copy of the Foodmart 2000 database. If you want to use another database, you can adapt these steps to fit your own data.

ShowCreating the cube

You follow this process to create a cube with Excel:

  • Choose a data source.
  • Create the query that extracts data from the database.
  • Create the cube from the extracted data. When you reach this step, keep one principle in mind: When you select data for a cube, use only the minimum amount needed to properly answer your questions. If you include too much extraneous data, Excel can take hours to process and create your cube.

The following sets of steps explain how to perform each task. The steps assume you use Excel 2000 or Excel 2002.

ShowTo select a data source

  1. Start Excel.
  2. On the Data menu, point to Import External Data, and then click New Database Query.
  3. In the Choose Data Source dialog box, click the Databases tab, select New Data Source and then click OK.
  4. In the Create New Data Source dialog box, enter a name for the data source in the first text box, select Microsoft Access Driver (*.mdb) in the second list, and then click Connect.
  5. In the ODBC Microsoft Access Setup dialog box, click Select.
  6. In the Select Database dialog box, navigate to the foodmart 2000.mdb file, and then click OK.

 Note   By default, SQL Server copies the foodmart database file to C:\Program Files\Microsoft Analysis Services\Samples\foodmart 2000.mdb.

  1. Click OK twice more to return to the Choose Data Source dialog box.

ShowTo create the query

  1. In the Choose Data Source dialog box, select the data source you created in the previous set of steps.
  2. Make sure Use the Query Wizard to create/edit queries is selected, and then click OK.
  3. In the Query Wizard - Choose Columns dialog box, select the columns of data you want in your cube. To do this:
    • Go to the Available tables and columns list, and click + to expand the table.
    • Select a column, and then click > to move the column into the Columns pane in your query list box.

For this example, select the following tables and columns:

Table Column(s)
sales_fact_1998 store_sales, store_cost, unit_sales
time_by_day the_date
product brand_name
product_class product_category, product_subcategory
customer country, state_province, city, lname
store store_country, store_state, store_city, store_name
  1.  Note   If you're using your own database, include the data from at least one fact table to provide measures for your cube, and data from one or more dimension tables, including a time dimension.

  2. Click Next, and then click Next in the next two dialog boxes.
  3. In the Query Wizard - Finish dialog box, select Create an OLAP Cube from this query and click Finish. This launches the OLAP Cube Wizard; you use the wizard to build your cube.

ShowTo create the cube

  1. Click Next in the Welcome to the OLAP Cube Wizard dialog box.
  2. In step 1 of the wizard, ensure that store_sales, store_cost, and unit_sales are selected in the Source field column.
  3. Ensure that Sum is selected for each of those fields in the Summarize by column, and then click Next.
  4. In step 2 of the wizard, drag the_date from the Source fields box to the blank dimension in the Dimensions box.
  5. Right-click the_date, click Rename, and enter Time as the name of the dimension.
  6. Clear the Week and Day check boxes under the dimension name.
  7. Drag product_category to the next available blank dimension. Drag product_subcategory to product_category, and then drag brand_name to product_subcategory.
  8. Rename the dimension to Product.

When you finish, the resulting structure looks like this:

A properly created dimension

Follow this approach when creating the dimensions for any cube.

  1. In the following order, drag country, state_province, city, and lname to the next available blank dimension.
  2. Rename the dimension to Customer.
  3. In the following order, drag store_country, store_state, store_city, and store_name to the next available blank dimension.
  4. Rename the dimension to Store, and then click Next.
  5. Select Save a cube file containing all data for the cube, enter a path and file name for the cube, and then click Finish.

Excel then uses the query file to create a cube when you open the workbook.

  1. In the Save As dialog box, enter a filename for the query definition that you just created and click Save.

Saving the query definition allows you to reuse it later. Excel saves the file with a .iqy file name extension, and the OLAP Cube Wizard creates the cube file. This may take several minutes. After it finishes, you can optionally follow to the next set of steps and create a PivotTable report.

ShowUsing the cube to create a PivotTable report

After the OLAP Cube Wizard creates the cube, the PivotTable and PivotChart Wizard - Step 3 of 3 dialog box appears. You can use the dialog box to create a PivotTable report from the data in the cube you just created. The following steps explain how to use the dialog box to create a simple PivotTable report.

  1. In the PivotTable and PivotChart Wizard - Step 3 of 3 dialog box, click Layout.
  2. In the PivotTable and PivotChart Wizard - Layout dialog box, drag Product to the ROW area of the PivotTable diagram.
  3. Drag store_country to the COLUMN area of the diagram.
  4. Drag Sum Of store_cost, Sum Of store_sales, and Sum Of unit_sales to the DATA area of the diagram.
  5. Click OK, and then click Finish.