Calculation views can adapt its Behavior to the list of columns that are projected for example the granularity of the rank node can be determined dynamically depending on whether the query results by country or by country and customer
Calculation views are read only, and cannot change data in the SAPHANA Cloud database.
A calculation view is instantiated at run-time when a query is executed.
After this, the SQL optimizer applies further optimizes and determines the query execution plan β for example, it determines the optimal sequence of operations, and also those steps that could be run in parallel.
Dimension View
Projection node is default second node
Dimensions are re-useable objects. When they have been created, they can are shared by developers and used to develop star schemas.
Processing Engine: Join Engine
Dimension calculation views are only built from attributes. They do not include measures. Every column from the data source is treated as an attribute. This means that any numerical column in the source record, such as price or salary, is treated as an attribute and not a measure.
When you query a dimension calculation view, a complete list of every single individual value in the source is produced in the output. Even the numerical values such as quantities are listed individually and not aggregated. If you want to sum values then you need a cube calculation view where it is possible to define measures.
If you would like to produce only a distinct list of attribute values, you can specify aggregation on any column. Then each unique value appears only once in the result.
Cube View
Aggregation View is default node
When you would like to create a calculation view that includes measures, you use a calculation view of the following type: cube.
Processing Engine:OLAP Engine
By default, all the measures in this type of calculation view will always be aggregated by the attributes requested by the query. Consequently, even though the calculation view may be able to provide many attributes, the measures are always automatically aggregated only by the attributes that were requested by the query.
For example, your cube calculation view provides the measure: revenue, and the attributes: country and city. The query requests only country, and so revenue is summed by country and not by city. If the next query requests the measure: revenue, and the attributes: country and city, then the revenue would be summed by city and also country. This means that you will have two levels of aggregation.
This type of calculation view is optimized for ad-hoc analysis, where unpredictable Slice-and-Dice is required over the measures by any combination of attributes within the model.
Time Dimension View
You use views to automatically generate various date-related attributes like year, month, day, week, days in a week from a base date dd-mm-yy in customer table. All that is needed is the base date in the source record. From that we can derive all possible time attributes automatically.
Granularity
Year β _SYS BI:M_TIME_DIMENSIONYEAR
Month β _SYS BI:M_TIME_DIMENSIONMONTH
Week β _SYS BI:M_TIME_DIMENSIONWEEK
Second, minute, hour, day β _SYS BI:M_TIMEDIMENSION
The tables M_TIME_DIMENSION is precreated and then you join
time dimension artifact β schema:m_time_dimension_week/SYS_BI:M_TIMEDIMENSION
Gregorian Calendar
is the standard international calendar used for civil purposes.
It consists of 12 months and 365 days (or 366 days in a leap year).
It starts on January 1st and ends on December 31st.
SYS.update_time_dimension
You need to ensure T005T and T005U are available and filled
This procedure allows you to generate time data using SQL statements
For example, you can choose to generate records between 2020 β 2024.
You can regenerate the data at any time to keep the table up-to-date. The generated table is used as the data source to the time-based dimension calculation view
granularity β 'WEEK'
START_YEAR β 1999
END_YEAR β 2024
FIRST_DAY_OK_WEEK β 1
TARGET_SCHEMA_NAME β '' // You can give the schema name if you don't give schema name then default schema is _SYS _BI
TARGTE_TABLE_NAME β '' //This will create table in the target schema based on granularity if not specified then default table m_time_dimension_week in target schema or _SYS _BI
RECORD_COUNT β ?
Fiscal Calendar
Also called a financial year or accounting year) is used by governments,
the fiscal year does not always start on January 1st. It varies by country and organization.
SYS.update_fiscal_calendar
You need to ensure T009 and T009B are available and filled
Create your own table having structure _SYS BI.M_FISCALCALENDAR
When you create a time-based dimension calculation view, a table is also generated that is automatically filled with records that represent the data attributes for a range of dates that you specify. For example, you can choose to generate records between 2020 β 2024. You can regenerate the data at any time to keep the table up-to-date. The generated time table is used as the data source to the time-based dimension calculation view. You then consume the time-based dimension calculation view in your cube calculation view.
Hierarchies
A hierarchy is a structured representation of an organization, a list of products, the time dimension, and so on, using levels.
It is often used to provide the easy navigation of a large data set in a drill-down.
Is available only in Symantics node
Level Hierarchy
A level hierarchy requires each level to be stored in a separate column. Rows represent leaf nodes.
Heterogeneous fields (possibly with different data types) are combined in a hierarchy.
A level hierarchy is defined by choosing columns from a source data set and organizing them in a sequence that provides a drill-down path. Each level is based on a separate column in the source data.
In the Semantics node, select the Hierarchies tab and click the '+' button in the Hierarchy pane.
Add the columns to the hierarchy in the correct level order from top to bottom, with the lowest granularity at the lowest level of the hierarchy.
Additionally, you can define an ascending or descending sort direction per level.
Node styles are used to define the output format of a node ID.
Using a fiscal hierarchy example, the following table demonstrates the different node styles:
Level Style | Output | Example
Level Name | Level and node name | MONTH.JAN or COUNTRY.GERMANY
Name Only |Node name only | JAN or Hamburg
Name Path | Node name and its ancestors | FISCAL_2015.QUARTER_1.JAN or EUROPE.GERMANY.Hamburg
Level Types
A level type specifies the semantics for the level attributes. For example, the level type TIMEMONTHS indicates that the attributes are months such as January, February, or March.
The level type REGULAR indicates that the level does not require any special formatting.
Order By β dropdown list, an attribute can be selected for ordering the hierarchy members in the order specified in the Sort Direction column.
Parent and Child
A parent-child hierarchy requires columns of the same data type for parents and children. Rows represent each node.
Parent-child hierarchy columns usually contain IDs or key fields instead of plain text.
You can define multiple parent-child pairs to support the compound node IDs β for example:
CostCenter and ParentCostCenter
ControllingArea and ParentControllingArea
Hereβs an example of a compound parent-child definition to uniquely identify cost centers:
CostCenter: CC1001, CC1002, CC1003
ParentCostCenter: CC1000, CC1000, CC1001
ControllingArea: CA01, CA01, CA02
ParentControllingArea: CA00, CA00, CA01
In this example:
CostCenter CC1001 is a child of ParentCostCenter CC1000 within ControllingArea CA01.
CostCenter CC1002 is also a child of ParentCostCenter CC1000 within ControllingArea CA01.
CostCenter CC1003 is a child of ParentCostCenter CC1001 within ControllingArea CA02.
Advanced Properties of a Parent-Child Hierarchy
Aggregate All Nodes
defines whether the values of intermediate nodes of the hierarchy should be aggregated to the total value of the hierarchyβs root node. If you are sure that there is no data posted on aggregate nodes, you should set the option to False. The engine then executes the hierarchy faster.
Root Node: Company
Intermediate Node: Department A
Leaf Node: Cost Center 1
Leaf Node: Cost Center 2
Intermediate Node: Department B
Leaf Node: Cost Center 3
Leaf Node: Cost Center 4
Cost Center 1: $10,000
Cost Center 2: $15,000
Cost Center 3: $20,000
Cost Center 4: $25,000
Aggregation:
Department A: $10,000 (Cost Center 1) + $15,000 (Cost Center 2) = $25,000
Department B: $20,000 (Cost Center 3) + $25,000 (Cost Center 4) = $45,000
Company: $25,000 (Department A) + $45,000 (Department B) = $70,000
Default Member
The Default Member value β helps you to identify the default member of the hierarchy.
This default member acts as a fallback option when a hierarchy level is not explicitly defined in a query, ensuring consistent and predictable query results.
If you do not provide any value, all members of the hierarchy are default members.
Orphan Nodes
An orphan node in a hierarchy is a member that has no parent member.
Level hierarchies offer four different ways to handle orphan nodes. They are as follows:
Root Nodes
Any orphan node will be defined as a hierarchy root node.
Error
When encountering an orphan node, the view will throw an error.
Ignore
Orphan nodes will be ignored.
Step Parent
Orphan nodes are assigned to a step parent you specify.
If you choose to assign an orphan node to a step parent, the following rules apply:
The step parent node must be already defined in the hierarchy at the ROOT level.
The step parent ID must be entered according to the node style defined in the hierarchy
Root Node Visibility
The value helps modeler know if it needs to add an additional root node to the hierarchy.
This ensures that the hierarchy has a well-defined starting point
Consider an organizational structure stored in a parent-child hierarchy, where each employee reports to a manager, and the hierarchy culminates at the CEO.
Add Root Node: By setting the Root Node Visibility to "Add Root Node" and specifying "Company" as the root node value, the hierarchy would display "Company" at the top, under which the CEO and subsequent reporting lines are organized.
Ignore Root Node: If the Root Node Visibility is set to "Ignore Root Node," the hierarchy would begin directly with the CEO, followed by the respective managerial levels, omitting the overarching "Company" node.
Time-Dependent Hierarchies
If elements in your hierarchy are changing elements (time dependent elements) such as human resources applications with their organizations, or material management systems with BOMs where information is reliant on time., you can enable the parent-child hierarchy as a time dependent hierarchy. In other words, if you are creating hierarchies that are relevant for specific time period, then enable time dependency for such hierarchies. This helps you display different versions on the hierarchy at runtime.
Your source data needs to contain definition columns consisting of a Valid From and a Valid To column.
When a hierarchy needs to show elements from an interval, you have to define two input parameters; a FromParameter, and a To Parameter
If the hierarchy needs to show elements valid on a specific date, you need one input parameter defined as the Key Date.
Consider a company's organizational chart where an employee transfers from the "Marketing" department to the "Sales" department on January 1, 2025.
The employee's record would have two entries: one with a Valid To date of December 31, 2024, under "Marketing," and another with a Valid From date of January 1, 2025, under "Sales."
By setting the query's key date to December 2024, the employee appears under "Marketing." Setting it to February 2025 shows the employee under "Sales."
Type of Generated Hierarchy
When you define a hierarchy in an SAPHANA Cloud calculation view, whether it is a level or parent-child hierarchy, the hierarchy is materialized upon build/deployment by various tables and/or views in the HDI Container schema (column views or classic SQL views)
These generated objects provide detailed hierarchy node relationship data to enable processing of the hierarchy when the front-end tool is not able to generate the hierarchy relationships itself.
A key setting allows you to influence the way SAPHANA translates the defined hierarchy into technical tables and views in the database. This is the Hierarchy Type setting, which you can define in the Semantics of the Calculation View, in the View Properties β General tab.
Auto (default value)
This setting is useful if you exchange views between SAPHANA Cloud and On-Premise, because upon build, SAPHANA generates the following:
In SAPHANA Cloud: classic SQL views to materialize the SQL hierarchy
In SAPHANA On-Premise: MDX hierarchies (only compatible with SAPHANA on-Premise), including the metadata defining the hierarchy, as well as column views materializing the MDX hierarchy. No SQL Hierarchy view is generated.
SQL Hierarchy Views
Upon build, classic SQL views are generated to materialize the SQL hierarchy.
No Hierarchy Views
Upon build, the metadata of the hierarchies is generated (_SYS_BI.BIMC* tables) but the hierarchies themselves (detailed list of members, and so on) are NOT generated.
This setting is should be used when the consuming front-end tool itself can generate the set of hierarchy members and their relationships, based on the hierarchy metadata defined in the BIMC tables.
Assume one level hierarchy, PROD_LEV_HIER, has been modeled within the Calculation View HC300::CVC_SALES_HIER.
Then the SQL Hierarchy view is called HC300::CVC_SALES_HIER/PROD_LEV_HIER/sqlh/PROD_LEV_HIER.
To select only the required columns from a data source.
To define calculated columns.
To define Input parameters and Variables that request values at run-time, such as user-prompts.
To apply a filter on the data source.
Cube with Star Join Calculation View
An extension to the cube calculation view is the cube with star join.
Processing Engine:OLAP Engine
In addition to the capabilities of the cube type of calculation view, a cube with star join calculation view allows you to connect dimension calculation views so that you significantly expand the capabilities for analysis by providing additional attributes. For example, if you create a sales cube calculation view, which provides only limited attributes such as a product number, you could then join the product dimension calculation view to provide many more product-related attributes such as product description, supplier, color, and price. You could then aggregate the sales revenues by product color, supplier, and so on.
The type of joins between the fact and dimension tables within the star schema can be defined in the Star Join node
Client-Dependent Views
Almost all tables in SAP applications are client-dependent. When you select data from an SAP system database, it usually only makes sense to request data for one client number.
calculation view can use the client column property to enable automatic client filtering when you work on data from SAP applications. You do not need to define an explicit filter on the client column each time you create a calculation view.
Default Client
Cross-Client: All the data is retrieved regardless of the client number.
Fixed Client: You specify an SAP Client number (for example, 200) and the data sources are automatically filtered to include only the rows with this client number.
Session Client: The source tables are filtered dynamically based on a client value that is specified for each user in the USERS table of SAPHANA Cloud.
As the technical user has no default client assigned, if you specify a Column Client property for a calculation view and set the Default Client to Session Client, the data preview in the Developer perspective will not retrieve any data.
To by-pass the technical user and apply client filtering based on a classic database user, you can do one of the following with this user:
Use the data preview feature Data Preview with Other Database User
Client Columns
For databases coming from the remote system
Select the table in the mapping and expand the properties tab at the bottom
You can find a client column select field click on the value help and select the client column
SQL Views
created using the SQL language
Column Tables
SAPHANA Cloud supports optimized tables.
These tables are optimized to provide you with a very fast read-performance
Row Tables
SAPHANA Cloud supports traditional tables
It makes no difference to a modeler whether the table is row or column and all modelling functions are available with both types
Virtual Tables
Is a table that is part of the SAPHANA Cloud database and is mapped to a remote table outside of SAPHANA Cloud.
you reach external data sources (database tables, flat files, spreadsheets)
Table functions
Functions can be used to define complex data sources using SQLScript are used to return a tabular data
All output parameters of the table function are offered as input columns to the calculation view node. If your table function requires input parameters you can first define input parameters in the calculation view and then use the Parameter Mapping function to map them.
FUNCTION "com.sap.hana.example::TABLE_FUNC_SCALE" (VALCHAR) RETURNSTABLE (A INT, B INT) LANGUAGESQLSCRIPT
AS BEGIN RETURNSELECT A, :VAL * B AS B FROMMYTABLE; END
Scalar Function
Think of them as dynamic tables that are created at runtime.
Reusable block of data processing logic using the powerful SQLScript language to generate a result as either a single value (scalar), or a complete data set (table ).
These are always read-only. This means it is not possible to use any data definition language such as create table or alter table. It is not possible to use data manipulation statements such as update or insert into. These block never change the database.
One of the common uses of a scalar function in a calculation view, is to derive values for input parameters of calculation view
Can call other functions, which encourages a modular design approach to maximize reuse.
FUNCTION "com.sap.hana.example::SCALAR_FUNC_ADD_MUL" (x DOUBLE, y DOUBLE) RETURNS RESULT_ADD DOUBLE, RESULT_MUL DOUBLE LANGUAGESQLSCRIPT
AS BEGIN
RESULT_ADD := :x + :y;
RESULT_MUL := :x * :y; END
