Viewing Results in proCube (.olp file)

Viewing Results in proCube (.olp file)

With the Dimensions you have defined in the preceding steps—in our example, Measures, Products and Customers—you can now finalize the creation of the Cube.

  1. From the Dimensions dialog (refer to Saving Your Work) click OK.

  2. A message box opens with a twirling proCube “P”, like the following image, while Xchange replicates the data from the underlying relational database with the newly created Cube.

synchronizingwithrelationalsource.png

Figure 1.    Synchronizing with Relational Source

  1. When the replication process is completed, you will receive a message similar to the following, which indicates how many multidimensional data points have been created in the Cube.

multidatapointscreationmessage.png

Figure 2.    Multidimensional Data Points Creation Message

If there are any ERRORS in transferring data, the message will indicate how many data points could not be transferred to proCube. A log file for both Metadata and Fact Data errors will be created as Xchange_[Cubename][Date/Time].log. Go to Error File Reporting for more information.

  1. Click OK.

You are now at the familiar proCube Management Studio.

Cube properties

proCube records the properties of the created Cube, enabling you to obtain an overview of the Cube—the Dimensions; some of the options you selected when creating the Cube; and, critically, the underlying relational database as well as the driver you used to access this database. This is especially useful later, when you open a database and want to determine these particulars about a Cube.

We will now take a look at the properties of the example Cube:

  1. From the Data ribbon's Xchange Group, click Cube Properties to open the OLAP Exchange Cube Properties dialog.

olapexchangecubepropertiesdialogsalesorder.png

Figure 3.    OLAP Exchange Cube Properties Dialog - Sales Order

Enable Multidimensional Editing Allowed, so that the Fact Data is no longer “read-only”—i.e., you will be able to enter data in the Cube through the proCube interface.

  1. Click OK.

  2. Click OK in the message box, “No changes were made.”

  3. From the Server View, highlight the database, right-click to open a menu, and click Save to save the database with all current information.

We can take a closer look at the Cube through the proCube interface. Directly, you can create a slice, to verify that the actual figures have been loaded into the Cube.

  1. From the Model ribbon's Meta Objects group, click the arrow just to the right of Slices and select New.

  2. With the Sales Order cube selected in the New Slice dialog, click OK. The slice grid opens.

  3. Press F9 to update the slice grid with actual figures.

  4. The Page Dimension, Measures, shows the Page Member UnitPrice for all Customers (rows) by all Products (columns). The Page dimension list box is shown below:

measurespagedimension.png

Figure 4.    Measures Page Dimension

  1. In order to see how many of each product each customer ordered, change the Page Member to Quantity:

  2. Double-click on Measures: UnitPrice in the Page dimension list box.

  3. In the Edit Slice dialog, double-click on Quantity in the Slice Content list box, on the right. Then click OK (green check mark) to return to the slice.

  4. Press F9 to update the slice so that it shows Quantity data—i.e., the units ordered of each Product (shown in columns) by each Customer (shown in rows). The slice will appear as in the following figure:

measuresquantitypagememberslice.png

Figure 5.    Generated Slice

To take a couple of selected examples of the data: the Slice shows that the customer Antonio Moreno Taqueria ordered 18 units of Alice Mutton and 10 units of Boston Crab Meat, etc. (you will see more products in the Slice by scrolling to the right). And the customer Berglunds snabbkop ordered 10 units of Alice Mutton, 30 units of Aniseed Syrup, etc.

In considering these figures, you might have wondered when the customers placed their orders. You are already, therefore, “thinking multi-dimensionally” because you are curious about comparing across a “Time” dimension—e.g., by Period, by Month, by Year—how many products were ordered by a particular customer. In this 3-dimensional Cube we did not add a Time dimension, whereas in typical implementations there will often be a Time dimension. This might be accomplished by using a Field that includes Months for each transaction. Go to Creating a Time Dimension for more information.

At this point, we can check other characteristics of the Dimensions we built via Xchange—namely, the Alias Groups we created; the Properties we assigned; and the Hierarchies we built.

Defining alias groups

  1.  From the Model ribbon, click, Dimensions.

  2. Double-click on Customers in the Dimensions dialog.

  3. In the ‘Customers’ Hierarchy dialog box,  click the Alias Groups button on the toolbar,  . The Alias Groups dialog opens.

aliasgroupscontactname.png

Figure 6.    Alias Groups Dialog - ContactName

Note that an Alias Group has been given the name ContactName, from the field that exists in the underlying Northwind database. The unique Contact Name is listed for each Product, in the column to the right.

  1. Click OK in the Alias Groups dialog box to return to the  ‘Customers’ Hierarchy dialog.

Assigning property groups

Next, let’s consider the Properties of the Customers dimension, which we assigned earlier through the use of Xchange.

  1. In the ‘Customers’ Hierarchy dialog box, click Properties Groups on the toolbar, . The Property Groups dialog opens as shown in the following figure:

propertygroupscontacttitle.png

Figure 7.    Property Groups Dialog - Contact

  1. Just as you specified, ContactTitle has been created as the Property Group for the Members of the Customers dimension; thus, the entry in the ContactTitle field (in the underlying database) appears next to the Member as the Property for that Member. For example, the ContactTitle for Alfreds Futterkiste is “Sales Representative”; for Ana Trujillo Emparedados y helados  and Antonio Moreno Taqueria it is “Owner” as well—demonstrating that the entries for Properties need not be unique for each Member, as they must for Aliases.

  2. Click OK.

Assigning hierarchy

Finally, let’s consider the Hierarchy of the Customers dimension, defined earlier through the use of Xchange.

  1. In the ‘Customers’ Hierarchy dialog, expand the hierarchy of the dimension in the right-hand pane so that all the children for Mexico and Sweden are shown, as in the following figure:

customershierarchydialog.png

Figure 8.    'Customers' Hierarchy Dialog

  1. You can see in the expanded Hierarchy for these two Aggregate members that the individual Customers exist within Cities (e.g., Ana Trujillo Emparedados y helados and Anotono Moreno Taqueria and others within Mexico D.F.; Berglunds snabbkop in Lulea; and Folk och fa HB in Bracke), which themselves aggregate into Countries (Mexico D.F. intoMexico, and Lulea and Bracke into Sweden). This is precisely the way the Customers dimension was constructed using Xchange, providing the opportunity to do reporting and analysis, as well as budgeting and forecasting, not only by Customer, but also by City and by Country. As such, Xchange has exploited the logic of the underlying SQL Server relational database, Northwind, since each transaction recorded the customer’s city and county.

  2. Click OK (green check mark) in the ‘Customers’ Hierarchy dialog, then click OK in the Dimensions dialog to return to the slice.

Performing a drill through

proCube has the ability to perform a Drill Through for a value in the cube, i.e., to go back to the original source for the value in the relational database. In addition to fields used to construct the cube, a user can select additional fields for the Drill Through. These additional fields are defined during the creation of the Cube through Xchange (refer to Extended Drill Through for more information).

We noted earlier that the customer Antonio Moreno Taqueria ordered 18 units of Alice Mutton; we also saw that, in addition to Antonio Moreno Taqueria, Ana Trujillo Emparedados y helados, Centro comercial Moctezuma, Pericles Comidas clasicas and Tortuga Restaurante all aggregate to Mexico D.F., the city.

Scroll down towards the bottom of the slice to the entries for the Customers under Alice Mutton, you will notice an entry of 18 for Tortuga Restaurante as well, which, along with the 18 for Antonio Moreno Taqueria, constitute the 36 units in Mexico D.F., as shown in the figure below (note that the 36 units appears for Mexico, the country, since its only “child” is Mexico D.F.).

customergroupsberlinmexicomexicodf.png

Figure 9.    Customer Groups - Berlin, Mexico, Mexico D.F.

  1. Right-click in the cell at the intersection of Alice Mutton and Mexico D. F.; in the pop-up dialog box that appears, select Drill Through (shown in the following figure):

rightclickmenudrillthrough.png

Figure 10.           Right-click Menu - Drill Through...

The Drill Through dialog opens.

  1. Select Drill Through to Relational Source.

drillthroughdialog.png

Figure 11.           Drill Through Dialog

Notice that Express Drill Through is selected, which provides lightening-fast access to drill-through data because it only shows data points for members with one or more non-zero values in the multi-relational database.

Notice that in the above dialog, you have other Drill Through options besides the default Express Drill Through. If you select the top-most option, Drill Through to RelationalSource, the three check boxes below it become active:

  • Show Summed Relational Values: Selecting this option allows you to see the granular detail (contained in the database) behind higher level roll-ups created in proCube. Select only if you have roll-ups and want to see the actual values that resulted in the roll-up figures. (For more information, go to Define Rollups Drill Through with Rollups.)

  • Express Drill Through: As indicated previously, Express Drill Through only shows data points for members with one or more non-zero values in the multi-relational database.

  • Show Defined Drill Through Values: When using an Extended Drill Through, you can see additional, defined fields that are included in the relational data source—but were not included in the cube itself. To use this feature, select it; below this, the arrows between the Dimensions and Values and Display Order become active, and the available dimensions and values appear become active. Highlight the dimensions and values you want to see in your drill through, and use the arrows to move them to the right box (which shows what will be viewed). Then click OK.

In rare cases, you may want to include members that contain zeros in a drill through. In such cases, deselect Express Drill Through in the drill-through pop-up window. Doing so will  by default select Non-Express Drill Through, which shows all data points in the Express Drill Through, plus all members’ data that contain zeros in the database. Reducedperformance occurs because proCube must read all records in a relational database, rather than only those members with non-zero data points.

proCube has the ability to drill through (by selecting Extended Drill Through) to additional user-defined fields. We will look at this function later in this topic.

  1. Click OK. The following slice opens:

salesorderdrillthroughslice.png

Figure 12.           SalesOrder:Drill Through

We see the 18 for the Customer Antonio Moreno Taqueria, but what are we to make of the two entries for Tortuga Restaurante, 8 and 10, which in fact add up to the 18 we saw in the proCube cell? These are the individual transactions, as recorded in the underlying relational database, that comprise the value of 18 as shown in the cell. Reading across, you see the constituent Dimensions/Members (in the above, for example, Measures/Quantity; Products/Alice Mutton; Customer/Tortuga Restaurante) from the Fields in the underlying database for that transaction. (You can not see additional Fields in the Drill Through, like OrderID, Invoice, etc.)

If you perform a Drill Through with the top box unchecked in the Drill Through to Relational Source dialog, you will see the constituent proCube points that comprise the cell value. In other words, rather than the individual transactional values, you would see the results for Tortuga Restaurante and Antonio Moreno as summary values, 18 and 18, respectively.

Drilling through from Excel

You can perform a similar drill through to the relational source from Excel. You first create a worksheet from the slice; next, select a cell in the worksheet; then, select the Drill Through icon button on the proCube toolbar,  .

A new sheet will be created in the workbook, a shown in the following  figure (which is a Drill Through of the Excel cell corresponding to the proCube cell, above).

exceldrillthroughxchangeexample.png

Figure 13.           Excel Drill Through Example

This information, including the figures in the Value column, can be used elsewhere in Excel—for calculations, reports, graphics, etc.

It is recommended in Excel that, if you Drill Through to Relational Source, you do so only for a cell at the intersection of what are Detail Members in proCube. This will minimize the number of rows that the Drill Through must bring into Excel.

If you have proceeded to this point using the demonstration as a guide—or if you read to this point—you have a solid grounding in how to build a cube using Xchange.

The summary steps to build a cube are as follows:

  • Create DSN setup.

  • Set options for cube creation.

  • Define the Measures dimension, identifying the Field(s) where data appears, and specifying those Fields as the Members of Measures.

  • Define the 2nd and 3rd dimensions and define Links between the Measures table and other tables.

  • Create the cube and display the results in proCube.

Modifying Relational Dimensions focuses on changes you might make to the Cube “after the fact”—after the Cube was created. In general, it is better to know the exact characteristics of a Cube you want to create before you embark on its creation, rather than going back to make modifications. Still, certain aspects of Cube creation will become absolutely clear as you proceed through Modifying Relational Dimensions and Rebuilding Cubes.

If you are upgrading from an earlier version of proCube and are planning to use the new Express Drill Through feature on an existing OLP file, you must first rebuild the Xchange cube with proCube (and select the Rebuild Metadata option).

 

 

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.
Powered by Zendesk