General Development Concepts
IFS Business Reporting & Analysis services development means creating new Information Sources. An Information Source is a Star Schema consisting of one Fact, i.e. a transaction source, and one or more connected Dimensions. It is important to understand the basic characteristics of the Star Schema model to be able to develop Information Sources.
IFS Business Reporting & Analysis services supports execution with respect to two access types; Online or Data Mart. Even if the Information Source model is the same for both cases there are some important differences when it comes to implementation.
BI Services acts as a service provider between IFS Cloud and advanced clients like e.g. IFS Business Reporter
IFS Business Reporter presents Information Source related data by retrieving Meta Data from IFS Business Reporting & Analysis services. A report execution is made with respect to data access type, Online or Data Mart, and the data is gathered from referenced Information Sources.
Star Schema Model
An Information Source is a Star Schema consisting of one Fact and one or more Dimensions.
The Fact represents transactions, measurable data. The Dimensions normally represent basic data entities.
Facts and Dimensions are entities. The public data for such an entity is represented by a view, either a dimension view or a fact view.
In a Star Schema, the normal case is that one column in the Fact view is connected to one column in the Dimension view.
However to be able to support both Online and Data Mart execution, there must be other possibilities to define a connection between a Fact and a Dimension. The reason is mainly performance.
The general principle is:
- Data Mart The unique row identity column in a dimension, normally named ID, is connected to a column in the Fact named <dimension_name>_ID that keeps the dimension row identity. The big advantage with the Data Mart solution is that the data is stored in Materialized Views, meaning that all identities, even if derived, can be stored in single columns
- Online For Online execution it is normally not a good idea to use the default star schema join mechanism. The reason is the unique identity of a dimension row, in many cases, has to be derived from more than one physical column, leading to join of "virtual" non-indexed columns.
The solution is to define specific join information for each Fact-Dimension connection in the Meta Data that describes the Fact (Information Source centre). The join is in this case specified as a join between one or many columns for each connection, i.e. natural key joins.
Some Fact characteristics:
- The Fact represents the Information Source center
- Normally the Fact is associated with one transaction source.
The Fact has the following main items:
- Measure Items Represents measurable data like amounts and quantities. Make sure that a Fact has at least one measurable item
- Light Items Represents non-measurable data, i.e. extra information available in the information source
To be able to build a Star Schema that adapts to the IFS Business Reporting & Analysis services concepts, the light items are divided into sub categories:
- Visible Light Items Refers to all light items that are supposed to be visible or public
- Key Column Items Refers to all key columns in the original sources that defines the Fact It is recommended that these columns are represented by non-visible light items
- Parent Key Items Refers to items that represents the natural parent keys, e.g. company for Financial sources. In most cases defined as non-visible light items. The items are necessary for List of values functionality and can also be opened up as visible performance items if necessary.
- Dimension ID Items Each connection to a dimension is represented by one non-visible light item. The item refers to a view column that has the unique identifier of one row in the corresponding dimension. The items are used when building a join statement, for Data Mart access, that connects the fact with each referred dimension
- Join Items - Joiners For a Data Mart solution the Dimension ID Items are sufficient as join items between a fact and its dimensions. However for an Online solution this is not good enough. For Online it is necessary to be able to refer to the natural join columns, more or less the key columns in the main source of each dimension. These columns must also be available as items in the fact and are normally defined as non-visible light items. The join items can be opened up as visible performance items if necessary.
The Fact must consider security so that the resulting Information Source can provide the same security as similar sources used elsewhere in IFS Applications
- In some cases it is hard to distinguish a Fact from a Dimension. If this is the case, one solution is to create both a Dimension and a Fact from the same source. It is however not recommended to create a Dimension from a transaction source, since it might lead to performance problems during report execution or when performing List of Values.
Some Dimension characteristics:
- A Dimension normally represents basic data in the Star Schema.
- Dimensions will enrich the Star Schema and provides the possibility to categorize the transaction information in different ways.
- The Dimension should contain as many interesting items as possible that are related to the Dimension. The information can come from many sources in the database, meaning that a de-normalization is done. The reason for having many items is to provide more possibilities. In a client like IFS Business Reporter the design and end-user experience benefits from having many possibilities when it comes to viewing/presenting data.
Items in a dimension:
- Unique Identity This item represents a unique row/record identifier and the suggested name is ID. The ID is created as a concatenation of the natural keys, e.g. parent keys and keys. The ID should not be visible
- Parent Items Refers to items that represents the natural parent values, e.g. company for many of the Financial dimensions. These items are defined as non-visible items. It is important that the corresponding parent dimensions are available. E.g. the Account dimension has company as a natural parent item, leading to that there should be a Company dimension where the company identity is visible.
- Code Item There should be one item that represents the natural end-user value in a Dimensions. This item is normally the natural key. The items is called the Code Item and the suggested name is CODE. The CODE is in the metadata also defined as the CodeKey. E.g. in the Dimension Account the code is the account code, in the Project dimensions the code is the project identity. It can also be so that the code consists of more then one value. One example is the Accounting Period dimension, where the CODE is defined as a concatenation of the accounting year and the accounting period. The reason is that there is no parent dimension representing Accounting Year, only the main parent Company is available. This is a visible item.
- Attribute Items Refers to all other items in the dimension and they are normally visible.
- Standard Most part of the dimensions a re created as Standard dimensions. This means that they can be used in a Star Schema, connected to a Fact.
- Supportive A Supportive dimension looks nearly the same as a standard dimension, with the biggest difference that is cannot be connected to Fact in a Star Schema. The main purpose is to serve as a List of values source, e.g. related to Info Service functionality.
- Inherited An Inherited dimension can be used to create a copy of a dimension using a different Dimension Id with automated duplication of the all items belonging to the the source dimension.
- The information related to a Dimension might in IFS Cloud be available in different components. Generally this is handled in the following way:
Define unique dimensions in each component, typed as Standard dimensions.
- One Dimension can act as a so called Add-On Dimension, a dimension that provides information to another dimension. When a Dimension is defined it can either get information from a Dimension, typically a static dependent parent Dimension, or it can put information to its parent Dimension.
- Normally only one of these Dimensions is connected to a Fact. One example in the Dimension Account in Financials that will get information from dimensions representing Accounting Attributes and Accounting Structures. The Account dimension is connected to the facts, not the ones representing extra information (Accounting Attributes and Structures).
An other way is to skip getting or putting information and instead make sure that the separate dimensions are connected to the target facts.
- When a Star schema is defined, the Fact refers to one or more Dimensions. All the Dimensions might not be available at the time when the Fact is defined, since the referred Dimensions might belong to other components that are not yet available. This will lead to that an inactive reference is defined. An invalid reference will be active/valid in all Information Sources when the Dimension is defined/installed.
Some general recommendations for Information Source development,
- In general a dimension should not be considered as a one-to-one relation to a LU entity. Instead a dimension should contain as many attributes as possible that directly or indirectly are related to the dimension entity. This means that a dimension might represent information that can be found on several LU entities.
- Avoid creating dimensions associated with referenced basic data in transaction tables.
- One example is a Financials specific transaction table that almost always contains an account, since account is a mandatory attribute in the code string.
- The company and the account can be used to refer to the LU Account.
- The LU Account has other references, e.g. to LU entities AccountGroup and AccountType.
- It is possible to create a dimension representing the entity AccountGroup and another dimension representing the entity AccountType. However this leads to the following:
- The number of dimensions will increase and actually be too many. There is nothing that says that one dimension entity is associated with one LU entity.
- The transaction tables in Financials will not contain any value for Account Group and Account Type but the value can be retrieved by performing a join or by using a function call. This leads to increased complexity of Fact specific views and in many cases reduced read performance.
- The correct thing to do would be to only have an Account dimension and instead add attributes from LU entities AccountGroup and AccountType to the Account dimension.
- Always develop dimension support for both Data Mart and Online data access types. The reason is that dimensions are general entities that can be used by Information Sources in different product domains and it is not known when developing the dimensions if these Information Sources will support execution based on data access type Online or Data Mart.
- The Data Mart version of a dimension should consider NULL identities by generating special NULL identifiers.
- The Online version of a dimension should normally NOT consider NULL identities. If a dimension identity is available or not in the fact part of an Information Source, will in the Online case be handled by defining special join information based on the natural key items in the dimension and the joiners in the fact.
About Information Sources
- Try to add useful measure items. Think about how these measures could improve the user experience when using a e.g. Business Reporter report.
- Always consider performance. Try to avoid function calls, since these function calls will in almost all cases reduce the performance significantly. Instead use ordinary SQL as e.g. joins and SUB selects, consider snapshot functionality, modified Business Logic that stores calculated/derived measure items to avoid later calculations.
- A Fact entity does not necessarily have a one-to-one relation to a LU entity. It is not uncommon that a Fact retrieves data from a combination of tables and views.
- One transaction table does not necessarily lead to creation of one related Information Source. Look at the Use Cases and try to simplify. It might be more natural to create two or even more individual Information Sources. In most cases it will be possible to combine data from several Information Sources rather easily in e.g. Business Reporter.
- Always check performance of the developed Fact views.
- Always find out the most common usage scenario and develop support according to this, i.e. Online and/or Data Mart support.
- If Data Mart access support is developed; It extremely important to find out if the refresh of the Materialized Views representing the fact part are really refreshable with acceptable performance in the customer environment. Also find out if it is necessary to refresh all transactions or if it is possible to introduce a refresh criteria to limit the number of transactions.
- Consider creating grouped Information Sources in the Data Mart case. One example can be to create a Materialized View that is based on grouped information from other Materialized Views.
- Consider developing support for incremental load. This means that Materialized Views will be replaced by ordinary Oracle tables acting as snapshot tables.