HIERARCHIES: Hierarchies are used to structure and define the relationships among attributes of attribute views used for business analysis.HANA supports two types of hierarchy.
This discussion will helps us to create a Level Hierarchy or a Parent Child Hierarchy in order to structure and define relationship between view attributes.
You can set Aggregate All Nodes to true if there is a value posted on the aggregate node and you want to compute that value while aggregating data.
LEVEL HIERARCHIES:
Option | Description |
---|---|
Root Node | Treat them as root nodes |
Error | Stop processing and show an error |
Ignore | Ignore them |
Step Parent | Put them under a step-parent node Note This enables you to create a text node and place all the orphaned nodes under this node. |
Let us take a scenario to explain Level Hierarchies. In eFashion package we created an Attribute View for Article_Lookup table below is the table fields
Here we can define a hierarchy by using field CATEGORY & ARTICLE_LABEL.
CONSUMED HEIRARCHY USING MDX PROVIDER:
PARENT/CHILD HIERARCHY:
Option | Description |
---|---|
Root Node | Treat them as root nodes |
Error | Stop processing and show an error |
Ignore | Ignore them |
Step Parent | Put them under a step-parent node Note This enables you to create a text node and place all the orphaned nodes under this node. |
Note: The hierarchies belonging to an attribute view are available in an analytic view that reuses the attribute view, in read-only mode. However, the hierarchies belonging to an attribute view are not available in a calculation view that reuses the attribute view.
Let us take a scenario of Parent –Child relationship in ITEM_MASTER table. Here for Item ID 2 and 3 the parent ID is 1 i.e. to say item id 2 & 3 falls under CONSUMABLES. Similarly Item 5 to 8 fall under STEEL category. Parent/Child Hierarchy type can be used to define Hierarchy in this scenario.
ITEM_ID | PARENT_ITEM_ID | ITEM_DESCRIPTION |
---|---|---|
1 | CONSUMABLES | |
2 | 1 | Cutting Disc 4” |
3 | 1 | Grinding Disc 4” |
4 | STEEL | |
5 | 4 | Plate 10mm |
6 | 4 | Beam |
7 | 4 | Angle |
8 | 4 | Channel |
RESTRICTED COLUMN:
Restricted Columns are used to filter the value based on the user defined rules on the attribute values.
Restricted Column dialog helps to create a restricted column and filter its value based on the columns that you select in the Restrictions view. In the Column dropdown, you can select a column of type measure for which you want to apply filter. In the Restrictions view, to apply a filter, you need to choose Add Restriction. Select a Column, an Operator, and enter a value. For example, you can create a restricted column to view the Revenue of a particular country, where Revenue is the measure and Country is the attribute having a list of countries. If you have added many restrictions, and do not want to apply all of them but want to retain them, deselect the Include checkbox.
Creating a Restricted Column
You use this procedure to create a restricted column to filter the value based on the user-defined restrictions for the attribute values.
For example, to filter the sales of a product based on a category you can create a restricted column Sales based on a measure Total Sales amount, and attribute category where you can restrict the value for category.
1. In the Output panel of the Logical Join, right-click Restricted Columns, and choose New.
2. Enter a name and description for the restricted column.
3. From the Column dropdown, select a measure.
4. In the Restrictions view, choose Add Restriction.
1. In the Column dropdown, select column to define filter.
2. Select the required operator.
3. Enter the filter value.
4. If you want to remove a particular filter on the column, deselect its corresponding Include checkbox.
5. Choose OK.
Data preview shows Sales amount for only restricted attributes i.e, category "Jewelry" & Category "Pants".
Lets review below senario where in "Jewelry" category has been excluded.
Here you can see sales amount of all category except category "Jewelry".
CALCULATED COLUMN:Calculated columns are used to derive some meaningful information in the form of columns, from existing columns.
Calculated Column dialog helps you to derive a calculated column of type attribute or measure based on the existing columns, calculated columns,restricted columns, and input. You can write the formula in the Expression panel or assemble it making use of the available Elements, Operators and Functions.
You can specify how to aggregate row data for calculated column of type measure using the Calculate Before Aggregation checkbox and specifying Aggregation Type. If you select the 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. After writing the expression, you can validate it using Validate.
You can also associate a calculated column with Currency or Unit of Measure using the Advanced tab page.
CREATING CALCULATED COLUMN
You use calculated columns to derive some meaningful information, in the form of columns, from existing columns, calculated columns, restricted columns and input parameters.
For example:
Procedure
1. In the Output panel of the Logical Join,right-click Calculated Columns, and choose New.
2. Enter a name and description for the calculated column.
3. Select the data type, and enter length and scale for the calculated column.
4. Select the Column Type to specify the calculated column as attribute or measure.
5. In case of measure column type, 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 aggregatoin is not set, then it will be considered as attribute.
6. In the expression editor enter the expression or assemble it using the menus in the below window.
7. 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.
8. Choose OK.
INPUT PARAMETER:
You use this procedure to allow you to provide input for the parameters within stored procedures, to obtain a desired functionality when the procedure is executed.
In an Analytic View you use input parameters as placeholders during currency conversion, formulas like calculated columns where the calculation of the formula is based on the input you provide at runtime during data preview. Input parameters are not used for filtering attribute data in Analytic View that is achieved using variables.
In calculation Views you can use input parameter to during currency conversion, calculated measures, input parameters of the script node and to filter data as well.
You can apply input parameters in analytic and calculation views. If a calculation view is created using an analytic view with input parameters, those input parameters are also available in the calculation view but you cannot edit them.
The following types of input parameters are supported:
Type | Description |
Attribute Value/ | Use this when the value |
Currency (available | Use this when the |
Date (available in Calculation | Use this when the |
Static List | Use this when the |
Derived From Table | Use this when the |
Empty | Use this when the |
Direct Type | To specify an input |
Each type of input parameter can be either mandatory or non-mandatory. For a mandatory input parameter, it is necessary to provide a value at runtime. However, for a non-mandatory input parameter, if you have not specified a value at runtime,the data for the column where the input parameter is used remains blank.
Note:You can check whether an input parameter is mandatory or not from the properties of the input parameter in the properties pane.
Measures used in analytic views and calculation views can be defined as amount or weight in the analytical space using currency and unit ofmeasure. You can also perform currency conversion and unit of measure conversion.
For example, you need to generate a sales report for a region in a particular currency, and you have sales data in database tables ina different currency. You can create an analytic view by selecting the table column containing the sales data in this other currency as a measure, and perform currency conversion. Once you activate the view, you can use it to generate reports.
Similarly, if you need to convert the unit of a measure from cubic meters to barrels to perform some volume calculation and generate reports, you can convert quantity with unit of measure.
To simplify the process of conversion, system provides the following:
Currency conversion is performed based on the source currency, target currency, exchange rate, and date of conversion. You can also select currency from the attribute data used in the view.
Similarly, quantity unit conversion is performed based on the source unit,target unit, and conversion factor.
You can also select the target currency or unit of measure at query runtime using input parameters. If you use this approach then, you have to first create an input parameter with the desired currency/unit specified, and use the same input parameter as target in the conversion dialog.
Note
Currency conversion is enabled for analytic views and base measures of calculation views.
Prerequisites
You have imported tables T006, T006D, and T006A for Unit of Measure.
You have imported TCURC, TCURF, TCURN, TCURR, TCURT, TCURV, TCURW, and TCURX for currency.
Procedure
a. In the Measure Type dropdown list, select the value Amount with Currency.
b.In the Currency Dialog,select the required Type as follows:
Type | Purpose |
---|---|
Fixed | To select currency from the currency table TCURC. |
Attribute | To select currency from one of the attributes used in the view. |
c. Select the required value, and choose OK.
d. If you want to convert the value to another currency, choose Enable for Conversion.
i.To select the source currency, choose Currency.
ii..Select the target currency.
Note: For currency conversion, in addition to the types Fixed and Attribute, you can select Input Parameter to provide target currency at runtime. If you select an input parameter for specifying target currency and deselect Enable for Conversion checkbox, the target currency field gets clear because input parameters can be used only for currency conversion.
iii.To specify exchange rate type, in the Exchange Rate Types dialog, select the Type as follows:
Type | Purpose |
Fixed | To select exchange rate from the currency table TCURW. |
Input Parameter | To provide exchange rate input at runtime as input parameter. |
iv.To specify the date for currency conversion, in the Conversion Date dialog, select the Type as follows:
Type | Purpose |
Fixed | To select conversion date from the calendar. |
Attribute | To select conversion date from one of the attributes used in the view. |
Input Parameter | To provide conversion date input at runtime as input parameter. |
v.To specify the schema where currency tables are located for conversion, in the Schema for currency conversion, select the required schema.
vi.To specify the client for which the conversion rates to be looked for, in the Client for currency conversion, select the required option.
e. From the dropdown list, select the required value that is used populate data if the conversion fails:
Option | Result |
Fail | In data preview, the system displays an error for conversion failure. |
Set to NULL | In data preview, the value for the corresponding records is set to NULL. |
Ignore | In data preview, you view the unconverted value for the corresponding records. |
4.If you want to associate a measure with a unit of measure other than currency, perform the following substeps:
a. Select the value Quantity with Unit of Measure in the Measure Type drop-down list.
b. In the Quantity Units dialog, select the required Type as follows:
Type | Purpose |
Fixed | To select a unit of measure from the unit tables T006 and T006A. |
Attribute | To select a unit of measure from one of the attributes used in the view. |
c. Select the required value, and choose OK.
5.Choose OK.
Note You can associate Currency or Unit of Measure with a calculated measure, and perform currency conversion for a calculated measure by editing it
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
5 | |
5 | |
5 | |
4 | |
4 | |
4 | |
4 | |
4 | |
3 | |
3 |