The process of data analysis process by means of using an OLAP tool can be divided into 3 stages:
- building a slice
- rolling a slice
- drilling a slice
In building a cube, you specify the dimensions and measures (including any calculated measures). The developer or user then decides which dimensions will be displayed on the x and y axis, and which measure to display. This 'view' of the cube is called a slice. In PivotCube terms, a slice is also called a Map. All other actions, such as data aggregation, checking coordinates visibility etc. are executed on-the-fly and are hidden from the end user.
| 3.1. Building the slices |
3.1.1 With toolbars Building a slice by means of using the toolbar involves dragging the available dimension from the TPVDimToolbar toolbar, to either the cube's column toolbar (TPVColToolBar) or row toolbar (TPVRowToolBar). Similarly, to remove a dimension from the slice, drag the dimension from the row or column toolbar to the available dimensions toolbar.
The order of dimensions on the column/row toolbar will determine which the drill down order of the cube. The order of the dimensions on the rows and columns can be set at run time by using the Rows and Columns properties of the TPivotMap component.
To control the display of measures, use the measure toolbar (TPVMeasureToolBar). You can change the order in which measures are displayed by simply dragging the measure item to the needed position on the measure toolbar.
3.1.2 With ‘TPivotMap’s MapManager and MeasureManager Using toolbars to configure a map is very convenient but takes up space on the user's display unit. The toolbars can be hidden when not in use to obtain a larger view of the data, but PivotCube offers another way to configure a map - using the MapManager and MeasureManager dialog. To bring up these dialogs, call the ExecuteMapManager or ExecuteMeasureManager methods of TPivotMap.
Using these dialogs to build/configure a slice offers the following advantages:
- allows the user to concentrate on the task of building a slice
- the screen does not need to refresh every time a dimension or measure is moved, thus less flickering
- all changes are executed only one, after the user has clicked on the <Accept> button
- changes can be cancelled by clicking on the <Cancel> button
These dialogs can be fired also from the TPivotGrid my clicking on the MapManager and MeasureManager icons.
You can disable these dialogs by settings the TPivotGrid.Settings.Specific.DialogButtons property to False.
| 3.2 Rolling (dicing) a slice |
The components (toolbars and dialog) used for building a slice are also used to roll a slice. Unlike other OLAP software, PivotCube does not differentiate between the building and rotating processes. Additionally, PivotCube offers one method to quickly swap the dimensions between the columns and rows. This is the Swap method in the TPivotMap component. All other current map settings are preserved.
| 3.3 Drilling a slice |
For carrying out top-down analysis (drilling), PivotCube provides the following set of properties.
3.3.1 Using several nested dimensions (move up by dimensions) To access a nested dimension, just click on the '+' icon button next to the dimension value. To collapse the nested dimension, click on the '-' button.
3.3.2 Using hierarchical and nested dimensions Besides using nested dimensions for drilling, it is also possible to use hierarchical dimensions or a combination of hierarchical and nested dimensions.
You can also create your own hierarchy in any dimensions by using the DimensionEditor. You can open the DimensionEditor by clicking on the dropdown arrow on the dimension item.
Create a new group by clicking on the
button, and drag the relevant dimension items into that new group. If the number of items is large, you may use Repository area to temporary hold dimension items.
After you apply the changes, the pivot grid will be immediately updated to display your changes.
3.3.3 Data filtering by inactive dimensions Very often, it is necessary to limit data aggregation by some user-defined dimension items. For example, to aggregate data only by current year, or for selected customers.
PivotCube provides two methods by which you can filter data, which can be used independently or in combination.
3.3.3.1 Excluding selected items
To exclude an item from aggregation, click on the checkbox until the red cross appears (
). If you want to just hide an item but still include it in the aggregation, click on the checkbox until the blue cross appears (
).
3.3.3.2 By choosing item only You can also choose individual items to filter on directly from the dimension toolbar. To do this, first enable the filter area by clicking on the expand/collapse button on the dimension toolbar, then click on the up/down arrows to choose which dimension value will be used in the aggregation functions. Note that you can only do this for non-hierarchical dimensions.
3.3.4 Measure values filtering by aggregating values (grid cells) PivotCube has a unique feature in that it allows you to exclude measures data based on criteria entered by the user.
For example, you have a 'Summary' measure that in your map. You may decide to exclude all values from that measure which fall below a given value. This will reduce volume of data displayed, and allows the end-user to better concentrate on analyzing significant data.
3.3.5 Filtering measure values on original records level (requires a supersaturated cube) You can also create filters on the Facts data source itself. This will then allow you to get the same results as if the data cube was built with same filter criteria.
For example:
You want to remove all wares that were sold only once or twice from aggregation. It may be done in 2 ways:
- Change the SQL query for the Facts table (or set appropriate filter on table) and rebuild the cube. This also requires the source database to be available.
SELECT Ware, Client, Store, Summary, Quantity FROM FactTable WHERE Quantity > 1
- Enter the filter in the MeasureManager, in the 'Filter by fact table records' section. No rebuilds are needed, and can be done when the data source is inaccessible/off- line..
