The last improvements in the analytical engine of BW/4HANA were shipped with BW/4HANA 2.0. For details read the blog ‘ Improvements of BW/4HANA Analytical Engine in Q1 2019 with BW/4HANA 2.0’. Those features we now brought down also to NW BW customers with NW BW 7.50 SP16. In addition, we are now with BW/4HANA 2.0 SP 4 and NW BW SP16 shipping those new features. The main driver for those innovations is the SAP Analytics Cloud integration. But a lot of features can also be used in SAP Analysis for Office .
The main improvements done till first quarter of 2019 are:
Let us have a glimpse on each of the topics in detail.
Custom Exception Aggregation for Analysis Office and SAC on the fly
We already mention the custom aggregation being available for SAP Analysis for Office with the upcoming release 2.8 in the previous blog ‘ Improvements of BW/4HANA Analytical Engine in Q1 2019 with BW/4HANA 2.0’.
Now with 7.50 SP16 we brought this function also down to the NW BW customers. It will also be adopted by SAP Analytics Cloud and is planned for wave 22. As for HANA live connections one can in the calculation editor now choose custom aggregations for BW live connections. Depending on the aggregation type you have at maximum 5 dimension you can choose to where the exception aggregation acts on. This is the same as in the query designer for backend modelled exception aggregation.
We also added for the custom formulas created on the fly in for Analysis for Office and SAP Analytics Cloud the calculation of GrandTotal(GT) and % GrandTotal (%GT). In Analysis for Office also other calculations like percentage of total (%CT) or query total and also totals for x or y axis are available.
This will also be supported with the upcoming Analysis for Office 2.8 release. For SAP Analytics Cloud we only support grand total and percentage grand total as in case of the HANA live connection.
In SAP Analytics Cloud the calculation editor als recognizes now the syntax and highlights the formula.
In BW we had for years the limitation that it was not possible to create restrictions on formula. Only for RKF as reusable component it was possible to define those on CKF. Then the resolve algorithm was calculating the restriction on each operand. This can be also stacked though multiple levels of reusable key figures. Now this resolve algorithm was also applied to restrictions and in query designer and customer restrictions created on the fly. With NW BW FP16 and BW/4HANA 2.0 SP 4 we can now lift this limitation.
In addition restriction as well as formula are possible. In 7.50 SP16 one had in addition apply note 2868267 .
In the staging layer of BW, it was possible since 7.50 to have XXL-attributes. However, they were never shown to the UI. The consumption layer was not adopted since the on-premise web UI showed not much interest in adoption.
However, with SAP Analytics Cloud and wave 23 it is now possible also to display XXL attributes to the end consumer.
As configuration the new SICF node GetXXLValue under path /default_host/sap/bw/ina/ needs to be activated in order to consume the underlying pictures.
With BW4HANA 2.0 SP 2 XXL attributes can be defined as referencing to another content server/url outside BW. The XXL attribute must then be created as a reference to 0EXT_URL which has data type string (CLOB).
In addition, also routing from the BW server to the content server can be done. The XXL attribute must then have an entry to table BW4XXLU2D
Here for a given characteristic and XXL-attribute a SM59 RFC-destination must be maintained. In addition, an URI template can be specified. Here the only allowed variable is the value ‘chavl’. The given characteristic value of the characteristic is replaced in this URI template. If no URI template is set, then the content of the XXL-attribute is interpreted as URL and resolved. The resolving is done using an http client that uses the above mentioned SM59 destination. To be able to address the content server, trust relations must be maintained. This is done as usual in transaction STRUST.
Currency conversion from UI side set and triggered was always possible on classical BW clients like Bex Web, Analysis office or Design Studio (Lumira). However, it was not possible from new clients using the INA- and not the BICS- protocol. We now also added the currency conversion to the INA protocol with NW BW 7.50 SP16 and BW/4HANA SP1. With clients like SAP Analytics Cloud from wave 18 it is now possible to also trigger currency conversion from the UI.
Or in SAP Analytics Cloud once can select it on the chart/table
And choose the target currency and then the conversion type
Then the result displays the amount in the new currency.
When BW live connection was released for SAP Analytics Cloud we mainly focused on the table control. While certain charts- such as bar chart – already were usable, other chart types have very special requirements towards the backend. We therefor focused on enabling the missing chart types with BW/4HANA 2.0. Those we now also brought down with 7.50 FP16 to the NW BW code line. For details see again `Improvements of BW/4HANA Analytical Engine in Q1 2019 with BW/4HANA 2.0’ In addition to 2.0 scope we developed following missing links
Linked Analysis with hierarchies
We enabled tuple filter which arises in linked analysis when having more than one dimension in BW/4HANA 2.0. Tuple filter take the combination of characteristic values on different characteristics into account. In BW, the filter was always the cartesian product of characteristic values on different dimension. Example: Consider dimension A and B and a tuple filter (A=1 AND B=’X’) OR (A=3 AND B=’Y’) OR (A=1 AND B=’Z’) then the cartesian product filter would be ( A=1 OR A=3 ) AND (B=’X’ OR B=’Y’ OR B=’Z’). We still had limitations if those tuples contain hierarchy nodes. This we now lifted with NW BW 7.50 SP16 or BW/4HANA SP4. See the example below where we show linked analysis between two charts having hierarchical representation
We marked three fields on the left-hand side (France and AUS/NRD/35 being 112.10, Germany with AUS/NRD/33 and AUS/NRD/37 being 280.25 and 224.20). On the left side the characteristic 0BC_Filed is no in the drill. So, we aggregate on this characteristic. However, since France is not part of Germany we neglect this value on the right side also. We therefore wee the sum of 280.25 + 224.2 being 504.45. This value is split up correctly between north and south part of Germany.
We still have the limitations on leading NOT. Also, in linked analysis we will not support a source with a general advanced filter above tuple filter coming from any data source outside BW.
Geo maps on BW live models except Choropleth layer were enabled with BW/4HANA 2.0. We now also enabled the Choropleth layer with NW BW 7.50 SP 16 and BW/4HANA FP 4.
We recall the set of requirements we now have.
Setting the GISTO to the right value the attributes longitude and latitude are automatically added to the InfoObject. The creation of a shadow table in Hana with Hana Spatial Type is also triggered behind the scenes. Reason is this is that the DDIC Type is still missing in the ABAP Application Server.
This table is used for the HANA geo operations orchestrated by SAC. If the geographical type is already set, a reactivation of the InfoObject triggers the creation of this shadow table.
Now you can load master data with geographical coordinates longitude and latitude in staging. In NW BW 7.50 in addition the report RSD_GEO_SYNC_ATTR has to be executed after alteration of the master data. This can be done manually but best is to use process chain and combine it with the master data load process.
For the GISTP following values can be chosen:
Then you can use any query containing geo InfoObjects and a BW live model on top in SAC. You can use those models in any Geo chart and use various layers. Choropleth layer is added in NW BW 7.50 SP16 and will also be contained in the next feature pack 4 in BW/4HANA 2.0. Geo filtering as well as the within distance filter is working as in the HANA live connections. Only custom shape files and point of interests will not be supported.
In the picture above for example we see choropleth layer with filter
Custom shape files are also developed and will be available on in BW/4HANA SP4. Also then HANA revision larger than HANA 2.0 45 will be required. The shapes can be uploaded where the Geo type ist set as in Design Studio and BexWeb
Waterfall chart was enabled with BW/4HANA 2.0 or notes 2688726 and 2734214. However, the delta calculation did note work from the beginning. This was now also enabled with notes 2660034, 2745245 and 2784681.
With note 2660034 we support SAC Variance Charts based on two different measures. We also added the support versions (note 2745245 and 2784681). Only time-based variance was still missing. We faced 2 challenges. The first was that we needed to allow the usage of internal variables like current member in local formulas at a point of time where the variable submit was already done. This we achieved with the price of resubmit which we must pay anyway with restrictions on the fly.
The second challenge was that BW has not leveled based hierarchy on time. The virtual time hierarchies are all normal parent child hierarchies. The only level-based hierarchy in BW is the universal hierarchy which spans across the whole axis and only needs all characteristics of the different level to be part of the query. For the time variance we now developed a partial drill group which is similar to the universal display hierarchy but only affects part of the characteristic of the axis. We still require of all-time dimension to be part of the query. In addition we needed to develop new current member operators ‘Parallel Value’ was introduced. This all cam in BW/4HANA with SP4 and is available in NW BW with SP17 or note 2786890 - Enabling SAC Time Variance Charts with partial Universal Display Hierarchies. This also provides the capablities `UniversalDisplayHierarchyCustomDimensions` and `INACurrentMember`. for embedded analytics this is planned for the 2009 release.
We also will support custom sorting on dimension members in BW/4HANA FP04 for SAC. This is not available for NW BW
The main improvements done till first quarter of 2019 are:
- Custom Exception Aggregations on the fly
- GrandTotal and %GrandTotal in custom formula
- Allowing Restrictions on Formula
- Display of XXL attributes in INA based clients
- Currency Conversion from INA based clients
- SAC integration through BW Live Connection for chart support
- Linked Analysis with hierarchies
- Geo Map with Choropleth
- Waterfall Chart with Delta
- Variance Chart including version and time
Let us have a glimpse on each of the topics in detail.
Custom Exception Aggregation for Analysis Office and SAC on the fly
We already mention the custom aggregation being available for SAP Analysis for Office with the upcoming release 2.8 in the previous blog ‘ Improvements of BW/4HANA Analytical Engine in Q1 2019 with BW/4HANA 2.0’.
Now with 7.50 SP16 we brought this function also down to the NW BW customers. It will also be adopted by SAP Analytics Cloud and is planned for wave 22. As for HANA live connections one can in the calculation editor now choose custom aggregations for BW live connections. Depending on the aggregation type you have at maximum 5 dimension you can choose to where the exception aggregation acts on. This is the same as in the query designer for backend modelled exception aggregation.
GrandTotal and % GrandTotal in custom formula
We also added for the custom formulas created on the fly in for Analysis for Office and SAP Analytics Cloud the calculation of GrandTotal(GT) and % GrandTotal (%GT). In Analysis for Office also other calculations like percentage of total (%CT) or query total and also totals for x or y axis are available.
This will also be supported with the upcoming Analysis for Office 2.8 release. For SAP Analytics Cloud we only support grand total and percentage grand total as in case of the HANA live connection.
In SAP Analytics Cloud the calculation editor als recognizes now the syntax and highlights the formula.
Allowing Restrictions on Formula
In BW we had for years the limitation that it was not possible to create restrictions on formula. Only for RKF as reusable component it was possible to define those on CKF. Then the resolve algorithm was calculating the restriction on each operand. This can be also stacked though multiple levels of reusable key figures. Now this resolve algorithm was also applied to restrictions and in query designer and customer restrictions created on the fly. With NW BW FP16 and BW/4HANA 2.0 SP 4 we can now lift this limitation.
In addition restriction as well as formula are possible. In 7.50 SP16 one had in addition apply note 2868267 .
Display of XXL attributes in INA based clients
In the staging layer of BW, it was possible since 7.50 to have XXL-attributes. However, they were never shown to the UI. The consumption layer was not adopted since the on-premise web UI showed not much interest in adoption.
However, with SAP Analytics Cloud and wave 23 it is now possible also to display XXL attributes to the end consumer.
As configuration the new SICF node GetXXLValue under path /default_host/sap/bw/ina/ needs to be activated in order to consume the underlying pictures.
With BW4HANA 2.0 SP 2 XXL attributes can be defined as referencing to another content server/url outside BW. The XXL attribute must then be created as a reference to 0EXT_URL which has data type string (CLOB).
In addition, also routing from the BW server to the content server can be done. The XXL attribute must then have an entry to table BW4XXLU2D
Here for a given characteristic and XXL-attribute a SM59 RFC-destination must be maintained. In addition, an URI template can be specified. Here the only allowed variable is the value ‘chavl’. The given characteristic value of the characteristic is replaced in this URI template. If no URI template is set, then the content of the XXL-attribute is interpreted as URL and resolved. The resolving is done using an http client that uses the above mentioned SM59 destination. To be able to address the content server, trust relations must be maintained. This is done as usual in transaction STRUST.
Currency Conversion from INA based clients
Currency conversion from UI side set and triggered was always possible on classical BW clients like Bex Web, Analysis office or Design Studio (Lumira). However, it was not possible from new clients using the INA- and not the BICS- protocol. We now also added the currency conversion to the INA protocol with NW BW 7.50 SP16 and BW/4HANA SP1. With clients like SAP Analytics Cloud from wave 18 it is now possible to also trigger currency conversion from the UI.
Or in SAP Analytics Cloud once can select it on the chart/table
And choose the target currency and then the conversion type
Then the result displays the amount in the new currency.
SAC Chart Adoption through BW Live Connection
When BW live connection was released for SAP Analytics Cloud we mainly focused on the table control. While certain charts- such as bar chart – already were usable, other chart types have very special requirements towards the backend. We therefor focused on enabling the missing chart types with BW/4HANA 2.0. Those we now also brought down with 7.50 FP16 to the NW BW code line. For details see again `Improvements of BW/4HANA Analytical Engine in Q1 2019 with BW/4HANA 2.0’ In addition to 2.0 scope we developed following missing links
- Linked Analysis with Hierarchies
- Geo Map with Choropleth
- Waterfall Chart with Delta
- Variance Chart including version and time
- Outlook BW/4HANA 2.0 : Custom Sorting
Linked Analysis with hierarchies
We enabled tuple filter which arises in linked analysis when having more than one dimension in BW/4HANA 2.0. Tuple filter take the combination of characteristic values on different characteristics into account. In BW, the filter was always the cartesian product of characteristic values on different dimension. Example: Consider dimension A and B and a tuple filter (A=1 AND B=’X’) OR (A=3 AND B=’Y’) OR (A=1 AND B=’Z’) then the cartesian product filter would be ( A=1 OR A=3 ) AND (B=’X’ OR B=’Y’ OR B=’Z’). We still had limitations if those tuples contain hierarchy nodes. This we now lifted with NW BW 7.50 SP16 or BW/4HANA SP4. See the example below where we show linked analysis between two charts having hierarchical representation
We marked three fields on the left-hand side (France and AUS/NRD/35 being 112.10, Germany with AUS/NRD/33 and AUS/NRD/37 being 280.25 and 224.20). On the left side the characteristic 0BC_Filed is no in the drill. So, we aggregate on this characteristic. However, since France is not part of Germany we neglect this value on the right side also. We therefore wee the sum of 280.25 + 224.2 being 504.45. This value is split up correctly between north and south part of Germany.
We still have the limitations on leading NOT. Also, in linked analysis we will not support a source with a general advanced filter above tuple filter coming from any data source outside BW.
Geo Map with Choropleth
Geo maps on BW live models except Choropleth layer were enabled with BW/4HANA 2.0. We now also enabled the Choropleth layer with NW BW 7.50 SP 16 and BW/4HANA FP 4.
We recall the set of requirements we now have.
- A HANA database version 1.0.122.06 onwards or release 2.0 with the uploaded Geo shapes is a prerequisite. Please follow the SAP HANA Spatial Reference to see how to upload the shape file of the geo content. This contains following steps
-Contains a world hierarchy of Regions with
-Creation of Spatial Reference System 3857.
-Import of two .tgz files - Activation of Content info objects 0GSHAPE3857 and 0GEOFEATID in that order. 0GEOFEATID corresponds to Regions in the HanaDU
- Creation of BW hierarchy SAPBWWORLDHIERARCHY for 0GEOFEATID via report RSD_GEO_SYNC_HIER derived from world hierarchy in HanaDU.
- Setting the correct Geographical type (GISTP) on the characteristic. In the eclipse modeling tools or transaction RSD1 for NW BW 7.50 you find on the ‘BI Clients’ tab:
Setting the GISTO to the right value the attributes longitude and latitude are automatically added to the InfoObject. The creation of a shadow table in Hana with Hana Spatial Type is also triggered behind the scenes. Reason is this is that the DDIC Type is still missing in the ABAP Application Server.
This table is used for the HANA geo operations orchestrated by SAC. If the geographical type is already set, a reactivation of the InfoObject triggers the creation of this shadow table.
Now you can load master data with geographical coordinates longitude and latitude in staging. In NW BW 7.50 in addition the report RSD_GEO_SYNC_ATTR has to be executed after alteration of the master data. This can be done manually but best is to use process chain and combine it with the master data load process.
For the GISTP following values can be chosen:
- Characteristic with geographic type ‘Dynamic Geographical Characteristic’ or ‘Static geo-characteristic with geo-attributes’ gain geo table with HANA spatial types upon activation is triggered.
- Char with geographic type ‘Dynamic Geographical Characteristic’, Dynamic geo-characteristic with values from attribute or ‘Static geo-characteristic with geo-attributes’ upon selection the 0GEOFEATID as navigation attribute (2,4) or transitive attribute should be added. This has to be done manual and is also necessary in cases the geo characteristic already exists.
Then you can use any query containing geo InfoObjects and a BW live model on top in SAC. You can use those models in any Geo chart and use various layers. Choropleth layer is added in NW BW 7.50 SP16 and will also be contained in the next feature pack 4 in BW/4HANA 2.0. Geo filtering as well as the within distance filter is working as in the HANA live connections. Only custom shape files and point of interests will not be supported.
In the picture above for example we see choropleth layer with filter
Custom shape files are also developed and will be available on in BW/4HANA SP4. Also then HANA revision larger than HANA 2.0 45 will be required. The shapes can be uploaded where the Geo type ist set as in Design Studio and BexWeb
Waterfall Chart with Delta
Waterfall chart was enabled with BW/4HANA 2.0 or notes 2688726 and 2734214. However, the delta calculation did note work from the beginning. This was now also enabled with notes 2660034, 2745245 and 2784681.
Variance Chart including version and time
With note 2660034 we support SAC Variance Charts based on two different measures. We also added the support versions (note 2745245 and 2784681). Only time-based variance was still missing. We faced 2 challenges. The first was that we needed to allow the usage of internal variables like current member in local formulas at a point of time where the variable submit was already done. This we achieved with the price of resubmit which we must pay anyway with restrictions on the fly.
The second challenge was that BW has not leveled based hierarchy on time. The virtual time hierarchies are all normal parent child hierarchies. The only level-based hierarchy in BW is the universal hierarchy which spans across the whole axis and only needs all characteristics of the different level to be part of the query. For the time variance we now developed a partial drill group which is similar to the universal display hierarchy but only affects part of the characteristic of the axis. We still require of all-time dimension to be part of the query. In addition we needed to develop new current member operators ‘Parallel Value’ was introduced. This all cam in BW/4HANA with SP4 and is available in NW BW with SP17 or note 2786890 - Enabling SAC Time Variance Charts with partial Universal Display Hierarchies. This also provides the capablities `UniversalDisplayHierarchyCustomDimensions` and `INACurrentMember`. for embedded analytics this is planned for the 2009 release.
Custom Sorting
We also will support custom sorting on dimension members in BW/4HANA FP04 for SAC. This is not available for NW BW
- SAP Managed Tags:
5 Comments
