Creating and Managing Access Views

Use this page to learn how to create Access Views but also about management and other miscellaneous subjects related to Access Views.

To get an overview of Access views, please refer to the Access Views page.

Contents

• Creating Access Views
• Access View
  • Overview Access Views
  • Access View Details
  • Information Source Dimension Joins
• Add Filter Criteria to an Access View
• Available Access View Options
• Security Remarks
• Installation Specific Utility Scripts
• Technical Remarks

Creating Access Views

The starting point for Access View creation is an Information Source. The reason is basically that when e.g. a MS OLAP Cube is to be built, the sources for defining Data Source Views will be one or more Information Sources, making it natural to select the Information Sources for which Access Views should be created.

1. Use the command Create Access Views in the Information Sources or in the Information Source page
2. An assistant will start
   
3. Select one or more Information Sources in the item picker.
4. On the next page:
   1. Select Data Access Type, i.e. On Line or Data Mart
   2. Select Create Fact Views Only if only the fact views and not the associated dimension views should be created.
      This can typically be an option when it is required to use incremental Data Mart access for facts but online access for the associated dimensions.
   3. Select Recreate Existing Access Views if it is required to recreate existing Access Views.
      Normally this is not necessary since once a Access View has been created based on a data access type, it is likely that the Access View should not be replaced. If e.g. more than one Information Source is selected it is also rather likely that there will be some dimensions that are shared by these Information Sources, and it is then not necessary to recreate the dimension specific views once they have been created.
5. Next verify selections and Finish to start the view creation.
6. When the creation process is ready, an information message will pop up specifying how many Access Views that were created. It is now possible to choose to navigate to the Access View overview window.

Note: The result of creating Access Views from Information Source is that there will be one new view created for each source view related to the fact part of the Info Source and one for each dimension the is directly or indirectly referenced via the Information Source.

Access View

Overview Access Views

When creating Access Views from the Information Source page, the user can choose to navigate to the Access Views page.

The overview displays all existing Access Views. The following information is presented:

The window supports the following:

1. Validate
2. Recreate
3. Recreate from Source
4. Remove
5. Details

   Select one or more Access Views and use standard detail navigation to open the detail form.

Access View Details

The Access View page shows all details for an access view.

Header attributes:

Detail attributes:

The window support the following:

1. Validate
2. Recreate
3. Recreate from Source
4. Remove
5. Information Source Dimension Joins
6. Information Source Crieria

Information Source Dimension Joins

The Information Source Dimension Joins for Access View page looks as follows:

The window is only accessible for Access Views representing Information Source access.

The upper part presents dimensions that the Information Source supports and the name of the column in the Access View that can be used when joining the Information Source with the ID of the associated dimension.

By selecting one or more dimensions and navigating to details, i.e. the Information Source Join Columns for Access Views page, the columns representing the dimension key can be viewed. The information is used to create the Column Definition of Dimension ID Columns correctly.

The purpose with these pages:

1. Give an overview of dimensions that are supported by an Information Source and also to show the columns that defines the unique dimension identity.
2. Allow modification of the information if for some reason the source information is not accurate or if it is necessary to modify due to other reasons.
   1. In a Data Warehouse or MS Cube scenario, the connection between an Information Source and a dimension is done by using the following principle:

      FACT_BI_VIEW.DIM_ABC_ID = DIM_ABC.ID

      This means that we always want to join an Information Source with a dimension by using one single join column. It is also required that the same type of join is done regardless if the origin of the Access View is a On Line or a Data Mart view. To handle this correctly, it is important that if one column that is part of dimension identifier has the value NULL then the identity should be NULL and not a concatenated string like e.g.

      <key_column1> || '#' || '#'

      To accomplish this is important that for each connected dimension, all columns that represent dimension keys, natural join column, are defined.

   2. If e.g. the MS Cube model does not support some dimensions, or if it the information is not accurate to define a join correctly, then modifications can be done manually.

Add Filter Criteria to an Access View

The Access View will not filter any data when used, which means that if no specific criteria definitions are added, the Access View might transfer very large amounts of data. When building a Data Warehouse or a MS Cube, it can be a good idea trying to reduce the number of transactions already as part of the initial Access View definition. If e.g. a transaction table in Financials General Ledger contains transactions for the last 10-15 years, it is probably possible to reduce the number of transactions by adding a criteria on the accounting year, reducing the number of transferred years to only a small portion of the total amount. By doing this the transfer of data from IFS Applications to a Data Warehouse of a MS Cube can be reduced, reducing the transfer time but also the time to fill the Warehouse tables and/or MS Cubes.

To add criteria definitions for a fact specific Access view, use the page Information Source Criteria for Access View.

In the above example a parameter named YEARLIMIT has been added to the Information Source FACT_GL_TRANSACTION. The parameter has a criteria that says that the accounting year should be greater than 2016.

When the Access View(s) related to Information Source FACT_GL_TRANSACTION are recreated the criteria will be automatically built into the Access View definition. In the example above it means that the following fact views, related to FACT_GL_TRANSACTION, will get a WHERE clause that contains the criteria definition:

• FACT_GL_TRANSACTION_BI (based on FACT_GL_TRANSACTION_DM)

The criteria will be built according to the following:

CREATE OR REPLACE VIEW FACT_GL_TRANSACTION_BI AS 
SELECT 
COMPANY_KEY                                       COMPANY_KEY,
ACCOUNTING_YEAR_KEY                               ACCOUNTING_YEAR_KEY,
VOUCHER_TYPE_KEY                                  VOUCHER_TYPE_KEY,
...
...
FA_OBJECT_ID                                      FA_OBJECT_ID,
SEQUENCE_NO                                       SEQUENCE_NO 
FROM FACT_GL_TRANSACTION_DM
WHERE ACCOUNTING_YEAR_KEY >= Bi_View_Fact_Criteria_API.Get_Number_Parameter('FACT_GL_TRANSACTION', 'YEARLIMIT', 1)   
WITH READ ONLY

If the parameter value has to be modified, then just go to the Information Source Criteria for Access View page and modify the value.

Available Access View Options

Once a Access View has been created there are some available command, both in the overview and the detail form. The availability of the below options depends in the state of the Access View.

Recreate

This option recreates the current Access View by using the current definitions in the Access View specific windows. This means that manual modifications that have been made in these windows, will affect the creation of the view. Existing criteria definitions will be considered for Information Specific views.

Note: In most cases this operation does not change the available columns in the Access View. This means that the view is still valid when it comes to number of columns but the view might produce different values than before the recreation. It is important to consider this since it might be necessary to perform modifications where used, e.g. in 3rd party tools or in IFS Analysis Models.

Recreate from Source

This option recreates the current Access View by using the source view definition, i.e. the current state on the  dimension or fact/IS view is used to create a new Access View. Existing criteria definitions will be considered for Information Specific views.

Note: Recreating a Access View might lead to that the view changes This specially applies if the status is Invalid. Thus it is necessary to make sure that 3rd party tools or the IFS Analysis Models are modified as well in order to consider the changed interface.

Validate

Validates the current Access View by comparing it to the original source view and related fact/dimension metadata.

If the source view and the Access View have different number of columns, if the natural join information differs etc. the status will be set Invalid and the Info field will contain the warning or error message.

Remove

Remove is handled by using the standard remove command and it deletes the Access View from the database but still keeps the definition in the Access View window. Some further possibilities are:

1. After remove, recreate the view from the current definitions or from the source.
2. If it is necessary to also the Access View itself, then remove the record using standard remove.

Security Remarks

The following information is useful when it comes to security:

1. If the original source view, a dimension or an Information Source view, includes security mechanisms as e.g. user access filters, then the created Access View will have the same filters. This means that when using a Access View as the source for e.g. a MS Cube, the access permissions for the user that is accessing the view will apply. So it is important to make sure that the accessing user has the necessary privileges to access the information in IFS, else it will come as a surprise that a loaded MS Cube does not contain the expected data.
   1. Use ordinary product specific pages to administrate user access to e.g. companies, sites, GL access etc.
2. When the Access Views are created, all views will be connected to the Presentation Object with the technical identity BIAXSVbiAccessViews and the name IFS Access Views, owned by the component Access Views (BIAXSV).

   To give the accessing user the possibility to access the Access Views, the easiest way is to grant the mentioned Presentation Object to a permission set that the user is included in.

Installation Specific Utility Scripts

When an upgrade of IFS Applications is performed it might happen that Access Views available in the source database will get invalidated.

There are two types of invalid states:

1. Case one is when a Access View is considered as an invalid object in the Oracle database.

   The reason can be that something has changed in the views and packages referenced by the source view that the Access View reads from.

   In some cases this situation can be handled by recreating the Access View.

   There is a utility script that can be used for this purpose.

   The name of the script is biaxsv_Recreate_Db_Invalids_From_Src.sql and can be found in the folder manualdeploy\database\biaxsv in component BIAXSV.

   The script will try to recreate all Access Views, that are considered as invalid in the Oracle database, by using information about the referenced Fact/Dimension source view.

   Note: Please consider that recreating a Access View might mean that the view changes. Since a Access View is an interface to be used by 3rd part tools or by the IFS Analysis Models, it is important to make sure that if a view is changed during recreation, this means that the interface has changed which might lead to necessary modifications where the access view is used.

2. Case two is when a Access View is considered Invalid by the Access View framework.

   If an Access View gets the status Invalid it means that the referenced source view has changed in one way or another. This status is there to make an administrator aware of that the access view is not in synch with its source view.

   The Access Views form can be used to find not valid views and it is also possible to recreate them via this form.

   Note: Before recreating an invalid Access View it is important to know that this operation might change the access view. Thus it is necessary to make sure that 3rd party tools or IFS Analysis Models is modified as well.

    

Technical Remarks

When a user creates Access Views via the IFS client, the system creates identical views in the IAL schema as well. The reason for this is to enable external access by using the IAL schema user (<IFSINFO> user) instead of having to use the application owner. This means that the ETL process related to IFS Analysis Models or access from IFS EOI using the EOI integration can be performed using the IAL user.

Creation of Access Views will handle all necessary actions to simplify use of IAL user. The steps are:

1. Grant SELECT privileges on the Access Views to the IAL User in the AppOwner schema
2. Create views identical to the generated Access Views in the IAL schema
3. Grant SELECT privileges on the newly created views in the IAL schema to the AppOwner user
4. Grant SELECT privileges on the newly created view\s in the IAL schema to IFSSYS user
5. Register the new views in the IAL schema
6. Register the new views in IFS internal security privileges table
7. Grant SELECT privileges on the newly created views to the BI_VIEW_ACCESS role

The following views that are mandatory for the ETL process (IFS Analysis Models) are also created in the AppOwner and IAL schemas during the installation process:

• Component ACCRUL
  • ACCOUNTING_ATTRIBUTE_CON_BIA
• Component GENLED
  • ACCOUNTING_STRUCTURE_ITEM_BIA
• Component BUDPRO
  • BI_GL_BAL_SET_CUBE_CONFIG_BIA
• Component KPISRV
  • FIN_KPI_BIA
  • FIN_KPI_DETAIL_BIA
  • FIN_KPI_DOMAIN_BIA
  • FIN_KPI_DOMAIN_COMPANY_BIA
  • FIN_KPI_DOMAIN_STRUCT_BIA
  • FIN_KPI_ELEMENT_BIA
  • SSAS_CUBE_BIA
  • SSAS_CUBE_DOMAIN_BIA
  • SSAS_CUBE_MEASURE_BIA