Figure 1: Multi-fact Model Overview
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 shows the alternative way of creating and using Multi fact dimensions in Analytic model within Datasphere. Currently, multi-fact is supported within fact models in Business Layer in Datasphere and we plan to have the similar functionality available for Analytic models in future as well.
For time being, users waiting for this functionality in Analytic Models, can follow the alternate approach highlighted in this article.
This article is part of the blog post series listed below:
- Blog Post #1: Introducing the Analytic Model in SAP Datasphere
- Blog Post #2: Data Model Introduction
- 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
- Blog Post #9:SAP Datasphere Analytic Model Series – User Experience and Navigation Paradigm | SAP Blogs
- Blog Post #10: SAP Datasphere Analytic Model Series Blog 10– Design Multi Fact in Analytic Model (Current Blog)
What is Multi-fact?
In layman’s terms, the concept of multi-fact is to combine 2 or more facts with the same dimensions for reporting in the data warehouse.
The concept is similar to Multi-provider in Business Warehouse. Now if you are not familiar with BW, then don’t panic. In simple terms, here, we will do a union on two datasets having different facts and then associate the union output to the same dimensions.
The advantage of using multi-fact is that it helps us in streamlining reporting KPI from different facts by linking them to the same dimension. A classic example is to report on Plan and Actuals data at the same time. These always reside in different data sources, but they can obviously be brought together over some albeit not all their dimensions. So, Planning might happen by country, quarter & product category, but Actuals might be daily sales of products within those product categories in stores located within those countries.
As a more complex example, you can picture headcount reporting in HR. While current and last year’s personnel details might be in the same sources (but don’t have to be), you still might want to include data on hires & terminations, open positions, and internal movements to get a wholistic picture.
Currently, the business layer of SAP Datasphere allows you to model these facts independently and then bring them together in an entirely model-driven approach.
Figure 2 : Multi-Fact Model with Plans and Actuals in the Business Layer
While, we don’t have this functionality yet in the Analytic Model, you can still achieve the same through sql view modelling in graphical or SQL views of the Data Layer
Modelling Scenario
Let say, I am a consultant looking for insights on how my product forecast is behaving in comparison to the revenue generated by the sales opportunities across products within a company. For my requirements, I would like to combine the Opportunity Actuals data from the ERP system with the Plan data for the Targets maintained in the CSV files.
My Actuals data consists of tables – Opportunities_Item, Opportunities_Header table and Plan data consists of file Planned_Volumes, which is loaded into the Datasphere in the form of tables.
- Opportunities_Item
Columns: ID -> This is line-item ID, Opportunity-> This is Opportunity ID, ProductId, Value.
- Opportunities_Header
Columns: ID -> This is Opportunity ID , SalesOrg, ExpectedClosingDate, Status, Responsible, Customer
- Planned_Volumes
Columns: ID -> This is ProductID, Quarter, Target_Volume.
The Actuals tables relationship will look like as something shown below.
Figure 3: Actuals Tables Relationship
Here, I have associated Actuals, with Time dimension to get the quarter information . I need this as Plan data is showing target volumes across quarter for each product, whereas, in my Actuals, booking is happening for product at date level. Further, I have linked product dimension to both my Actuals and Plans to get more details regarding the Product.
The Plan tables relationship will look like below:
Figure 4: Plan Tables Relationship
From above table relationships, we can use Productid and Quarter to link Plan and Actuals to common dimensions Product and Time respectively for building a Multi-fact in DS, as shown below.
Figure 5: Multi-Fact Tables Relationship
In next section, I will describe the steps on how we can build a Multi-fact.
Modelling Steps
1. As a first step, I choose to Create Time Tables and Dimensions using default options at space level, as shown The time dimension will be used later in step 3&4 to join it with the Actuals data and retrieve the Quarter information.
Figure 6: Enable Time dimensions in the space
2. Next, I went to graphical view designer in the Data builder layer and clicked on New Graphical view.
Figure 7: Create Graphical View
3. In my new view, first I did a left join between my table Opportunities_Header with the generated ‘Time Dimension – Day’ view, generated from step 1 using columns ‘Expected closing date’ and ‘Date’ to get the Quarter information.
Figure 8: Actuals Data Modelling 1
4. Then, I did a left join between table Opportunities_Item and the intermediate result from step 3 on column OpportunityID to add the Calendar Quarter attribute and make it available alongside product from the Opportunities_Item table.
Figure 9: Actuals Data Modelling 2
5. Afterwards, I did a union and combine the output from above step with the Planned_Volumes table, as shown below.
Figure 10: Union Plan Mapping
Figure 11: Union Actuals Mapping
6. Subsequently, I changed the view semantic type to Analytical Dataset(ADS) and changed the columns – Value and TargetVolume semantics to Measures.
Figure 12: Change View to ADS
7. Then I created the associations in the ADS to link the related dimensions Products and Time Dimension – Quarter view.
Figure 13: Associate Product and Time dimension Quarter
8. Finally, I created the Analytic Model on top of the ADS.
Figure 14: AM Design
9. Finally, I can compare the Planned Targets data with Actuals for each quarter in AM Data preview, as shown below.
Figure 15: AM Data preview
Also, I could have created a Calculated measure in AM to calculate the variance between my Plan and Actuals but that is something I would like you to explore on your own. If needed, please refer the blog SAP Datasphere Analytic Model Series – Calculated and Restricted Measures.
Conclusion
This blog introduced you to the alternate way of building the Multi-fact scenario in Datasphere using the Data Builder.
Thanks for reading! I hope you find this blog 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 Datasphere .
Original Article:
https://blogs.sap.com/2023/05/24/sap-datasphere-analytic-model-series-blog-post-10-design-multi-fact-in-analytic-model/