Blogs / SAP / SAP Data Warehouse Cloud / SAP Data Warehouse Cloud – Data Modeling of CSV Source Files

SAP Data Warehouse Cloud – Data Modeling of CSV Source Files

Dec 9, 2019

SHARE

SAP Data Warehouse Cloud is a brand-new Data Warehousing solution on the cloud by SAP. It is powered by SAP HANA Cloud Services and enables you to perform advanced analytics using in-built SAP Analytics Cloud.

Scenario

Consider you have Retail Sales and Cost Transaction data across various Product Categories along with the definition of each of the Product Categories in three different CSV files – Retail Sales, Retail Cost, Product Category Text. You may need to import these files into SAP Data Warehouse Cloud and create a Data Model called ‘Retail Data’ that would help you to derive KPIs, metrics and other key data points that will benefit your retail business.

Sample Data present in the three CSV files are as shown below.

SAP Data Warehouse Cloud – Data Modeling of CSV Source Files
Retail Sales
SAP Data Warehouse Cloud – Data Modeling of CSV Source Files
Retail Cost
SAP Data Warehouse Cloud – Data Modeling of CSV Source Files
Product Category Text

Solution

Converting CSV Files to tables

1. Click on the ‘Data Builder’ icon in the left panel which will list the available Spaces in which you can create Data Models. Select the required Space.

SAP Data Warehouse Cloud – Data Modeling of CSV Source Files

2. Click the ‘Import CSV File’ option and select the CSV file – Retail Sales Data.

SAP Data Warehouse Cloud – Data Modeling of CSV Source Files

3. The ‘Import CSV file’ dialog gives us a preview of the data to be loaded. You can edit the field names and datatype of the fields(by default all the fields take String(5000)). In this case, you can change the data type of the field Sales_Amount to Decimal(15,2). Click on the ‘Import’ button once the modifications are complete.

SAP Data Warehouse Cloud – Data Modeling of CSV Source Files

4. Now, the table ‘Retail Sales Data‘ gets created in the Space. Repeat the steps 2 and 3 to create the tables ‘Retail Cost Data’ and ‘PD Text’ from the remaining CSV files. Once you are done, you will have all the tables ready for Data Modeling in the Space, ’RETAIL_SPACE’.

Graphical Modeling

1. Click on the New Graphical View icon available in the Data Builder screen which will navigate you to the Data Modeling screen.

SAP Data Warehouse Cloud – Data Modeling of CSV Source Files

2. The list of artifacts available for modeling can be seen in the left panel. Drag and drop the table ‘Retail Sales Data’ from the Repository into the canvas.

SAP Data Warehouse Cloud – Data Modeling of CSV Source Files

3. Now drag and drop the table ‘Retail Cost Data’ on top of the node ‘Retail_Sales_Data’. You will get two options to combine the sources – Join and Union. Select the Join node to combine the two data sources.

SAP Data Warehouse Cloud – Data Modeling of CSV Source Files

4. The Join Properties panel in the right has the option to select the join type. In this case, you can choose Inner Join. The list of fields from both the Data Sources can be seen listed where by default, the two tables will be joined based on the names of common fields present in both the tables. However, you can manually join by choosing columns from each of the tables based on the requirement. Here, the tables are joined by default on the columns Product_Category and Calendar_Year_Month. You can notice that an additional “Rename/Hide” node is added next to the ‘Join’ node in the Canvas. You may rename and hide the resultant columns from the ‘Join’ node if required.

SAP Data Warehouse Cloud – Data Modeling of CSV Source Files

5. Similarly, you can drag and drop the table ‘PD_Text’ which has Product Category Text details after the Rename/Hide node to bring in the text details of Product Category.

SAP Data Warehouse Cloud – Data Modeling of CSV Source Files

6. Having combined the tables, you can modify the properties of the Output node. You can set up the type of view as one of the following types – Relational Data set, Dimension, Analytical Data Set. In this case, since this is a Fact-based model with Measures and Attributes, you can select the Analytical Data Set.

SAP Data Warehouse Cloud – Data Modeling of CSV Source Files

7. In this model, you can set the Semantic type “Calendar – Year Month” for the field “Calendar_Year_Month”.

SAP Data Warehouse Cloud – Data Modeling of CSV Source Files

8. The fields “Sales Amount” and “Cost” fields can be changed to Measures. By default, the Aggregation Type of measures is set to “SUM”. You can choose “MAX”,” MIN” and ”COUNT” based on the requirement.

SAP Data Warehouse Cloud – Data Modeling of CSV Source Files

9. Now you can save and deploy the view using the highlighted icons in yellow on the top panel. Once deployed successfully, you will be able to consume the model and create SAP Analytics Cloud Stories within the SAP Data Warehouse Cloud Platform and also consume it in building other Data Models as well.

The SAP Analytics Cloud Stories will help you visualize Sales vs Cost across various Calendar Months and Product Categories, Growth Rate by Product Category/Calendar Month etc.,  which will aid you to make better business decisions.

SAP Data Warehouse Cloud – Data Modeling of CSV Source Files

To explore additional modeling functionalities available in SAP Data Warehouse Cloud please check the upcoming blogs. For more information visit this link.


Corporate HQ:
5920 Windhaven Pkwy, Plano, TX 75093

+1 888-227-2794

+1 972-232-2233

+1 888-227-7192

solutions@visualbi.com


Copyright © Visual BI Solutions Inc.

Subscribe To Our Newsletter

Subscribe To Our Newsletter

Join our mailing list to receive the latest news and updates from our team.

You have Successfully Subscribed!

Share This!

Share this with your friends and colleagues!