Create OLAP Cube

What is OLAP

OLAP stands for OnLine Analytical Processing - a concept neither new, nor uncomplicated. Use OLAP when you want to organize a lot of data, for instance several years of sales records - into simple, understandable graphs and tables.

What Kind of Data

OLAP involves working with information, where you want to group data, in order to summarize numerical fields, or to count occurrences or get the highest/lowest value in a series of data. Thus, your data should contain numerical information, such as sales totals, work time, production totals etc.

What Kind of Databases

You can use OLAP Tool to connect to several different databases. It is required that you have an ODBC data source for that database installed and configured on your client computer. How to set up an ODBC data source for your particular database is beyond the scope of this document. Please contact your IT staff for help.

Microsoft Query

Update: This feature has been removed in Excel 2007.

To be able to create an OLAP cube from the information in the database, we must be able to structure the data as a single table containing the information we want. To do this, we use a tool called Microsoft Query, which comes with Microsoft Excel.

The simplest way to start Microsoft Query is from Excel. Select from the menu "Data -> PivotTable and PivotChart Report…" The dialog below will open.

Select "External data source" and press Next.

Press "Get Data…" This starts Microsoft Query (in background) and opens the "Choose Data Source" dialog.

(Note: You can also start Microsoft Query directly. It is called MSQRY32.EXE and can be found in your Microsoft Office installation folder.)

Connecting to the Database

The steps involved to connect to your database vary a little depending on the type of your database. In this example, we will connect to an Access database file (mdb file).

Select "MS Access Database*" and press OK. A file browser dialog will open. Find your Access database file, mark it and press OK.

Creating the Query

After you have connected to your database, the Query Wizard will help your create your database query, in order to create the OLAP cube. First step involves selecting the tables and fields you are interested in. In this example, we use the Microsoft Northwind example database. We are interested in customers and the orders they have placed, the products they ordered and the quantity and subtotals of the orders.

From the many tables of information, we select CompanyName and Country from the Customers table, Quantity, Subtotal and OrderDate from Orders tables, and ProductName and CategoryName from the Product tables.

Pressing "Next" opens the chosen tables in Microsoft Query.

After moving around the tables in MS Query for better overview, we can now easily see the tables, fields and the relations between them. This is a good way to verify the logic and consistency of your query and data. In some cases, you may have to link the relations that MS Query cannot identify automatically.

The table of data listed below in MS Query shows us the result of the query. This is also a good way to verify the query. If you enlarge the table, you can see that it now contains the information we are interested in – all in one simple table. Each row contains information about one product order, together with information about the customer name and country, the quantity and subtotal of the order, the order date and the product name and category.

You can rename the fields to more user friendly names. Select a column and choose "Records -> Edit column…"

We are now ready to create the OLAP cube.

Creating the OLAP Cube File

Within Microsoft Query, select the menu option "File -> Create OLAP Cube…" This will open the OLAP Cube Wizard.

The first step involves selecting the fields that you want to make available as summarized fields in the OLAP cube. Select the fields, the method of summary and give the field a name. Press "Next" when you are done.

Next step involves creating the dimensions of the cube file. A dimension is one or a group of fields that can be selected to an axis in a table. Drag and drop the available fields to create new dimensions or groups in the same dimension. In this example, we have created three dimensions – Customer, Product and OrderDate. Each dimension contains two or more levels of detail. You can also rename the fields and dimensions, to give them more suitable names. Press "Next".

The final step involves saving the cube file. To be able to use the cube with our Excel OLAP Tool, you have to save the file with all data in it, and save the file in the save folder as OLAP Tool. Select a file name for the cube file, ending with .cub. Press "Finish" to save the file.

You have now created an OLAP cube file. Please contact us for more information and OLAP solutions.