Introduction
Calculation views offer many possibilities in terms of modelling on SAP HANA. Several types of views can be developed (Attribute or Dimension, Analytical, Calculation or Fact, Script, Datamart).
In this article we will talk about a type of view that is widely used within BI data models: Datamarts.
Datamarts are data counters, based on a star schema (a type of modelling very frequently used in BI) allowing a fact view to be joined to dimensions. They are usually the top layer of the information model and are the views used by the reporting tools.
These datamarts can then be used by all reporting tools with a HANA connector (SAP BO, SAP Analytics Cloud, Power BI, Tableau, etc.).
Before building a datamart, it is essential to understand the difference between a factual view and a dimensional view, as each has its own specific place in the datamart.
The distinction between these two types of views is based on the type of data they expose.
Factual view
A factual view illustrates a quantifiable activity over time. Among the data reported, we find indicators (aggregable data), such as quantities, amounts, or volumes. The indicators are intended to measure an activity, often on a daily basis. Indeed, the source tables at the base of the factual view model are fed very regularly by the system.
For example, it is possible to build a factual view from tables containing customer invoices or production orders. What these tables have in common is that they contain temporal and quantitative data : invoice amounts, quantities produced, etc.
Dimensions
The essence of a dimensional view is to provide analysis axes. It does not contain any quantitative data, but only attribute fields. The dimension can contain master data or labels.
We can take the case of a dimension dedicated to customers. This view can contain all the information related to customers, such as their name, location (country, region, city), bank data, the name of the sales representatives, the hierarchy, etc.
Subscribe to the Rapid Views Newsletter !
Stay up to date with our latest blog posts, upcoming webinars and news!
Datamart
To link a factual view to the dimensional views following good relational modelling practice, a star schema should be used. This is called a datamart.
In HANA Studio, only calculation views have the “Star Join” feature, which is used to model a star schema. Attribute views and analytic views do not.
Use cases on HANA Studio
In this example, if we want to view the amounts invoiced by customer city and by items manufactured by the factories, then we need to :
- Develop a factual view based on the invoice tables
- Then create two dimensions that will carry the attributes we need :
- A customer dimension for the customer city
- A material dimension for the manufacturing plant
With HANA Studio, the calculation views are configured differently depending on the type of view you want to create (dimensions, fact views or datamart).
A dimensional view should not contain quantitative data, and should be set to “Data Category = DIMENSION” in the view properties:
The factual view must be set to “Data Category = CUBE”.
It is possible to configure the Data Category in the view creation window :
For a datamart, the Data Category must also be set to CUBE.
Unlike the factual view, the datamart contains a Star Join. To add it, check “With Star Join” when creating the view.
The factual view is integrated into a Projection in the Star Join input. Its content is then defined by dragging and dropping from the list of available factual views.
The dimensions are dropped directly into the Star Join, by dragging and dropping from the list of available dimension views:
Lastly, the joins between the different fact and dimension views are created in the graphical interface. It is necessary to ensure that all the necessary join keys are available in the fact view.
Use cases on Power BI
The calculation views are compatible with many reporting tools (Power BI, Tableau, SAP BO, etc.).
Let’s take the example of Power BI to show the datamart developed in the previous section.
Power BI offers a connection to a HANA database, with the possibility of connecting using either an import mode (data stored in cache) or a direct query mode (data in real time).
Once the connection is made with the HANA database, the list of data marts is available in Power BI, and we can then choose a visual from the palette offered by Power BI.
Here is an example of a “map” visual
A bar chart
The invoice amounts come from the factual view, while the customer name comes from the dimension. These two views joined in the datamart by the customer number allow the data to be analysed.
All this complexity is hidden from Power BI users, as it is handled by the datamart, fact and dimension views in HANA. Users of the reporting tool have access to a data counter (the datamart) with the full semantic layer allowing them to create their own analyses.
Conclusion
Thanks to the functionalities of the HANA platform, the different types of views and the “Star Join” options, we can create datamarts in HANA, also called data counters, which make SAP data readable and easily exploitable in reporting tools.
The data marts are created with the “best practices” of a BI system and allow users to make the most of the data made available, and to exploit it in their operational and BI reporting.
In addition, all these HANA data marts are compatible with most of the reporting tools on the market; these data marts can therefore be used identically with the same data in one or more reporting tools (SAP WebIntelligence, SAP Analytics Cloud, Power Bi, Tableau Software, Excel, …)
