Excel Data Cubes
In another article titled
Creating data cubes with Excel and a database, the Microsoft« Data Analyzer
User Assistance team explained how to create cubes by connecting to an Access
database, querying the database for information, and then using Excel to convert
that information into a cube. But what if you don't have a copy of Access or
Microsoft SQL Server or you don't know how to create a database? In that case,
you can create limited data cubes from text files or comma-separated-value
files, and then explore the results with Data Analyzer.
Consider this scenario: Say your company uses
mainframe computers, and the system only provides reports as text files. You can
use Excel to query those files for data that you want to place in a set of
cubes, and then create the cubes. From there, you can analyze the cubes with
Data Analyzer or import your data into PivotTable┬« reports.
This article explains how to use Excel to create
a data cube from a tab-delimited or comma-separated-value text file. It also
points you to information about connecting to the cube with Data Analyzer.
Important Your text file must contain
data in columns and rows (tabular data). You cannot convert a PivotTable report
to a text file and obtain meaningful results. Also, the text file must contain
Finding source data and creating a cube
If you have a text file that contains meaningful
data such as a sales report, feel free to use it at this point. If you don't
have some source data, or you're unsure about how to create some, you can
download a sample text file from the Office Download Center. The sample file
contains geographic and sales data from the sample Foodmart 2000 database. Most
of the data takes the form of ID numbers, so you cannot analyze it, but you can
use it to learn the procedures described here.
Once you have your source data, you follow this
process to create a cube with Excel:
- Choose a data source. In
this case, you select the .txt or .csv file.
- Create a query that
extracts data from the text file.
- Create a cube from the
The following procedures explain how to perform
To select a data source
- On the Data menu in
Excel, point to Import External Data, and then click New Database
- In the Choose Data
Source dialog box, click the Databases tab, select New Data
Source, and then click OK.
- In the Create New Data
Source dialog box, type a name for the data source in the first text
box, select Microsoft Text Driver (*.txt; *.csv) from the second
list, and then click Connect.
- In the ODBC Text Setup
dialog box, clear the Use Current Directory box and click Select
- In the Select Database
dialog box, locate the folder that contains the sample file that you
downloaded, or your own text file, and then click OK.
not select the file, just the folder.
- Click OK twice more
to return to the Choose Data Source dialog box.
To create the query
- In the Choose Data
Source dialog box, select the data source you created in the previous
procedure. Make sure Use the Query Wizard to create/edit queries is
selected, and then click OK.
- In the Query Wizard -
Choose Columns dialog box, select the file that you want to query. If
you're using the sample text file, click it and move all the columns from
the Available tables and columns pane to the columns in our query
pane. If you're using your own file, move the columns that you want to use.
- Click Next, and
then click Next in the next two dialog boxes.
- In the Query Wizard -
Finish dialog box, select Create an OLAP Cube from this query and
click Finish. This launches the OLAP Cube Wizard, which you use to
build your cube.
To create the cube
- Click Next in the
Welcome to the OLAP Cube Wizard dialog box.
- In step 1 of the wizard,
select only the source fields that you want to use as measures. To do so,
check the box in the Source field column. If you're using the sample
text file, select only store_sales, store_cost, and unit_sales. Make sure
that "Sum" is selected for each of those fields in the Summarize by
column, and then click Next.
- In step 2 of the wizard,
move the fields that you want to use as dimensions from the Source fields
pane to the Dimensions pane. If you're using the sample text file, create
the following data structure:
- Click Next.
- In step 3 of the wizard,
select Save a cube file containing all data for the cube. Enter a
path and filename for the cube, and then click Finish.
- In the Save As
dialog box, type a file name 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 .oqy filename extension, and the OLAP
Cube Wizard creates the cube file. This may take several minutes.
Using the cube
After the OLAP Cube Wizard creates the cube, the
PivotTable and PivotChart Wizard - Step 3 of 3 dialog box appears. At
this point, you can:
- Use the dialog box to
create a PivotTable report. For more information about creating PivotTable
reports, see Excel help.
- Connect to the cube with
For information about connecting to a cube, see
Connecting to data cubes and creating views, on Office Online.