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.
Creating Access Views¶
The starting point for Access View creation is an Information Source. Access Views can be seen as a read interface. These views serve as the main sources for the Tabular Models within Analysis Models.
Necessary Access Views are created by using required Information Sources as the stating point.
- Use the command Create Access Views in the Information Sources or in the Information Sourcepage
- An assistant will start
- Select one or more Information Sources in the item picker.
- On the next page:
- Select Data Access Type, i.e. On Line or Data Mart
- 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. - 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.
- Next verify selections and Finish to start the view creation.
- 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:
Attribute | Description |
---|---|
Access View Name | Name of the Access View. An Oracle view is created using this name. |
Source View Name | Name of fact or dimension view that the Access View originates from. |
Custom Source View Name | Name of customized originating fact or dimension view |
Data Access Type | Data Access Type, On Line or Data Mart |
Last Modified | Timestamp when the Access View record was last modified. |
Status | Current status of the Access View |
Information Source View | Displays Yes if the view represents an Information Source view |
Source Info Source 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 Info Source ID | Information Source that was originally used as starting point when creating the Access View. |
The window supports the following:
- Validate
- Recreate
- Recreate from Source
- Remove
- 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:
Attribute | Description |
---|---|
Access View Name | Name of the Access View. An Oracle view is created using this name. |
Status | Current status of the Access View |
Information Source View | Displays Yes if the view represents an Information Source view |
Original Info Source ID | Information Source that was originally used as starting point when creating the Access View. |
Source Info Source ID | Identity of the Information Source that the Access View applies to. Valid only for Access Views created for Information Sources |
Data Access Type | Data Access Type, On Line or Data Mart |
Source Dimension ID | Identity of the Dimension that the Access View applies to. Valid only for Access Views created for Dimensions |
Source View Name | View name of fact or dimension view that the Access View originates from. |
Custom Source View Name | Name of customized originating fact or dimension view |
Last Modified | Timestamp when the Access View record was last modified. |
Info | Warnings or Error messages are logged here |
Access 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 value or not. |
The window support the following:
- Validate
- Recreate
- Recreate from Source
- Remove
- Information Source Dimension Joins
- 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:
- Give an overview of dimensions that are supported by an Information Source and also to show the columns that defines the unique dimension identity.
- 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.
- In a Data Warehouse 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 Tabular 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,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 Cloud to a Data Warehouse can be reduced, reducing the transfer time but also the time to fill the Warehouse tables.
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:
- After remove, recreate the view from the current definitions or from the source.
- 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:
- 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. Tabular Models, the access permissions for the user that is accessing the view will apply. 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 when a processed Tabular Model does not contain the expected data.
- Use ordinary product specific pages to administrate user access to e.g. companies, sites, GL access etc.
- 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 Cloud is performed it might happen that Access Views available in the source database will get invalidated.
There are two types of invalid states:
- 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.
- 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 the system creates identical views in the <IFSINFO> user schema as well. The reason is to enable source access for IFS Analysis Models, thus reading from the <IFSINFO> user schema instead of accessing the application owner schema objects.
Creation of Access Views will handle all necessary actions to simplify use of <IFSINFO> user views. The steps are:
- Grant SELECT on the Access Views to the <IFSINFO> user.
- Create views wih identical names in <IFSINFO> user schema. These views reads from the views in the Application owner schwma.