SAP HANA Modeling Overview SAP HANA Product Management
November, 2013
SAP HANA Modeling Overview
SAP HANA Modeling Views - Overview Data and Processing in SAP HANA In-Memory Data Stores: Column- and Row Store Optimized for Query and OLTP Workload SQL & OLAP Processing and specific Calculation Operators Application Function Library for specific Scenarios, e.g. Predictive Analytics
Modeling in SAP HANA Information Models (SAP HANA Views) are optimized for SAP HANA Engines and Calculation Operators Data/Columns are classified as Attributes or Measures in SAP HANA Views Attributes – descriptive data (known as Characteristics SAP BW terminology) Measures – data that can be quantified and calculated (known as key figures in SAP BW) No materialized aggregates
Three levels of modeling: Attribute View > Analytic View > Calculation View
© 2012 SAP AG. All rights reserved.
Public
3
SAP HANA Modeling Views - Overview
Attribute Views
Analytic Views
Calculation View
• Compose a dimensional view • Combines Fact-Tables with • Great flexibility for advanced use with a series of attributes derived Attribute-Views to Star-Schema- • Approach to model custom from a collection of tables or OLAP Cube-like objects for scenarios like e.g. Master Data Views multidimensional reporting. Combined use of Multiple-Fact Highly re-used and shared in • Stores no aggregates and massTables/Analytics Views Analytic- and Calculation Views aggregates on the fly Build Models on Normalized Data Used to build Hierarchies • Hierarchies are key for multi Re-Use and stack views Hierarchies are key elements in dimensional access (navigation, Make use of custom scripted views use with Analytic View for multifiltering, slicing and aggregation) dimensional reporting
© 2012 SAP AG. All rights reserved.
Public
4
SAP HANA Modeling Views - Multidimensional Model Scenario Reporting Tools can usually directly consume HANA Calculation Views or Analytic Views.
Calculation View
Multidimensional Tools support Hierarchies for Navigation, Filtering and Aggregation and HANA Prompts (Variables & Input Parameters) for efficient Pre-Filtering of Data.
Calculation Views are usually build upon Analytic-, AttributeViews, and Column Tables
© 2012 SAP AG. All rights reserved.
Analytical View
Attribute View
Column Table
Public
5
SAP HANA Modeling Views - Normalized Data Model Scenario
SAP HANA Calculation Views provide the means to model sophisticate views based on normalized data structures.
SAP HANA Calculation Views typically feed data to Business Applications, like SAP HANA XS build Applications
Complex Calculation Views demand a more explicit intent and control of the modeled set-based data flow, i.e. slicing, aggregation and filtering of sets as input to joins, unions etc.
See SAP Note 1857202 © 2012 SAP AG. All rights reserved.
Public
6
The SAP HANA Studio Model Editor Model Scenario
General View Properties, Semantic Information
The Graphical Model Editor Standardized graphical editing across different SAP HANA view types Build of different common panels – Scenario provides Overview – Semantic node provides better summary of output structure of the model + editor view of output objects + general view properties – Logical-Join- and Data Foundation-Nodes are specific to Attribute- and Analytic Views – Calculation-Views are modeled in the scenario panel based on a palette of node-objects like join, union, etc. to compose a custom data flow.
© 2012 SAP AG. All rights reserved.
Logical Join of Data Foundation & Dimensions
Data Foundation
Public
7
SAP HANA Studio Modeling Environment
SAP HANA Studio – Modeling Perspective The SAP HANA Modeler within the SAP HANA studio • Eclipse-perspective targeting Power Users for Content Design • Graphical Information Model Design Environment for HANA optimized Models ‒ Attribute-, Analytic- and Calculation Views ‒ without materialized aggregates
• Development of advanced Calculation Models ‒ SQL & SQLScript-based, Use of Application Function Library- and SQLScript-based Stored Procedures © 2012 SAP AG. All rights reserved.
Public
9
SAP HANA Studio – Modeling Perspective Typical View-Windows
The SAP HANA Modeler Eclipse-perspective
Perspectives
Perspectives are build upon different ViewWindows in the Eclipse environment
© 2012 SAP AG. All rights reserved.
Public
10
SAP HANA Studio – Modeling Perspective The SAP HANA Modeler within the SAP HANA studio • Quick Launch access to common modeling tools
Wizards for creating / graphical designing HANA Content objects as Information Models
Managing the work environment and system connections
© 2012 SAP AG. All rights reserved.
Actions or wizards for working with content
Actions or wizards for working with data or working with a SQL console
Public
11
SAP HANA Studio – The HANA System Environment
The content contains the HANA Studio Models meta data, e.g. for Analytic Views, Attribute Views, Calculation Views. The content is organized in packages.
The catalog contains the HANA database schema and the stored objects, like column tables
From content tree and the package structure the Editor to build new HANA Views can be called © 2012 SAP AG. All rights reserved.
Public
12
SAP HANA Studio – The Unified Model Editor Model Scenario
General View Properties, Semantic Information
Unified Graphical Editor Standardized graphical editing across different HANA Database view types Build of different common panels – Scenario provides Overview – Semantic node provides better summary of output structure of the model + editor view of output objects + general view properties – Logical-Join- and Data Foundation-Nodes are specific to Attribute- and Analytic Views – Calculation-View* supports different nodes
Logical Join of Data Foundation & Dimensions
Data Foundation
*New in SAP HANA SPS6 © 2012 SAP AG. All rights reserved.
Public
13
Getting Started with Modeling Modeling Attribute Views Modeling Analytic Views Modeling Calculation Views
Modeling Attribute Information – HANA Attribute Views What is an Attribute View? • ... are the reusable Dimensions Used for Analysis which add context to data.
Semantic Attribute Information
• Can be regarded as Master Data Views Build a semantic attribute information collection from various source tables (e.g. join ‘Plant’ to ‘Material’) Can be re-used as dimensions in Analytical Views Specific time-based Attribute Views are supported
© 2012 SAP AG. All rights reserved.
© SAP AG 2011
Attribute Data Foundation
Public
15
Modeling Attribute Information – HANA Attribute Views What are the capabilities of Attribute Views? • Attribute Views supports Description Mapping & Text-Join Lookup master data tables e.g. used for handling of multi-language master data Various joins types between data foundation tables Time Dimension Attribute Views Hierarchies (Level, Parent-Child)
Calculated Attributes (static or dynamic calculations) Define filter values on column …
© 2012 SAP AG. All rights reserved.
© SAP AG 2011
Public
16
Attribute View – Data Foundation Building the Attribute Data Foundation • An Standard Attribute View is a join of several tables Referential, Inner, Left Outer, Right Outer and Text Join Text Join are used to join a text table to a master data table to lookup language dependent texts like product name
Filter (static or dynamic) on source object columns The output structure of the view is explicitly defined Calculated attributes added
© 2012 SAP AG. All rights reserved.
Public
17
Attribute View – Define View Semantics Define Semantic Output Representation of Attributes • Classify the Columns and Calculated Columns as attributes and key attributes
• Maintain General View Properties Default Client, Enable Analytic Privileges
• Attributes can be specified as hidden, if only used in calculated attributes
• For each output column a ‘label column’ can be specified, which points to another column containing a description for the original column
• Define Attribute Hierarchies Level Based and Parent-Child Hierarchies
© 2012 SAP AG. All rights reserved.
Public
18
Attribute View – Save, Activate and Preview Create executable version of the view • Save the view: Creates a Repository/Design-Time Object This information model itself is not visible to reporting tools • Activate the view: Creates a Catalog/Run-Time Object This creates a database view in schema ‘_SYS_BIC’ (a so-called ‘column view’) This column view can be accessed from reporting tools
• Data Preview
© 2012 SAP AG. All rights reserved.
Public
19
Getting Started with Modeling Modeling Attribute Views Modeling Analytic Views Modeling Calculation Views
Modeling Facts and Dimensions – OLAP Star Schemas What is an OLAP Star Schema? • Star schema usually has one fact table containing the key figures • Key figures are measureable numbers • Dimensions describe the key figures and enrich them with additional information (usually master data)
HANA Analytic Views build and extend this concept of a OLAP Star Schema Model
• Cardinality in star schemas is generally N:1 fact to dimension
• Joins in star schemas are generally Left Outer Joins • Slicing and dicing is a feature whereby users can take out (slicing) a specific set of data of the cube and view (dicing) the slices from different viewpoints. © 2012 SAP AG. All rights reserved.
Public
21
Modeling Facts and Dimensions – HANA Analytic Views What is an Analytic View? • Can be regarded as Cube-/OLAP Star Schema-like, but without storing aggregated data Data is read from the joined database tables
Joins and calculated measures are evaluated at run time
• Fact Data from the ‘Data Foundation’ is joined against modeled Dimensions (Attribute Views) fact table contain the key figures ‘Measures’
Semantic Information
Dimensions describe the key figures and enrich the data Logical Join of Data Foundation & Dimensions
HANA Analytical Views are highly optimized for aggregating mass data
© 2012 SAP AG. All rights reserved.
© SAP AG 2011
Public
22
Modeling Facts and Dimensions – HANA Analytic Views What are the capabilities of Analytic Views? • Defining Calculated & Restricted Measures Aggregation (sum, max, ..) Expressions / Operators / Functions
• Currency and Unit Conversion • Variables and Input Parameters Variables and Parameters are usually entered manually by the users using UI Prompts Note: Variables are bound to attributes and used to filter using WHERE clauses (Single, Interval, Range).
© 2012 SAP AG. All rights reserved.
© SAP AG 2011
Public
23
Analytical View – Structural Overview Semantics Information • Classify the Columns and Calculated Columns as attributes and measures. You can also create variables/input parameters, and assign variables to the columns in the Semantics node.
Semantic Information
Logical Join of Data Foundation & Dimensions
Logical Join with Attribute View • Join attribute views to these tables in the Logical Join node. Data Foundation
Data Foundation of Fact Data • Create a fact table by adding and joining the tables
© 2012 SAP AG. All rights reserved.
Public
24
Analytical View – Data Foundation Building the Analytic View Data Foundation • Fact Data is modeled in the ‘Data Foundation’ pane Add source fact table(s), which contain the key figures ‘Measures’
Note: multiple tables can be joined, however measures may only be selected or derived from one of the table Select attributes and measures from table(s) this defines the data foundation Input Parameter can be defined
Filter on source object columns
Data Foundation
• Can be based on any table column • Column does not need to be selected for output ([key] attribute) • Input Parameters* are supported.
*Since SPS6 Input Parameter references can be used (typed in) for filters on data foundation in Analytic and Attribute Views © 2012 SAP AG. All rights reserved.
© SAP AG 2011
Public
25
Analytical View – Logical View Building the Logical Analytic View • Define joins between Attribute Views and Data Foundation Typically Left Outer-, Inner-, Referential- or Temporal Joins
© 2012 SAP AG. All rights reserved.
© SAP AG 2011
Public
26
Analytical View – Logical View Building the Logical Analytic View • Creating Calculated Columns in the Logical View Output Aggregation (sum, min, max, count), Counter (DistinctCounts) Data Types (decimals, numbers etc.) Expressions / Operators Functions (String, Date Math, Conversion…)
• Advanced Semantic Calculations Unit of Measure and Currency Conversion • Creating Restricted Columns Support constraints to apply for data used for calculating the measures
• Creating Input Parameters Prompted Parameter use e.g. in Calculation and/or Filters © 2012 SAP AG. All rights reserved.
© SAP AG 2011
Public
27
Analytical View – Define View Semantics Define Semantic Output Representation of the Analytical View
Set View Properties
Classify columns as attributes or measures Assign Variables to columns Create Measures Display Folders Order Columns, specify as hidden, select label columns, …
© 2012 SAP AG. All rights reserved.
Inspect Hierarchies from underlying Attribute Views
Create Variables and Input Parameters
Public
28
Analytical View – Define View Semantics Define Semantic Output Representation of the Analytical View • Maintain General View Properties Default Client, Enable Analytic Privileges and further advance properties.
• Semantic Column Area Filter Variables can be directly specified and Variable Names specified with Filter-Information For Multidimensional Reporting Clients, Measure Display Folder can be defined to better structure measures Column Semantic-type can be specified and full unit/currency conversion specifications Calculated column definition reviewed Where-used analysis of columns
© 2012 SAP AG. All rights reserved.
Public
29
Analytical View – Excursus Variable and Input Parameters What are Variables and Input Parameters? • Variables and Input Parameters Variables and Parameters are usually entered manually by the users using UI Prompts
Variables are bound to attributes and used to filter using WHERE clauses (Single, Interval, Range) Input Parameters are passed by Placeholders and used in Formulas (Attribute, Currency, Date, Static List) & Filters Variables can accelerate the joining of views by reducing the data set before join occurs
Input Parameter SELECT as Filter SUM(ORDER_QTY), COUNTRY FROM "_SYS_BIC"."eim260/SALES_DELIVERY_CV" ('PLACEHOLDER' = ('$$IP_COUNTRY$$', 'US')) WHERE ("CREATED_ON" BETWEEN ('20120101') and ('20120501')) AND ("DELIVERY_DATE" BETWEEN ('20120101') and ('20120501')) GROUP BY "COUNTRY" Variables Input Parameter in Calculations if("COUNTRY"= '$$IP_COUNTRY$$',"GROSS_AMOUNT",0)
© 2012 SAP AG. All rights reserved.
© SAP AG 2011
Public
30
Analytical View – Excursus Variable and Input Parameters Creating Variables • Variables and Input Parameters Revisited create variable dialog texts to be more self-explaining Selected Attributes in Variables are automatically defaulted as applied for the variable filter Variables can be directly created from the details area of the semantic node where the variable name is show along with the filter icon External views or tables for value help dialogs can be referred to. Special default values are now supported and can be authored via an expression editor – E.g. formulas as default like “date(Now())“ for today
© 2012 SAP AG. All rights reserved.
© SAP AG 2011
Public
31
Analytical View – Excursus Variable and Input Parameters Creating Input Parameters • Input Parameters Special default values are now supported and can be authored via an expression editor – E.g. formulas as default like “date(Now())“ for today – Evaluated expression will be shown, e.g. for now-function the day will be displays
External views or tables for value help dialogs can be referred to Semantic type for input parameter specifications
© 2012 SAP AG. All rights reserved.
© SAP AG 2011
Public
32
Analytical View - Save, Activate and Preview Create an executable version of the analytical view and preview • Save the view: Creates a Repository/Design-Time Object
• Activate the view: Creates a Catalog/Run-Time Object • Call the preview , for which there are 3 modes available Raw data (table display) Number of distinct values per column
Interactive graphical analysis Can create filters Number of records is limited (as specified in the SAP HANA Studio preferences)
© 2012 SAP AG. All rights reserved.
Public
33
Analytical View - Save, Activate and Preview Create an executable version of the analytical view and preview • Save the view: Creates a Repository/Design-Time Object
• Activate the view: Creates a Catalog/Run-Time Object • Call the preview , for which there are 3 modes available Raw data (table display) Number of distinct values per column
Interactive graphical analysis Can create filters Number of records is limited (as specified in the SAP HANA Studio preferences)
© 2012 SAP AG. All rights reserved.
Public
34
Analytical View - Run-time object: column view Activation creates a column view can be accessed from reports
Reports
Exposed like regular views via SQL/MDX interfaces
Column Store
Activate © 2012 SAP AG. All rights reserved.
© SAP AG 2011
Public
35
Getting Started with Modeling Modeling Attribute Views Modeling Analytic Views Modeling Calculation Views
Modeling Custom Scenarios – HANA Calculation Views What is a Calculation Views?
Application UI
• Calculation Views are composite views and can be used to combine other views Can consume other Analytical-, Attribute-, other Calculation Views & tables • Approach to model custom scenarios like
Combined use of Multiple-Fact Tables/Analytics Views Build Models on Normalized Data Re-Use and stack views Make use of custom scripted views
Calculation View
• Great flexibility for advanced use • Modeling Approaches: Graphical Modeler or SQLScript-based Editor
© 2012 SAP AG. All rights reserved.
Public
37
Modeling Custom Scenarios – HANA Calculation Views What are the capabilities of Calculation Views? • Graphical Calculation Views Can consume other Analytical-, Attribute-, other Calculation Views & tables Built-in Union, Join, Projection & Aggregation nodes Calculation View Besides flexibility provides additional features like DistinctCount calcualtion, dynamic joins, …. No SQL or SQL Script knowledge required • SQLScript-based Calculation Views
Attribute View
Can be regarded as a function, defined in the HANA-specific language “SQLScript”, which is build upon SQL commands or special HANA pre-defined functions.
Analytical View
Must be read-only. Calculation View © 2012 SAP AG. All rights reserved.
Column Table Public
38
Graphical Calculation View – Building the Scenario Designing the Calculation View Scenario Select View Type: Select Graphical or SQL Script Select Output Type: should the CV’s final output set aggregate measures or rather produce a list-like output without aggregating the measures. – Enable Data Category “CUBE” aggregates measures, then the default (top output)-node is pre-selected as an aggregation node (can be switched later) otherwise as an projection node If Data Category is left ”
