Creating Compound Keys in Datasphere

Business value

When defining tables in Datasphere, sometimes, one key will not be refined enough to define the uniqueness of records.

The sample data below shows 2 columns that are keys

• Controlling area
• Cost Center

Sample Dataset

How to apply compound keys

Modelers will first need to create an entity of one of the below semantic usages selected:

• Dimension
• Text
• External Hierarchy

This can be defined at the local table level or a view level.

In addition, another view of transactional data will be required to complete the model.

This example highlights creation of a compound key for the below flow from the dimension to the Analytic Model preview.

To implement the compound key, 4 steps are required.

• Step 1: Create a dimension and ensure it has key values
• Step 2: Define key order and representative key
• Step 3: Create a model and associate the dimension to the model
• Step 4: Build analytic model for consumption

Step 1: Ensure the dimension has key values

In the local table, define the primary keys (this can also be done at a view level)

Step 2: Define key order and representative key

The compound key will be defined at this step. It is important to take two considerations while completing these steps

• Define a representative key that holds the most granular level of data
• Order the key columns in the appropriate manner if there is more than 2 keys

Step 3: Create a model and associate the dimension to the model

Define an analytical dataset and create an association to the CostCenters dimension.

Step 4: Build analytic model for consumption

We can now create an analytic model by dragging in the analytical dataset with the associated dimension and view the result of the compound key in the result set.

Summary

Now should be able to view compound keys in our Analytic Models, SAC stories and SAC Add-in for Microsoft Office.

Please like, comment or post a question!