Creating a basic Data Mart based on a classic star schema with SAP Data Warehouse Cloud
One of the purposes of cloud software is to simplify, empower and enable all non-IT users with all about the benefits of corporate software investments, focusing on the “functional aspect” rather than all the challenges involved through new software implementation.
SAP Data Warehouse Cloud continues using classic “measures and dimensions” concepts as the foundation for the highest level objects(“Model consumption” and “Perspectives”) also there exist some specialized roles in the data warehousing life cycle(data warehousing techniques, Non-relational DBMS, Multidimensional Models, ETL processes, connections, etc), that means that the technical layer (Data Builder, Data Integration, Connections, and Configuration ) have to be done under best practices and using the most widely accepted techniques.
next, we are going to use a classic data warehousing approach to easily construct a basic data mart (“Consumption model” and “Perspective”) based on a classic star schema located under the Data Builder of SAP Data Warehouse Cloud since right there is where all required data engineering gets performed.
- You have a SAP Data Warehouse Cloud Tenant
- You have your own Space
- You have constructed a star schema(measures and dimensions associated with keys) located under Data Builder, in case you do not have one please check: https://blogs.sap.com/2020/01/18/my-first-story-with-sap-datawarehouse-cloud/
- Log- On to your SAP Data Warehouse Cloud Tenant
- Hit the Business Builder
Here is where you can define/reuse your dimensions from Data Builder:
Next, choose the dimension, it should exist deployed as View Dimension in the Data Builder layer, commonly time dimension is ever required in data warehousing(repeat steps for all dimensions needed):
If the dimension is correctly defined and under best practices for data warehousing, SAP Data Warehouse Cloud will detect its attributes and key definitions, it is very relevant since data relations of our multidimensional model(data mart) and every data warehouse using best practices should be defined by these key definitions. next confirm attributes and key definition auto-detection:
Finally, verify your attributes and key definitions, set your dimension as “Ready to Use” and save it:
repeat all previous steps for all dimensions needed
Creating measures (Analytic Set):
Click on New Analytical Dataset:
Next, select the corresponding Analytic Dataset containing measures and data relations to dimensions, it should exist deployed as Analytic Dataset(Business Entity) in the Data Builder layer
if the Dataset is correctly defined and under best practices for data warehousing, SAP Data Warehouse Cloud will detect its attributes, key definitions, and measures, it is very relevant since data relations of our multidimensional model(Data Mart) and every data warehouse should be defined by these key definitions. next confirm properties detected:
Finally, verify your attributes, measures, and key definitions, set your Dataset as “Ready to Use” and save it:
Next, we need to define data associations between measures and dimensions, click on “Associations” and click on add icon:
Select the required dimension and click on Apply:
Now click on Foreign Key Field, and select that one corresponding with the foreign key field located in the respective dimension, remember key relations is a common and widely used method for data warehousing construction:
Immediately an auto validation process is launched:
We will get 100% validation if data association integrity is correctly defined, it means that for every record in fac table(measures) exists at least one record in the dimension table, after that save it.
Creating Fact Model:
From here we will full define our classic star schema-based data mart, click on” New Fact Mode”:
Define a name for your model and click on step 2:
Select your Analytic Dataset defined previously in the “Creating measures (Analytic Set)” section:
Next, a diagram with the associated objects will be displayed, check that there exist all the objects involved in your model, next proceed to include all the attributes that exist under the associated dimensions, click on add icon:
Select the corresponding dimension:
To continue click on step 3
Next click on “Link Association Path”:
next, the dimension view should be inside our fact view indicating that attribute for that dimension is now available for use, click on “create”:
Finally, verify that now our dimension is listed in the “Dimension Sources” section:
Continue clicking on “Attributes” and select all available attributes, repeat all previous steps for all other dimensions related to the fact table in order to complete all attributes:
when completed all dimension attributes association verify the final list:
Change the Status to “Ready to Use” and save it:
Finally, we need to expose the dimensions associated with our fact model when constructing the Consumption Model and Perspective, click on “Exposed Dimension Sources” and select the corresponding dimensions:
Repeat steps for all required exposed dimensions required:
Finally, check measures, attributes, and Exposed Dimension Sources sections to verify that everything is correctly defined
Creating Consumption Model:
At this point, we have constructed a basic data mart under a classic approach, however, there is missing security, and consumption best practices to fit most of the analytics solutions in the nowadays market, that’s the case of SAP Analytics Cloud and others, to do so, finally, we will construct a Consumption Model, it is very similar to Fact Model but with some relevant differences:
Click on “New Consumption Model”
Select base model fact, click on “on step 3” and “create”:
In the “General” section enable “Public Data Access”:
In the “Source Model” section, add and choose the dimension created previously under the “Creating Dimensions” sections:
Again as in previous sections, click on “Link Association Path” and repeat this step for all required dimensions:
Continue clicking on “Attributes” and “Measures” and select all available attributes/measures:
Go to the “Perspective” section, define a significant name for your “Perspective” and select all available Measures and Attributes that also enable “Run in Analytical Mode” and click on “Deploy”:
As the last step, verify that all your constructed objects look similar to the next list image:
Finally, to validate the correct creation of all about our model, launch the Story Builder and verify there is listed our “Perspective” constructed all previous steps through.
This blog post covers a very simple model, with just a measure and two dimensions, focusing on the steps, in the end, basic examples always let us go from the simple to the complex.
thank you for reading.
Please follow me for more related blogs