Models Based on Live Data Connection to HANA Views

You can create models by connecting to SAP HANA database views. Functionality available for models based on HANA views is slightly different in comparison to other models.

With analytics models based on HANA views, you can use your existing data with SAP Analytics Cloud. Many of the features used in planning-type models are not relevant for this type of model, including financial data types, and currencies. These are some of the main differences and features:

Managing Dimensions and Versions

In HANA models, only a single Measures dimension is initially visible in the model, but all other dimensions are available on the All Dimensions tab. Because the number of dimensions may be large, and many of these dimensions won't be relevant in SAP Analytics Cloud, you can use the Hide check boxes to make dimensions unavailable in stories based on this model.

For models based on HANA views, version management functionality is also available. You can use this feature to map any of the imported dimensions to selected SAP Analytics Cloud planning categories such as Budgets, Actuals, or Forecasts.

You can define variables for all types of models, but if variables have been defined in HANA you will also be prompted to enter the required values in SAP Analytics Cloud when you open the data output.

Calculated Columns

When using calculated columns in SAP HANA live data connection models, you'll need to keep in mind the output type of any formulas and operations in use. For data correctness, it is important to ensure the following:

  • Any intermediate results are correctly cast to the appropriate type.
  • The final result has the appropriate type and scale set.

For example, if you use a function that returns an integer, and divide the result by another integer, the produced output will be an integer, resulting in the loss of any digits to the right of the decimal point. This loss could cause critical data correctness issues.

  • Example 1: Calculated Column (Data Type: INTEGER)

    "Age (in Years)"=DaysBetween("Birth_Date", now())/365

    • The formula will return only whole integers for age, which corresponds to the Data Type specified for the calculated column, and is the expected result.
  • Example 2: Calculated Column (Data Type: DECIMAL, Length=34, Scale=2)

    "Tenure (in Years)"=DaysBetween("Hire_Date", now())/365

    • The formula will return only whole integers for tenure, but the calculated column settings indicate that a floating point number is expected as the result, meaning that a significant amount of information will be lost for each tenure calculation.
    • In cases like this, there are two recommendations:
      • Use a conversion function on one or both integer values being divided, to convert to the floating number type with the desired precision (for example, DECFLOAT).
      • Increase the Scale setting to minimize the loss of precision as values for the calculated column are aggregated.
    • A corrected Calculated Column definition could be (Data Type: DECIMAL, Length=34, Scale=7)

      "Tenure (in Years)"=DECFLOAT(DaysBetween("Hire_Date", now()))/DECFLOAT(365)

Security

Analytics models that are based on underlying HANA database views will inherit the security restrictions applied in HANA. These restrictions can be modified in SAP Analytics Cloud by assigning specific user role security to them.

The Data Access option that is available for other model types to restrict input to specific named cells is not available for this type of model.

Note that access to models may be secured, and you may be prompted for credentials before opening the model.

Enriching String-Based Time Dimensions

When you create a model based on a live data connection HANA view, if your HANA view contains string-based time dimensions or date dimensions that you want to enrich with time-hierarchy information, complete the following steps.

If you enrich the string-based time dimensions or date dimensions in your HANA view, you can use time-related features such as Difference From story calculations, trend series charts, and time range sliders for filtering.

Note
As a best practice, we don't recommend creating string-based dimensions derived from another string-based time dimension that only exists in SAP Analytics Cloud and not in the HANA Calculation View. For more information, see SAP Note 3256812 Information published on SAP site.

Before you can follow these steps, you'll need these prerequisite configurations:

  • Your remote HANA system needs to be on baseline level 2.
  • The HANA MODEL DATE ENRICHMENT Delivery unit needs to be imported to the remote system. You'll need to grant SELECT access to the views in the delivery unit to end users.
Restriction
This feature isn't available with SAP HANA as a Service (HaaS) deployments.
  1. After you select a view, select (Create Time Dimension).
  2. In the Dimension field, select the HANA string-based time dimension or date dimension that you want to enrich (for example, 0CALMONTH, which needs to have the format YYYYMM – Calendar Year/Month). Make sure to follow the guidelines listed below:
    • Year must be of type String, with the format YYYY.
    • Quarter must be of type String, with the format YYYYQ.
    • Month must be of type String, with the format YYYYMM.
    • Day must be of type String, with the format YYYYMMDD, or a DATE type column.
    • Other types (for example, Timestamp and SECONDDATE) and formats (for example, MM for month) are not supported.
  3. In the Time View field, select the Calculation View that you want to use to enrich the dimension with.
    Note
    Choose the granularity that matches the granularity of the dimension you're creating.
  4. In the Field in Time View field, select the column in the Calculation View that will be used as the join dimension for enriching the dimension. Its format should exactly match the format of the dimension being enriched.
  5. Choose the Default Hierarchy for the new dimension.
    Note
    You'll be able to work with these enriched dimensions the same way you work with date dimensions in import data models.

Enabled Features

Some SAP Analytics Cloud features are enabled for models based on HANA views only if the SAP HANA system meets certain criteria:

SAP HANA System Configuration Features Enabled

Live HANA baseline level 1

SAP HANA 1.0:

  • SAP HANA 1.0 SPS12 rev 122.14

SAP HANA 2.0:

  • SAP HANA 2.0 SPS01 rev 012.02 or higher
  • The latest matching EPMMDS plug-in must be installed. For more information on EPMMDS plug-in versions, see SAP Note 2444261 Information published on SAP site.
  • Exception aggregation types:

    • COUNT excl. NULL
    • COUNT excl. 0, NULL
    • AVG excl. NULL
    • AVG excl. 0, NULL
  • Modeler formulas:

    • IF
    • RESULTLOOKUP
    • MIN
    • MAX
    • MOD
    • FLOOR
    • CEIL
    • ROUND
    • TRUNC
    • SQRT
    • EXP
    • GRANDTOTAL
    • %GRANDTOTAL
    • ISNULL
    • NOT
  • Story calculations:

    • Difference From
  • Search to Insight
  • Advanced restricted measures, such as Constant Selection

Live HANA baseline level 2

SAP HANA 2.0:

  • SAP HANA 2.0 SPS02 rev 024.05 or higher, or
  • SAP HANA 2.0 SPS03 rev 033.00 or higher
  • The matching EPMMDS plug-in must be installed. For more information on EPMMDS plug-in versions, see SAP Note 2444261 Information published on SAP site.

All of the above features that are enabled with Live HANA baseline level 1, plus the following additional features:

  • Enriched time dimensions
    Note
    You will need to download a Delivery unit (DU) and import it to your HANA live system. The DU will be named “SAC HANA Model Date Enrichment - SP0 for HANA_MODEL_DATE_ENRICHMENT” or similar.
  • Calculated dimensions
  • Measure-based filters
  • Blending between import data models and live data models
  • Date Difference story calculations
  • Custom sort order
  • Timestamp dimension support in time series charts
  • Histogram support for live data models
  • Natural language querying on models with timestamp dimensions
  • Story and modeling formulas:
    • LENGTH
    • LIKE
    • SUBSTR

Live HANA baseline level 3

SAP HANA 2.0:

  • SAP HANA 2.0 SPS02 rev 24.06 or higher, or
  • SAP HANA 2.0 SPS03 rev 34.00 or higher
  • The matching EPMMDS plug-in must be installed. For more information on EPMMDS plug-in versions, see SAP Note 2444261 Information published on SAP site.

All of the above features that are enabled with Live HANA baseline level 2, plus the following additional features:

  • ToNumber and ToText calculations
  • Improved Count and Average exception aggregation
  • The display of units and currency in charts, tables, and tooltips is based on the user profile

Live HANA baseline level 4

SAP HANA 2.0:

  • SAP HANA 2.0 SPS02 rev 24.10 or higher, or
  • SAP HANA 2.0 SPS03 rev 38.00 or higher, or
  • SAP HANA 2.0 SPS04 rev 40.00 or higher
  • The matching EPMMDS plug-in must be installed. For more information on EPMMDS plug-in versions, see SAP Note 2444261 Information published on SAP site.

All of the above features that are enabled with Live HANA baseline level 3, plus the following additional features:

  • Performance improvements when filtering across models

Live HANA baseline level 5

SAP HANA 2.0:

  • SAP HANA 2.0 SPS03 rev 37.02 or higher, or
  • SAP HANA 2.0 SPS04 rev 41.00 or higher

All of the above features that are enabled with Live HANA baseline level 4, plus the following additional exception aggregation types:

  • FIRST QUARTILE
  • FIRST QUARTILE excl. NULL
  • FIRST QUARTILE excl. 0, NULL
  • MEDIAN
  • MEDIAN excl. NULL
  • MEDIAN excl. 0, NULL
  • THIRD QUARTILE
  • THIRD QUARTILE excl. NULL
  • THIRD QUARTILE excl. 0, NULL