Business Reporting & Analysis¶
Overview¶
This section deals with different aspects of Business Reporting & Analysis in IFS Cloud.
Business Reporting & Analysis provides a Star Schema based framework for IFS Cloud. 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:
- Reporting based on IFS Business Reporter
- IFS Analysis Models, i.e. a framework that supports Tabular Models powered by SQL Server Analysis Services (SSAS)
An IFS Information Source can support data access via online data as well as via a data mart repository built into the IFS Cloud database. IFS Analysis Models uses so called 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. IFS Business Reporter is one example. This client reporting tool extends MS Excel from a desktop tool to an enterprise reporting tool by exposing services within Business Reporting & Analysis to IFS Business Reporter.
Business Reporting & Analysis also supports Ad hoc reporting, a set of services and also integrations.
Background¶
To support 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 reporting via IFS Business Reporter and transfer of data to the IFS Analysis Models specific data layer in SQL Server.
Information Sources support both Online or Data Mart access. Using Data Mart access provides an obvious advantage compared to online access, since the stored data has been processed and also since it is easy to enhance performance by adding indexes. In many cases however it is necessary to use Online information, especially when working with budgeting and planning.
The IFS Data Mart is a scaled down version of Data Warehouse and it is residing in the IFS Cloud database. The framework supports true incremental load but it is necessary to adapt each Information Source. IFS will however out-of-the box deliver incremental support for the most essential Information Sources.
Having a separate data layer instead of using the ordinary base tables provides the ideal means of tuning performance of the Data Mart without affecting the standard IFS Cloud. Necessary means to configure and administrate this data layer are provided via Business Reporting & Analysis
Concepts¶
Data Warehouse and Data Mart¶
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.
Star Schema, Measures and Dimensions¶
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 statements 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.
Materialized Views¶
One way to represent the built-in Data Mart in IFS Cloud is to use Materialized Views. Materialized Views are snapshot tables that will store fetched and processed data taken from ordinary base tables and can be tuned for higher performance without affecting the other functionality.
A Materialized View (MV) is neither a table nor a view. When an MV is created, it extracts the data from one or more base tables and stores data in a snapshot table, i.e the MV. Thus, it is possible to create indexes and tune the MV without affecting the base table.
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 Business Reporting & Analysis framework along with required additional information such as dimension connections, parent details, zoom-in / drill-down details, URL navigation details etc.
Incremental Load of Information Sources¶
Another way to represent the built-in Data Mart in IFS Cloud 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.
Online Access¶
Fact and Dimensions normally also support Online access. This access type is the obvious starting point when the requirement is to get quickly started with reporting via e.g. IFS Business Reporter. Accessing data Online is also important in cases where a report must be executed many times each day and there is no time to wait for a refresh of the Data Mart. Online and Data Mart views represent the same information. Online views are also registered in the Business Reporting & Analysis framework along with other related metadata.
Information Sources¶
The generic Star Schema model is a good starting point for reporting and analysis. 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:
- Measure Items
- Light Items
- One or more connected Dimensions
- List of Values related information like Parent Dimension, List of Values items
- URL Information
- Zoom-in, Drill-Down information
- Write back information
Solution¶
The Business Reporting & Analysis runtime framework is implemented so that requests based on Information Source attributes are analyzed, a SQL query is built and executed, the result is formatted and is sent back to the client. Business Reporting & Analysis 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 Business Reporting & Analysis 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 Business Reporting & Analysis 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 Finance, provides pre-packaged Information Sources that can be used to easily visualize the underlying data sources, without requiring any knowledge about details of the implementation. It is possible to customize these using the Information Source page in IFS Cloud.
Configuration and Administration features are provided in Solution Manager.