Skip to Content
Technical Articles
Author's profile photo Jai Gupta

SAP Datasphere Analytic Model Series – Data Model Introduction

Figure%201%3A%20A%20complex%20data%20model%20that%20we%u2019ll%20use%20along%20the%20entire%20blog%20series

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:

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%202%20%3A%20Impact%20and%20Lineage%20Diagram%20of%20the%20sample%20data%20model

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.

https://sap-my.sharepoint.com/:u:/p/jai_gupta/ETch7zHuRxRMs1LmzLS0ydYBgI1HqZmMgaML4oAVJu-77A?e=pQ3n75

 

Figure%203%3A%20Item%20List%20screenshot%20zip%20file

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%204%3A%20Space%20Builder%20Time%20Dimension%20and%20Tables

Figure 4: Space Builder Time Dimension and Tables

3. Then click on Create in the opened dialog without changing anything.

Figure%205%3A%20Time%20Dimension%20and%20Tables%20Dialog

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%206%3A%20Time%20Dimension%20and%20Tables%20Data%20Builder

Figure 6: Time Dimension and Tables Data Builder

5.  Once verified, click on Import Objects from CSN/JSON files, as shown below.

Figure%207%3A%20Import%20CSN/JSON%20File%20Data%20Builder

Figure 7: Import CSN/JSON File Data Builder

6.  Here, in the opened dialog select the file ER_MCT_MEDIUM.json for import.

Figure%208%3A%20Choose%20JSON%20File

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%209%3A%20Import%20CSN/JSON%20File%20Objects

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%208%3AImported%20Tables%20status

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%209%3A%20Deploy%20Table

Figure 9: Deploy Table

10.   Next, upload the csv file to import the data in the MCT Opportunity Items, as shown below.

Figure%2010%3AUpload%20CSV%20data%20in%20Table

Figure 10:Upload CSV data in Table

11. Click on Choose File and select MCT_OpportunityItems.csv, as shown below.

Figure%2011%3A%20Choose%20the%20csv%20file

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%2012%3A%20Preview%20CSV%20file%20for%20Import%20in%20Table

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%2013%3A%20Verify%20Records%20in%20table

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%2014%3A%20Check%20Associated%20Tables

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%2015%3A%20Example%20-%20Upload%20CSV%20data%20in%20Table

Figure 15: Example – Upload CSV data in Table

Figure%2016%3A%20Select%20relevant%20CSV%20file

Figure 16: Select relevant CSV file

Figure%2017%3A%20Preview%20CSV%20data

Figure 17: Preview CSV data

Figure%2017%3A%20Deploy%20Table%20if%20needed

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%2018%3AOpen%20ADS

Figure 18:Open ADS

 

Figure%2019%3A%20Deploy%20ADS

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%2020%3A%20Analytic%20Model%20Lineage

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%2021%3A%20Analytic%20Model%20Lineage

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%2022%3A%20Analytic%20model%20Data%20preview

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.

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Martin Kreitlein
      Martin Kreitlein

      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

      Author's profile photo Jai Gupta
      Jai Gupta
      Blog Post Author

      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

      Author's profile photo Martin Kreitlein
      Martin Kreitlein

      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