Spotfire® User Guide

Combining OLAP data and other data in the same analysis

Hierarchy values in a cube have both a caption and key. The key will always be unique, but the caption can be the same as for another hierarchy value. The key can be used for identifying a value. When you visualize data from an in-database cube in Spotfire, you may encounter cases where you cannot separate multiple instances of a value from each other (for example, when there are more than one city in the world with the same name (i.e., caption), like the case with Paris, France and Paris, Texas) in a visualization. In that case, you probably want to match values based on the keys, rather than on the values themselves. This is done by selecting the KeyOf method on the matching column of the cube directly.

About this task

Note: When you want to combine cube data and other data (for example, data from a relational database) you must have a column that matches the cube's KeyOf values in the relational data. The keys can often be exported from the cube to provide good matching.
Note: If a KeyOf value is missing in a cube, an error message will be displayed in the visualization that shows the cube data. In the details for the error it will be indicated that an error occurred in the external data source. For example, this could occur if you configure a relation between a cube and a data table coming from another external system, and the data in the other system contains the key Hannover whereas the cube key is called Hanover. When Hannover is used in cube database queries, no key is found and an error is shown.

Defining a new relation between a relational data table and a cube data source using keys

If you have data in a cube data source and want to be able to use this data to retrieve details about a certain value from a relational data source on demand, or if you simply want to be able to propagate markings from the cube data source to visualizations based on the relational source, you may want to define a relation between the two data tables.

Before you begin

You must have a data table based on a cube data source and one data table from a relational source available in the analysis.

Procedure

  1. On the menu bar, select Data > Data table properties.
  2. In the Data Table Properties dialog, go to the Relations tab.
  3. Click Manage Relations.
  4. In the Manage Relations dialog, select the data table to Show relations for.
  5. Click New.
  6. Select the cube data table you want to connect to from the Left data table drop-down list and the relational data table from the Right data table drop-down list.
  7. Select the column containing the identifiers in the Left column and Right column drop-down lists.
  8. Provided that the cube data source is on the left, under Left method, select the KeyOf method.
    Comment: This specifies that the match should be done using the cube key of that identifier and not the actual value.
  9. Click OK to close all dialogs.

Results

The relational data table and the cube data table are now related.
Note: When you are using a cube hierarchy on an axis in a visualization you will automatically see the caption and not the key. To explicitly show the keys in your visualizations, you need to set up a custom expression.

Creating column matches based on keys to view cube data in the same visualization as other data

If you have two columns with the same dimension values in a cube data source as well as in a relational data source and you want to display the data in the same visualization you may need to apply the KeyOf method to match the values.

About this task

Before you begin

You must have a data table based on a cube data source and one data table from a relational source available in the analysis.

Procedure

  1. On the menu bar, select Data > Data table properties.
  2. Go to the Column Matches tab.
  3. In the Data tables list, select the cube data source.
  4. Click New.
  5. In the New Match dialog, select the other data table of interest under Right data table.
  6. Make sure the columns with the matching information are selected in the Left matching column and Right matching column selectors.
  7. Open the column selector for the cube column under Left Matching column and select Methods to open the menu.
  8. Select KeyOf.
    Comment: It is not possible to use KeyOf as a transformation, it must be applied directly as a method on the matching column.
  9. Click OK to close all dialogs.

Results

You can now show data from both the cube data and the relational data in the same visualization.
Note: When you are using a cube hierarchy on an axis in a visualization you will automatically see the caption and not the key. To explicitly show the keys in your visualizations, you might need to set up a custom expression.
Copyright © Cloud Software Group, Inc. All rights reserved.