How to create Attribute and Analytical Views in SAP HANA.
In my previous blog, I have explained about how to create tables and load data into those tables. Link to this is below.
In this blog, I will explain step by step process on how to create Attribute and Analytical Views.
Step 1: Create a Package in Modeler.
You can create either a structural package or a non-structural package. The two types of packages are
A structural package helps to organize the content in a logical package tree. It categorizes objects using the subpackages. This ensures that each object is associated with a particular package.
A Non Structural Package contains subpackages and content objects.
From the perspective toolbar select Modeler.
In the Modeler Perspective you can see a Quick launch view, click on Package and click create.
Enter the name and description of the Package. If you want to create a sub package then separate the package name with ‘DOT’. For ex: “a.b” is considered to be a subpackage of “a” and “a.b.c” is considered to be a subpackage of “a.b”.
- If you want to specify the entity with which a package and its objects are exported to a server, select the delivery unit from the drop-down list.
- From the Original Language drop-down list, select the same language for the package as your logon language.
- If you know the user responsible for maintaining the content in the package, select the user from the Person Responsible dropdown list.
Click OK button to create the Package.
From the Navigator pane, expand the content folder to see your package.
To specify the Package type follow the below steps.
a. In the Navigator pane, select the package.
b. In the Properties pane toolbar, choose Edit Package Details.
c. From the Structural drop-down list, choose the required option.
d. Choose OK.
The package system-local and all of its subpackages are not transportable. All other packages are transportable.You can view the package property Transportability to check whether or not the package can be transported.
All sub-packages of system-local.generated should be used for generated content (content that is not created by manual user interaction). You can view the package property Content type to check whether the package contains generated or edited content.
Now that we have created a Package we will create an Attribute and Analytical View.
Attributes – descriptive data (Known as Characteristics in SAP BW terminology). There are three types of Attributes, Simple Attributes, Calculated Attributes and Private Attributes.
Simple Attributes are individual non-measurable analytical elements that are derived from the data
- 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. For example, deriving weekday name from a calday.
Private Attributes are used in an analytic view to allow you to customize the behavior of an attribute for only that view. For example, if an analytic view or a calculation view includes an attribute view, it inherits the behavior of the attributes from the attribute view (set the parameter once and it is replicated in all views consuming it).
By contrast, if you create an analytic view for one specific use case in which you want a particular attribute to behave differently than it does in the attribute view to which it belongs, you can define it as a private attribute.
Measures – data that can be quantified and calculated (Known as Key Figures in SAP BW terminology)
We create Attribute views to model descriptive attribute data using attributes. (Attribute Views are known as Dimensions in SAP BW terminology)
Please follow the below steps to create an Attribute View.
Right click on the Package and select Attribute View from the context menu or from the Quick launch view select Attribute view and click create button.
From the New Attribute View window, enter name and description.
Attribute View Type –
Ø Select Standard to create a view with table attributes.
Ø Select time to create a view with time characteristics.
a. Choose Time.
b. In the Calendar Type field, select the required option. If the calendar type is Fiscal, Select a variant table, and a fiscal variant. If the calendar type is Gregorian, select the granularity for the data.
Ø To derive a view from an existing view, select Derived option– in this case, you cannot modify the derived view.
o Choose Derived and select the required Attribute view.
Ø To copy a view from an existing view, select Copy From option– in this case, you can modify the copied view.
o Choose Copy From and select the required Attribute view.
Add the attribute table and click Finish. You can add more than one table to the Attribute view. For ex. You can also select Material Text table and join these tables.
Expand the Package to see the Attribute View folder. Expand the Attribute view folder to see the Attribute View we just created.
Double click on the Attribute view we just created to open its definition.
Right click on the Attribute view to select the objects for output. You can either select an object as Key Attribute or just Attribute. If you define an object as Key Attribute then it acts as primary key.
Note: At least one Key Attribute is Mandatory. You can select any number of non-key attributes.
Select Apply Filter to define static filter values. This can be based on any table column and column does not need to be selected for output.
Once you have finished defining the Attribute view click on the Save and Validate button to validate the definition and click Activate.
In the Properties View you can see the job log in the Job Log tab
Attribute Properties: –
By default only Key Attributes are shown in the Excel. To show an attribute in the excel output select the Attribute from the Output folder and In the Properties tab change the Hierarchy Active option to True.
We have successfully created an Attribute view. We will now create and Analytical View.
Analytical View: –
An Analytical View can be regarded as a “Cube” (SAP BW terminology)
Ø Multi dimensional reporting model.
Ø Fact table (Data foundation) joined against modeled dimensions (Attribute Views)
Analytical Views do not store the data.
Ø Data is read from the joined data base tables.
Ø Joins and calculated measures are evaluated at run time.
Ø Master data for MDX/BICS are stored in system tables.
To create Analytical view follow the below steps.
In the Navigator View right click on the Package and select Analytical View or from the Quick launch view select Attribute view and click create button.
In the New Analytical View window, give a technical name and description.
Ø Select Create New option to create a new Analytical View.
Ø Select Copy From option to choose existing view as template.
Ø If you want to perform currency conversion for measures, select the required schema from the Schema for Conversion.
Click Next to select the tables.
Table Selection Wizard
Ø Same as with Attribute Views (search and Select)
Ø Can only select measures from one table (transactional data)
Ø Can select attributes from several tables (must be joinable)
Ø It is also possible to select the tables later via single table selection wizard or via drag and drop from the Navigator tree (same as Attribute Views)
Click Next to select the Dimensional tables (Attribute Views)
Ø Select any suitable attribute view from any package.
Ø Analytical View and Attribute View do not need to be in the same package.
Ø It is also possible to add Attribute views later visa drag and drop from the navigator tree.
Ø You can only drop into the “Logical View” tab of the view editor.
Once you click Finish the Analytical View is created.
Expand the Package to see the Analytical View folder. Expand the Analytical view folder to see the Analytical View we just created.
Double click on it to open the view in the editor.
Tab Data Foundation (It is circled at the bottom of the above image)
Ø Create the data foundation (“Fact Table”)
Ø Select Attributes and measures from tables to define the data foundation.
Tab Logical View (It is circled at the bottom of the above image)
Ø This is where you drag attribute views into the editor.
Right click on the view to Select Attributes and measures from tables to define the data foundation.
Ø Include table field in the output structure. From the context menu, choose Add as Attribute.
Ø Add measures based on which you want to view data from the fact table for analysis purposes. From the context menu, choose Add as Measure.
Ø Include table field in the output structure and specify a filter condition based on which system must display data for this field in the output.
o From the context menu, choose Apply Filter.
o Select the required operator and enter the values.
Ø Hide attributes and measures that are not required for client consumption.
o Select the required attribute or measure.
o In the properties pane, assign value True to the Hidden property.
Note: You can choose to hide only private attributes of an analytic view. Must have at least one attribute and one measure.
Once you have defined the Data Foundation you now have to define Logical View.
To create a Logical View follow the below steps.
Click on the Logical View tab. This will open the Logical View editor.
In the above steps we have created Data Foundation (Fact Table), now we have to link the Dimensional tables to the fact table.
- Drag the Attribute Views from the Attribute View folder and drop them in the Logical view editor.
- Now using your mouse you can drag the Key Attribute from Attribute View to the Key Attribute in the Fact Table. This will create a join between Attribute View and Fact Table.
Once you are done with joining the tables, click on Save and validate to Validate the Analytical View, if there are no errors you can click on Activate button to Activate your Analytical View.
This Brings to an end on how to create an Attribute and Analytical View.