For many customers moving to HANA as their EDW, it becomes worthwhile to look at moving their existing BW models to native HANA models. In this blog, we look at how to replicate existing BW models into a HANA system. This is not about exposing BW models as external HANA views but to replicate the same data model within HANA.
Let’s consider a scenario where the client has BW system of version 7.3 on HANA and wants to replicate only a part of the module/logic and existing Bex queries in HANA/HCP where we have the following constraints:
- Master data tables must already be present in HCP
- Minimal creation of views / virtual data models
- Replicate some of the BEx features
Now let’s move on how to replicate some of the features in the BW system to HANA/HCP
Database Views
These views in BW are created using DSO tables or combination of both InfoObject(IO) tables and DSO tables. They can be used as a Datasource for the data flow. However in BW we won’t be able to define the join type but only define the join condition. The following are the joins that needs to be replicated in HANA when we need to join the respective tables.
| BW Objects | Join |
| DSO – IO | Left Outer Join |
| DSO – DSO | Inner Join |
1. Master Data attribute reference in Transformation
Associating the Master’s Attribute in the transformation can be replicated by using Left Outer Join with the Master Table. Also in HANA we can make use of Star Join node for all Master Data Attribute joins provided all the Attributes info is available in form of Dimension type calculation view or Attribute views.
2. BW Routines
Consider the following scenarios involved in routines where T & F represent Tables & Fields respectively. Here some common scenarios are considered but more complicated scenarios are also possible but that would depend on a case by case basis.
| Scenarios | Join |
| T1.F1 = T2.F2 & fetch fields from T2 | Left Outer Join |
| T1.F1 = T2.F2 & delete fields in T1 that is not present in T2 | Inner Join |
3. DSO overwrite property for Key Figures or Delete adjacent entries after sorting in routines
To implement these features in HANA use Rank Node in Calculation view and partition by all the Key fields then choose Ascending (Bottom 1)
4. Join between two DSOs on the fields which has NULL values
This kind of join is completely possible in BW as it considers record by record for the join conditions to validate. Whereas in HANA, when we have NULL values in the join conditions those records are completely neglected. In order to mimic the same join type as in BW we need to substitute those NULL values with some dummy values by creating a Calculated column and include those columns in the join conditions
5. Exceptional Aggregation with Reference Characteristics in BEX
Consider we have Company ABC’s Sales Data that has two Key Figures Sales & Profit which is summed up based on Product & Branch characteristic respectively in BEx.
In order to mimic the same kind of aggregation we need to follow the below steps:
a) Create a new Calculation view and include the base view/table that has company ABC’s Sales Data
b) Create 2 separate Aggregation nodes on top of this base projection node
c) In Aggregation 1 node make ‘Sales’ as Aggregated measure and define its Aggregation type as Sum. Set the Keep Flag property as TRUE for ‘Product’ Dimension. Expose all the fields except for ‘Profit’ field
d) In Aggregation 2 node make ‘Profit’ as Aggregated measure and define its Aggregation type as Sum. Set the Keep Flag property as TRUE for ‘Branch’ Dimension. Expose only the Key fields and the ‘Profit’ field
e) Union the above two Aggregation nodes (Left Outer Join) using the Key fields
f) Now the Measures are aggregated based on the Reference Dimensions respectively even though those dimensions are not included in the final query.
These are some of the tips that can be followed during BW to HANA replication.
Got questions? Click here to get in touch.
