Analysis Models - Custom Config - Add IAL Source Example¶
This page provides an example how to add a new table based on an IAL source to a Tabular Model.
The IAL Object tool will be used to create the IAL. This tool provides a lot possibilities but requires that the user has knowledge about the IFS Cloud database.
One of the important things with the tool is that it creates objects in the
What will be done in this example:
- Creating an IAL using IAL Object tool.
- Creating a new Data Source the uses the IAL view as a read-source.
- In IFS_GENERAL_LEDGER model
- Adding a new table with a partition that refers to the new data source.
- Adding columns to the table.
- Adding a relation between the new table and an existing table.
- Deploying objects related to the new Data Source in the target SQL database.
- Transfer data from IFS Cloud to target SQL database with respect to new Data Source.
- Deploying and processing the model.
- Using Power BI to view modified model.
Create a New IAL Object¶
An IAL Object that can provide company area information will be created.
There is a core view called COMPANY_AREA that could be used as source but the problem is that the view also implements Row Level Security (RLS). This makes it almost meaningless to use this view since transfer of data from IFS Cloud to the SQL Server/Azure SQL database must be done without considering RLS. For this reason the table is used instead.
Using the IAL Object page in IFS Cloud to create a new IAL object named COMPANY_AREA.
When the object has been successfully created, there will be a view named COMPANY_AREA in the
Create a New Data Source¶
Navigate to the Data Source page and create a new record and fill in information in the group Tabular Data Source, Oracle Source, SQL Server Source and SQL Server Destination.
Data Source ID can be anything but good to provide a name that is easy to recognize.
In Oracle Source group, set View Name = COMPANY_AREA, the name of the view we created using IAL Objects.
- Type is selected as Other i.e., it is a source that is not a Dimension, not a Fact and not a Configuration. There is no support for incremental load.
- SQL Server Source group is not used in this case.
In SQL Server Destination a destination table is defined in Table Name.
SQL Server Destination Indexes
- A unique index is defined on columns COMPANY_ID, AREA_CODE.
- Set Index Param to 1 to create a unique index.
- In SQL Server Source Views
- Define one view with ID = COMPANY_AREA_BI Performs SELECT * FROM COMPANY_AREA_TAB
- Define one TM view with ID = COMPANY_AREA_TM Selects from the COMPANY_AREA_BI view and makes sure that column aliases are readable.
Good to create a unique index on the table to make sure that the table does not get duplicated data.
The view definitions can be done differently but the core standard is to have one BI view that reads from the SQL Server table and then transformation views with suffix _Tn , where n is an integer starting with 1 and then the final TM or front facing view has the suffix _TM and it only selects the needed columns and column aliases are user friendly.
Add a New Table and Partition¶
The next step will be to do some configurations to the IFS_GENERAL_LEDGER model.
First the new table COMPANY_AREA is added. Create a configuration of the model.
Now add the new table in the Tables in the model group.
Table ID = COMPANY_AREA
Name = COMPANY AREA
The table must have a partition that refers to the new Data Source with identity COMPANY_AREA.
Columns are added in the Columns in the table group.
Make sure that the Source Column is defined correctly according to the TM view in the data source.
Add Table Relation¶
The idea is to connect the new table COMPANY_AREA with the core table COMPANY.
Relations are defined for the model, thus navigate to the Tabular Model details page.
Relations are define in the Relations in the model group.
The ID column in each table should be connected.
Note: There should be a many-to-one relationship between tables COMPANY_AREA and COMPANY.
Deploy Objects to Target Db¶
Since a new data source has been created it is necessary to make sure that tables, views, indexes are defined in the target database.
From either the Tabular Models overview page or the Tabular Model details page, use the command Deploy to SQL Server Db.
Toggle off the deploy flag for all listed objects and actions,
Focus on the new table COMPANY_AREA (Data Source COMPANY_AREA).
Continue with next steps to run all necessary scripts.
Transfer Data to Target Db¶
Two load definitions will be created.
One that covers all data sources used by the model. It is created from the Tabular Models overview or Tabular Model details page using New Data Load Definition command. The second one only considers the new table.
The first one will be needed when running an ordinary refresh of the IFS_GENERAL_LEDGER model.
The second load definition only considers COMPANY_AREA and will only be used to add information in this new table in the SQL database.
The above image shows a successful Data Load Run of the Data Load Definition named COMPANY_AREA.
Deploy and Process Model¶
Next step will be to deploy the model to Analysis Services.
In the Tabular Models overview or Tabular Model details page, use the command Deploy Tabular Model.
When the deployment is successfully completed, a check will be made that the model looks ok. The following is done using Tabular Editor:
- Open the model from the Analysis Services database.
- Check that the new table COMPANY_AREA is available with expected columns.
- Check that the relationship exists.
Next step will be to process the model in Analysis Services.
In the Tabular Models overview or Tabular Model details page, use the command Process Tabular Model.
Use Process Mode = Database and Process Type = Full
Continue with next steps to process the model.
View the Tabular Model¶
The last step will be to view the processed model and to make sure that the new table COMPANY_AREA can be used as expected.
Power BI is used for this purpose.
To be really sure that all changes have been made properly, it can be a good idea to perform a model import.
All mentioned actions will be done using the Tabular Models page or the Tabular Model page.
Import can be made using an existing model like IFS_GENERAL_LEDGER as the target. If this option is chosen it is a good idea to first export the current definition of the model to file. This is done be starting the Deploy Tabular Model assistant and sing the possibility to Download .bim. Once the model is downloaded the assistant can be closed.
Import should be done by using the model deployed in Analysis Services.
If the the existing IFS_GENERAL_LEDGER model (in IFS Cloud Web) is used as target, the import should be successful and all configurations should be maintained. Use the command View Configurations to check the existing configurations.
Another option is to create a new tabular model in the Tabular Models page. Supply Model ID, Name and Description. Use this model as the target model and perform the import from Analysis Services. The import should be successful and all configurations should be defined correctly. Use the command View Configurations to check the existing configurations.