Working with Cubes

proCube’s Cube-Based Formulas  extend your ability to calculate cell values far beyond the simple summations and weight assignments that define Aggregate members within Dimension hierarchies. With Cube formulas, you can create more complex relationships between variables—using standard, pre-built mathematical expressions or formulas you build yourself—to define values in single cells, across a range of cells, or even between cells in different Cubes within a database.

You have already seen how, within Dimension hierarchies, “sibling” members can be weighted to affect the summed value of an Aggregate—the “parent” member of the individual sibling Members. (This was demonstratedWorking with Dimensions and Members when you added Margin to the Sales Account dimension, and created an Aggregate Weight of –1 for Cost of Sales.)

Most likely, you will want to develop more complex expressions than aggregations to solve real-world business problems. proCube enables you to build formulas to perform these types of calculations, both for Detail members and for Aggregate members. You will find that proCube provides extended support via dialog features and through formula “grammar” to help you create arithmetic calculations, Functions, and algebraic expressions that meet your needs. In addition, proCube allows you to write Dependencies for your formulas, which greatly increases the speed of calculations in large, sparsely populated databases. Dependencies will be the subject of the latter portion of this Section.

Fact Data is stored within Cubes at the intersections of Dimension members. We have already learned a great deal about Dimensions and Members. Now we will learn how to Model Cubes and how to populate Cubes via the Cube Transfer feature. In addition, this section will discuss ways to optimize Cube performance.

About fact data in cubes

A proCube database can support either string or numeric  data in any cell at the intersection of Dimension members. As you will see, when you create a Cube, you can specify whether it can contain only numeric data.

Once a Cube is created, it can be populated with Fact Data in the following ways:

• Entering or copying numeric values or ASCII strings into a slice (i.e., entering data directly in to the slice).

• Entering or copying numeric values or ASCII strings into a dynamically connected Excel worksheet .

• Using proCube’s import features.

• Via Comma-Delimited Value text files imported through Excel.

• Using the Cube Transfer feature.

• Via Cube Formulas.

• Via proCube’s Xchange component.

The remainder of this topic reviews the basics of modeling Cubes. It will also discuss the multidimensional structures in which you enter and store proCube data (and from which you create “Slice views” so to instantaneously plan, analyze and report on key business information).

Modeling cubes

You can create, rename, and delete cubes in the currently opened database.

Creating a cube

To create a cube:

1. From the Model ribbon, click Cubes to open the Cubes dialog.

The Cube field, which is located at the top of the dialog, is used to enter the name of the Cube to be created. If you are creating a new database, the list box will be empty. Figure 1.    Cubes Dialog - Region Cube

1. Enter Region into the Cube field and click Add.. The Define Cube dialog opens, where you can select the dimensions to be used within the new cube.

All Dimensions contained in the current database are shown in the Available Dimensions list to the left. To create the cube you have named, select Dimensions from the list box and place them in the Selected Dimensions list box on the right. (The name is shown at the top of the dialog is the name of the Cube, which in this case is Region). Figure 2.    Define Cube Dialog - Region Cube

The procedure for constructing a cube from Selected Dimensions is as follows:

1. Select the Dimensions you want to use in the cube.  (You can demonstrate this by selecting the Dimensions that appear in the Selected Dimension list box in the figure below.) Use Shift or Ctrl to select more than one Dimension.

The four buttons in the center of the dialog move dimensions between the Available and Selected Dimensions lists.

1. Click the appropriate button. The selected dimensions are moved to the Selected Dimensions list.

Note the impact of the order of Dimensions. The first Dimension(s) in the Selected Dimensions box will appear as Pages in the initial Slice view of a Cube. The last two Dimensions moved into the Selected Dimensions box will appear as the Columns and Rows in the initial view of a Cube. Therefore, when moving Dimensions into the Selected Dimensions box to create a new Cube, think first about the initial Slice view you want to have each time you create a new Slice from that Cube. In our example, we will see Regions as Columns and Salespeople in Rows in the initial slice.

1. Select Numeric Data Only to force the cube to accept only numeric data or leave the check box empty.

A cube that accepts only numeric data will not accept text-based information. Both options have to do with validation. Performance will not be affected.

1. Click OK when complete to return to the Cubes dialog.

2. Click OK again to exit the Cubes dialog.

Renaming a cube

proCube Cubes can be renamed without losing or deleting Metadata or Fact Data.

To change the name of a cube:

1. From the Model ribbon, click Cubes to open the Cubes Dialog.

2. Select the Cube to rename by highlighting it.

3. Enter a new name in the Cube text box.

4. Click Rename.

5. Click OK to close the Cubes dialog .

If you have defined Cube Formulas, you must replace the old Cube name, wherever it appears, with the new Cube name in those Formulas.

Deleting a cube

Deleting a cube results in the loss of all data contained within the cube. Exercise caution when deleting cubes.

The Cube dialog also gives you the ability to delete a cube. You will need to delete all slices in the cube prior to deleting it.

To delete a Cube:

1. From the Model ribbon, click Cubes to open the Cubes Dialog.

2. Select the Cube to be deleted.

3. Click the Delete button. (Remember to delete all slices in the cube before deleting the cube.)

4. Click OK to close the Cube dialog.

Transfering a cube

The Cube Transfer feature allows you to:

• Transfer data from one Cube to another Cube within the same database.

• Transfer data from an area of a Cube to another area of the same Cube.

• Clear out portions of a Cube, overwriting existing values with zeros.

To access the Cube Transfer feature:

1. From the Model ribbon, click the arrow beside the Cubes icon and select Transfer Cube... to open the Transfer Cube dialog. Figure 3.    Transfer Cube Dialog

Transfer Methods

There are four methods used to transfer data: Set, Accumulate, Clear and Transform, as pictured below. You can select several methods during transfer (i.e., you can use Set for some areas of the Cube to which you transfer data, and Clear for other areas).

• Set replaces any data in the destination Cube with the data from the source Cube.

• Accumulate adds the data from the source Cube to the data in the destination Cube.

• Clear ignores data in the source Cube and zeros the area specified in the destination Cube.

• Transform allows you to specify a formula to apply to the data in the source Cube as it is being transferred to the destination Cube.

Defining Source and Destination Areas

When transferring data, you must first select the source and destination Cubes from two combo-boxes. These can be the same Cube if you wish to transfer data to a different area of the same Cube. The Dimensions list box will contain all the dimensions for each Cube.

To define the source and destination areas:

1. From the Source drop-down, select the Sales cube.

2. Select the Sales cube as the Destination cube. This is being done for simplicity reasons - refer to Transfering a cube — points to consider.

You can specify the exact source and destination areas within a Cube by selecting Members within a Dimension. Selecting a Dimension from the Dimensions list box will display its Members in the Members list box (withRegion selected as a Source). Figure 4.    Transfer Cube Dialog - Source:Region

You can also specify a single Member of a Dimension from the source to be transferred to one or more members of the same Dimension in the destination.

1. Select the member USA from which to transfer data.

2. Under Destination, select Region.

3. Select the members Canada and Argentina, both of which are detail members. Figure 5.    Transfer Cube - Destination:Region

1. Verify that Method is Set and then click OK. The following window opens to confirm the selection. Figure 6.    Transfer Cube Prompt

1. Click Yes.

You can make multiple selections of data to set exact parameters for the data by choosing from multiple Dimensions and Members.

Transfering a cube — points to consider

• If the Dimension is found in both the source and destination Cubes:

If the specified dimension is found in both the source and destination cubes, the All radio button will automatically be selected.

Selecting the All option means that all members for that dimension will be copied from source to destination.

You can also specify a single Member of a Dimension from the source Cube to be transferred to one or more Members of the same Dimension in the destination Cube. To select one or more Members to copy into, select the corresponding same Dimension in the destination Cube and then select the Member(s) to copy into.

• If a Dimension exists in the source Cube and not the destination Cube:

If a dimension exists in the source Cube and not the destination Cube, you must select exactly one Member from the source Cube dimension to copy.

• If a dimension exists in the destination Cube and not in the source Cube:

If a dimension exists in the destination cube and not in the source cube, then you must select at least one member in that destination Cube dimension into which values will be transferred.

Skipping Zeros in the Source Cube

Selecting the Skip Zero check box will eliminate any attempts to write zero values from the source Cube into the destination Cube. In other words, zeros will not be transferred to the destination Cube, and values in the destination corresponding to the zero points from the source will be left intact.

The Skip Zero check box is a performance optimization feature, and therefore, the check box is checked by default. Uncheck this option if you want all values, including zeros, to be transferred and, thus, to overwrite the existing destination values.

Skipping Aggregates in Source Cube

Selecting the Skip Aggregates check box will eliminate any attempts to write aggregated values from the source Cube into the destination Cube. This feature is a performance optimization and therefore the check box is checked by default.

Skipping Calculated Values in Source Cube

Selecting the Skip Calculated Values check box will eliminate any attempts to write formula values from the source Cube into the destination Cube. If this check box is checked, values calculated by formulas will not be written to the corresponding points in the destination cube. This is checked by default.

Deselecting Skip Calculated Values check box will attempt to write formula values from the source Cube into the destination Cube. Depending on the size of the source cube and complexity of the formulas, performing this operation can take a considerable amount of time.  However, enabling and writing Dependencies for the source cube can expedite this operation.  For more information go to Cube Dependencies.

Data Transformation

You may want to apply a Data Transformation formula to a set of data points during the cube transfer. Clicking the Transform radio button opens the standard Formulas dialog . Figure 7.    Formulas Dialog

By specifying a left-hand side that corresponds to the destination cube, you define the set of data points to which the formula will apply. The right-hand side of the data transformation formula specifies how the data point will be transformed.

Example Formulas:

Assume you want to transfer all the Actual member data values into the Forecast member of the same dimension in the same cube, and then add 10% during the transfer. Use the following formula:

Details and {Forecast} = [] * 1.1;

Assume you want to transfer all the Actual member data values greater than 5000 into the Forecast member of the same Dimension in the same Cube (e.g., Cube1), and then add 10% during the transfer. Use the following formula:

Details and {Forecast} = IF ([Actual] > 5000, [Actual] * 1.1, Cube1.[]);

Assume you want to transfer all the Actual member data values greater than 5000 from Cube1 into the Forecast member of the same Dimension in a different cube (e.g., Cube2), and then add 10% during the transfer. Use the following formula:

Details and {Forecast} = IF (Cube2.[] > 5000, Cube1.[Actual] * 1.1, Cube2.[]);

When the [ ] expression is used to specify the current cell in a destination Cube, the reference must always be qualified by the destination Cube name (even if that destination Cube is the same Cube as the source).

After a Cube Transfer is complete, a message box will inform you of the number of data points transferred (i.e., the number of points written to the destination Cube).

Optimizing cube performance

Cubes are the data storage objects in proCube databases. Composed of descriptive Metadata, they define axes or multidimensional intersection points where Fact Data is stored. By adding the least dense Dimension (i.e., the Dimension with the least number of Members) to the Cube first, and the densest Dimension to the last Cube, Cube performance improves substantially.

When you work with an open database, both Fact Data and Metadata are loaded into RAM, delivering input/output performance that is far superior to that of hard disk–based products. Slice views are also RAM-based objects, making recalculation faster. The more complex the model, the more RAM that must be used to further enhance performance.

proCube was designed on the premise that computers will continue to increase in speed due to advancements in CPU and RAM design, rather than advancements in hard disks. As a result, proCube has been designed to take advantage of CPU and RAM speed and will therefore improve in performance with each upgrade of your computer.

Automatic Cube Rotation

When new Cubes are created, they are internally rotated for optimized storage and performance in terms of their number of Members per Dimension, from least to greatest.