Advanced Filtering Support Using Structures¶
This page provides implementation information related to structures when the goal is to be able to use structures as advanced filters when create reports in IFS Business Reporter.
A structure defines a number of nodes to which leaves are attached. The leaves represent the information, e.g. basic data, that are defined in a hierarchy of nodes.
One example of a structure is the following where we have a geographical hierarchy with leaves that represent cities.
This is actually an accounting structure in one specific company where the code part B is defined to represent cost centers and the name of these cost centers are cities.
Many structures only consists of the following items and of course relations between them:
A structure also has levels but these are in many cases not used; they are only implicitly defined. In the example above the node ACME can be said to exist on the implicit level 1 and so on.
There are however also cases where the levels are explicitly defined, like in the case above for Accounting Structures in Financials. Each level in the structure is given an identity by the end-user. The main purpose is of course to simplify using accounting structures for analyzing purposes. So in this case a structure consists of the following items,
BI Services support both these cases and a client like IFS Business Reporter can take advantage of this functionality to create flexible, dynamic and advanced reports.
Advanced Filtering Support¶
Structures can be used as advanced filters, especially in a tool like IFS Business Reporter. Instead of having to manually specify a report criteria by listing a large number of single values, it i easier to group the data in a structure and then refer to the that structure. Assume that we want to filter only cost centers, cities, associated with SRI LANKA in the example structure. This can be done by using a filter where the following is specified:
- Parent values, like company and structure identity
Level identity = 'COUNTRY'
Node identity = 'SRI LANKA'
Even if the the level part seems unnecessary it is recommended to specify a filter criteria based on structures as exact as possible.
The criteria can be interpreted as if the structure only has two levels and all leaves below the COUNTRY level are moved up in the structure to this very level. The node criteria then cuts out the node SRI LANKA and all the leaves attached to this node (after having moved the leaves).
We will now focus on how to implement structure support for advanced filter criteria in Business Reporting & Analysis services and especially to support IFS Business Reporter.
Consider the following:
- Business Reporting & Analysis services only support star schema models.
There is no support in neither Business Reporting & Analysis services nor in IFS Business Reporter to handle filters (criteria) by addressing an arbitrary structure specific entity.
- To achieve this it is necessary to have a generic structure entity in IFS Cloud and that is not currently the case.
- Currently different product groups develop their own structures, based on component specific implementation and not on a generic structure definition.
The solution to the problem is the following:
- For each specific structure in IFS Cloud, like e.g. projects and accounting structures, special dimensions representing the leaf values have to be developed.
- The new dimensions must flatten out the structure information. This most likely means that there has to be functionality in the business logic for each case that handles this operation. If in the future there is a general structure entity, the work can be delegated to only one place.
- The new dimensions acts as add-on dimensions.
Assume we have the following star schema:
In this case we have two dimensions that in Financials are based on code parts, meaning that accounting structures can be defined with code part values as leaves.
If we take a look at
DIM_CODE_B it represents code part
B values. In one company we have created the accounting structure exemplified above, where the code part values are cities that acts as leaves in the structure.
DIM_CODE_B has the following values:
Each line has a unique identity created as a concatenation of the company and the code part value.
Since the code part value is a leaf in an accounting structure, this means that the leaf value can appear many times in a company but actually only once per structure. The first thing that must be taken care of is to flatten out the structure information so that all leaves are moved up in the structure. The goal is to make sure that each level shows all leaves that affects it.
Here we see the example structure flattened out. We can see that all code parts, leaves, are present on all levels. It would of course look differently if there were leaves attached to the upper nodes as well. We also see that in the flattened out structure the code part value, leaf value, appears many times.
Next step will be to create an add-on dimension that will add code part
B information to the base dimension (
DIM_CODE_B). The flattened data is use as a base for this dimension and it is named
The dimension has the following values:
IDcolumn is built exactly as for the
DIM_CODE_Bdimension, i.e. as a concatenation of company and the code part value.
IDis not unique. There a multiple lines with the same identity.
- The dimension has the same parent,
COMPANY, as the base dimension (
DIM_ANALYTIC_STRUCT_CODE_B has its own unique Metadata description. The important thing with the Metadata file is to provide the add-on information, i.e. adding Metadata to
The following items will be added:
In the Meta Data file the adding of items looks as follows for some of the mentioned items:
DIM_ADD_ON = DIM_CODE_B
DIMENSION = DIM_ANALYTIC_STRUCT_CODE_B
- Only two of the items are exemplified. Each item is added by
DIM_ADD_ON. The instructions are owned by
DIMENSION. The data is owned by
- For each item the attribute
Display_Folderhas the value
\Standard Accounting Structure Itemswhich means that in the IFS Business Reporter client, these items will appear in a sub folder. In IFS BR this looks as follows:
It is also important to provide nice List of values support for the added items. This is also done in the Metadata file for
List of values details are added to the items newly added to
Sorting instructions are also defined for the List of values items added to
The Meta Data file is named MetaData_GenledBIDimAnalyticStructCodeB.ins
By adding the structure items to a base dimension in the way described, it is possible to handle advanced filtering based on structures. Note that it is important to:
- Make sure that in the Data Mart version, the Materialized Views that store structure related data have indexes on most commonly used combination of columns.
- Makes sure that also the Online tables, representing the flattened out data, have indexes that can enhance queries.
- Specify the filter criteria as detailed as possible. E.g. even if it is enough to specify the structure and the node, also add the level as a criteria.
- Be aware of the fact that the added structure information does not have single unique rows. A dimension identity can appear on multiple rows.