More and more SAP system users are implementing SAP HANA as the foundation and database for existing SAP BW installations. Some may just be replacing an existing database with the modern SAP HANA database to gain speed in query execution and warehouse management. This is, of course, a keystone of an SAP HANA implementation within SAP BW, but not the only one.
From my point of view, the tight integration of SAP HANA functionality using SAP HANA studio or Eclipse to design new ways of data warehouse management is at least as important as reporting performance.
Why use old habits with SAP application layers, storing more and more data in persistency layers, when it’s possible to calculate new reporting data on the fly? Using SAP HANA-based staging to enhance data flows without the physical staging of incoming data in several layers (SAP staging layer architecture [SLA]) is something I am working on at a customer site.
My approach can help you to be flexible when it comes to design changes because there is no unload or reload of data in staging layers when business logic changes. It also keeps your operation costs for SAP HANA at a minimal level because less data in an SAP HANA database means less expense for licensing.
(In a modern SAP BW powered by SAP HANA environment, it is wise to switch to virtual data staging instead of old-school data staging via persistency layers. SAP HANA calculation views and procedures for complex scenarios help to calculate data on the fly to cut down unload and reload phases to zero when it comes to business-related changes in data staging or extension/reduction in a data model. In the SAP HANA environment using SAP HANA studio or Eclipse, SAP BW operators can very easily switch from the old-fashioned models to the new calculation-view-based modeling concept or run a mixed scenario of both worlds.)
An SAP HANA procedure is a database-stored procedure that gives you programming functionality with the help of SAP HANA SQL Script, similar to ABAP functions. Now I guide you through the creation of SAP HANA data flows with the help of calculation views (graphical and SQL Script-based views), as well as some pitfalls you may run into.
My example (Figure 1) is a simple mixed approach.
Figure 1
An example of the mixed data approach using calculation views
That means you use existing SAP BW inbound data (DataStore object [DSO] based, as in an SAP BW entry layer). You also join SAP BW table information (such as an SAP Data Dictionary [DDIC] table) into one combined calculation view you can use in combination with an SAP HANA Composite Provider for BEx or Analysis for Office reporting. You can also use calculation views directly in Analysis for Office reporting. (I do not cover authorizations such as direct reporting on calculation views without a Composite Provider).
All the screenprints are based on Eclipse as the development studio for SAP HANA BW development. I am running Eclipse Neon 3 and the latest SAP BW and SAP HANA add-ons. The actual set of SAP BW modelling tools can be found using SAP Note1944835 - SAP BW Modeling Tools - Delivery Schedule 1944835 - SAP BW Modeling Tools - Delivery Schedule.
In the following explanations and examples, I use data based on an external SAP HANA view. In my example, I use technical DSO content data to feed data and consume data in a graphical calculation view. I use this data in combination with a SQL Script calculation view in a third graphical calculation view to join both data. I explain the difference in accessing data by regular joins in a graphical calculation view compared with SQL Script-based calculation views and explain the advantages of SQL Script calculation views. At the end, you can use the third calculation view, joining all views to a single point, to feed a Composite Provider for reporting users with the help of BEx queries or directly in combination with Analysis for Office.
(Note: I assume that you are familiar with SAP HANA studio or Eclipse. Therefore, I do not explain the basics, such as switching perspectives.)
Start Your Walk Through SAP HANA Calculation Views
First, switch to the SAP HANA Development perspective (Figure 2). I recommend using this perspective in the case of SAP HANA development. The reason for this recommendation is that in all other perspectives you are unable to create SAP HANA hdb procedures.
Figure 2
The SAP HANA Development perspective
The first calculation view is a plain graphical view. Right-click your development package and select New and then Calculation View as shown in Figure 3.
Figure 3
Dialog to choose a calculation view
This action displays the screen shown in Figure 4.
Figure 4
Calculation view dialog
(Note: If your screen looks different, make sure that you have selected the Repositories tab. I recommend using this approach because via an SAP HANA development perspective and repository display, all functionalities that can be used that might be obsolete in the SAP HANA modeling perspective and Systems tab are displayed on the screen.)
In Figure 4 enter your desired calculation view technical name, such as MY_NEW_VIEW (not shown). Keep the default Type, which is Graphical, and click the Finish button.
By default, the system generates two objects: Semantics and Aggregation objects (Figure 5).
Figure 5
SAP HANA calculation view scenario area
On the left, you find all the available design objects. Start with the Projection option. Drag and drop it to the blank area of the scenario. The Projection gives access to all available objects within SAP HANA, such as DDIC tables, InfoProviders, master data, and already existing SAP HANA views.
When moving your mouse over the projection object (Figure 6), you see a green plus sign.
Figure 6
Projection with displayed data access symbol
Click that plus icon (data access) to open a dialog in which you can search for your desired object. You can read the data and then add that object to your actual view.
The dialog that opens after you click the plus icon allows you to search for your desired object. In my example, I entered the search string for SAP BW technical content object WIS_C03 (a copy of SAP technical cube TCT_C03, which is an InfoCube). As you can see in Figure 7, the search result returns all available SAP HANA objects, such as the InfoProvider itself and all existing partial (e.g., dimensions) tables.
Figure 7
Search result for projections
I choose the InfoCube itself, which is indicated by the Cxx postfix (the third item from the top), by double-clicking the entry or simply selecting the entry and selecting the OK button. I recommend creating ADSO (a type of standard DSO) technical names that refer to its type such as C (for type Cube).
Besides adding the selection to your projection and displaying the name, you see the structure of the selected object in the studio detail pane, (in my example the InfoCube structure) displaying InfoObjects as well as cube SID entries (Figure 8).
Figure 8
Projection with displayed structure
Clicking the bullet-shaped icons in the detail view adds that particular InfoObject to the output (Figure 9) of that projection and changes its color to orange (selected). This selection is similar to adding fields of a table to a customized view in transaction SE11. The LED shaped icon works like a toggle switch. A gray color means the field is turned off (for usage), while orange means the field has been turned on.
Figure 9
Projection with three selected InfoObjects
Because InfoCubes use SIDs as well as InfoObject keys, the selection might be a bit time-consuming. For a better view to InfoCubes, I recommend activating the external SAP HANA view of that particular InfoCube via the change view in the Administration Workbench. Or you could automate the activation of all cubes /DSO.
If you already activated the external SAP HANA view, you can select that view directly from the Projection search dialog (Figure 10).
Figure 10
Activated external SAP HANA view
Selecting this external SAP HANA view makes your life with calculation views much easier. As you can see (Figure 11), only InfoObjects and their text elements, if they exist, are displayed. You can add them to the output of your projection.
Figure 11
Projection of external SAP HANA view for DSO type InfoCube WIS_C01
To map the projection to the existing aggregation object in the scenario pane, click the circle icon above Projection_2. Hold down the mouse, drag a connection (line) to the bottom icon of the aggregation object, and release the mouse. Now your projection is connected (Figure 12).
Figure 12
Projection is connected to Aggregation
Selecting the aggregation object unveils the structure of all the fields of your projection. You can decide if you like all the fields or just a few. If you like all of them, the best approach is to right-click the header area (black header) and select the Add All To Output (Figure 13) option, which automatically maps all existing fields to the output without having to select the entries one by one.
Figure 13
Add all the fields to the output dialog
After this step is done, you can activate the calculation view by clicking the activation icon from the top menu in Eclipse (Figure 14).
Figure 14
Activate the calculation view
After activation, you can directly display the data for each individual object by right-clicking the object (e.g., projection) and selecting Data Preview (Figure 15). (The details of querying the data are beyond the scope of this article.)
Figure 15
Dialog to display the data preview
To enrich my example with some more details, I add the data from a new ADSO. As before, drag Projection to the scenario, click the plus icon, and select your desired object (in my case the InfoProvider is named WIS_D01 as shown in Figure 16).
Figure 16
Add DSO WIS_D01 to the second projection
You should see both projections. The first one is already connected to the aggregation object. Now you join those two tables.
(So that you do not lose all existing mapping, drag and drop the join object from the right side directly onto the connection line between the first projection and the aggregation object. This automatically adds the join without breaking any mapping. Now you should have the scenario shown in Figure 17.)
Figure 17
A scenario with the added join object
The last step is to drag the output of the second projection to the input of the displayed join.
Since you now have a join between both objects (WIS_C01 and WIS_D01), you need to define the join condition between your desired fields.
Just select the output fields by clicking the orange bullets and for the join condition drag and drop a line between the join objects. I joined 0CALDAY by transferring the data from Projection_2 and just turning on additional fields of Projection_1. InfoObject 0CALDAY (Figure 18) is now the join condition, but it will not be transferred to the output data because otherwise you would have the field 0CALDAY twice.
Figure 18
Join with join condition on 0CALDAY
Right-clicking the join connection opens a dialog in which you can swap the tables (important for outer joins) and display the Edit… option (Figure 19).
Figure 19
Join options
When you are using the join edit mode, a new dialog appears in which you can set the different join conditions as well as cardinality (Figure 20). To keep it simple leave the standard join condition (inner join) active.
Figure 20
Join option dialog
As you can see, the graphical join does not allow you to define complex join conditions (e.g., with WHERE statements or complex filters) as done in ABAP joins. To create such joins, you need SQL-based calculation views. In these calculation views you can define whatever join condition you like.
As an alternative, you can pass the coding for such joins and table accesses to an SAP HANA procedure to be more flexible. These procedures can be used system wide for all purposes. First, you have to create a new procedure. Right-click your package and select New and then Other… as shown in Figure 21.
Figure 21
Dialog for new objects
In the next dialog select Stored Procedure (Figure 22).
Figure 22
Select Stored Procedure
Provide a name and target schema for the new procedure (Figure 23).
Figure 23
The initial dialog for stored procedures (hdb procedures)
Now enter your SQL Script code (Figure 24). My example adds the activation and request information from SAP BW tables RSREQDONE and RSMONICDP to the final calculation view.
PROCEDURE "JOEBOE"."system-local.public.JOEBOE::P_GET_BW_MONITOR_DATA"
(OUT TABLE_OUT table
(
"RNR" NVARCHAR (30),
"ICUBE" NVARCHAR (30),
"DP_NR" NVARCHAR (30),
"STATUS" NVARCHAR (4),
"QMSTATUS" NVARCHAR (4),
"REC_INSERT" INTEGER,
"REC_UPDATE" INTEGER,
"DATUM" NVARCHAR (8),
"UZEIT" NVARCHAR (6),
"TSTATUS" NVARCHAR (4),
"TDATUM" NVARCHAR (8),
"TUZEIT" NVARCHAR (6))
)
LANGUAGE SQLSCRIPT SQL
SECURITY INVOKER
DEFAULT SCHEMA "MY_SCHEMA"
READS SQL DATA AS
BEGIN
/*****************************
The upper table has to be defined exactly like the receiving SQL Script calculation View
*****************************/
-- Table out will receive all data in exactly the order as given by upper table from
-- next SQL call. It should be aligned to the fields being used in SQL scipt calc view
table_out =
select distinct
TB1."RNR" ,
TB1."ICUBE" ,
TB1."DP_NR" ,
TB1."STATUS",
TB1."QMSTATUS" ,
TB1."REC_INSERT" ,
TB1."REC_UPDATE",
TB2."DATUM",
TB2."UZEIT",
TB2."TSTATUS",
TB2."TDATUM",
TB2."TUZEIT"
from "SAPT04"."RSMONICDP" as TB1
INNER JOIN "SAPT04"."RSREQDONE" as TB2
on
TB1."RNR" = TB2."RNR"
WHERE TB2."DATUM" > '20160101' AND
TB1."ICUBE" like '0TCT%' AND
(TB1."QMSTATUS" is NOT NULL AND TB2."RECORDS" > 0)
order by TB1."ICUBE" ASC;
END;
Figure 24 — Sample SQL Script code for a join between tables in SAP HANA
The first part in my sample code is the declaration of the table fields used in the join condition. Those fields that are declared are sent to the calculation view later (similar to internal tables in ABAP).
SECURITY INVOKER respects the authorization the individual user might have running the procedure later on. The table out parameter receives the result from the select statement and passes the information to the output table (table_out) you use later.
Activate your procedure after the coding is bug free by using Ctrl + F3 or the activation icon from the Eclipse menu.
The next step is to use this procedure in a calculation view type SQL Script. (Remember the procedure is not mandatory; you could use the code directly in SLQ script-based calculation views.) Right-click the package and select New, then Calculation View. Within the familiar dialog (Figure 25) make sure to select type SQL Script (the default is Graphical) and provide mandatory information such as the name.
Figure 25
The Calculation View dialog with selected type SQL Script
The next dialog displays a familiar scenario view, but with only two simple objects (Figure 26).
Figure 26
The SQL script calculation view
Click the Script_View object and see that the code as well as output pane are displayed. The code part is rather simple (Figure 27). As you can see, you only call the procedure that you just created.
/********* Begin Procedure Script ************/
BEGIN
--Call procedure that does Join and structure definition to return its value to calculation view
call "MY_SCHEMA"."system-local.public.JOEBOE::P_GET_BW_MONITOR_DATA"(:var_out);
END /********* End Procedure Script ************/
Figure 27 — Sample code for SQL Script-based calculation views
The var_out table declared in the procedure must now be connected to the output fields of the calculation view. The type of fields (Figure 28) and the exact sequence have to be aligned to the type and sequence of your table_out definition inside your procedure (see the code in Figure 24).
Figure 28
Calculation view output fields
After all the settings are done, activate and test (data preview) the SQL-based calculation view.
The final step is to combine this calculation view with the previous example. Just add a new projection, click the plus icon, and search for your SQL-based calculation view (Figure 29).
Figure 29
Search for the calculation view
Calculation views allow you to insert not only standard SAP BW objects but also all SAP HANA objects (such as calculation views).
Turn on the desired output fields in your new added item, as described with Figures 8 and 9. Add a new join condition and map the existing components with the new SQL Script-based calculation view. The output should look similar to my example (Figure 30).
Figure 30
The final calculation view integrating SAP BW and SAP HANA components
You can use this introduction to SAP HANA calculation views to help build scenarios combining graphical and SQL-based calculation views for all kinds of purposes. You could in some cases bypass the classical staging in DSO and cubes completely by directly adding this calculation view to a Composite InfoProvider for reporting purposes.
The benefit of using SAP HANA calculation views if that you can use the view I just created in a projection of several other views (reusable) as I did with the SQL Script-based view in this article.
Source:SAP HANA Note
From my point of view, the tight integration of SAP HANA functionality using SAP HANA studio or Eclipse to design new ways of data warehouse management is at least as important as reporting performance.
Why use old habits with SAP application layers, storing more and more data in persistency layers, when it’s possible to calculate new reporting data on the fly? Using SAP HANA-based staging to enhance data flows without the physical staging of incoming data in several layers (SAP staging layer architecture [SLA]) is something I am working on at a customer site.
My approach can help you to be flexible when it comes to design changes because there is no unload or reload of data in staging layers when business logic changes. It also keeps your operation costs for SAP HANA at a minimal level because less data in an SAP HANA database means less expense for licensing.
(In a modern SAP BW powered by SAP HANA environment, it is wise to switch to virtual data staging instead of old-school data staging via persistency layers. SAP HANA calculation views and procedures for complex scenarios help to calculate data on the fly to cut down unload and reload phases to zero when it comes to business-related changes in data staging or extension/reduction in a data model. In the SAP HANA environment using SAP HANA studio or Eclipse, SAP BW operators can very easily switch from the old-fashioned models to the new calculation-view-based modeling concept or run a mixed scenario of both worlds.)
An SAP HANA procedure is a database-stored procedure that gives you programming functionality with the help of SAP HANA SQL Script, similar to ABAP functions. Now I guide you through the creation of SAP HANA data flows with the help of calculation views (graphical and SQL Script-based views), as well as some pitfalls you may run into.
My example (Figure 1) is a simple mixed approach.
Figure 1
An example of the mixed data approach using calculation views
That means you use existing SAP BW inbound data (DataStore object [DSO] based, as in an SAP BW entry layer). You also join SAP BW table information (such as an SAP Data Dictionary [DDIC] table) into one combined calculation view you can use in combination with an SAP HANA Composite Provider for BEx or Analysis for Office reporting. You can also use calculation views directly in Analysis for Office reporting. (I do not cover authorizations such as direct reporting on calculation views without a Composite Provider).
All the screenprints are based on Eclipse as the development studio for SAP HANA BW development. I am running Eclipse Neon 3 and the latest SAP BW and SAP HANA add-ons. The actual set of SAP BW modelling tools can be found using SAP Note1944835 - SAP BW Modeling Tools - Delivery Schedule 1944835 - SAP BW Modeling Tools - Delivery Schedule.
In the following explanations and examples, I use data based on an external SAP HANA view. In my example, I use technical DSO content data to feed data and consume data in a graphical calculation view. I use this data in combination with a SQL Script calculation view in a third graphical calculation view to join both data. I explain the difference in accessing data by regular joins in a graphical calculation view compared with SQL Script-based calculation views and explain the advantages of SQL Script calculation views. At the end, you can use the third calculation view, joining all views to a single point, to feed a Composite Provider for reporting users with the help of BEx queries or directly in combination with Analysis for Office.
(Note: I assume that you are familiar with SAP HANA studio or Eclipse. Therefore, I do not explain the basics, such as switching perspectives.)
Start Your Walk Through SAP HANA Calculation Views
First, switch to the SAP HANA Development perspective (Figure 2). I recommend using this perspective in the case of SAP HANA development. The reason for this recommendation is that in all other perspectives you are unable to create SAP HANA hdb procedures.
Figure 2
The SAP HANA Development perspective
The first calculation view is a plain graphical view. Right-click your development package and select New and then Calculation View as shown in Figure 3.
Figure 3
Dialog to choose a calculation view
This action displays the screen shown in Figure 4.
Figure 4
Calculation view dialog
(Note: If your screen looks different, make sure that you have selected the Repositories tab. I recommend using this approach because via an SAP HANA development perspective and repository display, all functionalities that can be used that might be obsolete in the SAP HANA modeling perspective and Systems tab are displayed on the screen.)
In Figure 4 enter your desired calculation view technical name, such as MY_NEW_VIEW (not shown). Keep the default Type, which is Graphical, and click the Finish button.
By default, the system generates two objects: Semantics and Aggregation objects (Figure 5).
Figure 5
SAP HANA calculation view scenario area
On the left, you find all the available design objects. Start with the Projection option. Drag and drop it to the blank area of the scenario. The Projection gives access to all available objects within SAP HANA, such as DDIC tables, InfoProviders, master data, and already existing SAP HANA views.
When moving your mouse over the projection object (Figure 6), you see a green plus sign.
Figure 6
Projection with displayed data access symbol
Click that plus icon (data access) to open a dialog in which you can search for your desired object. You can read the data and then add that object to your actual view.
The dialog that opens after you click the plus icon allows you to search for your desired object. In my example, I entered the search string for SAP BW technical content object WIS_C03 (a copy of SAP technical cube TCT_C03, which is an InfoCube). As you can see in Figure 7, the search result returns all available SAP HANA objects, such as the InfoProvider itself and all existing partial (e.g., dimensions) tables.
Figure 7
Search result for projections
I choose the InfoCube itself, which is indicated by the Cxx postfix (the third item from the top), by double-clicking the entry or simply selecting the entry and selecting the OK button. I recommend creating ADSO (a type of standard DSO) technical names that refer to its type such as C (for type Cube).
Besides adding the selection to your projection and displaying the name, you see the structure of the selected object in the studio detail pane, (in my example the InfoCube structure) displaying InfoObjects as well as cube SID entries (Figure 8).
Figure 8
Projection with displayed structure
Clicking the bullet-shaped icons in the detail view adds that particular InfoObject to the output (Figure 9) of that projection and changes its color to orange (selected). This selection is similar to adding fields of a table to a customized view in transaction SE11. The LED shaped icon works like a toggle switch. A gray color means the field is turned off (for usage), while orange means the field has been turned on.
Figure 9
Projection with three selected InfoObjects
Because InfoCubes use SIDs as well as InfoObject keys, the selection might be a bit time-consuming. For a better view to InfoCubes, I recommend activating the external SAP HANA view of that particular InfoCube via the change view in the Administration Workbench. Or you could automate the activation of all cubes /DSO.
If you already activated the external SAP HANA view, you can select that view directly from the Projection search dialog (Figure 10).
Figure 10
Activated external SAP HANA view
Selecting this external SAP HANA view makes your life with calculation views much easier. As you can see (Figure 11), only InfoObjects and their text elements, if they exist, are displayed. You can add them to the output of your projection.
Figure 11
Projection of external SAP HANA view for DSO type InfoCube WIS_C01
To map the projection to the existing aggregation object in the scenario pane, click the circle icon above Projection_2. Hold down the mouse, drag a connection (line) to the bottom icon of the aggregation object, and release the mouse. Now your projection is connected (Figure 12).
Figure 12
Projection is connected to Aggregation
Selecting the aggregation object unveils the structure of all the fields of your projection. You can decide if you like all the fields or just a few. If you like all of them, the best approach is to right-click the header area (black header) and select the Add All To Output (Figure 13) option, which automatically maps all existing fields to the output without having to select the entries one by one.
Figure 13
Add all the fields to the output dialog
After this step is done, you can activate the calculation view by clicking the activation icon from the top menu in Eclipse (Figure 14).
Figure 14
Activate the calculation view
After activation, you can directly display the data for each individual object by right-clicking the object (e.g., projection) and selecting Data Preview (Figure 15). (The details of querying the data are beyond the scope of this article.)
Figure 15
Dialog to display the data preview
To enrich my example with some more details, I add the data from a new ADSO. As before, drag Projection to the scenario, click the plus icon, and select your desired object (in my case the InfoProvider is named WIS_D01 as shown in Figure 16).
Figure 16
Add DSO WIS_D01 to the second projection
You should see both projections. The first one is already connected to the aggregation object. Now you join those two tables.
(So that you do not lose all existing mapping, drag and drop the join object from the right side directly onto the connection line between the first projection and the aggregation object. This automatically adds the join without breaking any mapping. Now you should have the scenario shown in Figure 17.)
Figure 17
A scenario with the added join object
The last step is to drag the output of the second projection to the input of the displayed join.
Since you now have a join between both objects (WIS_C01 and WIS_D01), you need to define the join condition between your desired fields.
Just select the output fields by clicking the orange bullets and for the join condition drag and drop a line between the join objects. I joined 0CALDAY by transferring the data from Projection_2 and just turning on additional fields of Projection_1. InfoObject 0CALDAY (Figure 18) is now the join condition, but it will not be transferred to the output data because otherwise you would have the field 0CALDAY twice.
Figure 18
Join with join condition on 0CALDAY
Right-clicking the join connection opens a dialog in which you can swap the tables (important for outer joins) and display the Edit… option (Figure 19).
Figure 19
Join options
When you are using the join edit mode, a new dialog appears in which you can set the different join conditions as well as cardinality (Figure 20). To keep it simple leave the standard join condition (inner join) active.
Figure 20
Join option dialog
As you can see, the graphical join does not allow you to define complex join conditions (e.g., with WHERE statements or complex filters) as done in ABAP joins. To create such joins, you need SQL-based calculation views. In these calculation views you can define whatever join condition you like.
As an alternative, you can pass the coding for such joins and table accesses to an SAP HANA procedure to be more flexible. These procedures can be used system wide for all purposes. First, you have to create a new procedure. Right-click your package and select New and then Other… as shown in Figure 21.
Figure 21
Dialog for new objects
In the next dialog select Stored Procedure (Figure 22).
Figure 22
Select Stored Procedure
Provide a name and target schema for the new procedure (Figure 23).
Figure 23
The initial dialog for stored procedures (hdb procedures)
Now enter your SQL Script code (Figure 24). My example adds the activation and request information from SAP BW tables RSREQDONE and RSMONICDP to the final calculation view.
PROCEDURE "JOEBOE"."system-local.public.JOEBOE::P_GET_BW_MONITOR_DATA"
(OUT TABLE_OUT table
(
"RNR" NVARCHAR (30),
"ICUBE" NVARCHAR (30),
"DP_NR" NVARCHAR (30),
"STATUS" NVARCHAR (4),
"QMSTATUS" NVARCHAR (4),
"REC_INSERT" INTEGER,
"REC_UPDATE" INTEGER,
"DATUM" NVARCHAR (8),
"UZEIT" NVARCHAR (6),
"TSTATUS" NVARCHAR (4),
"TDATUM" NVARCHAR (8),
"TUZEIT" NVARCHAR (6))
)
LANGUAGE SQLSCRIPT SQL
SECURITY INVOKER
DEFAULT SCHEMA "MY_SCHEMA"
READS SQL DATA AS
BEGIN
/*****************************
The upper table has to be defined exactly like the receiving SQL Script calculation View
*****************************/
-- Table out will receive all data in exactly the order as given by upper table from
-- next SQL call. It should be aligned to the fields being used in SQL scipt calc view
table_out =
select distinct
TB1."RNR" ,
TB1."ICUBE" ,
TB1."DP_NR" ,
TB1."STATUS",
TB1."QMSTATUS" ,
TB1."REC_INSERT" ,
TB1."REC_UPDATE",
TB2."DATUM",
TB2."UZEIT",
TB2."TSTATUS",
TB2."TDATUM",
TB2."TUZEIT"
from "SAPT04"."RSMONICDP" as TB1
INNER JOIN "SAPT04"."RSREQDONE" as TB2
on
TB1."RNR" = TB2."RNR"
WHERE TB2."DATUM" > '20160101' AND
TB1."ICUBE" like '0TCT%' AND
(TB1."QMSTATUS" is NOT NULL AND TB2."RECORDS" > 0)
order by TB1."ICUBE" ASC;
END;
Figure 24 — Sample SQL Script code for a join between tables in SAP HANA
The first part in my sample code is the declaration of the table fields used in the join condition. Those fields that are declared are sent to the calculation view later (similar to internal tables in ABAP).
SECURITY INVOKER respects the authorization the individual user might have running the procedure later on. The table out parameter receives the result from the select statement and passes the information to the output table (table_out) you use later.
Activate your procedure after the coding is bug free by using Ctrl + F3 or the activation icon from the Eclipse menu.
The next step is to use this procedure in a calculation view type SQL Script. (Remember the procedure is not mandatory; you could use the code directly in SLQ script-based calculation views.) Right-click the package and select New, then Calculation View. Within the familiar dialog (Figure 25) make sure to select type SQL Script (the default is Graphical) and provide mandatory information such as the name.
Figure 25
The Calculation View dialog with selected type SQL Script
The next dialog displays a familiar scenario view, but with only two simple objects (Figure 26).
Figure 26
The SQL script calculation view
Click the Script_View object and see that the code as well as output pane are displayed. The code part is rather simple (Figure 27). As you can see, you only call the procedure that you just created.
/********* Begin Procedure Script ************/
BEGIN
--Call procedure that does Join and structure definition to return its value to calculation view
call "MY_SCHEMA"."system-local.public.JOEBOE::P_GET_BW_MONITOR_DATA"(:var_out);
END /********* End Procedure Script ************/
Figure 27 — Sample code for SQL Script-based calculation views
The var_out table declared in the procedure must now be connected to the output fields of the calculation view. The type of fields (Figure 28) and the exact sequence have to be aligned to the type and sequence of your table_out definition inside your procedure (see the code in Figure 24).
Figure 28
Calculation view output fields
After all the settings are done, activate and test (data preview) the SQL-based calculation view.
The final step is to combine this calculation view with the previous example. Just add a new projection, click the plus icon, and search for your SQL-based calculation view (Figure 29).
Figure 29
Search for the calculation view
Calculation views allow you to insert not only standard SAP BW objects but also all SAP HANA objects (such as calculation views).
Turn on the desired output fields in your new added item, as described with Figures 8 and 9. Add a new join condition and map the existing components with the new SQL Script-based calculation view. The output should look similar to my example (Figure 30).
Figure 30
The final calculation view integrating SAP BW and SAP HANA components
You can use this introduction to SAP HANA calculation views to help build scenarios combining graphical and SQL-based calculation views for all kinds of purposes. You could in some cases bypass the classical staging in DSO and cubes completely by directly adding this calculation view to a Composite InfoProvider for reporting purposes.
The benefit of using SAP HANA calculation views if that you can use the view I just created in a projection of several other views (reusable) as I did with the SQL Script-based view in this article.
Source:SAP HANA Note
- SAP Managed Tags:
6 Comments
