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.
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. So it is natural to then select the Information Sources for which we would like to create Access Views.
Note: It is possible to select/deselect all Information Sources by the RMB option Select All. Please click RMB somewhere to right of the listed Information Sources.
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.
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.
Note: If it is necessary to recreate all Access Views related to one Information Source, either using the same data access type as previously or by selecting another data access type, then the check box Recreate Existing Access Views must be selected.
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.
When creating Access Views from the Information Source feature, the user can choose to navigate to the Access Views overview.
The overview displays all existing Access Views. The following information is presented:
Attribute | Description |
---|---|
Access View Name | Name of the Access View. An Oracle view is created using this name. |
Source View Name | View name of fact or dimension view that the Access View originates from. |
Data Access Type | Data Access Type, On Line or Data Mart |
State | Current state of the Access View |
Information Source View | Displays True if the view represents an Information Source view |
Source IS ID | Identity of the Information Source that the Access View applies to. Valid only for Views created for Information Sources |
Source Dimension ID | Identity of the Dimension that the Access View applies to. Valid only for Access Views created for Dimensions |
Original IS ID | Information Source that was originally used as starting point when creating the Access View. |
The window supports the following RMB options:
Select one or more Access Views and use the RMB Details... to move to the detail form.
This form displays Access View details.
Header attributes:
Attribute | Description |
---|---|
Access View Name | Name of the Access View. An Oracle view is created using this name. |
State | Current state of the Access View |
Info | Warnings or Error messages are logged here |
Original IS ID | Information Source that was originally used as starting point when creating the Access View. |
Source IS ID | Identity of the Information Source that the Access View applies to. Valid only for Access Views created for Information Sources |
Source Dimension ID | Identity of the Dimension that the Access View applies to. Valid only for Access Views created for Dimensions |
Data Access Type | Data Access Type, On Line or Data Mart |
Source View Name | View name of fact or dimension view that the Access View originates from. |
BI View Definition | Complete Oracle view definition of the Access View |
Detail attributes:
Attribute | Description |
---|---|
Column Name | Alias for the column definition. This is the name of the column in the Access View. |
Column Definition | Definition of the column. Might be a derived definition based on other existing columns. |
Column ID | Rank of the column. |
Data Type | Oracle data type of the data contained in the column |
Dimension ID Column | Specifies if the column is a dimension ID column, i.e. a column that can be used to connect to the corresponding dimension. Valid only for Access Views based on Information Sources. |
Nullable | Specifies is the column can have a NULL values or not. |
The window support the following RMB options:
The Dimension Joins for Access Views window looks as follows:
The window is only accessible for Access Views representing Information Source access.
The upper part presents dimension 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.
The lower part species for each fact-dimension connection, the name of the columns in the Access View that is defines natural join columns between fact and dimension. The information is used to create the Column Definition of Dimension ID Columns correctly.
The purpose with this window is to:
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.
The Access View will not filter any data when used, which means that if no specific criteria 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 a fact specific BI Access view, use the form Information Source Criteria for Access View.
In the above example a parameter named P1 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 2014.
When the Access Views 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:
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', 'P1', 1)
WITH READ ONLY
If the parameter value has to be modified, then just go to the Information Source Criteria for Access View form and modify the value.
Once a Access View has been created there are some available RMB options, both in the overview and the detail form. The availability of the below options depends in the state of the Access View.
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.
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.
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.
Removes the Access View from the database but still keeps the definition in the Access View windows. Possibilities here are typically:
The following information is useful when it comes to security:
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.
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:
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.
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.
When a user creates Access Views through IEE via the Information Sources feature, 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:
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: