Summary of Views Modelling Knowledge on Analytic for Hana 1.0

[Former Member]

After spending a fair amount of time studying for the Hana certification, this my collection of key notions about how to model views in Hana.

This is only a gathering based on the training material that leads to the certification "Hana Application Associate 2013".

This does not deals with all sections of the training. It doesn't include Data provisioning, Security, SQL objects, Studio, Search, Landscape, and so on.

Let me know if it is of any use. The version 1 is only a direct extract of my notes and could be improved...

MODELLING FLOW

- Importing source system definition and metadata

- Providing data

  * load flat file, ETL, DXC or SLT

- Creating information model

  1 - Model attribute views (aka. Dimension)

    # identify master table and fields

    # identify measures

    # join with text tables

    # attribute views can have attribute key (at least 1, separate from table keys)

  2 - Model analytical views

    # Identify key figures (desirable measures) for analytic

    # by joining attribute views with the fact table (aka. info-cubes)

    # becomes data foundation in 'Analytic Views'

    # A star schema consist of 1 fact table

- Hana views can have hierarchies and calculation (Analytic, Calculation views)

- Deploying data (creation of columns)

- Consuming data (SQL, MDX,etc.)

- Do not build table constant from OLAP Views

ATTRIBUTE VIEW

- From Studio > <hana-system>/content/.../<package-name> -> new Attribute

- Can be 'Derived' from another view

- Can move (Studio>System Panel> <a-analytical-object> > Refactor)

- Source items can be a table, sql view, etc.

- If an attribute view need to be used twice in the same analytic view; for the second reference,

  * Duplicate the attribute view with the subtype 'derived'

  * ! Limitation: The derived attribute view is in read-only mode

- Define column type: key attribute (at least one), attribute

- Have calculated columns (with date functions, string functions, math function, etc.)

    # calculated data type

    # !limits: no column type, aggregation or measure

- Have join (at least one)

  * In textJoin,

  * Join Cardinality can be 1:1, 1:N, N:1

  * Join types can be

    # inner: Facts with matching dimensions (default join type)

    # left outer: Facts whether with a matching dimension or not

    # right outer: All dimension whether with facts or not

    # referential (default) (reference guaranteed on each record) (works within the calling analytic view (OLAP engine), otherwise seen at runtime as inner join.

    # A join is taking into account if the right table has outputs. Otherwise, the projection is ignored

- Text Join for translation

  * with SAP ERP tables (SPRAS field) or equivalent design)

  * ! Limitation: not available in analytic view

    # it is possible to redesign a text join in using a dynamic filter on the language field ($$language$$). The 'language' input would get its value in an additional analytic view

- Raw data tab shows attributes data preview

- Analysis tab shows statistics

- All views are created in the SYS_BIC schema in the 'Column Views' folder

- ! Limitation: no 'Attribute View' through MDX

TIME

TIME based attribute view allows to create calendars and periods:

- based on the ERP fiscal calendar tables

- view subtype =Time; Calendar Type: gregorian; Granularity: Year, Week, Date,... Second,

Hierarchy

- In the semantic box

- Attributes from multiple data table

- The child column has to be a 'key attribute' in a Parent / Child hierarchy (same data type and recursive)

- Sorting possible on hierarchy levels

CONTRAINT FILTER

- Constraint filter is defined at design time on table

  * in Studio > Analytical view>data foundation> a attribute desc.> drop-down list>Filter

  * applied before the table join

  * faster than where clause

  * filter on the client

    # in a analytical view > semantic box> Default Client: Dynamic (versus Cross Client)

    # Session client which is defaulted in the User definition

    # In some tables, a field MANDT has the client number

  * filter on 'Domain Description' fixed values : these are description of the context.

    # Translatable by DDLANGUAGE abd DDTEXT fields

- ! Limitation: a left outer join may disregard a filter on the right table

ANALYTIC VIEW

- Are processed by in the "Calculation Engine" then the "OLAP Engine"

- With a "Data Foundation" box (to define the data tables in input)

  # ! Limitation: only 1 fact table with aggregation (star schema pattern). In 'Calculation Views', multiple foundation tables with measures or aggregations

- With the "Semantics" box > click on auto assign (assigns the "attribute or measure" type for each view output) variables, input    

    # column type: measure (at least one) or attribute

    # can disable the analytic privileges

    # can set the default schema

    # has outputs (with name and labels)

    # Aggregation type (SUM, Min, Max, etc.)

      § By default, calculation is done after aggregation

      § In order to be meaningful, For certain aggregation on a calculated column, the calculation has to be done before the aggregation (check-box on calculation design screen)

      § Calculation over a column already aggregated column may not be possible

        * Use the option "Calculate before aggregation"

        $ Ex: (sum(unit_price)*sum(units)<>sum(unit_price*units))

    # Advanced type (currency or unit measure)

    # can create restricted columns

      § On a calculation, a measure or another attribute

      § the restricted columns is either set to <Null> or only the corresponding row put to the result-set

      § NO impact on the number of rows of the statement

    # ! Limitation: Not all functions available !

    # ! Limitation: In MDX, only key attributes, measures and 'Leveled Hierarchy'  ('Hierarchy Active') can be used ()

- Temporal Join (match a key date with a validity period)

    # behave as a referential join from a key date

    # validity period with a 'from column' and 'to column'

    # ! Limitation: not on Attribute view

    # ! must have its left table in the data-foundation

- Currency Join

    # Attribute type: Measure; advanced type: Amount with Currency;

    # Currency type (for target or source attribute):

      $ Fixed: Target curency is a constant;

      * Column: An attribute (part of the output columns) define the currency

      * Input parameter: A input of type 'Direct Currency' define the target currency NOT the source)

    # Define source and target currency, conversion date

    # 'Upon Conversion Faillure' field can have 'Fail', 'Null' or 'Ignore' the calculation

    # ! Required tables:

      $ TCURR (Exchange Rates),

      $ TCURV (Exchange Rate Types; i.e.: current, average, historical),

      $ TCURF (Conversion Factors), TCURN (Quotations), TCURX (Currency Decimal)

- Analysis tab (Preview command) has calculation on limited set of data from the view

  * has "Value Axis" (data to analyze)

  * has "Label Axis" (filter and grouping criteria)

CALCULATION VIEW

- Like Analytical view with more calculation

- Row store procedures are processed in a separate engine

- Can be partially done by a graphical modeling

- In the Semantic Box

  # a multi-dimension 'Calculation Reporting' allows multiple dimension and corresponding aggregations

  # a 'Simple Calculation View' behave like a attribute view with distinct values only in the result-set (no aggregation or measure)

- Union Box (as SQL UNION):

  # ! Limitation:  Union Join is only possible in calculation view (then, for performance, use union verses Join box)

  # Multiple input views to union together (only 2 in SQL Script)

  # If the second table lacks some columns, a default value can be set instead

- Graphic calculation can be have joins, projections, aggregations boxes

  * Projection box can define expressions, define output field

    # the column KPLIKZ cannot be in the output, its value is 0 (Actual data) or 1 (Planned data) (CO-PA)

VARIABLES in MODELLER

- A variable definition filters the result-set

- Variable is bound to an attribute and has a value contained in the attribute available value

- Filter is defined at design time on the result set (Where clause is defined at runtime on query)

- Its 'Selection Type' is single value, range or IN-list

- If defined in a dependency view, a variable is accessible in read-only

- System variables: $$language$$, $$client$$

- ! Limitation: no variables or input for 'Attribute View'

INPUT in MODELLER

- Inputs are passed with the PLACEHOLDER reserved word

- The 'Input Type' is

    * Direct (with Data Type description),

    * Direct Currency(to use along with conversion),

    * Direct Date (Date picker on GUI),

    * Static list (with Data Type description),

    * Column (from the analytical model),

    * 'Derived from table'

DATA TYPE

- Satandard based; SQL-92 and supported by SQLScript

- Numeric type: TINYINT, SMALLINT, INTEGER, BIGINT

- DECIMAL(p,s), REAL, FLOAT, DOUBLE

- VARCHAR, NVARCHAR, CLOB (Character Large Object), NCLOB

- VARBINARY, BLOB

- Date: DATE, TIME, TIMESTAMP

SQLSCRIPT

- Cannot process in parallel if

  * a SQL statement that is not assigned to a variable

  * a local scalar or variable is used in the procedure

  * DDL / DML operation (read + write procedure)

  * Imperative logic (conditions,loops) -> try to do a minimum into those

FUNCTIONS

ex:SESSION_CONTEXT('CLIENT') -> client number of the user session

SESSION_CONTEXT('LOCALE') -> language of the client device (in POSIX; set by ODBC, JDBC, ODBO)

SESSION_CONTEXT('LOCALE_SAP') -> language of the client device (in SAP format for spras field)

PROCEDURE

in Project Panel > on a folder > new SQLScript procedure

in .../models/<nam>.procedure

Cyclic dependencies are not possible

in SQLScript (default), L language or R language (defined with 'LANGUAGE <language> is a procedure declaration')

Read-only procedure

- defined with 'READS SQL DATA' in a procedure declaration

- can only call other procedures that are readonly

Security mode:

- Definer (under _SYS_REPO user)

- Invoker (under the user connected)

- SQLSCRIPT VARIABLES: When using it, variables and inputs are prefixed by ':'

CALL

- CALL [Schema-name](in_param1 => in_val1, out_param => ? [, ...])WITH OVERVIEW';

- ex:CALL "_SYS_BIC"."workshop.sessiona.00.models/get_bp_adresses_by_role_sql"(

  partnerrole => '02', bp_addresses => ? )  - ? is an output binding with a empty parameter

    # Adding 'WITH OVERVIEW' to the call will add temporary tables with the resultset

  - A table can be found with an output

- Internal procedure call:

  * within calculation view or other proc.

  * CALL [Schema-name](:in_param1, out_param, [, ...]);

CREATE PROCEDURE {schema.}name {({IN OUT INOUT}

param_name data_type {,...})}

{LANGUAGE <LANG>} {SQL SECURITY <MODE>}

{READS SQL DATA {WITH RESULT VIEW <view_name>}} AS

BEGIN ...

END

ex: CREATE PROCEDURE get_bp_adresses_by_role_sql (

  :in partnerrole nvarchar(3),

  out bp_addresses tt_bp_addresses)

  LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  READS SQL DATA AS

BEGIN

declare lv_category nvarchar(40) := null;

declare lv_discount decimal(15,2) :=20;

lt_product = select * from "<schema>"."<package>::<table>""

select Category into :lv_category from :lt_product

if :lv_category = "Notebook" then

  : lv_discount := .20;

elseif :lv_category like '%Handheld%' then

  : lv_discount := .20;

endif; 

product_sale_price = select "ProductId" , "Price",

  "Price" - cast(("Price" * :lv_discount) as decimal(15,2)) as "SalePrice"

  from :lt_product;

bp_addresses =

SELECT a."PartnerId", a."PartnerRole",a."EmailAddress", a."CompanyName",

  a."AddressId", b."City", b."PostalCode", b."Street"

FROM "SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::businessPartner" As a

INNER JOIN "SAP_HANA_EPM_DEMO"."sap.hana.democontent.epm.data::addresses" AS b

ON a."AddressId"=b."AddressId"

WHERE a."PartnerRole" = :partnerrole;

END;

create type tt_bp_addresses as table(

  PartnerId nvarchar(10), ...)

# Create a local table type of a proc. (in a specific tab of a <name>.procedure file)

# Table Type are tabular result for procedures

# CREATE TABLE TYPE <name> (<name1> INTEGER, etc.) (or CREATE TYPE <name> AS TABLE)

WHILE <bool-stmt> DO

{stmts}

END WHILE;

FOR <loop-var> IN {REVERSE} <start> .. <end> DO

{stmts}

END FOR;

CALCULATION VIEW SQLSCRIPT

- often faster than SQL execution

- has 1 output box and 1 SQLScript box

  * Output can be a table type or a predefined table structure

  * use var_out as output name

- SQLScript

  * has definition blocks. They may run all in one statement

  * CE_COLUMN_TABLE blocks to declare a dataset for one table

  * CE_PROJECTION block for filtering the rows (where condition)

  * CE_JOIN block

  * CE_UNION block

  * has input parameters (prefixed by ":" in a script)

  * has data preview (with SQL statement available in "Show log" command)

  * ! Limitation: only DML and read-only proc. call

- ! Don't mix SQL statement with CE functions for performance

CE FUNCTIONS

- Processed in the "Calculation Engine" then processed by the "Join Engine" or "OLAP engine"

- Is in parallel mode unless

  * has imperative and loop logic

  * has DDL operations

  * scalar parameters or variables used within procedure

  * SQL not assigned to variable

  * CE has usually better result than SQL Optimizer

ex:it_books=CE_COLUMN_TABLE("BOOKS",["TITLE","PRICE"]); equivalent to

it_books=SELECT TITLE,PRICE FROM BOOKS;

it_pubs_books = CE_JOIN (:it_pubs, :it_books,["PUBLISHER"],

["TITLE","NAME","PUBLISHER","YEAR"]); equivalent to

it_pubs_books = SELECT title, name, P.publisher AS publisher, year FROM :it_pubs AS P, :it_books AS B

WHERE P.publisher = B.publisher;

ot_books1 = CE_PROJECTION (:it_books,["TITLE","PRICE", "CRCY" AS "CURRENCY"], "PRICE" > 50); applies a where condition

out = CE_COLUMN_TABLE("COLUMN_TABLE", [A, B, C])

# equivalent to out = SELECT A, B, C from "COLUMN_TABLE"

# resulting attributes cannot be rename

out = CE_JOIN_VIEW("ATTRIBUTE_VIEW", [A, B, C])

# equivalent to out = SELECT A, B, C from "ATTRIBUTE_VIEW"

out = CE_OLAP_VIEW("ANALYTIC_VIEW", [A, B]);

# equivalent to out = SELECT A, SUM(B) from "ANALYTIC_VIEW" group by A

# Aggregation is applied for key figures (defined in the referenced analytic view)

# similar syntax for CE_OLAP_VIEW and CE_CALC_VIEW

col_tab= CE_COLUMN_TABLE("COLUMN_TABLE");

out = CE_PROJECTION(col_tab, [A, B, C], ' "B" = ''value1'' AND "C" = ''value2'' ');

# similar WHERE  B = 'value1' AND C = 'value2'

col_tab= CE_COLUMN_TABLE("COLUMN_TABLE");

out = CE_AGGREGATION( (col_tab, SUM(D), [A, B, C]);

# similar to out = SELECT A, B, C, SUM(D) FROM"COLUMN_TABLE" GROUP BY A, B, C

CE_JOIN("COLTAB1","COLTAB2", [KEY1, KEY2], [A, B, Y, SUM(D)])

- similar to out = SELECT A, B, Y, SUM(D) from "COLTAB1" INNER JOIN "COLTAB2" WHERE "COLTAB1"."KEY1" = "COLTAB2"."KEY1" AND "COLTAB1"."KEY2" = "COLTAB2"."KEY2"

- the default join type is "INNER"

- similar syntac for CE_LEFT_OUTER_JOIN, CE_RIGHT_OUTER_JOIN

- CE_JOIN and CE_PROJECTION , CE_AGGREGATION

  # accept only analitycal views as inputs

  # attributes can be evaluated with CE_CALC

CE_PROJECTION

CE_PROJECTION (:var_table,\["param_name" [AS "new_param_name" ]\],{[Filter]})

- example:

  proj_tab = CE_COLUMN_TABLE("COLUMN_TABLE");

  out = CE_PROJECTION( :proj_tab, ["A", "B", "C", CE_CALC('midstr("D",2,5)', string) ]);

- midstr("D",2,5) for SQL SUBSTRING(D,2,5)

out = CE_UNION_ALL(:col_tab1,:col_tab2);

# equivalent to out = SELECT * FROM :col_tab1 UNION ALL SELECT * FROM :col_tab2;

CE_CALC (<expression>, <type>)

- example:

CE_PROJECTION(:product,["CID","CNAME","OID","SALES"],CE_CALC("SALES"*:vat_rate, decimal(2,10) AS SALES_VAT)],"CNAME=:cname");

- only in CE_JOIN, CE_PROJECTION, CE_UNION

cannot debug proc. with inputs or step by step debugging

CE_AGGREGATION (:var_table,[<AGGREGATION_OPERATOR> ("COLUMN") {AS "renamed_col"}],{["COLUMN-AGGREGATED"]})

ot_book=CE_AGGREGATION(:"it_books",[COUNT("PUBLISHER" AS "CNT"],["YEAR"])

if no COLUMN-AGGREGATED are mention, aggregation is grouped by the entire result-set

CE_UNION_ALL (:var_table1,:var_table2);

EXEC '<sql-statement>'

- execute SQL statement dynamically generated:

- risk of code injection

- recompiled at runtime and limit optimization

ex: CE_CONVERSION (:<input_table>,[error_handling='Keep unconverted' ,client = :client, family='currency', method='ERP', conversion_type='EZB', source_unit= :sourceCurrency, target_unit= :targetCurrency , reference_date= :'20101010', schema="EPM_MODEL",output='input,converted,output_unit'],

["AMOUNT_EUR" AS "AMOUNT_USD"]);

DECISION TABLE

- will generate a SQLScript as a runtime artifact

  1 - In REPO content > <package-name> > New 'Decision Table'

  2 - Define Vocabulary

    * within analytic views, tables,

  3 - Define Business Rule

    * Conditions (expressions on date enumeration or numbers)

    * Actions

  4 - Explore, simulate ans adapt decisions

    * Slice and Dice operations

DEBUG SQLSCRIPT

# in order to debug

Studio > administration > configuration > xsengine.ini > Add section > "debugger" > Add property > "enabled" = true

Studio > debug command > debug configuration > XS Javascript > New command > <hostname>, <port>, <SAP_SYSTEM_USER> <sap-system-password>

start the application in debug

get the session Id from the first http resquest > header > cookies > sessionId

- On user: ATTACH DEBUGGER