Technical Articles
SAP Datasphere Analytic Model Series – Data Model Introduction
Figure 1: A complex data model that we’ll use along the entire blog series
Introduction
The SAP Datasphere Analytic Model Series is intended to provide you with useful guidance on how to utilize the new Analytic Model to leverage the potential of your data landscape. The Analytic Model allows for rich analytical modelling in a targeted modelling environment and will be THE go-to analytic consumption entity for SAP Datasphere.
This article is the second in the blog post series and introduces an example data model that we’ll use along the entire blog series listed below:
- Blog Post #1: Introducing the Analytic Model in SAP Datasphere
- Blog Post #2: Data Model Introduction – Current blog
- Blog Post #3: Motivation & Comparison with the Analytical Dataset
- Blog Post #4: SAP Datasphere Analytic Model Series – Calculated and Restricted Measures
- Blog Post #5: Exception Aggregation
- Blog Post #6: Using Variables in Analytic Model
- Blog Post #7: Time Dependency for Dimensions and Texts in Analytic Model
- Blog Post #8:SAP Datasphere Analytic Model Series – Data Preview
In this blog, we describe the data model structure and help you import it with data. As a result, you’ll have a rich model for your own experiments with the Analytic Model and you’ll also be able to immediately apply the learnings of upcoming blogs since they are all based on exactly this model.
Understanding the Data Model
Data model consists of sales opportunity data of employees for various products and customers of the company across different cost centers within sales organizations of the company.
Also, Time related Dimensions linked to data model provide options for doing time related drilldown, reporting and filtering. Moreover, Hierarchies for Employees and Products can be used for further analysis.
Some of the example reporting scenarios are mentioned below:
- Identify Top/Bottom N Employees in current month/quarter/year in terms of Sales opportunity value.
- Identify total value of Missed opportunities across products in current month/quarter/year
- Identify Top/Bottom N Products in terms of Sales opportunity value
PFB list of tables in our Data Model:
- Sales Opportunity Data
- Header Table – MCT_Opportunities
- Item Table – MCT_OpportunityItems
- Product data
- Product dimension with hierarchy – MCT Products
- Product categories dimension table – MCT_ProductCategories
- Product Groups texts table – MCT_ProductGroupTexts –
- Controlling Area data
- Dimension MCT_ControllingArea
- Cost Center data
- Dimension MCT Cost Center
- Sales Organization data
- Dimension – MCT_SalesOrganization
- Sales Organization Texts – MCT_SalesOrgText
- Item Status Texts – MCT_ItemStatusTexts
- Employee data
- Dimension with hierarchy data – MCT_Employees
- Time data
- Dimension Day – SAP.TIME.VIEW_DIMENSION_DAY
- Translation Table – Quarter – SAP.TIME.M_TIME_DIMENSION_TDAY
- Translation Table – Month – SAP.TIME.M_TIME_DIMENSION_TMONTH –
- Translation Table – Day – SAP.TIME.M_TIME_DIMENSION_TQUARTER
These tables are associated to each other as is depicted in the image at the top of this blog or in the impact & lineage diagram shown below:
Figure 2 : Impact and Lineage Diagram of the sample data model
To check the association further in detail, please refer the tables and views associations once you have imported the data model into SAP Datasphere.
Importing the Data Model
For data model import, it’s preferred to create a new space. However, you can use existing spaces if you are confident that there will be no conflict which will arise due to the existing objects like Table, Views in your space.
1. As a first step, download the ZIP files from below path to your local directory. It contains the data CSV files and the CSN export for the data model.
Figure 3: Item List screenshot zip file
2. Next, go to your space and click on “Create Time Tables and Dimensions”, as shown below.
Figure 4: Space Builder Time Dimension and Tables
3. Then click on Create in the opened dialog without changing anything.
Figure 5: Time Dimension and Tables Dialog
4. Next, open your space in the Data Builder and check whether the Time Dimensions and tables you created previously are deployed in the space, as shown below
Figure 6: Time Dimension and Tables Data Builder
5. Once verified, click on Import Objects from CSN/JSON files, as shown below.
Figure 7: Import CSN/JSON File Data Builder
6. Here, in the opened dialog select the file ER_MCT_MEDIUM.json for import.
Figure 8: Choose JSON File
7. From the opened dialog, select all the objects and click on Import CSN File, as shown below.This will create the missing tables and views for the data model in your space and will create associations between them.
Figure 9: Import CSN/JSON File Objects
8. Next, go to Tables in Data Builder and check for imported objects, as shown below. All the tables will be in Not Deployed status, as shown below.
Figure 8:Imported Tables status
9. Now, open the table MCT Opportunity Items and click on Deploy, as shown below. This will deploy the table along with all the associated tables.
Figure 9: Deploy Table
10. Next, upload the csv file to import the data in the MCT Opportunity Items, as shown below.
Figure 10:Upload CSV data in Table
11. Click on Choose File and select MCT_OpportunityItems.csv, as shown below.
Figure 11: Choose the csv file
12. If you are importing the data for the first time then keep the default settings and upload the file by clicking on Import, as shown below. Otherwise, select the checkbox Delete Existing Data before Upload to make the table empty before importing anything.
Figure 12: Preview CSV file for Import in Table
13. Next, check the Table data preview to validate if the file data has been uploaded correctly.
Figure 13: Preview Table Data
14. Once everything is verified, switch back to Data builder and you will notice all the associated tables have been deployed.
Figure 14: Check Associated Tables
15. Next upload the data in the remaining tables by opening them and importing the CSV files for each table like we did in the above steps for MCT Opportunity Items tables.
As an example, please refer below screenshots where CSV file is being uploaded to table MCT Employees.
Figure 15: Example – Upload CSV data in Table
Figure 16: Select relevant CSV file
Figure 17: Preview CSV data
Figure 17: Deploy Table if needed
Note: Please do remember to complete the data upload activity for all the remaining tables and don’t forget to deploy the tables if they are in Not Deployed status.
16. Next, go to Data Builder -> Views and deploy the Analytical Data Set(ADS) V_MCT_OpportunityItems_v2 by opening it and clicking on Deploy.
Figure 18:Open ADS
Figure 19: Deploy ADS
17. Once deployed, you can create an Analytic Model directly on top of this ADS by going to its Details properties and clicking on Create Analytic model, as shown below.
Figure 20: Analytic Model creation from ADS
18. Give your Analytic Model a Name and click on Deploy. Next check the Impact and Lineage, as shown below.
Figure 20: Analytic Model
19. Choose Dependency Analysis, and observe the relationships and associations between different tables and views which are feeding the Analytic Model.
Figure 21: Analytic Model Lineage
20. Then, quickly check the data preview in Analytic Model to make sure it is working fine, as shown below:
Figure 22: Analytic model Data preview
20. Subsequently, you can use Analytic Model in SAP Analytics Cloud as well. For that go to SAP Analytics Cloud and choose Optimized Design Experience. Furthen, you can add custom calculated measures or attributes to stories created on top of Analytic Model within SAP Analytics Cloud.
Currently, we are working on making this data model available as a data product on SAP Datasphere Marketplace which will remove the need of using zip files and further streamline the import process.
Once it is available, the blog we will be updated or a new blog will be published to show an alternative on how the data model can be imported from SAP Datasphere Marketplace and used in Analytic model.
In the upcoming blogs, we will explore the different features in Analytic Model and show few reporting scenarios which can be fulfilled using this data model.
Conclusion
This blog introduced the data model that we’ll use along the entire blog series, helped you fill it with data and subsequently build an Analytic Model on top of it.
Thanks for reading! I hope you find this post helpful. For any questions or feedback just leave a comment below this post. Feel free to also check out the other blog posts in the series.
Best wishes,
Jai Gupta
Further Links
Find more information and related blog posts on the topic page for SAP Data Warehouse Cloud.
Hello Jai Gupta
thanks for your Blog. Very helpful for the beginning.
I was trying to tweak the views/models a bit, but unfortunately I cannot achieve what I tried... e.g.:
The Sales Organization has its own text table, and the ID is contained in the Opp header table. However in my SAC story, the Sales Org does not show texts, so I tried to associate the text table with the header table, which somehow does not work:
I see an error message that I obviously need to assign "all key fields" which means the language key in the text table ... but there is no "source key" to assign ... and I cannot find a "constant" to use, like e.g. you would do in a CDS view.
I could deploy it, but in the Analytic model, my association does not appear, and the SAC story still shows keys only.
What is the solution to display "Key & text" for the Sales Org in my Story, based on the Analytic model?
Thanks, Martin
Hi Martin,
Thanks for replying.
I do see both ID and description in the SAC story, as shown below.
Can you please confirm if you have expanded the association for the sales org in the Analytic Model as shown below. You need to click on MCT Opportunity Header in the AM and select SalesOrg which will add the associated MCT Sales organization dimension to your Model.
Then in the SAC story you will notice two Sales org fields as shown below. Please pick the one from the associated dimension.
Also, please make sure in the sales org texts table, you have define the semantics as shown below.
And you have associated the MCT Sales Organization text table with the MCT Sales Organization table using text association, as shown below.
If still u have the issue, then please share the screenshots for the steps you followed.
Regards,
Jai Gupta
Hello Jai,
thanks a lot for the explanation via Screenshots.
In the meanwhile I also found this video: Analytic model explaining all the details 🙂
Now it works like intended.
Best regards,
Martin
Hello Jai,
looks like the link to the download isn't working anymore.
regards
Ingo Hilgefort, Rizing
Hi Ingo,
Link has been updated, pls chk.
Regards,
Jai
appreciated
ingo
Hi Jai,
Thank you for an informative blog. Unfortunately shared link is not accessible, can you please update it? would be good if you can upload them to a Github repository.
Thanks
Sree
Hi Jai,
Its great how you have depicted all the steps one by one, Thankyou for sharing this guided blog. Though the link provided for the CSV files is not working. Request you to please update it so that we can take advantage of this tutorial.
Thanks and Regards,
Kuhoo Nayak.
Link Updated, pls chk.
Regards,
Jai
Hello Jai,
It seems like the link to the download isn't working.
Regards,
Hang
Link Updated, pls chk.
Regards,
Jai