SAP Lumira Server is getting more attention for the connivence it offers to compose and host rich storyboards. As of its version 1.19, SAP Lumira Server has two main datasources: static CSV files and online HANA views created on the same HANA system where SAP Lumira Server is installed. Acquiring HANA views that are created on other remote HANA systems is currently not possible. Business analysts are always keen on having dashboards reflecting live data, when possible. This blog post will show you how you can acquire HANA views hosted on remote HANA platforms into SAP Lumira Server by defining a virtual table for the remote view.
The method that is going to be explained in this blog is particularly helpful to visualize HANA views generated by a BW on HANA system. BW is not compulsory as this method works for acquiring remote HANA views defined in a another HANA system.
To illustrate that, I will use the following exercise to walk you through the steps that need to be done:
A HANA view converted from an InfoProvider is hosted on a BW on HANA needs to be presented in a storyboard created using SAP Lumira Server which is deployed on a different server in the landscape. For simplicity, I will skip the part about converting an InfoProvider into a HANA view, a link to such a tutorial can be found here.
Starting from SPS07, HANA supports virtual tables that are located on remote sources. This is the technique that will be mainly used to make a table in a remote database accessible for modeling. Virtual tables are treated as if they were defined in the local HANA System, except for some delay due to network latency. The current implementation of virtual tables supports up to 10 remote database vendors. It treats them all the same regardless of whether the remote DB is a HANA system or not. In other words, their won’t be advantages for connection to a remote source that happens to be a HANA System. Thus, virtual tables are always stored in a row store and used as row tables.
Here is a summary of what is required and what I’m going to use and in this exercise:
- Access to a BW on HANA system, I will refer to as BW_ON_HANA
- A HANA view defined in a BW on HANA system. It is called: Z_NW_C01_INTERNAL
- Access to an SAP Lumira server that is deployed on a different server, I will refer to it as LUMIRA
- SYSTEM access to the HANA system on which LUMIRA is deployed, I will refer to it as HANA
The following table summarizes the minimum required version of used software components:
|Software Component||Minimum Required Version|
|SAP NetWeaver BW Powered by HANA||7.3 SP 8 (Only if BW on HANA is used)|
|SAP HANA Platform||1.0 SPS 5|
|SAP HANA Studio||1.0 SP05 Revision 58|
|SAP HANA Platform (Needed for Lumira Server)||1.0 SP08 Revision 80|
|SAP Lumira Server||1.17|
Locate Z_NW_C01_INTERNAL on BW_ON_HANA
- In HANA Studio, add a connection to the HANA system on BW_ON_HANA
- Find the view in question, in this case it is in CEG package and called Z_NW_C01_INTERNAL
- HANA views are stored in the _SYS_BIC schema. The view corresponding to your HANA view is named <package_name>/<view_name>. Locate your view, in our case, Z_NW_C01_INTERNAL is found here:
Prepare Lumira User on HANA
The Lumira user that needs to access the remote HANA view must have the CREATE REMOTE SOURCE system privilege. This is required in order to be able to add a remote source that references the remote HANA view.
- Using the SYSTEM user of the HANA System, select the Lumira user
- Add the CREATE REMOTE SOURCE system privilege to your Lumira user
Add Remote Source Connection for the Remote HANA View
After adding the proper system privilege to the Lumira user, it can now add a remote source that points to the HANA system where the remote view is defined. In this case, it is going to be the information of the BW_ON_HANA.
- In HANA Studio, expand the Provisioning item, then right-click Remote Sources.
- Click on “New Remote Source” from the context menu to add a new remote source for BW_ON_HANA
- Fill out the form with the details of the remote connection to the remote server
- Make sure you choose “HANA (ODBC)” as Adapter Name and click on save (as marked by the blue cirlce)
Add Virtual Table for the Remote HANA View
After saving the remote source connection. You can now navigate to the view defined for the HANA view in the _SYS_BIC schema on the remote BW_ON_HANA and then add a virtual table the references the remote HANA view.
- Expand the Remote Sources item and also expand the newly created item “BW_ON_HANA”
- That lists the schemas defined on the remote server. Expand the _SYS_BIC schema
- Look for the table of interest, in this case it is called: CEG/Z_NW_C01_INTERNAL
- Right-click the view and choose “Add as Virtual Table” from the context menu
- Add a name for the virtual table and choose the schema where this table is going to be added
- Click on Create
- Now go to the schema where the virtual table has been added, and expand the “Tables” item to verify that the table exists
Create Calculation View based on the Remote Virtual Table on HANA server
Now that the remote virtual table is added and defined on the HANA server where LUMIRA is deployed. We can remodel this virtual table slightly so that LUMIRA can show it in the launchpad–hence use it as a datasource. Because virtual tables are added to the row store of HANA, they can only be used inside a calculation view with SQL Script subtype. Thus, the only way to accomplish that is to add the virtual table, which in fact points to a HANA view, as a calculation view that uses SQL Script subtype, instead of graphical. This way select statements are forced to use aggregate functions when executed against the virtual table. Executing select statements that don’t include an aggregate function against a HANA view will result in an error. Remember that the virtual table we are using is pointing to a HANA view.In addition, all the columns that are marked as measures in the remote HANA view must be added as measures as well to the calculation view with matching data types for all columns — both measures and attributes. Although the virtual table is added to the row store, which may indicate poor performance during run-time, the execution of queries is performed on the remote server which already stores the tables in its column store. As result, the performance will be identical as if the queries were run directly on the remote server, of course, except for added network latency. The following steps are needed to wrap the virtual table with a calculation view:
- Right-click the Content item of the HANA connection as a Lumira user
- Choose New –> Calculation View
- Fill out the form and make sure you choose “SQL Script” as subtype
- Click on Finish
Edit the Calculation View
After the new calculation view is created, open it and click on the Script_View node to write the sql code that is returned by this view. The code is simple. It is only a select statement with approperiate aggregates against the virtual table. The aggregate functions used per measure must match the function in the remote view. In this example, all aggregate functions are of type SUM.You need to specify the virtual table name correctly. As usual, it follows the pattern: schema_name.table_name.In this example the select statement looks like the following:
/********* Begin Procedure Script ************/ BEGIN
var_out = SELECT “0CALMONTH”,“0CALYEAR”, “0D_NW_CNTRY”, “0D_NW_CNTRY.description” AS “0D_NW_CNTRY_DESCRIPTION”, “0D_NW_CHANN”,“0D_NW_CHANN.description” AS “0D_NW_CHANN_DESCRIPTION”, SUM(“Z_COSTV”) AS “Z_COSTV” ,SUM(“Z_NETV”) AS “Z_NETV”,SUM(“Z_OORDQTY”) AS “Z_OORDQTY” , SUM(“Z_OORDV”) AS “Z_OORDV” FROM “SAPHANAABAP”.“CEG/Z_NW_C01_INTERNAL” group by“0CALMONTH”,“0CALYEAR”, “0D_NW_CNTRY”, “0D_NW_CNTRY.description”, “0D_NW_CHANN”,“0D_NW_CHANN.description”;
END /********* End Procedure Script ************/
Importantly, the column names must match the ones in the remote HANA view. For simplicity, I only included some columns, not all. Note that the “FROM” clause of the select statement points to the local schema where there the virtual table has been added earlier.Now follow these steps to finalize the editing part:
- On the Output pane in HANA Studio, you need to add a column entry for each of the columns that appeared in your select statement
- Open the the original HANA view on the remote HANA server to check the data type of each of the columns
- Click on the plus ➕ to add a new column entry
- Add all the columns with their corresponding data types exactly as defined in the original view
- Click on the Semantics node (1) and define the columns that need to appear as measures by changing their Type to measure (2). This has to match all the measures that are defined in the original HANA view. In this case, we have 4 measures defined, therefore, the exact 4 columns have to be defined as measures
- Press on Activate to save and activate the view as shown by arrow (3)
Acquire the Calculation View as Datasource in the Launchpad
After successfully activating the calculation view, it will be available in the Lumira Server Launchpad. The view can easily be acquired as a datasource in the same way as the other HANA views defined locally.The following steps shows the outcome:
- Browse to the Lumira Server Launchpad URL and login as the Lumira user who has access to the calculation view created earlier
- The view created should appear on the list available datasources
- Click on the view and start composing your storyboard. Here is Live data visualization from BW on HANA that shows costs per country:
This blog explained how you can visualize live data coming from a remote HANA system in the launchpad of Lumira Server version 1.17 and newer. This procedure is helpful when multiple HANA systems are available in the landscape while Lumira Server is deployed only on one system. Virtual tables are defined on the HANA system where Lumira Server is deployed to reference remote views defined on different remote HANA systems in the landscape. In Addition, BW on HANA data can also be visualized once InfoProviders are converted into HANA views and then added to the HANA where Lumira Server is deployed as virtual tables. While there are well-known limitations in the process of converting BW InfoProviders into HANA views, this procedure works as an option to visualize live and online BW data.