SAP Datawarehouse Cloud [Part III]
- SAP Datawarehouse Cloud [Part III]
Data Modeling in SAP Data Warehouse Cloud
In previous blogs (Part I and Part II), we introduced SAP Data Warehouse Cloud and its concept and use of Spaces. This blog introduces Data modeling in a Space so it can be exposed to a Story for Analysis.
Data Models can be accessed from the Business Catalogue or the Data Builder.
The Business Catalog provides an overview of all models one can use across all the spaces one has access to. It guides the business user to find the right model to create a Story for Analysis.
The flow of a model and data provisioning can be traced from the Lineage view.
One can navigate to the Editor by clicking on “Open in Data Builder” from each object.
One can create a Local Table from the table builder or File import.
Models to use as the basis for a Story can be created with the: Table, Graphical, or SQL View Modeler.
The Entity-Relationship Model is a conceptual model to organize views, tables, and their relationships.
Entity Relationship Diagram (ERD)
In an ERD the relationship between the different entities and how they relate to each other are documented on a High level. The ERD model is a Semantic modeling type. It is a holistic view of the business scenario and describes the logical view of your model. An entity is a Data element of interest towards the business scenario, for Example, “Sales Organization”, “Customer” and “Sale” would each be an entity. Defining the Entities and how they relate, the ERD should be the first step in modeling of your Data.
From the model several actions are possible:
: Create a data model view directly from the selected entity object
: Create a data model Table directly from the selected entity object
: Create associations between entities, one can also use drag and drop
: To select multiple objects at once
Open in view Builder: To Edit objects
: To preview the data of the entire model or for each object
It is also possible to add a Level-based or Parent-Child Hierarchy ass additional semantic information.
There are three possible .
- : A relational dataset is the basic format of a model
- : Dimensions are used to group data whose content is logically connected. The customer dimension could, for example, be made up of the customer number, the customer group, and the levels of the customer hierarchy
- : An analytical dataset is a data that indicates a specific transaction, like the sale of a product. Analytical datasets can be used for stories
Makes the artifact available for consumption in the Story Builder (Analytical Datasets only) and other analytical clients (all types).
The Column properties are set:
and how to aggregate them and .
A view can be created in a Graphical way with the “Graphical view” or in code with the “SQL view”.
The Graphical view builder allows one to create a view in a graphical environment using drag and drop. Tables and views can be combined using joins and unions. It is also possible to modify the output by filters, renaming or removing fields, and add calculated elements. A description can be given to the output columns, so it has a relevant meaning towards the business.
To be able to be consumed in a Story the view needs to be defined as an analytical dataset and must include at least one measure.
The definition of the Entity-relationship model will guide and suggest in the setup of the Data Model Graphical view, so the set info is made available.
In the context button several transformations are available:
: to add and see filters.
/ : to rename, exclude, or restore a column. Learn more:
: Edit Expression in the Calculated Columns Properties panel to modify your expression.
: to connect the objects via . Previously defined associations help you to create the right connections.
: to edit your objects.
: to preview your data.
Just as in an ERD model, explained above, the properties are defined: type, allow for consumption, measures, and attributes.
With the SQL view, a view can be created with SQL code using the SELECT statement and set operators like JOIN/UNION. If one is unfamiliar with SQL the graphical tool is more intuitive.
From the Data builder, one can define a Table. These tables can be used in views and are visible in the Business Catalog. There are three types of tables: Relational Dataset, Dimension, and Analytical Dataset. Hierarchies can be defined for Dimensions. Stories can be built on Analytical Datasets.
One can import data locally to the defined table. All data can be deleted from the table with Delete. It is also possible to create a table from a file upload.
CSV files can be uploaded and saved as a local table. Once it is saved as a table it can be found in the table repository and edited.
SAP Data Warehouse cloud provides several ways to upload, store, and combine local and remote data in a Data Model so it can be used in a Story to provide Business insights in a Quick and efficient manner.
Blog by Youri Van Heester and Jeroen Coppens