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.
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.
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.
2. Click the ‘Import CSV File’ option and select the CSV file – Retail Sales Data.
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.
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’.
1. Click on the ‘New Graphical View’ icon available in the Data Builder screen which will navigate you to the Data Modeling screen.
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.
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.
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.
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.
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.
7. In this model, you can set the Semantic type “Calendar – Year Month” for the field “Calendar_Year_Month”.
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.
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.
To explore additional modeling functionalities available in SAP Data Warehouse Cloud please check the upcoming blogs. For more information visit this link.