Creating a Local Cube from an Existing Server Cube
The
first approach for creating our local cube, and the focus of this
article, will be to connect to the Analysis Server, as we did in our
article Reporting
Options for Analysis Services Cubes: MS Excel 2002, through the Excel PivotTable
report. We will then create a cube that represents a subset of the
data in a larger, server-based cube. In effect, we will create a PivotTable
report that is based upon source data from an OLAP cube on a server, and
then we will copy the source data to a separate file, called an offline cube
(.cub) file, that will be stored on our local disks.
As we
stated earlier, the local cube will allow us to perform analysis and
write reports on the data in the new cube without being connected to a network,
or, as a variation, to continue working when the OLAP server is unavailable. We
can also use our local cube to make data from the OLAP database available on a network
share so that other users can create reports from it, if such action
becomes useful.
We will start by
creating a connection from MS Excel to the data source. This will
parallel the steps we took in our article Reporting
Options for Analysis Services Cubes: MS Excel 2002, and will serve as an excellent "quick
refresher" of the procedure.
Connecting MS Excel to the OLAP Cube
The PivotTable
Wizard provides a guided process for connecting MS Excel to the OLAP
cube. We begin by taking the following steps:
1. Open a new MS Excel 2003
workbook.
2. Click the top left cell (cell A1)
of the new spreadsheet, to ensure that it is selected, before beginning our
procedures with the PivotTable and PivotChart Wizard.
3. Click Data --> PivotTable and PivotChart Report, on the main
menu, to initialize the PivotTable and PivotChart Wizard, as shown in Illustration
1:
Illustration 1:
Initialize the PivotTable / PivotChart Wizard
The Step 1 of 3
Wizard dialog appears.
4. Select the External Data Source
radio button.
5. Select the PivotTable radio
button under "What kind of report do you want to create?" (The
default) as shown in Illustration 2.
Illustration 2: The Step
1 0f 3 Wizard Dialog
6.
Click Next.
The Step
2 of 3 Wizard dialog appears, as shown in Illustration 3.
Illustration 3: The Step
2 0f 3 Wizard Dialog
We
specify the source of external data from this dialog. For this practice
session, we will use the sample OLAP cube called Warehouse and Sales (primarily
because it deals with dimensions and measures that might be of interest to
remote sales teams and so forth, such as customer and lead time data).
» 