Creating a basic Data Mart based on a classic star schema with SAP Data Warehouse Cloud

[former_member610869]

Introduction:

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.

https://learning.sap-press.com/sap-and-the-cloud

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.

https://www.sap.com/insights/what-is-a-data-warehouse.html

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.

Prerequisites:

1. You have a SAP Data Warehouse Cloud Tenant


2. You have your own Space


3. 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/

Creating Dimensions:

1. Log- On to your SAP Data Warehouse Cloud Tenant


2. Hit the Business Builder

accessing to Business Builder

Here is where you can define/reuse your dimensions from Data Builder:

defining dimensions

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):

selecting deployed View Dimension

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:

Auto Detection feature

Finally, verify your attributes and key definitions, set your dimension as "Ready to Use" and save it:

saving dimension

repeat all previous steps for all dimensions needed

Creating measures (Analytic Set):

Click on New Analytical Dataset:

New Analytic Dataset 1

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

V_F_Ingreso - Analytical Dataset

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:

Properties

Finally, verify your attributes, measures, and key definitions, set your Dataset as "Ready to Use" and save it:

Saving Dataset

Next, we need to define data associations between measures and dimensions, click on "Associations" and click on add icon:

Associations

Select the required dimension and click on Apply:

selecting dimension

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:

Foreign Key

Immediately an auto validation process is launched:

validation process

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.

validation success

Creating Fact Model:

From here we will full define our classic star schema-based data mart, click on" New Fact Mode":

New fact Model

Define a name for your model and click on step 2:

Fact Model Name

Select your Analytic Dataset defined previously in the "Creating measures (Analytic Set)" section:

Dataset selection

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:

adding attributes

Select the corresponding dimension:

dimension selection

To continue click on step 3

Step 3

Next click on "Link Association Path":

Link Association Path 1

next, the dimension view should be inside our fact view indicating that attribute for that dimension is now available for use, click on "create":

Link Association Path 2

Finally, verify that now our dimension is listed in the "Dimension Sources" section:

Dimension Sources

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:

selecting attributes

when completed all dimension attributes association verify the final list:

attributes list

 

Change the Status to "Ready to Use"  and save it:

Changing Status

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:

Exposed Dimension Sources

Repeat steps for all required exposed dimensions required:

Exposed Dimension

Finally, check measures, attributes, and Exposed Dimension Sources sections to verify that everything is correctly defined

Measures, Attributes, and Exposed Dimension Sources sections

 

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"

New Consumption Model

Select base model fact, click on "on step 3" and "create":

Fact Source

In the "General" section enable "Public Data Access":

Public Data Access

In the "Source Model" section, add and choose the dimension created previously under the "Creating Dimensions" sections:

Choosing Dimensions(Business Entity)

Again as in previous sections, click on "Link Association Path" and repeat this step for all required dimensions:

Link Association Path

Continue clicking on "Attributes" and "Measures" and select all available attributes/measures:

 

available attributes/measures

Creating Perspective:

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":

 

Perspective

As the last step, verify that all your constructed objects look similar to the next list image:

List Objects

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.

Story Creation Screen

Conclusion:

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

https://community.sap.com/topics/data-warehouse-cloud/best-practices-troubleshooting