Purpose

SAP Analytics Cloud is a public Software-as-a-Service (SaaS) product that redefines analytics in the cloud by providing all analytics capabilities (BI, Planning, Predictive) for all users in one product. In SAP Analytics Cloud, you can:

create models in Sap Analytics Cloud.
import models from other data sources in Sap Analytics Cloud.
create live data connection to on-premise or cloud systems without any data replication. This feature allows SAP Analytics Cloud to be used in scenarios where data cannot be moved into the cloud for security or privacy reasons, or your data already exists on a different cloud system.

This document is focused to the live data connection to the HANA database, explaining the limitations, tips and tricks for this connection type.

SAP Analytics Cloud - live data connection overview

SAP Analytics Cloud provides you the possibility to define live data connections to on-premise or clouds systems. Data is “live”, meaning that when a user opens a story in SAP Analytics Cloud, changes made to the data in the source system are reflected immediately. With live connection, data volume is processed in your back-end system. There is no theoretical limitation. Query is executed in back-end system. Query should limit volume returned to Web Browser by applying adequate input control or aggregation.

Some benefits of live data connection are:

No data replication and prevents transfer of large data sets from source systems
Automatically updated with current data – “live” data
Create complex models and calculation in source systems and leverage them within SAC
Sensitive data can stay in local network, behind your firewall

In case of live data connection, only metadata is stored in SAP Analytics Cloud. The browser is sending the queries direct to the back-end and any chart is rendered in base of queries results.

SAP Analytics Cloud - live data connection to HANA database

Prerequisites

The prerequisites for using the live data connection to HANA database are:

SAP HANA 1.0 SPS12 with SAP HANA Info Access Service (InA), version 4.10.0 or higher is required.
If you are on SAP HANA 1.0 on-premise, to use features available in SAP Analytics Cloud 2018.01 and newer, you must be on SPS12 revision 122.14 or higher.
SAP HANA 2.0 SP01 or newer, with the SAP HANA EPMMDS plugin installed on your SAP HANA 2.0 system ( for more details please check SAP Note 2456225 and SAP Note 2444261).

For more information please check the System Requirements and Technical Prerequisites

Before decide to use the live data connection to HANA, please check the limitations which the product has for HANA live data connection.

How to leverage the HANA features

Geo spatial models

In order to use the geo spatial capabilities, you have to enrich your HANA model with location information.

If you want to use Choropleth Layer, you must download Spatial Data Delivery Unit. Please find bellow the steps you have to follow:

install and configure the Spatial Data Delivery Unit for SAP Analytics Cloud (see the SAP Note 2395407 )
create a specific package (SAP_BOC_SPATIAL) to put you Calculation View containing location information
authorize your user to access views and package (SAP_BOC_SPATIAL)
for SAC model you have to define location dimension

Now you can use your data into the geo chart:

HANA Hierarchies

SAP Analytics Cloud supports for HANA live data connectivity:

Parent-child hierarchy (PCH)
Level based hierarchy (LBH)

concepts modelled as part of SAP HANA analytical and calculation views. This means that for Story widgets focused on visualization like Charts and Table, it is possible to visualize the hierarchies, and also interact with them (i.e. drill-down in Charts, expand and collapse in Table).

Related to hierarchy management, there are a couple of limitations:

multiple parents are not supported – as workaround you can define more hierarchies for a single dimension
support of time hierarchies modeled as start join in HANA require a minimal HANA version 122.05 and SAC wave 7.
Hierarchy on measures are not recognized (“Display folder” option in HANA)
Only DATE and DATE_SQL data types can be recognized by time series chart.

Important! When you are using HANA Studio to develop your hierarchies, it is important to use the correct version corresponding to your HANA DB version (see 2375176), oterwise even there is no error reported by HANA Studio, the hierarchy is not properly working in SAC.

Time Hierarchy Management

If you plan to use your time hierarchy in a time series chart, there are a couple of restrictions:

- - only level based time hierarchies are recognized.
  - data format should be (YYYY, H1/H2, YYYYQ, YYYYMM, YYYYWW, YYYY-MM-DD ).
  - hours, minutes, seconds not supported.
  - the level based hierarchy should be defined using the standard time levels: TIMEYEARS, TIMEQUARTERS, TIMEMONTHS, TIMEDAYS and Node Style should be Name Path (example bellow):

HANA Calculations

In case of live data connection, the recommendation is to do complex calculations directly in HANA in order to use the power of HANA calculation engine which more powerful than your local PC.

There are a couple of things you need to take in consideration:

n order to use aggregation AVG from HANA, you need to HANA 122.14 + SAC wave 1. For the previous versions you need to use exception aggregations or custom calculation defined in SAC.
when you define complex calculation in HANA, you have to use SQL "Language". This because the calculation engine is doing type related truncation and you need to explicitly define the cast into formula. I will give you an example below:

Let's take a simple calculation "SalesPerDay" = "Sales" / 365 where Sales is defined like INTEGER and SalesPerDay like DECIMAL(34,7)
If you define the calculation like this:

The SAC report will display the truncated values:

If you use for the fornula SQL as Language OR define the formula like decfloat("Sales") / 365:

Sac report will display the correct values:

Best practices

HANA Modelling

In case of live data connectivity, the model definition becomes very important. In order to avoid performance issues in SAP Analytics Cloud, you have to be sure the HANA calculations views used into the stories are properly optimized at the backend side.

Things to be taken in consideration while doing HANA modelling:

Always try to do calculation after Aggregation (Aggregation removes duplicate results or aggregated them)
One more thing is – try to avoid Complex Expression in Calculated column, either one can push down this into model itself
Doing JOINs on key or indexed column helps a lot in performance
Most important is Filter data as early as possible by using input parameters, Analytical Privileges, Aggregations, etc.
Also try to reduce data flow between views.
Limit the max memory usage by queries so that these queries do not cause issues to the overall stability of the system.

Things to be taken in consideration while using HANA live data connectivity in SAC:

Try not to transfer larger result sets between HANA DB and SAC
Define most part of calculations at the model level in order to use the power of backed to calculate.
Granularity of the time dimension could be limited only to the necessary level (for Example Hour, Min, Sec are not supported in SAC as of now)
Avoid “select *” type of queries and build queries only with required measures and dimensions – you can Hide the unused dimensions (read Dimension Settings) .
If you are using reverse proxy, make sure this doesn’t become performance bottleneck.

Story design tips and tricks

When you start to design a SAC story using HANA live data connectivity, you have to take in mind that data is stored at the backed side, all data are coming from database through the browser.

Things to take in consideration when you to build your SAC story:

General design concept:

Create a simple story – use indicators to tell you what to focus on. In 5s you need to understand what is not performing well
Avoid pie charts – comparing slice is never as effective as if you use bar charts
Limit the number of charts per page – easy to be read and understand
Use goal lines with shadow regions, thresholds which indicate something you should care
Avoid overuse of bold fonts – they are harder to read due to their thickness
Remove all colors except for indicators. Colors should be used when you actually what to draw attention.

How to avoid performance issues:

avoid charts with a large number of data points (>500)
limit the number of charts per page – the number of parallel connections that the browser can make to the same domain is limited to 6-8 (depending by version)
avoid to load all and filter after – use aggregated data and tabs for detailed information where you can jump using hyperlink (data is already filtered – easy to be read and understand)
define the calculations as much as possible in HANA – use the power of HANA to do calculations
use "Optimize story builder performance" option in order to reduce the number of queries sent to the back-end.

Tips and tricks:

if you want to display the dimension members in a certain order, define an aditional column in your HANA table where you define the order. Into chart you can use "Advance sorting" option in order to sort in base of your column. more details here .

Troubleshooting performance issues

There are multiple reasons why the performance is affected. In case of performance issues it is very important to isolate the problem and identify the real cause of problem. The main things which can affect performances in SAP Analytics Cloud are:

Local client resources
Network bandwidth and latency
Queries time at backend side

Steps to follow in case of performance issue in SAC (HANA live data connection):

Check the local client resources usage (CPU and memory usage). Try also to run from other PC to see if any difference.
If you are using a wireless or VPN connection, make a test using a direct network connection.
Open Google Developer Tools (F12) in order to investigate deeper.
- - Network tab (check disable cache and capture screenshots) to analyze network performance issue

- - - - Analyze the timing information for GetResponse requests:
        Timing tab - will give you an information about where the time is lost (Ssending the request, runnign the request at back-end or loading content into the browser).
        
        Response tab - display the response received by the browser. In case of data requests, you can see the time spent to run the query at back-end (Overall time)

All of these information will help you to understand where the bottleneck is.

You can also save the information as http archive if you right click and choose “Save as HAR with content”.

- - - Performance tab (capture screenshots)
  - Drag your mouse left or right across the Overview to select a portion of a recording.

You can see in each moment in time, what is the status of local resources, the network activity correlated with the interface activity (screenshots). This will give you a complete picture and help you to understand what can be the cause of the performance issue. The profile can be saved if you right-cllick and choose "Save Profile" option.

Recomendation! Reduce as much as possible the record sizing – it is difficult to be analyzed, load and save because of size.

__________________________________________________________________________________________________________

Use this structure to help you compose your contributions for WIKI and at the same time will ensure spelling and grammar

Page tree

Purpose

SAP Analytics Cloud - live data connection overview

SAP Analytics Cloud - live data connection to HANA database

Prerequisites