121. What is a Materialized view or Materialization?
In computing, a materialized view is a database object that contains the results of a query.
122. What is SAP Information Composer?
SAP Information Composer is a web-based application that allows business to upload to and manipulate (clean, and model) data on the SAP HANA database.
The SAP HANA information composer uses a Java server, which interacts with the SAP HANA database. The Java server communicates with the SAP HANA information composer client via HTTP (port 8080) or HTTPS ( port 8443).
The SAP HANA information composer client is accessible to users who are assigned the IC_MODELER role. This role allows users to upload new content into the SAP HANA database and to create physical tables and calculation views.
123. Explain Infomation Models?
Information Models are multiple database views of transactional data stored in the physical tables of SAP HANA Database used for Analytical purposes.
Analytical Data Modeling is only possible for Column Tables i.e. Information Modeler only works with column storage tables.
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, which is basically the combination of Attributes and Measures:
Attributes are individual non-measurable analytical elements. Attributes add context to data.
These are qualitative descriptive data similar to Characteristics of SAP BW.
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:
The various Join Types available while modeling Attribute Views are
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 select a Non-key Attribute for output.
In computing, a materialized view is a database object that contains the results of a query.
- For example, it may be a local copy of data located remotely, or may be a subset of the rows and/or columns of a table or join result, or may be a summary using an aggregate function.
122. What is SAP Information Composer?
SAP Information Composer is a web-based application that allows business to upload to and manipulate (clean, and model) data on the SAP HANA database.
The SAP HANA information composer uses a Java server, which interacts with the SAP HANA database. The Java server communicates with the SAP HANA information composer client via HTTP (port 8080) or HTTPS ( port 8443).
The SAP HANA information composer client is accessible to users who are assigned the IC_MODELER role. This role allows users to upload new content into the SAP HANA database and to create physical tables and calculation views.
123. Explain Infomation Models?
Information Models are multiple database views of transactional data stored in the physical tables of SAP HANA Database used for Analytical purposes.
Analytical Data Modeling is only possible for Column Tables i.e. Information Modeler only works with column storage tables.
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, which is basically the combination of Attributes and Measures:
- Attribute View,
- Analytic View,
- Calculation View, and
- Analytic Privilege.
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.
- 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.
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 Measures 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.
The various Join Types available while modeling Attribute Views are
- Referential,
- Inner,
- Left Outer,
- Right Outer, and
- Text Join.
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 select a Non-key Attribute for output.
126. Explain 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.
- Level Hierarchy is rigid in nature, where the root and the child nodes can be accessed only in the defined order. This needs one attribute per hierarchy level and the number of levels defined are fixed.
- For example, COUNTRY, STATE, and CITY.
- Parent/Child 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, and MGRID.
127. Explain Time Dimension Attribute View?
Two types of Time Dimension Attribute Views are supported in Information Modeler.
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.
The SAP HANA database supports Analytic Privileges that represent filters or hierarchy drill down limitations for analytic queries.
Analytic Privileges grant access to values with a certain combination of dimension attributes.
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.
SAP HANA Database Stored Procedure defines sets of SQL statements that can process data. These follow the same constructs like T-SQL of Microsoft SQL Server or PL/SQL of Oracle database.
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.
128. Explain Data Foundation & Logical View?
In the Data Foundation tab we need to select the physical fact table. Next we define the 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 ).
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.
In the Data Foundation tab we need to select the physical fact table. Next we define the 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.
130. Explain Attribute Views, Analytic Views, Calculation Views, Analytic Privilege, Package, and Procedure?
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.
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.
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.
The SAP HANA database supports Analytic Privileges that represent filters or hierarchy drill down 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.
SAP HANA Database Stored Procedure defines sets of SQL statements that can process data. These follow 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.
No comments:
Post a Comment