Creating local cube files with Microsoft Excel
You can create local cube files from a relational database using Microsoft Excel. Here are the steps for doing so using Excel XP or Excel 2003.
Getting Started
1. Start a new Microsoft Excel worksheet.
2. Select PivotTable and PivotChart Report from the Data menu. and the PivotTable and PivotChart Wizard will open.
The PivotTable and PivotChart Wizard
3. PivotTable and PivotChart Wizard - Step 1 of 3. Select the External Data Source option button and push Next.
4. Pivot Table and PivotChart Wizard - Step 2 of 3. Press the Get Data button.
5. In the Choose Data Source dialog select a relational database on the first tab. If the data source is not in the list, select <New Data Source> and you will have an opportunity to set up a new connection. DO NOT select the OLAP Cubes tab.
The Query Wizard
6 After connecting to the database, the Query Wizard should open. This wizard creates the query that will be used to retrieve all the information from the relational data source for your local cube.
7. Query Wizard - Choose Columns. Select all the columns that have data that you want included in your local cube. You need to include a column for every level of every dimension you're going to include in your local cube. You also need to include a column for every measure. Do not include fields that are only being used to join tables together.
If it's able to do so, the Query Wizard will automatically join the tables used in the query. If it can't, you will be asked to make the joins yourself in Microsoft Query. Continue with the instructions for using Microsoft Query directly in #11.
8. Query Wizard - Filter Data. If you want, you can choose filters to limit the data that you are putting into your local cube. You do not need to select anything on this tab.
9. Query Wizard - Sort Order. Skip this tab. It has no effect on your local cube.
10. Query Wizard - Finish. If the Query Wizard has enough information to create the query, the option Create an OLAP Cube from the query will appear. If you are satisfied with your query, select this option and press Finish. Continue with the instructions for the OLAP Cube Wizard inn #13. If you would like to make changes to your query select the option View data or edit query in Microsoft Query and press Finish.
Microsoft Query
11. Microsoft Query gives you many options for designing your query. You can add or remove tables, create joins between the tables, add or remove fields, and add or remove filters. You can work with the data in a visual interface or you can modify the SQL syntax directly.
12. When you are done editing your query, choose Create OLAP Cube from the File menu and continue with the OLAP Cube Wizard..
The OLAP Cube Wizard
13. The OLAP Cube Wizard will now open. Press Next.
14. OLAP Cube Wizard Step 1 of 3. Choose the fields that you want to include as measures in your local cube. All of the numeric fields will be selected by default. Also choose how you want to summarize the fields. Sum is the most common choice, but you may also want to use the Count function for some of the measures.
15. OLAP Cube Wizard Step 2 of 3. This screen allows you to create dimensions and levels. Drag and drop the fields you want to use as levels to organize them into dimensions. You can write-click on the names of the dimensions and levels and give them different names, if you choose. By default, the dimension is named for the first level that you add to it, which may or may not be a good name.
16. OLAP Cube Wizard Step 3 of 3 Select the option Save a cube file containing all data for the cube. Select a file name and directory for your local cube file. If you are planning to use this local cube with CubeSlice, you maybe would want to put it in the CubeSlice\Data directory.
17. After clicking Finish, you will be asked to save the query that is used to create the local cube. Give the query a name and save it. After you do so, the local cube file will be created.
The PivotTable and PivotChart Wizard
18. You will now be returned to the PivotTable and PivotChart Wizard. If you want, you can continue on and examine your newly created local cube in the Excel Pivot Table.
Using the New Local Cube in the CubeSlice Creator
19. To use your new local cube in CubeSlice, open the CubeSlice Creator and select the New option.
20. Select Local Cube Connection for the Connection Type and browse to find the new local cube file.
21. Select the name of the cube in the local cube file and choose Add Cube.
22. You can now create views from the local cube file, and include it in CubeSlice View, CubeSlice Data, and CubeSlice Share files.