Analytic views are typically defined on a fact table that contains transactional data (as measures). You can define analytic views in a single or joined table, create a selection of measures, add attributes and join attribute views.
Context
Analytic views can contain two types of columns: attributes and measures. Measures are simple, calculated or restricted. If analytic views are used in SQL statements, then the measures have to be aggregated. For example, using the SQL functions SUM(<column name>), MIN(<column name>), or MAX(<column name>). Normal columns can be handled as regular attributes and do not need to be aggregated.
Procedure
1. 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 ... and search for the required object.
Note
○ You can add the same table in the Data Foundation by using table aliases in the editor; for example, consider a table containing supplier and buyer information with postal code, and another table containing the link from postal code to region and country. Now, you want to join this geography table two time to the buyer and the supplier.
○ If your analytic view has more than one table, you need to specify which table is 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.
Restriction
You cannot add column views to the Data Foundation of an analytic view. However, you can add column views to a calculation view.
b) To query data from more than one table, go to the Details panel context menu, choose Create Join, and enter the required details.In the New Joinwindow, choose a left and a right table. To create a join, choose appropriate columns from each table and set relevant properties. At a time, you can create only a single join.
You can also join two tables by dragging and dropping the join from the column of one table to a column in another table . After creating the join, you can edit its properties, such as join type and cardinality, in the Properties view. You can choose to create Text Join between table fields in order to get language-specific data
SAP HANA Modeling Guide Information Views
P U B L I C
© 2013 SAP AG or an SAP affiliate company. All rights reserved. 51
You have a product table that contains product IDs without descriptions and you have a text table for products that contains language-specific descriptions for each product. You can create a text join between the two tables to get the language-specific details. In a text join, the right table should be the text table and it is mandatory to specify the Language Column.
Tip
You can set the cardinality of the join as required. By default, the cardinality of the join is empty. The empty cardinality can be specified if you are not sure about the right cardinality. If you specify the empty cardinality, the system will determine the the cardinality during the join evaluation phase.
Caution
Selecting the incorrect cardinality can lead to erroneous data and memory issues.
c) Perform one of the following steps to add the table columns to the output structure (Semantics node) that you want to use to define the facts.
○ Select the toggle button on the left of the table field.
○ Right-click the table field, and choose Add to Output.
d) To specify a filter condition based on which system must display data for a table field in the output,:
Only display revenue for companies that fulfill the filter conditions you have specified.
1. Right-click the table field, and choose Apply Filter.
2. Select the required operator, and enter the filter values.You can use the following operators for filtering.
Filter Operator Description
Equal To filter and show data corresponding to the
filter value
Not Equal To filter and show data other than the filter value
Between To filter and show data for a particular range
specified in the From Value and To Value
List of Values To filter and show data for a specific list of values separated by comma
Not in list To filter data and show data for the values other
than the ones specified. You can provide a list of values to be excluded using comma.
Is NULL To filter and show row data having NULL values
Is not NULL To filter and show data of all the rows that have
non NULL values
Less than To filter and show data with values less than the
one specified as filter value
Less than or Equal to To filter and show data with values less than or equal to the one specified as filter value
52 P U B L I C
© 2013 SAP AG or an SAP affiliate company. All rights reserved.
SAP HANA Modeling Guide Information Views
Filter Operator Description
Greater than To filter and show data with values greater than
the one specified as filter value
Greater than or Equal to To filter and show data with values greater than or equal to the one specified as filter value Contains Pattern To filter and show data that matches the pattern
specified in the filter value. You can use '?' question mark to substitute a single character, and '*' asterik to substitute many. For example, to filter data for continents that start with letter A, use Contains Pattern filter with value A*. This would show the data for all the continents that start with A like Asia and Africa.
Note
You can either specify a fixed value for the filter, or use an input parameter of the current analytic view to provide the filter value at runtime during data preview. You can specify the filter value as fixed or dynamic in the Value Help dialog by specifying the Type as Fixed or Input Parameter.
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:
1. Perform one of following steps to add the required attribute views in the Logical Join node:
○ 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 to add the attribute views.
2. Create joins between the views and the 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, that is r 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 in the 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 2. Create Calculated Column (Optional)
a) In the Output of Logical Join panel, right-click on Calculated Columns and in the context menu, choose New.
SAP HANA Modeling Guide Information Views
P U B L I C
© 2013 SAP AG or an SAP affiliate company. All rights reserved. 53
b) Enter a name and description (label) for the calculated column.Select a data type, and enter the length and scale for the calculated column.
c) Select the Column Type to determine whether it is a calculated attribute or a calculated measure.
d) 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. You should selecting Calculate Before Aggregation only when required as it may decrease the performance.
e) In the Expression Editor enter the expression.
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.
f) To associate the calculated column with the currency and unit of measuring quantity, select the Advanced view and select the required Type. Choose OK.
Remember
Calculated Columns can be created only at the Logical Join level and not at the Data Foundation level.
Tip
You can also create a calculated column based on the existing one by copying it and making the required changes. The copy paste option is available from the context menu of the calculated column. You can also use the CTRL + C and CTRL + V keyboard shortcuts. The copy paste functionality works only in the same editor, that is, if you copy a calculated column in one view editor you cannot paste it in another view editor.
Also, if you copy a calculated column in one editor and go to another view editor and again copy another object, the paste option in the previous editor is not available. The copy paste functionality for calculated columns only works in the Output panel.
3. Optional step: Create counters to obtain the number of distinct values of an attribute a) Choose the Logical Join node.
b) In the Output panel, choose Calculated Columns.
c) In the context menu of the Calculated Columns, choose New Counter.
d) In the Counter window, enter a counter name and a description.
e) To hide the counter during data preview, select the Hidden checkbox.
f) To add attributes in the Counters panel, choose Add.
g) Choose OK.
4. Optional Step: Create Restricted Columns
54 P U B L I C
© 2013 SAP AG or an SAP affiliate company. All rights reserved.
SAP HANA Modeling Guide Information Views
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.
Tip
You can also create a restricted column based on the existing one by copying it and making the required changes. The copy paste option is available from the context menu of the calculated column. You can also use the CTRL + C and CTRL + V keyboard shortcuts. The copy paste functionality works only in the same editor, that is, if you copy a restricted column in one view editor you cannot paste it in another view editor.
Also, if you copy a restricted column in one editor and go to another view editor and again copy another object, the paste option in the previous editor is not available. The copy paste functionality for restricted columns only works in the Output panel
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 local attribute name is ignored and the shared attribute name is considered, therefore the joined local attribute is not shown on the Semantics node.
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: Assign Semantics to Attributes, Measures, and Calculated Columns
a) To indicate what an attribute or a calculated attribute of an analytic view represent, in the Columns panel toolbar of the Semantics node, choose Assign Semantics.
b) In the Semantics dialog, select the required Semantics Type.
SAP HANA Modeling Guide Information Views
P U B L I C
© 2013 SAP AG or an SAP affiliate company. All rights reserved. 55
1. If you select Amount with Currency Code as Semantic Type, select an attribute or calculated column that represents the currency code in the Currency dropdown.
Note
Attributes and calculated columns having semantic type as Currency Code are highlighted for Currency selection.
2. If you select Quantity with Unit of Measure as Semantic Type, select an attribute or calculated column that represents the currency code in the Unit dropdown.
Note
Attributes and calculated columns having semantic type as Unit of Measure are highlighted for Unit selection.
3. Choose OK.
The supported semantic types for attributes and calculated attributes are:
○ Amount with Currency Code
○ Quantity with Unit of Measure
○ Currency Code
○ Unit of Measure
○ Date
○ Date - Business Date From
○ Date - Business Date To
○ Geo Location - Longitude
○ Geo Location - Latitude
○ Geo Location - Carto Id
○ Geo Location - Normalized Name
a) To indicate what a measure or a calculated measure of an analytic view represent, in the Columns panel toolbar of the Semantics node, choose Assign Semantics.
1. If you select Amount with Currency Code as Semantic Type, in the Currency field, select a currency code.
Note
You can choose the currency from the system table TCURC or from an attribute of the view based on the currency type as Fixed or Column respectively.
2. If you select Quantity with Unit of Measure as Semantic Type, select a unit of measure in the Unit field.
Note
You can choose the currency from the system table T006 and T006A or from an attribute of the view based on the unit type as Fixed or Column respectively.
3. Choose OK.
7. 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:
56 P U B L I C
© 2013 SAP AG or an SAP affiliate company. All rights reserved.
SAP HANA Modeling Guide Information Views
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 run-time the value set for the Session Client property is considered to filter table data. The Session Client property is set while creating a user.
8. Optional Step: Assign Variable
You assign variables to an attribute of the view at design time to filter data based on the values you provide for the variable. The variable values are interpreted as WHERE clause of the SQL statement by the clients/
consumers like Data Preview, MDX, Advanced Analysis for Office or BO Explorer. At runtime, you can provide different values to the variable to view the corresponding set of attribute data.
9. If you want to parametrize currency conversion, calculated columns and Data Foundation filters, create input parameters.
10. Activate the view using one of the following options:
○ If you are in the Modeler perspective, do the following as required:
○ Save and Activate - to activate the current view and redeploy the affected objects if an active version of the affected object exists. Otherwise only current view gets activated.
○ Save and Activate All - to activate the current view along with the required and affected objects.
Note
You can also activate the current view by selecting the view in the SAP HANA Systems view and choosing Activate in the context menu. The activation triggers validation check for both the client side and the server side rules. If the object does not meet any validation check, the object activation fails.
○ If you are in the SAP HANA Development perspective, do the following:
1. In the Project Explorer view, select the required object.
2. In the context menu, select Team Activate .
Note
The activation triggers the validation check only for the server side rules. Hence, if there are any errors on the client side, they are skipped and the object activation goes through if no error found at the server side.
Note
If an active version of the affected objects exist, activating the current view redeploys the affected objects.
In the Modeler perspective, even if the affected object redeployment fails, the current view activation might go through. However, in the SAP HANA Development perspective, if any of the affected objects
redeployment fails, the current view activation also fails.
Note
Restriction
The behavior of analytic views with the new editor is as follows:
SAP HANA Modeling Guide Information Views
P U B L I C
© 2013 SAP AG or an SAP affiliate company. All rights reserved. 57
When an object (a table of an attribute view) is removed and added again in an attribute view in order to reflect the recently modified columns with its data type, it reflects the previous state of the columns. To overcome this issue, you can close and open the editor, and then add the modified object. For more information, see SAP Note 1783668 .
11. In the Change Tracking dialog, either create a new ID or select the existing change ID. For more information about Change Tracking, see the section SAP HANA Change Management in the chapter Implementing Lifecycle Management of the SAP HANA Developer Guide.
12. Choose Finish .
Related Information
Using Currency and Unit of Measure [page 100]
Activating Objects [page 86]
You activate objects available in your workspace to expose the objects for reporting and analysis.
Creating Input Parameters [page 95]
You use input parameters to define internal parameterization of the view. Input parameters used in the views enables you to obtain a desired functionality when the view is executed.
Using Temporal Joins [page 58]
A temporal join indicates the time interval mapping between the master data and the transaction data for which you want to fetch the records.
Description Mapping [page 89]
Description mapping helps you to associate an attribute with another attribute, which describes it in detail. For
Description mapping helps you to associate an attribute with another attribute, which describes it in detail. For