Creating Compound Keys in Datasphere
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
How to apply compound keys
Modelers will first need to create an entity of one of the below semantic usages selected:
- 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.
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!