About creating an OLAP cube from relational data
This topic explains how you can create an OLAP cube from a query that retrieves data from a relational database, such as Microsoft Access or Microsoft SQL Server. You do not need an OLAP server product to create and use this type of OLAP cube. To understand and use this capability of Microsoft Query, you should be familiar with managing databases and using Microsoft Query to retrieve data for Microsoft Excel PivotTable reports or PivotChart reports.
This topic provides reference information about the following:
Refreshing and changing a cube
What is an OLAP cube?
On-Line Analytical Processing (OLAP) is a way of organizing data to fit the way you analyze and manage it, so that less time and effort is needed to create your reports. When you create an OLAP cube from a query, you turn the flat set of records into a structured hierarchy, or cube, that allows reports to focus on the desired level of detail. You also predefine the summary values for the reports, which speeds up report calculation.
Create an OLAP cube when you want to provide a large amount of external data for use in Microsoft Excel reports, especially if you have more records to analyze than you can return to Excel without running out of system resources. Because a cube allows Excel to retrieve only the data it needs for the current view of the report, creating and updating a report is usually faster for large amounts of data than if you retrieved the entire set of records from the database.
The data from your OLAP cube can be returned to Excel only as a PivotTable or PivotChart report, unlike the relational database records in your query, which can also be returned to Excel as an external data range on a worksheet.
Return to top
Creating a query for the data
To create an OLAP cube from relational database records, you set up a data source for the relational database and then create a query to retrieve the fields and records to supply the data for the cube.
Selecting fields for the query To ensure that the query runs as efficiently as possible, include only the tables and fields that you need for the cube in the query. However, make sure you add all the fields you want. After you have created a cube, you cannot add more fields to the query and then incorporate the new fields into the cube. To add more fields to a cube, you must completely re-create the cube.
Adding fields more than once Sometimes, you might want to use a field more than once in a cube. For example, you might want to summarize a Sales field in two different ways, by adding up the sales amounts and by counting the number of sales. Or you might want to include two different versions of a Date field: one that organizes the dates by quarter and month, and another that organizes the dates by quarter and week. To use a field more than once in a cube, you must add the field to the query once for each use. For example, add the Sales field to the query twice if you plan to both add up and count the values in the field.
See Help in the OLAP Cube Wizard for specifics of when to include a field in the query more than once. If you find that you need to add fields to the query while running the wizard, you can cancel the wizard, add the fields, and then run the wizard again.
Saving the query If you think you might want to re-create the cube later with different fields, be sure to save the query in a database query (.dqy) file. You can then open the .dqy file, change the query, and create new cubes from the changed query.
Return to top
Creating a cube
To create a cube, you run the OLAP Cube Wizard. See Help in this wizard for specific instructions for each wizard step.
Temporary cubes and offline cube files The wizard lets you create two types of cubes. One type is a cube definition that the wizard saves in an .oqy file. When you open a report that's based on this type of .oqy file, the cube is built temporarily in memory. The second type is a separate offline cube file that you store on your local disk or a network share. If stored on your local disk, this type of cube allows you to continue working with the data when you are disconnected from the network. You can set up an offline cube file as a data source for reports.
Saving a cube definition file Whether or not you save a separate offline cube file, the wizard saves a cube definition (.oqy) file. This file contains the information that Microsoft Query needs to load the cube so that you can change it, and the information Excel needs to either build the cube in memory or connect to an offline cube file, if you created one.
Using the cube definition file to create reports Instead of returning the records in your relational database query to Microsoft Excel, you can open the .oqy file in Excel to create a PivotTable report that's based on your OLAP cube. You can then save the PivotTable report in a workbook or create PivotChart reports that are based on the PivotTable report. For instructions to open .oqy files and create reports, see Help in Excel.
Return to top
Refreshing and changing a cube
Refreshing an OLAP cube To refresh a cube, you open a report that's based on the cube in Microsoft Excel and refresh the report. When you do this, new and changed data from the original database is brought into the cube, if the cube is built temporarily in memory. If the cube is an offline cube file, the file is rebuilt with the new data and the existing file is replaced. To create your own cubes and refresh them, you must have an OLAP provider, such as Microsoft SQL Server OLAP Services, that supports these capabilities. See Help in Excel for the procedure to refresh a PivotTable or PivotChart report that's based on an OLAP cube.
Changing an OLAP cube You can change an OLAP cube that you created in the OLAP Cube Wizard to organize the data differently or remove fields that you don't want to use in reports. If you created an offline cube file, you can save the file with your changes.
After you create an OLAP cube, you cannot add more fields to it from the original relational database. You can, however, create a new OLAP cube from the same database query (.dqy) file so that you can use different fields from the original database.
Return to top