Tutorial: How to Model Data with SAP Data Warehouse Cloud?
What is Data Warehouse Cloud?
The newest addition to the SAP Business Intelligence offering, SAP Data Warehouse Cloud (DWC) is a cloud-based data warehouse solution created by SAP. It is very easy to integrate data (from either a SAP system or an external one), to rework, complete this content with other sources, to finally render it into a summarized form in a dashboard.
It is a tool that allows managing end to end data flow. To achieve that, we will examine 3 levels:
The first SAP Data Warehouse Cloud accesses were granted to SAP partners in August. A business Intelligence enthusiast, I wanted to share my first experience with this new SAP Cloud product (beta release) with you.
In the following article, I will explain in four easy steps how to create a dashboard based on data from a third-party system, through an “OData” data connection.
Then, enter the user name and password to access your OData service (if no authentication is required, you will still be required to complete the “User Name” and “Password” fields to access the confirmation button).
Once those fields are filled, click on “Confirm.”
Once the connection is created, you need to check that it is operational by clicking on the “Status” button of the newly added line. If everything works, a message indicating that the connection is valid will appear.
You need to assign this new connection to the management space before you can use it. Go to your work space (1), then switch to the “Monitor space” mode.
Add users that will need to access it.
Finally, click on the “+” button to add a connection (4) and select the one previously created.
4) Modeling your data
Once the connection is established, you will be able to model your data.
In the left-side drop-down menu, click on “Data Builder,” and select the previously configured working space.
Once in that working space, create a new graphical view to model your data.
Once in the modeling space of the graphical view, select the “Sources” tab; in the connection folder, click on the one you just created.
You will be able to see all the entities that have been uploaded from the OData flow.
To begin, drag/drop an entity on “Orders” (1). The system will prompt you to import the table so it can deploy the data, accept.
A second box, called “Output” (2), is automatically created. It materializes the last step of a graphical view. It will allow us to preview the output data.
It is now possible to model the data as we wish thanks to the tool’s numerous functionalities.
In our example, I will join data from different entities among themselves, here, order-related data to those of the employees.
The drag/drop function of one entity onto another allows to easily join them and quickly get a finished model.
To explain this example’s modeling, five important functionalities should be highlighted:
A filter can be applied to the data exiting an entity to then be joined;
We can, at any time during the data modeling, see which data is being uploaded;
Formulas can be applied after each step (uploading of data, joins, etc.). For example, we will create a column based on two metrics of the previous entity;
It is possible to select the Join Type linking entities, there are six possible choices: inner, outer-right, outer-left, crossed, complete, natural;
The projection operates after each joint to select the data to upload on each side of the sources of data comprised in that joint.
At the last stage, after applying all the desired settings to ensure all data is appropriately uploaded, it is possible to preview the data.
To access that data in the next step, the graphical view must be set to “Fact” to differentiate the measurements of the dimensions.
You must then sort the measurements of the dimensions, and finally, save and deploy the graphical view.
4) Exposing Data
Let’s move on to the Data Visualization step
In the drop-down menu, chose “Story Builder” and then select a work space;
Click on “Create Story” to create a new presentation.
The system will prompt you to choose a data source.
In the drop-down menu, select the graphical view previously created.
Once the data source is added, start creating data charts such as the ones we can create with SAP Analytics Cloud.
Create charts by selecting dimensions as the analysis’s axis, and then the metrics you wish to explore.
Create as many graphs as you need to make a relevant analysis of your indicators.
Once your dashboard is completed, it is ready to be shared with stakeholders, during daily, weekly or monthly meetings.
In this article, we looked at how you can easily upload data from an OData data source to a SAP Data Warehouse Cloud. We could just as easily replicate this flow by importing data from a cloud-based or on-premise SAP S/4HANA system, a SAP BW/4 HANA, or SAP BW using to the two other suggested connection types “SAP ABAP” or “SAP HANA”.
The integration of the SAP Analytics Cloud presentation layer in SAP Data Warehouse Cloud allows gaining access to many of the functionalities of a product tested and approved for the creation of dashboards.
The modeling part is complete and provides all the functionalities required to filter data, add formulas, create joints, or to visualize data at any stage.
Finally, the most important innovation for this SAP Cloud tool is its ability to store a large volume of data online. All this without risking the safety and confidentiality of your data, thanks to the expertise SAP acquired in that field over the years.
Personally, I think SAP is heading in the right direction with its Cloud Suite. SAP Data Warehouse Cloud makes it easy to connect to the various data sources comprised in Information Systems (IS), SAP or not. As for licences, SAP applies a pay-per-use policy, a true plus to control the costs related to a solution.
I am impatient to see how this solution will evolve in time when considering the upcoming functionalities, such as the live creation of Cube or KPIs!