Data Modeling and Operation in SAP HANA
Data Modeling and Operation in SAP HANA : In SAP HANA, information views are created to model various slices of the stored data for analytical use case such as operational data mart scenarios or multidimensional reporting. There are three types of information views which are attribute view, analytic view, and calculation view. These are non materialized views. Various combinations of the non metadata is used to model a business use case. These non metadata which can be called as content data are classified as Attributes and Measure.Hierarchies are used to structure and define the relationship between attributes of attribute views and calculation views. SAP HANA Modeler is a powerful tool intended for users with extensive technical knowledge to model extensive functions in HANA. SAP HANA Information Composer helps users to create new SAP HANA based analytical models.
Data Modeling and Operation in SAP HANA
Attributes are the descriptive data such as country, region, province, customer number. There are Simple Attributes, Calculated attributes and Private attributes.
Simple Attributes: Descriptive data such as material number, material description.
Calculated attributes: Derived from an existing attribute by making use of arithmetic calculations.
Private attributes: Customized behavior of an attribute only for that view.
Measures are the quantifiable data such as sales quantity, invoiced quantity, margin etc. There are Simple Measures, Calculated Measures, Restricted Measures and Counters.
Simple Measures: A measureable quantifiable data
Calculated Measure: A measureable quantifiable data defined based on a combination of data from OLAP cubes, arithmetic operators, constants, and functions.
Restricted Measure: A measureable quantifiable data which are used to filter the value based on the user-defined rules for the attribute values.
Counters: A new measure to the calculation view definition to count the recurrence of an attribute.
4.3 Attribute view
Attribute view is used to model an entity based on the relationships between attribute data contained in multiple source tables. Simple attributes, Calculated attributes, Hierarchies can be modeled with in the attribute view.
Example: Customer number attribute can be modeled in an attribute view to define the relationship with various table which provide other customer number related attributes such as region, province, hierarchy, sold to customer, ship to customer, customer status etc to cater the business needs.
4.4 Analytic view
Analytic view is used to model data that includes measures and can contain multiple tables. These tables can be a combination of tables that contain both attribute data and measure data. The measures which are included in an analytic view must originate from only one of these tables. Simple attributes, Calculated attributes, Private attributes, Simple measures, Calculated measures, Restricted measures can be modeled in an analytic view.
Example: Sales history include different measures such as sales quantity, invoiced quantity. The attribute views created for customer number can also be included in the analytic view definition. This gives the coverage for the attribute data as the analytic view inherits the definitions of the attribute views that are included in the definition.
4.5 Calculation view
The calculation views are used to define more advanced slices on the data in SAP HANA database using calculation logic. The business logic with layers of calculation logic can be included in Calculation view to cater the business requirements. Simple attributes, Calculated attributes, Private attributes, Simple measures, Calculated measures, Restricted measures, Counters, Hierarchies (created outside of the attribute view) can be modeled in calculation view.
Example: A calculation view on Sales order history can mirror both the customer number attribute view and sales history analytical view. The modeling of a customer requirement to model the subcontracted quantity or sold to customer quantity based on advanced SQL logic sourced from multiple tables, column views, attribute views, analytic views can be done in calculation view. Joins, unions, projections, and aggregation levels can also be created in calculation view.
Hierarchies are used to structure and define the relationship between attributes of attribute views and calculation views. Level Hierarchy and Parent/Child Hierarchy are supported in SAP HANA.
4.7 Data model
The Output of the data model are Attribute Views which are dimensions, or Analytic Views which are cubes or Calculation Views which are similar to virtual provider with services concept in BW. These Attribute, Analytic and Calculation Views modeled from SAP HANA Studio. Reporting can be done with SAP Business Objects Analysis Office or SAP Business Objects Explorer or MS Excel 2007 (PivotTable).
The modeling starts with the Creation of Information Models where database views are created.
4.8 Non materialsied views
Pre-aggregation of data in cubes or storage of results in materialized views are no longer necessary due to HANA’s In Memory computations.
4.9 SAP HANA Modeler
SAP HANA Modeler is a powerful tool intended for users with extensive technical knowledge to model extensive functions in HANA:
• Creating information Views which are attribute views, analytic views, calculation views, and analytic privileges
• Processing models
• Managing modeling content by performing multiple administration activities
• Importing table definitions/schemas
• Loading data
4.10 Information Composer
SAP HANA Information Composer helps users to create new SAP HANA based analytical models. This is intended for business users with little or no technical database or modeling knowledge. It is an extremely user friendly tool with animations and hints that facilitates its use by business users. This is a web based tool which allows users to upload data to a HANA database and manipulate that data by creating Information Views. In the data acquisition portion, data can be uploaded, previewed and cleansed. In the data manipulation portion objects can be selected, combined and placed in Information Views which can be used by SAP Business Objects tools.
4.11 Example data model in SAP HANA
1.) Select required ERP tables such as Transactional, master data and texts tables
2.) Execute the initial load of ERP tables into HANA : Sybase Replication Server Data Services can be used.
3.) Recreate table relationships in HANA. Execute the master data modeling in HANA. Join conditions between tables and More complex logic in SQL script can be used.
4.) Create Analysis Authorizations
5.) Build reports on top of data model. Select suitable reporting tool such as MS Excel, Analysis (Office/Web), Explorer, WebI, Xcelsius Dashboard Designer, Crystal, etc. Create the report
6.) Validate and verify the data in report is correct with respect to the data in source.