Create Local Cubes with Microsoft SQL Server 2000 and Microsoft Office

Creating Local Cubes

Now that you understand what local cubes are, we can get down to learning how to make them. In the following section we will learn how to create a local cube by integrating Microsoft SQL Server and Microsoft Office.

The process of creating a local cube consists of the following steps regardless of the storage mode you choose:

• Define dimensions and measures and calculated members.
• Define levels, member properties and other objects.
• Populate the cube with data.
• Plot the dimensions and measures into the cube structure.
• Run the cube by connecting to the source provider.

Creating MOLAP Local Cubes with Office

To create a local cube, you must have a tabular data provider as its data source. Tabular data is made up of attributes defined in tables that set the parameters of the map features. In ROLAP you can pick up the data from a RDBMS, which is a tabular data provider, where as in MOLAP since you do not use a RDBMS, the analysis server acts as the tabular database. We will be using the PivotTable and PivotChart features of Excel only to define the local cubes. The PivotTable or PivotChart gets its external data from an OLAP server database.

Making a Local Cube Using the PivotTable in Excel

To make a local cube, you first need to create a new database on the analysis server with the help of an analysis manager. This database will serve as your source for building a multidimensional cube. We will skip the creation of a database as that is beyond the scope of this article. Instead, we will pick up the sample data called FoodMart 2000 Microsoft Access database that resides in the analysis services of SQL Server 2000. We will first create a PivotTable report, using Excel and connect it to the warehouse and sales server cube that is available in the FoodMart database. We will then use this report to create a local cube.

Create a PivotTable Report and Connect it to a Server Cube

1. Open a new workbook in Excel 2000. On the Data menu, click PivotTable and Pivot Chart Report.

2. In step 1 of the PivotTable and PivotChart Wizard, click External data source. Then click Next.

3. In step 2 of the Wizard, click Get Data.

4. In the Choose Data Source dialog box, click the OLAP Cubes tab and then click <New Data Source>. Click OK to go to the next step.

5. In the Create New Data Source dialog box, you will name your data source and select an OLAP provider for the database you want to access. Enter Remote Reps Cube in the first box and select Microsoft OLE DB Provider for OLAP Services 8.0 from the list in the second box.