I want to create a .cub file from Excel

[ORN99]

Hi everyone,

I need to create a .cub file so I can use the data in MS Data Analyzer. I tried to right-click on a pivot table but didn't work. Can someone please help?
Thanks.

 

[Div]

haven't used the Data Analyzer myself, but have a look at:

"Creating Data Cubes Using Excel and Text Files"
http://office.microsoft.com/assistance/2002/articles/daCreateCubeWithText.aspx

and maybe:

"Creating Data Cubes with Excel and a Database"
http://office.microsoft.com/assistance/2002/articles/DA_Use_Excel_to_Create_Cubes.aspx

HTH,

Div.

 

[kbahnassi]

Create OLAP Cube

Kinan said

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 DatabaseThe 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 QueryAfter 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 FileWithin 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.