Create Local Cubes with Microsoft SQL Server 2000 and Microsoft Office

6. While still in the Create New Data Source dialog box, click Connect to open the Multidimensional Connection dialog box. Here you have to choose the location of the multidimensional data source that you want to use.

7. In the Multidimensional Connection dialog box, select Analysis server. In the Server box, enter the name of your server. If the cube and Excel both reside on the same server, you should enter localhost as the name. Enter your User ID and Password for authentication if you are establishing a HTTP connection. Click Next.

8. In the second Multidimensional Connection dialog box, you will select the database/OLAP data source that you want to work with. Select FoodMart 2000 and then click Finish.

9. Back in the Create New Data Source dialog box, the FoodMart 2000 database is now listed beside the Connect button. In the last box, select Warehouse and Sales, which is the cube that contains the data you want. Click OK.

10. Back in step 2 of the PivotTable and PivotChart Wizard, click Finish.

11. The Excel workbook will show an empty PivotTable report. You can now browse the Warehouse and Sales server cube and generate reports. From here we can create a local cube.

However, before you begin find out the requirements of the client and figure out the data that you will need in the cube. For instance, for the inventory cube, you will need dimensions like product, stock in hand, reorder level, etc.