BI Services, IFS Business Intelligence Services, provides a Star Schema based framework for IFS Applications. It consists of a runtime framework that handles execution based on Online data or data from a built-in Data Mart. Other parts are administration and configuration features via Solution Manager and metadata translation support.
The solution extends the generic Star Schema to include IFS specific requirements such as URL navigation, List of Values etc. resulting in an Information Source. An Information Source is an extension of the Star Schema concept in Data Warehousing technology which encapsulates IFS specific information in such a way so that the users can easily visualize underlying Data Sources without the necessity for implementation details. The Star Schema model is used to support:
An IFS Information Source can support data access via Online data as well as via a Data Mart repository built into the IFS Applications database. The IFS Data Warehouse solution uses so called BI Access Views that will use either the Online or the Data Mart version of an Information Source.
It is possible to build many clients that uses this common runtime, BI Services. IFS Business Analytics is one example. This client report tool extends MS Excel from a desktop tool to a fully fledged enterprise reporting tool by exposing IFS Base Server through BI Services.
To support the IFS specific needs the general Star Schema model has been extended by creating Star Schema based Information Sources. These Information Sources represent functional areas and are built to contain information that facilitates creating a report via IFS Business Analytics as well as a building block for transfer of data to an external Data Warehouse.
Using IFS Business Analytics it is possible to choose if Online or Data Mart access should be used. For reports running in batch, the Data Mart version can be an obvious advantage since that storage can easily be enhanced by adding indexes or performance specific data aggregations, but also due to that the end user can wait until next day before using the report. In many cases however it is necessary to use Online information.
The IFS Data Mart is a scaled down version of Data Warehouse and it is residing in the IFS Applications database. The framework supports true incremental load but it is necessary to adapt each an every Information Source.
Having a separate data layer instead of using the same base tables from IFS Base server provides the ideal means of tuning performance of the Data Mart without affecting the standard IFS Applications. Necessary means to configure and administrate this data layer are provided via BI Services.
A Data Warehouse is a database that is geared towards analysis rather than transaction processing and a Data Mart is a scaled down version of Data Warehouse in which only a selected portion of the Master Database is encapsulated. Data Mart is ideal to be the Data Layer for a Decision Support System / Reporting Clients.
A Star Schema is one of the model schemas in Data Warehousing and
is probably the simplest. In this schema the Measure source, also called Fact,
is placed in the
middle and several Dimensions are connected to the measure forming the Star.
In a Star Schema the key is to have de-normalized
Dimensions so that the join with the Fact
can be done with one condition (fact.dim1_id = dim1.id) resulting
in rather straightforward SQL statement and many times good performance. Another
model schema in Data Warehousing is the Snowflake in which nested Dimensions are used,.
The Fact usually represents a transaction table or entity in which data varies quite frequently with time, for example Customer Order Line, General Ledger Transaction etc. A Dimension in most cases represents a basic data entity in which data does not vary that often, for example Company, Account etc. The Fact and the Dimensions are joined using the key columns in the Dimension and the associated fields in the Fact.
One way to represent the built-in Data Mart in IFS Applications is to use Materialized Views. Materialized Views are snapshot tables that will store fetched and processed data taken from IFS Base Server tables and can be tuned for higher performance without affecting the Base Server.
A Materialized View (MV) is neither a table nor a view. When an MV is created upon some base table, it extracts the data from base table and stores data in itself. Thus, it is possible to create indexes and tune the MV without affecting the base table.
As the MV stores data, it is required to perform the synchronization with the base table. It can be either performed on DEMAND, meaning that a refresh request is made, or on COMMIT meaning that whenever the base table gets updated the MV will also be updated.
Note: The COMMIT option is not recommended since it may affect the IFS Base server performance
The created Materialized Views are used as sources for Data Mart specific Fact and Dimension access views. The created views are then registered in the BI Services framework along with required additional information such as dimension connections, parent details, zoom-in / drill-down details, URL navigation details etc.
Another way to represent the built-in Data Mart in IFS Applications is to develop support for incremental load. This means that ordinary tables are acting as snapshot tables and that a dedicated framework is used to find incremental changes and then only transfers necessary transactions to the snapshot tables.
Fact and Dimensions normally also support On Line access. This access type is the obvious starting point when the requirement is to get quickly started with reporting via e.g. IFS Business Analytics. Accessing data On Line is also important in cases where a report must be executed many times each day and there is not time to wait for a refresh of the Data Mart. On Line and Data Mart views represent the same information. On Line views are also registered in BI Services along with other related metadata.
The generic Star Schema model is a good starting point for BI Services. But it does not fulfill all the requirements. On top of the Star Schema model some more IFS Specific information is added resulting in an entity called Information Source. An Information Source contains information about:
The BI Services runtime framework is implemented so that requests based on Information Source elements are analyzed, a SQL query is built and executed, the result is formatted and it is sent back to the client. BI Services is part of IFS runtime and thus get installed by default so that each product area can deploy / develop / configure / administrate their own Information Sources.
The selected Data Warehousing model for BI Services is the Star Schema. The star Schema could be implemented using either Online or Data Mart version. Depending on the requirements of the different product area either one of the versions or both could be implemented.
When implementing Data Mart solution, Materialized Views or incremental load specific tables are created to act as sources for both Fact views and Dimension views. Fact and Dimension views are registered in the BI Services framework along with required additional information such as dimension connections, parent details, zoom-in / drill-down details, URL navigation details etc.
Each IFS product area, like IFS Financials, provides Information Sources for you to work with to easily visualize the underlying Data Sources, without requiring you to know any details of the implementation. It is possible to customize these using the Information Source feature provided in IFS Solution Manager.
Configuration and Administration features are provided in Solution Manager in IEE.
BI Related Installation Subjects