Component Structure and Packaging

This document describes how IFS Analysis Models is packaged.

Contents

• Used Abbreviations
• Overview of the Analysis Models
  • About the ETL process
  • About Cubes
  • About Dashboards
  • About Reports
• Component Structure
• Component Structure Details
  • Structure for Dimension and Fact Components
  • Structure for Cube Components
  • Structure for the APTOOL Component
• IFS Build Home
• AM Instance Home
• About the AM Instance Home and Installation
  • SSMS (SQL Scripts)
  • SSIS
  • SSAS
  • Dashboards
  • Reports
  • Tools
  • Utilities
• Dependencies
  • Component Dependencies
  • Cube Dependencies

Used Abbreviations

Overview of the Analysis Models

Analysis Models is built entirely with Microsoft tools. The purpose is to create a Data Mart in MS SQL Server supporting pre-packaged OLAP cubes for different IFS product areas. The OLAP cubes serves as the main source for example reports and example dashboards that are part of the packaging, but note reports and dashboards should be regarded as unsupported example contents.

The ETL process is the process that copies data from IFS Applications Oracle database into the Data Warehouse in the SQL Server database.

About the ETL process

• The Extract step means executing Microsoft SSIS packages to load the staging area. Information Sources, e.g. facts and dimensions, are used as the IFS Applications source. Special so called BI Access views are used, serving as a layer on top of existing fact and dimension views in order to in a more generic way hide the issue with the data access type (On Line or Data Mart).
• The Transform step means executing Microsoft SSIS packages, moving information from the staging area to the data warehouse area. This step includes adding new (transformed) items.
• The Load step also means executing Microsoft SSIS packages, moving information from the data warehouse to the data mart area. The data mart serves as final storage and represents a set of star schemas with true technical keys as the connection between a fact table and dimension tables.

About Cubes

• Each cube has its own set of files, i.e. a cube is self-supplied.
• The cube definition means addressing the tables in the data mart in MS SQL Server.
• Each cube is built to represent a specific area.

About Dashboards

• Dashboards are no longer part the packaging but is seen as example content, i.e. no R&D support available.
• Each dashboard has an OLAP cube as data source.
• Dashboards are developed using Power BI.

About Reports

• Reports are a part of the packaging but only as example reports, i.e. no R&D support available.
• Each report has an OLAP cube as data source.
• Reports are developed using SSRS.

Component Structure

Specific components are included in IFS Analysis Models according to the following pattern:

1. <short_name>DI

   A dimension specific component related to the core component. Contains all extension specific files related to dimensions in the core component.

2. <short_name>FT

   A fact specific component related to the core component. Contains all extension specific files related to facts in the core component.

3. <cube_short_name>C

   A cube specific component containing cube specific files as SSAS, Dashboard and report files.

4. APTOOL

   A tools specific component for Analysis Models with common files, e.g. SSIS packages, as well as utility script to be used during deployment and installation.

All components are added into one Sales Part for the complete IFS Analysis Models.

* Maintenance Cube (Work Order) is not part of Apps 10 RTM release.

Component Structure Details

Structure for Dimension and Fact Components

The components ACCRDI and ACCRFT serves as an example for all dimension and fact components.

Component ACCRDI contains dimension files related to ACCRUL and has a sub folder for the IFS Analysis Models, called external\BIAnalysisPackage. This folder in turn has sub folders for the available supported versions of SQL Server. Each SQL Server version folder has two sub folders, SSMS and SSIS.

The SSMS folder contains all SQL files to create necessary tables in SQL Server for component ACCRDI.

The SSIS folder contains all SSIS packages necessary for component ACCRDI. The BIAnalysisPackage folder also contains a documentation folder containing the component specific cube documentation files.

Note: The documentation files are only used as source files for the online documentation. All cube documentation is available as online contents but to make the documentation maintenance easier, the source files are placed in the Documentation folder

The same structure is valid for the ACCRFT component.

A Visual Studio solution is located under the SSIS folder per component. This solution file is used for bug corrections and for customizations in customer projects.

Structure for Cube Components

The Project Common Cube serves as an example for all cube components.

Component PJCOMC contains cube files related to the Project Common Cube and has a sub folder for IFS Analysis Models, called external\BIAnalysisPackage. This folder in turn has sub folders for all supported versions of SQL Server.

The SQL Server folders have subfolders with component folders for SSRS, Report Builder reports, and SSAS, cube specific files.

The component folders under SSAS and SSRS are necessary to ensure that each cube gets a separate set of files in both the IFS Build Home and AM Instance Home.

Visual Studio solutions are located under the SSAS and SSRS folders per component. These solution files are used for bug corrections and for customizations in customer projects.

Structure for the APTOOL Component

The APTOOL component contains common files which are necessary for all installations of IFS Analysis Models. The component contains code for the IFS Analysis Models Installer, template files for the creation of new components, a SSISConfig tool for development purposes.

The BIAnalysisPackage folder contains the common files for the installation and a utility to add example content as Quick Reports to IFS Applications. It also contains SSMS and SSIS files under respective supported SQL Server version.

IFS Build Home

The IFS Configuration Builder handles the build of dimension, fact, cube and other Analysis Models components into a IFS Build Home.

The Analysis Models content can be found in the external folder, with the following sub folder:

1. BIAnalysisPackage
   Refers to the Analysis Models

Each supported version of SQL Server has its own version of the IFS Analysis Models installer and can be found in under external\BIAnalysisPackage folder.

AM Instance Home

The IFS Analysis Models Installer will compile files to a AM Instance Home. The necessary set of files will be moved from the IFS Build Home to the AM Instance Home to form a folder structure such as shown in the image below.

Installations of the IFS Analysis Models should be done from the AM Instance Home.

The IFS Analysis Models Installer must be executed on a Windows server where SQL Server has been installed. To handle this, do one of the following:

1. Copy the complete external folder from the build server to the server running SQL Server and start the appropriate installer from here
2. Copy only the appropriate installer folder from the IFS Build Home to the server running SQL Server and start the installer from here

The AM Instance Home will contain:

• folders with SSAS, SSIS, SSMS and SSAS content to be deployed on SQL Server
• the folder Tools that contains the Configuration tool
• the folder Utilities that contains scripts to define installed sample content as Quick Reports

Example content will be installed in SSRS if selected during the installation.

One the BI Instance Home has been built, the Installer folder can be used to start the IFS Analysis Models Installer if the instance has to be modified in some way.

About the AM Instance Home and Installation

The following section gives an overview of files involved in the installation when creating a AM Instance Home.

SSMS (SQL Scripts)

The SQL scripts for tables are located in the SSMS folder at the root. The SQL scripts in the sub folders are used for the creation of the database, indexes, lookup scripts and stored procedures.

The create database script must be run first. Thereafter the table scripts in the SSMS base folder, the index scripts, lookup scripts and store procedures.

The name standard for the table scripts is as the following:

1. Stage tables

   IFSAppl_<source_object_name>.sql e.g. IFSAppl_DIM_COMPANY_BI.sql, IFSAppl_FACT_CURRENCY_RATES_BI_OPT.sql

   <source_object_name> represents the name of the IFS BI Access View representing a dimension or a fact. In some special cases there is no available BI view, then the object name in Oracle is used instead, e.g. IFSAppl_ACCOUNTING_STRUCTURE_ITEM_TAB.sql.

   The prefix IFSAppl is used to distinguish the IFS stage from possible other stage sources.

2. Data Warehouse tables

   Dw_<entity_name>.sql e.g. Dw_DIM_COMPANY.sql, Dw_FACT_CURRENCY_RATES.sql

   <entity_name> is the name of the dimension or fact in the data warehouse, built from data in the stage area.

3. Data Mart tables

   Dm_<entity_name>.sql e.g. Dm_DIM_COMPANY.sql, Dm_FACT_CURRENCY_RATES.sql

   <entity_name> is the name of the dimension or fact in the data mart, built from data in the data warehouse area.

SSIS

The next part will be handle the ETL process, completely built using SSIS where each activity is represented by a SSIS package.

The SSIS packages are located in the subfolder called SSIS under the SSIS base folder. Each package handles one object/entity for one specific step in the ETL process.

Some examples:

1. 1.1.012 Load IFSAppl_DIM_COMPANY_BI.dtsx

   The package loads DIM_COMPANY_BI access view to the stage area.

2. 2.1.012 Load Dw_DIM_COMPANY.dtsx

   The package loads DIM_COMPANY into the DW area from the stage area.

3. 3.1.012 Load Dm_DIM_COMPANY.dtsx

   The package loads DIM_COMPANY into the DM area from the DW area

4. Similar packages exist for facts starting with 1.2.*, 2.2.* and 3.2.*
5. 0.1.* and 0.2.* packages handle the execution of other packages. The execution order of other packages could be optimized for specific installations.

The name standard used for SSIS packages ensures deployment in the correct order.

Also in the SSIS base folder there exist a command file to trigger the execution of packages. This command file can be run by a SQL Server Agent job.

Note: All solution files for the components, in the AM Instance Home, are also located in the top most SSIS folder. These should not generally be used. Modifications should be done to the code base on the component level.) the component level

SSAS

The cubes are handled by the files in the SSAS folder. This is an example of the relevant file types available for a cube:

1. IFS.ds XML file representing a database connection used by a data source view
2. GeneralLedger.dsv XML file representing the Data Source View named GeneralLedger in the GeneralLedger cube
3. ACCOUNT.dim XML file representing the dimension ACCOUNT in a specific cube.

   Note: Another cube can contain an account dimension also named ACCOUNT.dim. A dimension file might appear in more than one cube but each file is uniquely associated with a specific cube. Due to practical reasons all cube dimensions with same name should more or less have the content.

4. GeneralLedger.cube XML file representing the GeneralLedger cube.
5. *.sln solution file for the cube, keeping everything together and to be opened in SSDT.
6. IFS_BI_GeneralLedger.asdatabase in the bin folder. This file contains the cube definition, which is used by the IFS Analysis Models Installer

Important is that all the files representing a cube are unique for that cube.

Reports

Reports are considered as example content and are built using SSRS. Reports are built with a specific cube in mind. Each area (or logical set of report files) has its own solution file.

Tools

The Tools folder contains the configuration tool for the ETL process. Read more about how to use the tool to configure data volume when extracting data to stage area in the Data Warehouse and to configure lookup information to be used in the ETL process in the Analysis Models configuration page.

Utilities

Apart from above mentioned files there is a utility script, under the Utilities folder, which is used to add Quick Report objects to IFS Applications. These Quick Report objects are used to show the example content (reports and dashboards), inside IFS Enterprise Explorer.

Dependencies

Component Dependencies

Component dependencies are listed in the deploy.ini file for each component. The following general principle applies:

• Dimensions
  • Static dependency to the general component APTOOL
  • Static dependencies to other required dimension components
  • Dynamic dependencies to core components
• Facts
  • Static dependency to the general component APTOOL
  • Static dependencies to all required (used) dimension components
  • Dynamic dependencies to core components

Note: The dynamic dependencies means that even if a Analysis Models specific dimension or fact component gets data from core components, these core components do not have to be present in the installation for the ETL process to run! A typical case can be a fact that is referencing the project dimension. In this case the PROJDI component is a static dependency while the PROJ components is a dynamic dependency. If the customer installation does not include Projects, this does not lead to any problems with the ETL process.

Cube Dependencies

A cube consists of one or more Data Source Views (DSV).

In the IFS case there is only one DSV per cube. The DSV references dimension and fact tables in the Data Mart area in the warehouse. There is no dynamic way of handling references in a cube. If a dimension or fact is referenced, it means that the source entity/object must be available. However it is only necessary to install all statically dependent dimension and fact components. Analysis Models specific components always have a dynamic dependency to core components.

The general cube dependencies to tools and fact components are listed below:

* Work Order Analysis is not part of the Apps 10 RTM release

Some notes:

• A cube in one product area more or less always retrieves fact information from components mainly in that product area.
• Facts in other product areas are only referenced in special cases fact in other products are referenced.

  The typical case is FACT_REPORTING_CURRENCY_RATES that is derived from the Information Source FACT_CURRENCY_RATES in ACCRUL component within Financials.

• Dimensions referenced can belong to different product areas. In many cases only a few of all possible dimensions in a component are referenced.