SAP HANA Information Modeler

For that reason Replication Server creates SAP HANA tables in column store by default. Data Services also creates target tables in column store as default for SAP HANA database. The SQL command to create column table: "CREATE COLUMN TABLE Table_Name..". Also the data storage type of a table can be modified from Row to Column storage with the SQL command "ALTER TABLE Table_Name COLUMN".

We can Choose to publish and consume SAP HANA tables data at four levels of modeling using SAP HANA Studio Information Modeler Perspective.They are Attribute View, Analytic View, Calculation View and Analytic Privilege. These content data models are basically the combination of Attributes and Measures.

Attributes

Attributes are individual non-measurable analytical elements. Attributes add context to data. These are qualitative descriptive data similar to Characteristics of SAP BW. For example, MATERIAL_NAME. There are three types of Attributes in Information Modeling:

• Simple Attributes are individual non-measurable analytical elements that are derived from the data foundation. For example, MATERIAL_ID and MATERIAL_NAME are attributes of a MATERIAL subject area.
• Calculated Attributes are derived from one or more existing attributes or constants. The attribute is based on static value or dynamic calculation. For example, extracting the year part from the customer registration date, assigning a constant value to an attribute which can be used for arithmetic calculations.
  
• Private Attributes are used to model Analytic Views and cannot be used outside the view. Private attributes add more information to the data model. Private attributes of Fact tables are used to link to the subject area or dimensions i.e. Attribute Views. For example, we create an analytic view ANV_SALES to analyze the sales of materials, and select MATERIAL_ID as a private attribute from the database table SALES_ITEM. In this case, MATERIAL_ID could be used only for modeling data for ANV_SALES. We will learn about private attributes later when we will design Analytic Views.
  

Measures

Measures are simple measurable analytical elements. Data that can be quantified and calculated are called measures. They are similar to Key Figures in SAP BW. Measures are defined in Analytic and Calculation Views. Three types of measures can be defined in Information Modeling:

• Simple Measure is a measurable analytical element that is derived from the data foundation i.e. defined in the fact table. For example, SALES_AMOUNT.
• Calculated Measures are defined based on a combination of data from OLAP cubes, arithmetic operators, constants, and functions. For example, Net Revenue equals Gross Revenue - Sales Deduction, assigning a constant value to a measure for some calculation.
  
• Restricted Measures are used to filter the value based on the user-defined rules for the attribute values. For example, Gross Revenue of a material for country = US.
  

Attribute View

Attribute Views are the Reusable Dimensions or subject areas used for business analysis. Attribute Views are defined in Information Modeling to separate Master Data Modeling from Fact data. Examples of Attribute Views can be Customer, Material, Time. We define the Key or Non-key Attribute of the physical database tables of Master Data. We can join Text tables to Master data tables or two Master data tables like product and product group. Also tables can be selected from multiple schemas and are not restricted to one schema per Attribute View. Activated Attribute Views can be consumed for reporting or can be linked to the fact tables in Analytical Views.

Table Joins and Properties

The various Join Types available while modeling Attribute Views are Referential, Inner, Left Outer, Right Outer and Text Join. Apart from that the Join Condition and Cardinality ( 1:1, 1:N or N:1 ) needs to be defined accordingly. If we select the Join Type as Text Join then we need to define the Language Column and Description Mapping.

The Output structure of the Attribute View must be explicitly defined. At least one Key Attribute is mandatory. However any number of Non-key Attributes may be defined. We can also apply static Filter values ( List of Values ) on any columns of the tables selected in the Attribute View. Also this column does not need to selected as a Non-key Attribute for output.

Hierarchies

Hierarchies are used to structure and define the relationship between attributes of an Attribute View that are used for business analysis. Exposed models that consist of attributes in hierarchies simplify the generation of reports. For example, consider the TIME Attribute View with YEAR, QUARTER, and MONTH attributes. We can use these YEAR, QUARTER, and MONTH attributes to define a hierarchy for the TIME Attribute View. Two types of hierarchies are supported in Attribute Views of Information Modeler:

• Level Hierarchy - This hierarchy is rigid in nature, where the root and the child nodes can be accessed only in the defined order. This need one attribute per hierarchy level and number of levels defined are fixed. For example, COUNTRY, STATE and CITY.
• Parent/Child Hierarchy - This hierarchy is very similar to BOM ( Parent and Child ) and Employee Master( Employee and Manager ). The hierarchy can be explored based on a selected parent, and there can be cases where the child can be a parent. This hierarchy is derived based on the value. Variable number of levels for sub-trees within the hierarchy is possible. For example, EMPID, MGRID.
  

At present, Hierarchies defined in the Information Modeler are only accessible via MDX. Which means that at present such hierarchies can only be used from MS Excel.

Time Dimension Attribute View

Two types of Time Dimension Attribute Views are supported in Information Modeler. For Gregorian type Time Dimension the data is stored in _SYS_BI.M_TIME_DIMENSION. For Fiscal type Time Dimension data is stored in _SYS_BI.M_FISCAL_CALENDAR. Time Dimension Attribute Views are very often used while defining the Analytical View.

Analytic View

Analytic views are the Multidimensional Views or OLAP cubes. Analytic Views are used to analyze values from a single fact table of the data foundation based on the related attributes from the Attribute Views, looks very similar to Star Schema. We create a Cube-like view by joining Attribute Views to the Fact table data. For example, total sales of a material in a given region at a given time.

Data Foundation & Logical View

In the Data Foundation tab we need to select the physical fact table. Next we define the Attributes and Measures of the Fact table. We must define at least one Attribute and one Measure. In the Output structure the attributes of the fact table will appear under the Private Attributes as these as related only with the fact table. Optionally we can apply static Filter values on attributes of the fact table. We can also define Calculated Measures or Restricted Measures while designing the data foundation. Optionally we can also join database tables. We can select attributes from several tables but they must be joinable. But we can select measure from only one table( transactional data ).

In the Logical View tab we can join as many Attribute Views from any package to the Data Foundation. Attribute views are joined to the Private Attributes of the Data Foundation. Typically we include all key attributes of the Attribute View in the join definition. The default join type is Inner Join and the default Cardinality being N:1.

The foundation view shows the physical table with all fields that can be incorporated in to the final model. The logical view displays only those fields which have been selected to be included in the data model including the restricted and calculated measures defined.

Calculation View

Calculation Views are used to create data foundation using database tables, Attribute Views, Analytic Views, and Calculation Views to address a complex business requirement. If joins are not sufficient, we create a calculation view with SQLScript. Also Calculation Views are required if the Key Figures span across tables. A Calculation View is a composite column view visible to the reporting tools. When the view is accessed a function is implicitly executed. Calculation Views can be modeled via Graphical or SQL Script. Calculation Views support UNION. An example, comparing the sales of a material in a particular region for the last two years.

Analytic Privilege

Defines Privileges to partition data among various Users sharing the same Data Foundation. Analysis Authorizations for row-level security can be based on Attributes in an Analytic Views. The SAP HANA database supports Analytic Privileges that represent filters or hierarchy drilldown limitations for analytic queries. Analytic Privileges grant access to values with a certain combination of dimension attributes. For example, if we want to restrict access to a cube with sales data to values with dimension attributes of region = US and year = 2010. As Analytic Privileges are defined on dimension attribute values and not on metadata, they are evaluated dynamically during query execution.

Package

SAP HANA Packages are used to Group various related information objects in a structured way. Attribute Views do not need to be in the same package while defining Analytic View in some other package. Packages do not restrict access to Information objects for Modeling.

Procedure

SAP HANA Database Stored Procedure defines sets of SQL statements that can process data. We will learn how to write Procedures at a later section. Anyhow these follows the same constructs like T-SQL of Microsoft SQL Server or PL/SQL of Oracle database.

Notes

On Activation of the Information Models database Column Views are created in the schema _SYS_BIC. These Column Views can be accessed from reporting tools. Analytic Views do not store data. Data is read from the joined database tables. Joins and calculated measures are evaluated at runtime.

So typically while designing Information Models we will start with Creating a Package. Next we will design Attribute Views, Analytic Views and Calculation Views within the Content Package.