A Deep Dive into “Classic Account Model” and “New Model” in SAP Analytics Cloud
When we talk about data analysis, the word “Model” can never be avoided. The term “Model” is widely used in data warehouses and data analysis topics and was normally used to describe a structured way of organizing large amounts of data.
As a business data analysis platform in SAP Business Suite, SAP Analytics Cloud provides rich and flexible data modeling functions based on the concept of “model”, helping enterprises easily complete data analysis and gain insights. However, some data practitioners might get confused by the concept of “model” in SAP Analytics Cloud If they are new to the family of SAP, probably because:
- SAP Analysis Cloud was born out of SAP BPC, and has a strong focus on business planning and budget analysis, the definition of “model” in SAP Analysis Cloud is slightly different from other typical data intelligence products.
- In the 2021 update of SAP Analytics Cloud, another type of “model” was introduced: New Model. Users might need some time to understand the differences and connections between the “New model” and the original “Classic Account Model” in SAP Analytics Cloud.
Therefore, this article aims to do a deep dive into the “classic account model” and the “new model”, starting from the multi-dimensional data model, to help data practitioners who are new to SAP Analytics Cloud to better understand the modeling in SAP Analytics Cloud.
This article will describe:
- The basics of multi-dimensional data modeling: take star schema as an example
- The star schema in SAP Analytics Cloud: Classic Account Model
- Introduction and the benefits of the New Model
The basics of multi-dimensional data modeling: take star schema as an example
What is a multi-dimensional data model? Let’s first understand two data processing categories:
- OLTP (online transaction processing)
- A transaction-based business system with additions and deletions as the primary operation. It is mainly used to record the occurrence and changes of certain business events. For example, record purchase order data.
- OLAP (Online Analytical Processing)
- An analytical business system with query data as the primary operation. Mainly used to support complex analytical operations and provide decision-making support. For example, query the total number of orders in the previous month.
Most of the analysis techniques used by business analysis products can be classified as OLAP type applications. In OLAP, the “Multi-dimensional Model” is one of the most common types of models. So, back to our original question, what is a multi-dimensional data model?
The main feature of OLAP is to return results based on certain constraints by limiting the angles of how the users see the data. This pre-assembled data model based on multiple perspectives (dimensions) is called a “multi-dimensional data model”. Here, ” dimension ” refers to the angle from which the users analyze the data. For example, “the total number of orders in the last month” in “month” is the time dimension. “Female” in “Number of All-Female Orders” is the gender dimension. After organizing the data into a multi-dimensional data model, users can quickly obtain data from various dimensions, and dynamically and flexibly switch or combine various dimensions for comprehensive analysis. This is the charm of the multidimensional data model and why they have been widely used.
When constructing multi-dimensional data models, it is generally necessary to reorganize complex data in a structured manner, and separate dimensions (analytical perspective) and transaction data (fact tables) to form a multi-dimensional star schema model or snowflake schema model. Here is an example of a star schema multidimensional data model:
In this example, the transaction data of purchase orders is split into dimension tables and a fact table :
- 4 dimension tables : record time dimension, region dimension, product dimension, customer dimension respectively
- 1 fact table: is used to associate with the four dimension tables and record the number and amount of transactions. Items like quantities and amounts that can be added up/averaged are called measures in a multidimensional data model.
During the analysis, the user can choose one or several dimensions to analyze the quantity and amount of the purchase orders.
To sum up: the multi-dimensional data model splits the data into a fact table and dimension tables. The fact table is used to record measures and the dimension table is used to record dimensions.
2. The star schema in SAP Analytics Cloud: Classic Account Model
When designing SAP Analysis Cloud, the principles and concepts of multi-dimensional data modeling were fully absorbed to create the “Modeler” module in the product. Before 2021, there is only one type of data model in SAP Analytics Cloud, the “Classic Account Model”. The concept of the classic account model is slightly different from the multi-dimensional model we typically see in data warehouses and BI products to make it more suitable for the planning user’s real business scenarios. Below I list the differences between the classic account model and the traditional star schema model:
- Version (version) and Date (date) as default dimensions.
When we choose to create a “Classic Account Model”, we will enter the modeling page, and the model type is the default “planning” type (that is, the planning feature in SAP). As you can see, the structure of this model is very similar to the star schema structure we introduced earlier:
- The card in the middle of the model represents the basic information related to the model and the measures, which can be understood as the fact table in the star schema.
- The model already has two default dimension tables: Version and Date. It can be understood as two dimension tables in the star schema.
Here, SAP Analytics Cloud adds two default dimension tables: Version and Date based on the empty star schema.
- Basically, all planning processes are inseparable from the time dimension. By adding the default time dimension table, the user’s modeling process is accelerated.
- SAP Analytics Cloud provides complete end-to-end planning and budget management capabilities. In budget planning, data version (version) is an essential dimension. Therefore, all models of type “plan” will have a default “version” dimension table. If you convert the model to an “analytics” type of model, the “version” dimension table disappears.
2. Introduction of the concept of “Account dimension”
Next, we create three dimension tables “Customer”, “Region” and “Product” based on the example of the star schema model in the previous chapter. Operations such as creating new dimensions are not in the scope of this article, please refer to the official tutorial
Next, we need to introduce a concept that does not exist in the star schema: the Account dimension.
SAP Analytics Cloud was born out of SAP BPC (Business Planning and Consolidation), and the earliest business scenario was budget planning. In the BPC data model, the Account dimension is used to define each item in the charts of accounts, which is a very important financial concept. Therefore, in order to facilitate the integration with other SAP components such as BPC, SAP Analytics Cloud also uses the concept of “account dimension” based on the star model. So, how is the concept of an account used in a star schema?
In the above example, we have completed the construction of four dimension tables. Next, we should add two measures of “amount” and “quantity” to the model. In SAP Analytics Cloud, all measures need to be contained in a dimension with the type of “Account”. That is to say, unlike the typical star schema model where measures are contained in fact tables, the “Classic Account Model” adds a special type of dimension: the account dimension, which contains all the measures and calculations.
Here, we created a new dimension of type “Account” and added “amount” and “quantity” as members of the dimension. It is equivalent to adding two measures to the model.
Returning to the main page of the model, we have completed the construction of this multi-dimensional data model. Due to the existence of the “account” dimension, the “account” dimension and the data foundation together represent the “fact table” of the model, and the remaining branches represent the “dimension table”:
the data importing after the model is built should also be adapted to the concept of “account”. In the data table used to import the “Classic Account Model”, the name of the measures need to be displayed under the account dimension, and there can only be one column of values in the data to correspond to the value corresponding to each measure. The following gives the responding format we need to prepare for this model:
To sum up: The “classic account model” of SAP Analytics Cloud is based on the star schema model with some modifications. First, two default dimensions are added to better suit the business scenarios. Second, the concept of account dimension in SAP BPC is adopted: “account dimension” is used to contain “measures”.
3. Introduction and the Benefits of the “New Model”
From the above introduction, we learned that the “classic account model” in SAP Analytics Cloud has some modifications based on the multi-dimensional data model. These changes can help users complete data integration faster when conducting financial analysis and planning.
However, the “classic account model” also has some notable limitations:
- Most members in the SAP suites (such as SAP Business Warehouse, SAP Data Warehouse Cloud) and non-SAP data warehouses use the classic star schema as the basis for data storage. Therefore, users need to spend additional time and manpower to format the raw data in order to get it ready for SAP analytics cloud when connecting to the above systems .
- In the classic account model, since all the measures are contained in the account dimension, it is difficult for users to perform some complex operations on a single measure, and it is also difficult for users to convert exchange rates for a single measure in the model.
Therefore, in mid-2021, SAP Analytics Cloud has launched a new type of model: New Model. The new model uses the original concept of the star schema in the multi-dimensional data model. Users can directly create a new “measure” without using the “account” dimension to wrap it. This type of model offers more possibilities and flexibility.
Still using the above example, this time we create a “new model”. Please refer to the official guide to create the “new model”.
- Just like what we did for the “classic account model”, we add three dimensions: product, customer and region
- Different from the “classic account model”, here we directly add two metrics “quantity” and “amount”, as displayed on the left side of the screen.
- The display of the new model is very similar to the traditional multidimensional star schema model. In the center is a fact table containing measures, surrounded by various dimension tables.
With the “new model”, the data preparation also changed accordingly: we can directly use the name of the measure as the column name in the raw data. The following figure shows the format of the import data we have in the “new model”:
Note that before using the “classic account model”, 8 records of data are required to document the fact data, while in the “new model”, only 4 records of data are needed to complete the importing. The data volume of the original data is further reduced, which can improve the performance of the data query.
After the introduction of the “new model” type model in SAP Analytics Cloud, we offer the traditional multidimensional data model, which not only makes it easier to connect to other data sources but also makes it easier to customize calculated measures. With the “new model”, each record of the data represents more information, thereby improving query performance.
So far, we have discussed the concept of SAP Analytics Cloud data Model in detail and compared it with the typical multi-dimensional data model. The “Classic Account Model” and “New Model” in SAP Analytics Cloud are different representations of the star schema multidimensional data model. Users should choose the appropriate data model according to their own business scenarios.
This article does not discuss how to create the new model and the transformation between the classic account model and the new model. For this part, please refer to the official help manual and other references:
Official help manual:
Two remarks from my side after working with a measure bases model on top of an XL flat file:
Thanks for sharing! The newly born "new model" still has some limitations and pitfalls here and there, so it's great to see a summary documenting the remarks with the "new model".
Thanks for sharing! Would you be able to elaborate if the new model type can be now seen as best practice for new analytics/BI projects in SAC taking into consideration:
pros: performance, alligned format with BW,DWC and possible workload(?) when later on migrating to new model type from classical one (objects dependencies and import jobs?
cons: until release q42022 no support for Geo and only model first workflow, points from Yoav
Hopefully didn't mix it up:)
In my personal opinion, I would go for the "new model" if allowed. In the real world, it normally depends on the business/technical requirements of the project. I will do the following to evaluate if it's appropriate to use the "new model":
Otherwise, I will try to go for the new model even though I might need to use some workarounds to overcome some temporary limitations of the "new model" for now:
Thanks for Sharing
I don't know how to create new version like budget, forecast in new model. After Importing the date by default its coming under the version public: actuals . please guide me how to create a budget and planning version in new model