Designing & Creating the Local Cube with the PivotTable Report
We now have a PivotTable report, with its connection to the Warehouse and Sales server cube, in place, from which we can create our local cube. The rest of the process is easy, although planning is critical to ascertain the needs of the information consumers for whose remote use we are designing and creating the local cube.
For more information on the general creation and use of a PivotTable report, see Reporting Options for Analysis Services Cubes: MS Excel 2002.
23. Click-select the Customers dimension on the PivotTable Field List.
24. In the lower section of the PivotTable Field List, select Row Area.
25. Click the Add button to add Customers to the Row Area of the PivotTable report.
26. Click-select the Time dimension on the PivotTable Field List.
27. In the lower section of the PivotTable Field List, select Page Area.
28. Click the Add button to add Customers to the Page Area of the PivotTable report.
29. Click-select the Supply Time dimension on the PivotTable Field List.
30. In the lower section of the PivotTable Field List, select Data Area.
31. Click the Add button to add Customers to the Data Area of the PivotTable report.
The resulting, admittedly minimal, PivotTable report should resemble the one shown in Illustration 11.
Illustration 11: The Shell PivotTable Report
We will use the above as a basis for creating our local cube. We will see that this is enough to get the process underway in the next steps.
32. Click the PivotTable report to select it.
33. Click the downward pointing arrow on the right side of PivotTable, on the PivotTable toolbar.
34. Select Offline OLAP ... from the dropdown menu, as depicted in Illustration 12.
Illustration 12: Select Offline OLAP ... from the PivotTable Menu
The Offline OLAP Settings dialog appears, as shown in Illustration 13.
Illustration 13: The Offline OLAP Settings Dialog
35. Click the Create Offline Data File button on the Offline OLAP Settings dialog (circled in red in Illustration 13 above).
The Create Cube File - Step 1 of 4 dialog appears, as depicted in Illustration 14.
Illustration 14: The Create Cube File - Step 1 of 4 Dialog
36. Click Next.
The Create Cube File - Step 2 of 4 dialog appears, as shown in Illustration 15.
Illustration 15: The Create Cube File - Step 2 of 4 Dialog
A comparison to the items on the PivotTable Toolbar will reveal that the Create Cube File Step 2 of 4 dialog contains all the dimensions that are available for selection in the cube residing on the server. The checked boxes indicate the dimensions that appear in the PivotTable report itself.
Note: This avenue of selection, within the Create Cube File - Step 2 of 4 dialog, means we can do all selection here, avoiding the need to create an elaborate PivotTable report that includes all the dimensions and measures we want to incorporate into our local cube. This is an opportunity to save time, and the reason that we put a minimal amount of design into the PivotTable report earlier. (There is a minimal requirement for data to enable the Offline OLAP Settings option on the dropdown menu, however.)