Data Warehouse Cloud and Analytics Cloud – new ways of modeling data
More and more companies are aware of benefits that cloud technology could have on their business processes. It simplify IT operations, allowing enterprises to focus on business challenges rather than technical ones and offers increased flexibility and ease of adaptation compared to on-premise solutions.
While cloud adoption is still usually limited to supporting minor business processes, due to potential benefits and current market trends, organizations are turning their attention to cloud solutions for business-critical systems as well.
One of SAP proposals for such a demand is SAP Data Warehouse Cloud - a data warehouse based on the SAP HANA platform. It combines the capabilities of in-memory technology with advanced SAC analytics (SAP Analytics Cloud) in one solution, which, thanks to the use of cloud technology, can be adjusted to a company of any size.
DWC can combine data sources stored both online and locally, it also has a built-in semantic layer for data organization. For Analytics and Business Intelligence applications, the SAP Analytics Cloud solution is installed on each tenant.
The data modeling UI in DWC is very similar to the one from SAC, but use several new concepts such as "associations" or built-in semantic types. Using these functionalities, I will try to show how quick and easy it is to create a model, add data to it and create a report based on it.
Table of Contents:
- Loading tables with master data
- Creating an object of the dimension type
- Loading tables with transactional data
- Model building (combining transactional data with master data)
- Adding a hierarchy
When organizing data in DWC, we can use the built-in Graphical Views, SQL Views, or any other data integration tool. In this article, we will focus on the Graphical Views.
Step 1
Our task is to build a simple car sale model, based on data is stored in .csv files.
We will start by adding the Car Dealer master data. For this purpose, we load 3 tables into the system - one containing descriptions for the sellers, the second containing sellers attribute (location), and the third containing descriptions for the location
We import each table from the Data Builder view, using Import CSV File function.
After selecting the table and confirming the selection, the data editing screen will appear. Here we can perform data validation, select names for columns, check whether the column data type has been correctly detected etc. We can also perform transformations identical to the ones from SAC (concatenation, division, replacement, filter ...). In this example, we load a table with descriptions for car sellers and set up column names.
Press the "Deploy" button in the lower right corner and enter a name for the table in order to upload its data to the system.
Step 2
After importing 3 tables with data, we will combine them into one object of the dimension type. This will increase the readability of the model, as well as facilitate the addition of a semantic layer and associations.
For this purpose, In Data Builder we choose New Graphical View.
On the left side of UI, we can see all tables defined currently in the system. Drag the table with descriptions for the seller to the middle of the screen.
On the canvas we should now see two objects – table with Car Seller data and View 1, which is the final view of our object.
Now we drag the table with sellers attributes data and move it into canvas to join it with the seller descriptions table. If both tables share column names, the system will automatically map the tables. Otherwise, we must indicate the common field manually.
Next, drag the Location Text table and join it with Car Seller Attribute table.
Our dimension is almost ready. Now we will define the semantic data - it will give system some insight on how to read the data we just imported.
To do this, select the View 1 object. It will bring up the context properties menu on the right side of the screen. There, we can change the properties of our dimension, including its name. In the "Type" field, select the "Dimension" option. This will allow you to access additional options when defining the data. Click on the pencil icon next to the "Attributes”.
This brings up the editing window for master data. The "Semantic Type" field defines the type of information contained in the column (such as currency or date). In this example, we choose the Semantic Type "Text" option for all descriptions data, and then hide these columns (unchecking the eye icon), so they don’t appear needlessly in analytics tools. Next, we set IDs with appropriate descriptions using "Label Column" field. We also set the Sellers ID as the main key of dimension.
Now system knows which column should be treated as a key , and which column contains its description. We can now save and deploy our dimension using the Deploy button in the upper left corner.
Step 3
We can also as easily set up the table with transactional data. After importing the table using Import CSV option, we can edit it by selecting the imported table in Business Catalog. First, we change the data type to "Analytical Dataset" (equivalent of fact table/data model in DWC), which allows to define Keys, Measures and Data Types accordingly.
Step 4
Now, to combine our data using associations, we will create a new Graphical View.
Drag the table with transaction data to the canvas.
In the main object (View 1) properties window, go to the Associations.
Associations are used to define the associations between the fact-based model and the dimensions it references (e.g. adding hierarchies and descriptions).
Press the plus button to add a table.
Select the recently created Car Seller Dimension From the list of available items, then click ok. Make sure that the tables are connected by the proper Key column.
Turn the "Exposing" option on to allow the consumption of dataset. Save and select the option "Deploy”
We can now add our data to the story in the built-in SAP Analytics Cloud environment (or connect to it from your tenant) and see that the transactional data, along with descriptions and attributes for sellers, is available.
Step 5
Using associations, we can also easily enhance model by adding a hierarchy. For this purpose, open the seller dimension, select the main object of the view, and in the properties window, click on the hierarchy icon.
This brings up the editing window for hierarchy data, where we have two options for adding a hierarchy -Level Based and Parent-Child . As at the moment we only have one attribute (location for a seller) loaded into the system our options are limited, but for a purpose of example lets try adding Level-Based hierarchy ( in a Location -> Dealer schema). Press the plus button, select Level-Based, and set the order of the hierarchy, starting with the Location key (the higher the item's position on the list, the higher it is placed in the hierarchy).
In order to use this data in reporting, we need to save and deploy the dimension, then do the same for the actual model.
After refreshing the data in SAC, we can see that dealer data is displayed in the form of a hierarchy
