Fact Entity Concept
A Fact entity normally represents a transactional entity that acts as the star in a Star Schema model. The identity of the Fact entity will be equal to the Information Source identity.
A Fact can be built to represent information in one or more sources. This means that it is not given that a transactional LU is represented by a Fact entity. It is rather so that a Fact is a high level transactional entity that supplies useful measurable information within a given product area. To achieve this it is not uncommon that a Fact entity refers to many sources, in IFS Applications represented by master, detail and referenced LUs.
A fact model will have the following sections
There are three types of fact attributes
Represents the keys of the referenced sources. A key attribute is really a light attribute but is in the Dev Tool typed in order to facilitate some functionality and also to clarify.
Represents attributes that are measureable as e.g. amounts.
All non-measure attributes are light attributes. There are some sub categories of light attributes:
Attributes that refer to the original keys in the referenced sources.
General Light Attributes
Attributes in referenced sources that are not measures but supplies information about a transaction, e.g. a status value, a note etc.
Attributes that represent a unique identifier of a referenced dimension.
Attributes that represent that natural key columns of a referenced dimension.
There are following data types for fact attributes
Following are the code generation properties available for fact attributes. They can be used to alter default properties.
- DbRefPackageName (only for enumerations)
A fact is linked to one or dimensions by means of relationships. There are two types of relationships:
In the LU model a parent entity represents a parent in a master/detail relationship. This is not the case for a Fact. There is no concept of parent Facts. However, mainly to support List of Values functionality, it is possible to define one or more dimension attributes that act as parent attributes. The parent attributes supply a logical filter in IFS Business Analytics, if a user performs List of Values on a fact attribute. Company and Site are two examples of parent attributes that can be used to reduce the value context to only one specific company/site.
A reference relationship is always used to define a connection between the Fact and a Dimension. The reference is defined differently for Data Mart access than for Online access.
The Fact entity and its access alternatives can be visualized as follows:
An Information Source normally consists of one Fact and at least one Dimension. It is however not a requirement that Dimensions are referenced. An Information Source is based on a Star Schema model, meaning that there is one Fact, acting as the star, and a set of dimensions, acting as the planets. In this model a Fact and a Dimension is connected using a unique identity attribute (column). The join is exemplified by the following figure:
The recommendation for a Star Schema join is to use technical keys, e.g. a number, as the identity. For an Information Source (IS) there are however a few things to consider:
- The IS may support both Data Mart and Online access.
- For Data Mart access the fact-dimension join is handled more or less as the standard Star Schema join.
- For Online access it is not possible to use the Star Schema join. Instead a natural join principle should be used
- Fact/dimension sources tables do normally not contain any technical keys, leading to that an identity is typically defined as a string where the natural keys are concatenated.
- It is always necessary to define the natural key columns in the Fact that represent the unique identity for a dimension reference
Assume that a Fact has a reference to the dimension Account (DIM_ACCOUNT). Both Data Mart and Online access is supported. The Data Mart join should by default be defined as a Star Schema join.
In the Online access case it is almost always necessary to use natural join:
Start by defining the basic default information. The Fact should be connected to the dimension Account. The following should be specified:
- The dimension identity of the referenced dimension
- The attribute name in the Fact that represents the dimension identity
- The attributes in the Fact that represent the natural keys. NOTE that this information is necessary to support BI Access view generation.
The example shows that DimAccountId is the attribute that represents the dimension identity. The associated column is by default used in the Data Mart case to connect with the dimension column representing the ID (identity). The DimAccountId is also based on the natural key attributes CompanyKey and Account.
If Online access is supported then it is necessary to specify how the join between the Fact and the Dimension is handled.
The special keyword in this case defines the natural join between the Fact and the Dimension by explicitly specifying the attributes relationships. The access type is online and the join is exact (true) meaning that all transactions in the Fact have a value for both company and account.
It is also possible to define a special join condition for the Data Mart access if the default join is not applicable.
There is a big difference when comparing a Fact entity with a LU entity. Attributes in a LU entity might have references to another LU. These references are used to check referential consistency, to support List of Values etc. For Fact attributes there are no references to other entities except for Enumeration attributes. The basic idea is to handle enumerations in the following way:
- The attribute representing the db value is defined in the model file as an Enumeration type.
- The Materialized View definition (Data Mart access) only contains the db value column
- The Data Mart access view contains both the db and the client values.
- The Online access view contains both the db and the client values.
- The metadata file contains both the db and the client attribute.
The Fact attribute named SimulationVoucher is typed as an Enumeration. The enumeration refers to the Enumeration LU SimulationVoucher using the syntax Enumeration<Simulation Voucher>. The Materialized View will only contain the column Simulation_Voucher.
The Data Mart view contains the db value column Simulation_Voucher and also the column Simulation_Voucher_Desc representing the client value.
The Online view contains the db value column Simulation_Voucher and also the column Simulation_Voucher_Desc representing the client value.
Finally the metadata (INS) file will contain two attributes, SIMULATION_VOUCHER and SIMULATION_VOUCHER_DESC
The implementation means that each enumeration value in the fact source is available along with the corresponding description in the current user language. The description can typically be used when creating reports in order to display the client value instead of the db value. The db value can be used in a filter criterion, making it language independent.
A Fact supports the following navigation concepts:
- Zoom In
- Drill Down
- URL Navigation
These navigation possibilities are mainly supported by IFS Business Analytics.
Zoom In means the possibility to display the detail transactions in an Information Source that are associated with a specific aggregated cell value based on the same Information Source.
Drill Down means the possibility to display detail transactions in a drill down (detail) Information Source that are associated with a specific aggregated cell value based on an aggregated (Master) Information Source.
URL Navigation means the possibility to navigate from a Zoom In or Drill Down sheet in IFS Business Analytics to a IFS Applications client. A Fact can have many URLs defined. For each URL the target form/window in IFS Applications is supplied as well as the attributes that have to be available to enable the URL related RMB option in the IFS Business Analytics sheets.
List of Values
The List of Values handling for a Fact differs from the handling in a typical transactional LU. In the LU case, an attribute may have a reference to another LU that can act as the source when performing List of Values. This means that information will be retrieved from another LU than the transactional LU. In the Fact case, all List of Values requests are performed against the Fact source itself, since this is not a referential model. It is by default possible to perform List of Values on any visible Fact attribute in IFS Business Analytics. Assume that we are using a project specific Fact. If a List of Values request is made to list the available values for attribute ResourceId (column RESOURCE_ID), this means that a select will be made in the Fact source for that specific column.
In the example we will retrieve the ALL the marked rows. NOTE that it is not a very good idea to perform List of Values requests on Fact attributes since the Fact source might contain a large number of transactions and to find all values a scan through the complete source must be made. By default the generated metadata file will be generated such that:
- A DISTINCT SELECT is NOT performed
- The number of values to be retrieved is limited to 100 values
The default behavior can be overridden by codegenproperties.
With Write Back we mean the possibility to write Information Source specific information from a client as IFS Business Analytics client to IFS Applications. Two types, categories, of write back are supported:
- General Write Back
For this category the main part of the Write Back logic is handled by the IFS BI Services framework. A PL/SQL method must be written that takes care of a standard attribute string and then performs necessary actions as validation, insert/update.
- Advanced Write Back
For this category the main part of the processing logic has to be handled by a PL/SQL method that considers component specific needs as well as sending status responses back to IFS BI Services framework.
The model must for write back specify the category that is supported as well as defining write back attributes and properties.