Creating Analytic Views - Creating Views - Setting Up the Analytic Model

7 Setting Up the Analytic Model

7.2 Creating Views

7.2.5 Creating Analytic Views

Analytic views are typically defined on a fact table that contains transactional data (as measures). Using analytic views you can create a selection of measures, add attributes and join attribute views.

Analytic views leverage the computing power of SAP HANA to calculate aggregate data, e. g. the number of sold cars per country, or the maximum power consumption per day. They are defined on only one fact table, a table which contains for example, one row per sold car or one row per power meter reading, or some form of business transaction records. Fact tables can be joined to allow access to more detailed data using a single analytic view as long as the facts (measures) come from a single table. Analytic views can be defined in a single table, or in joined tables.

Analytic views can contain two types of columns, attributes and measures, for which measures are of type simple, calculated, and restricted. Measures are attributes for which an aggregation must be defined. If analytic views are used in SQL statements then the measures have to be aggregated e. g. using the SQL functions SUM(<column name>), MIN(<column name>), or MAX(<column name>). Normal columns can be handled as regular attributes. They do not need to be aggregated.

1. Set Parameters

a) In the Modeler perspective, expand the Content node of the required system. b) Select the package in which you want to save your information object. c) From the context menu of the package, choose New Analytic View .

Note

Once you have an analytic view in a package, you can also create another one from the context menu of the Analytic View node in the respective package.

d) Enter a name and description for the view.

e) If you want to create a view based on the existing one, choose Copy From option, and select the required view.

f) Choose Finish.

The analytic view editor opens. The Scenario panel of the editor consist of the following three nodes: ○ Data Foundation - represents the tables used for defining the fact table of the view.

○ Logical Join - represents the relationship between the selected table fields (fact table) and attribute views that is, used to create the star schema.

○ Semantics - represents the output structure of the view.

In the Details panel you define the relationship between data sources and output elements. 2. Define Output Structure

a) Add the tables that you want to use in any of the following ways:

○ Drag the required tables present in the Catalog to the Data Foundation node.

○ Select the Data Foundation node in the Scenario panel, and in the context menu of the Details panel, choose Add Tables.

Note

You can choose to add the same table again in Data Foundation using table aliases in the editor. For example, if you want to have different cardinalities from the same table.

Restriction

It is not allowed to add column views to the Data Foundation of an analytic view. However, you can add column views in a calculation view.

Remember

If there is more than one table, you need to specify one of the tables as the central table (fact table) from which the measures will be derived. You can specify the central table by selecting a value in the

Central Entity property of the Data Foundation node.

b) If you want to query data from more than one table, in the Details panel context menu, choose Create Join, and enter the required details.

Tip

After specifying the central table or choosing one of the field as measure, when you save the view automatic assignment of attributes and measures is done. All the numeric fields from the central table are assigned as measures and others as attributes. In this case, you will not be able to join measures with other numeric fields. Also, if you have specified all the attributes and measures of the view before saving it, automatic assignment of attributes and measures will not overwrite them.

Note

After creating the join, you can edit its properties such as join type, cardinality, etc in the Properties

view. You can choose to create a Text Join between table fields in order to get language specific data. For example, consider that you have a product table that contains product IDs but no product description, and you have a text table for products that has language-specific description for each product. You can create a text join between the two tables to get language-specific details. In a text join, the right table should be the text table and it is mandatory to specify the Language Column. c) Add the table columns to the output structure (Semantics node) that you want to use to define the facts

in any of the following ways:

○ Select the toggle button on the left of the table field. ○ Right-click the table field, and choose Add to Output.

d) If you want to specify a filter condition based on which system must display data for a table field in the output, for example to display revenue for only selected companies based on the filter value, do the following:

1. Right-click the table field, and choose Apply Filter. 2. Select the required operator, and enter filter values. The table fields selected above form the fact table.

e) To create a star schema that is, linking the fact table with the descriptive data (attribute views), do the following:

1. Add the required attribute views in the Logical Join node in any of the following ways: ○ Drag the required attribute views present in the Content node to the Logical Join node. ○ Select the Logical Join node in the Scenario panel, and choose Add button to add the attribute

views.

2. Create joins between the views and fact table.

Note

In the Logical Join, you can create a temporal join between the date field of the fact table to an interval (to and from) field of the attribute view. The temporal join has to start from the fact table such that the single column must be in the fact table and, the to and from columns must be in the

table that is directly joined to the fact table. The join type must be a referential join. The supported data types are timestamp, date, and integer.

Restriction

While creating joins you must ensure that a table does not appear twice in any join path (in particular a self join is not supported). A join path is the set of joins that links the fact table to other tables.

While creating joins between analytic view and attribute view the same table cannot be used both in the join path of analytic view and attribute view. Also, in a join path of analytic view and attribute view, the table of the attribute view which is linked to the fact table should not have an alias table.

3. Optional Step: Create Calculated Column

a) In the Output of Logical Join panel, right-click Calculated Columns. b) In the context menu, choose New.

c) Enter a name and description (label) for the calculated column.

d) Select a data type, and enter the length and scale for the calculated column.

e) Select the Column Type to determine whether it is a calculated attribute or a calculated measure. f) If you select Calculate Before Aggregation, select the aggregation type.

Note

If you select Calculate Before Aggregation, the calculation happens as per the expression specified and then the results are aggregated as SUM, MIN, MAX or COUNT. If Calculate Before Aggregation is not selected, the data is not aggregated but it gets calculated as per calculation expression (formula), and the aggregation is shown as FORMULA. If the aggregation is not set, then it will be considered as an attribute.

g) In the Expression Editor enter the expression. For example, you can write a formula:

if("PRODUCT" = 'ABC, "DISCOUNT" * 0.10, "DISCOUNT") which is equivalent to, if attribute PRODUCT equals the string ‘ABC’ then DISCOUNT equals to DISCOUNT multiplied by 0.10 should be returned. Otherwise the original value of attribute DISCOUNT should be used.

Note

The expression can also be assembled by dragging and dropping the expression elements from the menus below the editor window.

h) If you want to associate the calculated column with currency and unit of measuring quantity, select the

Advanced tab page and select the required Type. i) Choose OK.

Remember

Calculated Columns can be created only at the Logical Join level and not at the Data Foundation level.

Note

The aggregation type for calculated columns is only taken into account, if they are calculated before aggregation

4. Optional Step: Create Restricted Columns

You can create restricted columns if you want to filter the value for an output field based on the user-defined rules. For example, you can choose to restrict the value for the Revenue column only for Region = APJ, and Year = 2012.

a) In the Output panel of the Logical Join, right-click Restricted Columns, and choose New. b) Enter a name and description for the restricted column.

c) From the Column dropdown list, select the column for which you want to apply a restriction.

Caution

The column for which you apply a restriction must be defined as measure in the semantics node otherwise the validation will fail.

d) Choose Add Restriction.

e) In the Parameter field, select the column that you want to create a restriction for, then select the operator and value.

f) Choose OK.

Note

For a restricted column the aggregation type of the base column is applied. 5. Define Attributes and Measures

a) Select the Semantics node.

b) In the Column panel, select the Local tab page, and change the type as attributes and measures.

Note

The Shared tab page shows the attributes of the used attribute views. While generating the column views, the joined private attribute name is ignored and the shared attribute name is considered, therefore the joined private attribute is not shown on the Semantics node.

Remember

If the MultiDimensional Reporting property of the analytic view is set to false, the view will not be available for reporting purposes. If the value is set to true, an additional column Aggregation is available to specify the aggregation type for measures.

Note

You can choose to associate an attribute with another attribute, which describes it in detail. For example, when reporting via Label Mapping (also known as Description Mapping), you can associate Region_ID with Region_Text.

Before SP05, you could associate an attribute with another attribute in a model. In the runtime object an <attribute>.description column is generated and is shown during data preview. Now, from SP05 onwards the behavior is as follows:

○ For an attribute (CUSTOMER) you can now maintain label mapping by selecting another attribute (TEXT) from the same model as "Label Column" in the Semantics node. The result is "TEXT" displaying as the label column in data preview. Note that the CUSTOMER.description column is not generated and is not shown in data preview anymore.

You can choose to rename a label column as <Attribute>.description. For example, if A1 has a label column B1, then you can rename B1 to A1.description but not as B1.description. Once you rename a label column with .description as suffix, the related columns appear side by side during data preview.

○ If you have created an object using the old editor (which supported the old style of description mapping) and try to open it using the new editor you will see a new column

CUSTOMER.description (as an attribute) which is hidden and disabled because this column cannot be used in other places such as parameter/variable, calculated column, restricted column and so on. You cannot maintain properties for this attribute for example, description.

CUSTOMER.description displays in the data preview as long as you do not change it in the editor. You can change its name. After changing the name you can maintain its properties and use it like other attributes.

You can rename the label column as <Attribute>.description. For example, if A1 has a label column A1.description, then you can rename A1.description to B1 and again as A1.description but not as B1.description. Once you rename a label column with .description as suffix, the related columns appear side by side during data preview.

Note

You can change the type of a measure and perform currency conversion by selecting it in the Local tab page and changing the Measure Type property in the properties panel.

6. Optional Step: You can filter and view the table data in the modeled view for a specific client as specified in the table fields, such as MANDT or CLIENT, by doing the following:

1. Select the Semantics node, in the Properties panel, edit the Default Client property.

Note

The default value for the property is the one that is specified as a preference. If the property is set to

Dynamic, at runtime the value set for the Session Client property is considered to filter table data. The

Session Client property is set while creating a user. 7. Optional Step: Assign Variable

You assign variables to a field at design time for obtaining data based on the values you provide for the variable. At runtime, you can provide different values to the variable to view the corresponding set of attribute data.

a) In the Semantics node Variables/Input Parameter panel, choose the Create Variable option. b) Enter a name and description for the variable.

c) Select the required attribute from the dropdown list.

Note

At runtime, the value for the variable is fetched from the selected attribute's data. d) Choose the required Selection Type from the dropdown list.

Note

○ Single Value - Used to filter and view data based on a single attribute value. For example, to view the sales of a product where the month is equal to January.

○ Interval - Used to filter and view a specific set of data. For example, to view the expenditure of a company from March to April.

○ Range - Used to filter and view data based on the conditions that involve operators such as: ○ "="(equal to)

○ ">" (greater than) ○ "<" (less than)

○ ">=" (greater than or equal to) ○ "<=" (less than or equal to)

For example, to view the sales of all products in a month where the quantity sold is >= 100.

e) Select Multiple Entries if you want to provide different values at runtime to filter data. For example, to view the revenue from a period of 2000 to 2005 and 2012.

f) If you want to assign the variable to attribute(s), in the Apply variable filter to panel, choose Add. g) Select the attribute(s) from the dropdown list to which you want to assign this variable.

Note

You can also assign a variable to an attribute later in the Column panel of the Semantics node Details

panel. To assign a variable to an attribute, select a variable from the variable dropdown list in the

Variable column. h) Choose OK.

8. If you want to parameterize currency conversion and calculated columns, create input parameters. 9. In the editor toolbar, choose Save and Activate. This saves and activates the view.

Note

If an active version of the affected objects exist, activating the current view redeploys the affected objects. You can also activate an object from the object context menu in the Navigator view.

Tip

You can choose to activate the other objects (required or impacted objects) along with the currenct object using the Save and Activate All option in the toolbar.

Note

Restriction

The behavior of analytic views with the new editor is as follows:

○ When an object (a table of or an attribute view) is removed and added again in an attribute view and analytic view editor in order to reflect the recently modified columns with its data type, it reflects the previous state of the columns . For more information about the problem and its solution, see SAP Note 1783668.

○ When you open an analytic view and there is a missing column in the required object, an error is shown and the editor does not open. For information regarding the solution of this issue, see SAP Note 1788552.

7.2.5.1 Using Temporal Joins

A temporal join indicates the time interval mapping between the master data and the transaction data for which you want to fetch the records.

You create a temporal join using the temporal column that specifies the time interval with the start and the end date. The result set is fetched based on the time interval mapped using the temporal column.

A record is only included in the results set if the time interval lies within the valid time interval. A time interval is assigned to each record in the results set. The records are valid for the duration of the interval to which they are assigned.

You use temporal conditions to indicate whether to include or exclude the value of the FROM and TO date fields while executing the join condition.

In the logical join, you can create a temporal join between the date field of the fact table to an interval (to and from) field of the attribute view. The temporal join must start from the fact table such that the single column must be in the fact table and, the to and from columns must be in the table that is directly joined to the fact table. The join type must be Referential. The supported data types are: timestamp, date, and integer.

1. Create a referential join between the attribute of the fact table and the attribute view. 2. In the Properties panel, select:

a) The Temporal Column that indicates the single time column in the fact table.

b) The From Column and the To Column to specify the time interval from the attribute view. c) The Temporal Condition which would be considered while executing the join.

Example

Consider an attribute view Product that contains master data about Products with attributes like, ProductID, Validity_Date_From, Validity_Date_To, and so on.

Similarly, consider an analytic view Sales that contains transactional data corresponding to the products sales with attributes, ProductID, Date, Revenue.

Now, to analyze sales data for products you can create a join between the two views using ProductID.

But to fetch data for a particular time period you need to assign temporal properties to the join. The temporal column in our example would be Date field in the Analytic view, and the From date and To date would come from the Validity_Date_From, and Validity_Date_To of the attribute view.

In document hana_dev_en (Page 127-135)