Develop Facts

A Facts is the main entity in a Star Schema model and in most cases represents transactional data. By connecting dimensions to a fact we get an Information Source. A fact provides the following information:

• Original keys
• Measure items like amounts and quantities that can be used for analyzing purposes
• Light fact items; extra transaction information that is not measurable
• Connected/supported dimensions
• Special join information
• Performance indicator information
• Zoom In and Drill Down item details
• URL information

This section supplies some guidelines how to develop facts.

Note: All development of fact in IFS Applications should be done by using IFS Developer Studio.

Contents

• General
• Fact Specifics
• Data Mart Development
• Online Development
• Default Information Source Folder

General

The Business Reporting & Analysis services framework handles two data access types:

• Data Mart

  Fact data is in this case processed and stored either in Materialized Views or in snapshot tables in the Oracle database. For this case the following storage layer objects have to be created:

• Materialized View(s) or snapshot tables
• Specific views related to incremental load of snapshot tables
• Fact View

• Online

  In this case the fact data is retrieved from the ordinary core tables or views. For this case the following storage layer objects have to be created:

• Fact View

Metadata files:

• A fact has an associated Business Reporting & Analysis services specific metadata file that describes the Fact. This is currently an INS file.
• If a dimension supports incremental load, there is a specific metadata file that describes how to handle incremental load for the fact.

  This file currently also an INS file.

Fact Specifics

Some specific aspects to be considered when creating a fact:

• A fact represents the transaction entity in an Information Source.

  The information can be derived from one or more tables and the items can be either directly referenced or calculated.

• It is not necessary but recommended that the key columns in the original data source tables are created as view columns with the suffix _key

  The key columns can be useful for Drill Down functionality but also for handling of true/natural join.

• For each supported dimension there should be a column/item that keeps the identity of a row in the dimension.

  It is important that NULL identities are handled correctly, i.e. taking into account that a dimension identity can NULL in a transaction row.

  Note: The NULL handling for dimension identity columns differ between the Online and Data Mart version.

  Make sure that the dimension identity definitions in the fact match the identity definition in each dimension.

• Extra columns/items with the natural dimension keys should be available, also called joiners.

  This means that if a dimensions has an ID="<parent_column>^<code_column>", then it should be made sure that the columns <parent_column> and <code_column> are also available as separate columns in the fact.

  The main purpose is to support natural/true join for Online execution.

• The Business Reporting & Analysis services framework does not yet support Enumerations which means that enumeration values should be represented by two values:

• The database value
• The translated database value

• Even if the source is only one, it should always be considered if one or many facts should be created.

  Splitting a source into several sub sources might increase the understanding/clarity and might also be good for performance, since each source then has less transactions than the original source.

• The fact might have to consider Row Level Security. The security level of the fact should be the same as in any core window/feature.

  The security has to be implemented in the fact view. This applies both to Data Mart and Online.

• A fact in Business Reporting & Analysis services can have up to three different views.

• One view is always the standard view for the data access type.
• An optional view that is used if the view handling type is UseOptional.

  This view can e.g. be defined without Row Level Security definitions, to make it possible to switch to a faster view if the customer allows this.

• An alternative view, that can be used if the view handling type is AlternativeOrStd.

  The view is linked to an API that has to be defined.

  If the API for the current user, during execution, allows the user to use the alternative view, it can be used. Else the standard view is used.

• All views for a fact has to support the same items.

Data Mart Development

Development of Data Mart support means that the fact specific data is collected in one of the following ways:

1. In a Materialized View (sometimes even in more than one MV). This option relates to a Data Mart based on Materialized Views in the Oracle database.
    
2. In a snapshot table, e.g. an ordinary Oracle table. This option relates to a Data Mart based on the framework for Incremental Load.

The fact information is retrieved by a Data Mart specific fact view.

Learn more.

Online Development

Development of Online support means that the fact specific data is retrieved directly from the source tables via an online specific fact view.

Learn more.

Default Information Source Folder

A component that owns Information Sources, can define a default BR Client Navigator folder name for its Information Sources. This is done in a component specific INS file. Executing these component INS files during installation means that registered Information Sources will appear in the Information Source Navigator in IFS Business Reporter directly after the installation (as long as the user has been granted access to the Information Sources).

The following method is used to register a default folder for an Information Source:

Xlr_Def_Fact_Nav_Folder_API.Set_Default_Folder_Name(
	fact_id_ 	      IN VARCHAR2,
	default_folder_name_  IN VARCHAR2,
	always_add_           IN BOOLEAN DEFAULT FALSE)

The method actions are:

1. Register the folder as the default folder for the fact (IS).
2. If the fact (IS) is not present in the navigator, then
   1. Add the folder to the navigator if not already present
   2. Add the fact (IS) to the folder

If a fact (IS) is removed via the Setup BAClient Navigator then that fact (IS) will not be added again if the INS file is executed. This must be considered as the standard behavior. There might however be cases where it is necessary to force an addition of as fact to the BA Client Navigator. To handle this case it is necessary to define the always_add_ parameter to TRUE, i.e.

-- Default way of calling the registration method
Xlr_Def_Fact_Nav_Folder_API.Set_Default_Folder_Name(<'<fact_id>', 'Financials');

-- Making sure that the fact is always added to the default folder
Xlr_Def_Fact_Nav_Folder_API.Set_Default_Folder_Name(<'<fact_id>', 'Financials', TRUE);

Each component with Information Sources defines its own INS file. A template file can be found here.