Create the PivotTable
Now that we have put a
query in place to act as a data source, we are ready to set about the creation
of a sample report. In our present case, we will create a straightforward PivotTable
view to act as our report, keeping in mind, as we noted before, that any
number of more elegant, robust, and enterprise-oriented solutions might be more
appropriate in your own production environment.
within the Queries section of the Database window, let's take the
following steps to begin construction of our new PivotTable view.
the new Warehouse Cube Activity to select it
on the Database window toolbar.
query results set appears within the main window, as shown in Illustration 14.
--> PivotTable View from the
main menu, as shown in Illustration 15.
15: Select View --> PivotTable View
After a few seconds, a blank PivotTable appears, with a
floating PivotTable Field List appearing in the foreground, as shown in Illustration
16. Note the appearance of the two additional fields that are based upon
the StartTime field.
16: A Blank PivotTable View Appears
As I suggested
in the DatabaseJournal article Create
a PivotTable View in Access, the blank PivotTable offers an excellent "conceptual"
view of the basic makeup of a PivotTable. With a PivotTable we can create views
of our data that strongly resemble, but far out-power, a cross-tab query. The
PivotTable allows us to define the data values that we wish to occupy multiple
rows, columns, pages (or "layers") and summaries.
We can see above that the center of the table contains
numeric data ("measures"), while the rows and columns present (often
hierarchical) dimensional data. We will see a simple illustration of how these
areas of the conceptual "map" can be populated with our cube usage
analysis data in the steps that follow.
Let's define our view to
effectively present the data set returned from our new query.
If the PivotTable
Field List does not appear, initialize it clicking the Field List
button, shown in Illustration 17, on the toolbar.
17: The PivotTable Field List Button
In the Field
List, click MSOLAP_Cube to highlight it.
Area in the selector box, to the right of the Add to button (at the
bottom of the Field List), as shown in Illustration 18.
18: The PivotTable Field List with our Selection