Creating a Cube

Cube mode allows you to create a multi-dimensional cube out of your database tables using the Report Designer. Once the cube is generated, Cube mode allows you to apply functionality to analyze the data in the cube, such as drill-down, drill-across, and data pivoting.

To display the Cube tab in the Report Designer, select one of the following options:

•   Select the menu option ModeÆCube.

•   Select the Cube icon from the menu bar.

The Cube tab is shown below.

 

   Figure 152: Cube Tab

On the Report Designer window, the layout includes the Table, Column, Joins, Conditions, and Procedures tabs, as described in Chapter 4: Getting Started with Report Designer on page 119. Any data you entered in these pages is kept when you select the Crosstab tab. You may edit this information at any time by returning to those tabs.

Adding a Dimension

To add a dimension to a cube, highlight a Cube Dimensions row in the Dimensions: list. Choose a column in the Available Columns: list and click the Add Dimension button. When you add your first column, it is entered as the first level.

Editing a Dimension Name

When you generate a cube, you should assign meaningful dimension names rather than Dimension1, Dimension2. To edit a dimension, highlight a dimension name and click the Edit button above the Dimensions list. The Dimension Properties window is displayed. Enter a meaningful name for the dimension heading and click the OK button.

Adding a Level

To add a level to a dimension of a cube, make sure that the dimension level under which you want to add a new level is highlighted, choose a column from the Available Columns: list and click the Add Level button.

Defining Drill-Through Behavior

One of the major advantages of using a cube to display data is the ability to drill down on the data. This is extremely useful when your data is set up in a hierarchical fashion.

For example, if your database contains product sales data for the United States, you may have sales data for specific regions of the country, for specific states in those regions, and for specific cities in those states. So when defining your cube, you may set Region as a dimension, State as the first level in the dimension, and City as the next level.

When you preview this cube, a chart for all regions is displayed. If you double-click the Regions dimension, a chart is displayed showing data for the Northeast, Southeast, and other regions. Double-clicking Northeast displays data for Connecticut, Massachusetts, and Rhode Island. Double-clicking Massachusetts displays data for Boston, Worcester, and other cities. Viewing the data in this fashion is called drilling down.

By default, if you double-click a level at the bottom level of a dimension (for example, Boston in the example above), the status bar displays a message indicating that you cannot drill down further. You can configure the chart engine to open a specific report, chart, or crosstab when a user attempts to drill down on the bottom level of a dimension. The report that is opened can be an SQL report or a parameterized report, chart, crosstab, or cube.

To add this drill-through behavior to your cube, click the Add Drill-Through> button. The Drill-Through window is displayed, as shown below.

 

   Figure 153: Drill-Through Window

This window allows you to add drill-through behavior in different ways using the following radio buttons in the Drill-Through Mode section:

•   None - Cancels the drill-through and returns you to the Cube tab.

•   Parameterized Definition - This option allows you to display a parameterized report, chart, crosstab, or cube when the user attempts to drill down on the bottom level dimension of the cube.

•   SQL - This option allows you to display a tabular report when the user attempts to drill down on the bottom level dimension of the cube.

Adding a Parameterized Drill-Through

To add bottom level drill-through behavior to any dimension of a cube using the Parameterized Definition mode:

1.   Create the parameterized report, chart, crosstab, or cube you want to be displayed when the user attempts to drill down on the bottom level of the cube dimension. Specify at least one item in the cube dimension as a parameter and save the definition file.

2.   Create the cube as described in this section.

3.   From the Dimensions: list on the Cube tab, select the appropriate dimension (or any level in the dimension) for the drill-through behavior. You may specify one drill-through per dimension.

4.   Click the Add Drill-Through> button. The Drill-Through window is displayed.

5.   Select the Parameterized Definition radio button. The example window, shown in Figure 153 Drill-Through Window on page 233, shows the fields available for this mode.

6.   In the Parameter Source area, choose the report type from the Definition Type drop-down list.

7.   Choose the parameterized report you created in step 1 from the Definition File drop-down list. The parameters contained in the file are displayed in the Parameters: list.

8.   Select a parameter in the Parameters: list and the corresponding cube level in the Available Cube Levels: list.

9.   Click Assign Parameter to assign the parameter to the cube level.

10.   Repeat these steps for all parameters in the Parameters: list, then click the OK button.

 

   Note:


    You must match all parameters with a cube level. You do not have to match all cube levels with a parameter, but your report may make more sense if you do so.


The parameterized definition is displayed on the Cube tab in the appropriate dimension. When a user runs the cube, the parameterized report is displayed when the user attempts to drill down on the bottom level in the dimension.

Using Drill-Through to Access Large Cubes

You may open a parameterized cube when drilling-down on the bottom level of a dimension. This behavior may be useful in accessing large cubes.

When you run a query for a large cube, it may take several minutes to run or even overload the system. Instead of creating one large cube, you may create a several smaller, parameterized cubes in which one cube drills to another parameterized cube, and so forth.

Linking small cubes in this way can access the same data that would otherwise be stored in one large cube, but the query time for each of the smaller cubes may be significantly less than the query time for the large cube.

Adding an SQL Drill-Through

To add bottom level drill-through behavior to any dimension of a cube using the SQL mode:

1.   Create the cube as described previously in this section.

2.   From the Dimensions: list on the Cube tab, select the appropriate dimension (or any level in the dimension) for the drill-through behavior. You may specify one drill-through per dimension.

3.   Click the Add Drill-Through> button. The Drill-Through window is displayed, as shown below. Select the SQL radio button.

 

   Figure 154: Drill-Through Window SQL Mode

4.   In the SQL area, type an SQL query that is to be used to generate a tabular report.

5.   Select a cube level from the Available Cube Levels: list and click the Add Parameter button to create the second half of the where clause condition (for example, =CITY:$2) associated with this level. This should be inserted into a standard where clause (for example, select * from CITY where CITY=CITY:$2).

This statement does not need to be ended with a semicolon.

6.   Click OK to create the drill-through.

The SQL statement is not checked for accuracy. The drill-through is displayed on the Cube tab in the appropriate dimension. When an user runs the cube, a tabular report (created by the SQL query) is displayed when the user attempts to drill down on the bottom level in the dimension.

Editing a Drill-Through

You can edit a drill-through on the Cube tab using one of the following methods:

•   Double-click a drill-through in the Dimensions list.

•   Select a drill-through in the Dimensions list and click the Edit button above the list.

•   From the Dimensions list, select a dimension or level containing a drill-through, or select a drill-through then click the Add Drill-Through> button.

All of these options open the Drill-Through window, allowing you to edit the drill-through.

Removing a Drill-Through

To remove a drill-through, use one of the following options:

•   From the Cube tab, select a drill-through in the Dimensions list and click the <Remove button.

•   From the Drill-Through window, select the None radio button, followed by OK.

Restrictions Imposed by the Drill-Through

You cannot do the following in dimensions containing a drill-through:

•   Rearrange the levels in the dimension.

•   Add a level to the dimension.

•   Remove a level from the dimension.

If you wish to perform any of these operations, first remove the drill-through, alter the dimension, then recreate the drill-through after the operation is complete.

Adding a Measure

To add a measure, highlight a column name in the Available Columns: list. Make sure that the correct option is chosen from the Measure Function: drop-down list, and click the Add Measure button.

Only two measure functions are supported: COUNT() and SUM().

Editing a Measure Name

You may edit measure names in the same way you edit dimension names. In order to give meaningful names to measures, highlight a measure name and click the Edit button above the Measures: list. The Enter Measure Heading window appears. Enter a meaningful measure heading here and click the OK button.

Saving a Cube

After you have created the cube with all the dimensions and measures you want, you can save the cube to be used later. Click the Save Definition button on the Cube tab. The Save window is displayed. Enter a file name for the cube and click the OK button.

Setting Cube Properties

Click the Properties button on the Cube tab to open the Cube Properties window. For a description of the General tab in the Crosstab Properties window, see Cube Properties on page 271.

Creating a Custom Call

The Custom Call feature allows external actions to be triggered upon report creation. Click the Custom Call button on the Cube tab to display the Custom Call window.

For a description of the Custom Call feature, see Creating a Custom Call on page 242.

Previewing a Cube

You can preview your cube by clicking the Preview button. An example cube is shown below.

 

   Figure 155: Example Cube Preview

Analyzing a Cube

The picture you get when you preview a cube is not static. You can do the following types of analyses on the chart shown in Figure 155.

•   Change Views (table, chart, or both)

•   Pivot Dimensions

•   Drill Down

•   Drill Across

Changing Views

The default view of a cube is a chart. You can change to a table view or see both table and chart views side by side. To change a view, click the Show Chart, Show Table, or Show Both option from the menu bar.

Data Pivoting

You can swap dimensions by dragging a dimension heading and dropping it in place of another. For example, by dragging the Measure dimension to the Name heading on the X-axis, you get the output in the following figure.

 

   Figure 156: Example of Data Pivoting

Drilling Down on Data

Drill down on a dimension displayed on the X-axis by double-clicking the bar for that dimension. For example, by double-clicking the All Node bar shown in Figure 156, you get the individual Node bars, as shown below.

 

   Figure 157: Example of Drill Down

If additional levels are available, you can drill down further by double-clicking one of the Node bars.

You can also drill down on a different dimension by pivoting it into the main view, drilling down, and pivoting it back out.

Drilling Across

You can drill across a dimension that is not on the X-axis. Report Designer allows you to either cycle through different values of a dimension or choose the dimensions across which you can drill. To choose the values you would like to drill across, double-click a dimension. A window like the one shown below appears with all the values of the dimension in it.

 

   Figure 158: Choosing Values to Drill Across

Choose the values that you want to display and click the OK button.

To drill across the dimension, click the up and down arrows next to the dimension.