Introduction:

Since primary/foreign keys and measures/dimensions are fundamental concepts in data warehousing and data modeling, it is ever required to guarantee data integrity and bold reliability in our data models.

That´s why you may need it in most of the data warehouses & analytic software in the nowadays market and SAP Data Warehouse Cloud is not out of the rule,you will need to set referential integrity over your data in order to model and construct your top objects inside SAP Data Warehouse Cloud  (Model consumption and Perspectives).

Commonly, keys(unique and unambiguous IDs) are created as part of the master data catalogs creation, but sometimes dimensions can be based on a mix of master data records, next, we will explore how to easily auto-generate IDs for primary/foreign keys

Prerequisites:

1. You have a SAP Data Warehouse Cloud Tenant
2. You have your own Space
3. You have a table/view with dimension or catalog data without IDs or Primary Key

using SYSUUID function:

1. Get into SAP Data Warehouse Data Builder and open/create a view/table containing your records without IDs

2. Add a Projection artifact and activate “Distinct Values: ON” from the displayed menu

3. Click on the “Select All“ button and then on “Exclude selected Columns”

Excluding columns

4. Restore relevant columns for your dimension data

relevant columns

5. Add a Calculated Column artifact

Calculated Column artifact

6. Click on Add button and select “Calculated Column”

Calculated Column

7. Define a name for your ID column, using FK_YourColumnName format, next inside the Expression editor generate the corresponding unique and unambiguous IDs and convert it using: TO_NVARCHAR(SYSUUID()) functions:

Expression Editor

8. Next click on the target view, define a proper name for your view, and select “Dimension” under “Semantic Usage”.

9. Enable the “Expose for Consumption:” option, Enable “Run in Analytical Mode:” option, and “Set as Key” under “Semantic Type”

Setting target as dimension

10. Finally, validate if your key column got the key indicator, check your dimension definition, and check the outcome.

Checking definition

Conclusion:

After you have created your key column and filled it with the SYSUUID function you are enabled to use it within fact models, consumption models, and perspectives, also you can set now associations between dimensions and measures which es mandatory when modeling data in SAP Data Warehouse Cloud.

thank you for reading