Analysis Models - Custom Config - Add Dimension Source Example¶
This page provides an example how to add a new table based on a new Dimension to a Tabular Model.
The IAL Object tool will be used to create an IAL. This tool provides a lot possibilities but requires that the user has knowledge about the IFS Cloud database. The created IAL will be used as source for creating a Quick Dimension.
A configuration will also be made to make sure that the new Quick Dimension gets added to the FACT_GL_BALANCE.
What will be done in this example:
- Creating an IAL using IAL Object tool.
- Creating a new Quick Dimension using the quick dimension draft functionality.
- Creating a custom dimension connection where the new dimension is connected to the Information Source FACT_GL_BALANCE.
- Creating necessary access views.
- Creating a new Data Source the uses the Access View related to the new Dimension 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 return a distinct set of Accounts will be created.
Only the account code will be used. Accounts normally are defined in the context of a company but in this case only the distinct accounts will be selected and it is not possible to add a description since it may vary from company to company.
The core view ACCOUNTING_CODE_PART_VALUE is used.
Create a Quick Dimension¶
Next a quick dimension will be created by using the new IAL source.
The steps are:
Start to Draft a new QDIM (Quick Dimension) using the Draft Quick Dimension assistant.
Select Information Access Layer (IAL) as Source Origin.
Use LoV to select the create IAL source ACCOUNT_DISTINCT.
Finish the assistant and navigate to details.
Edit ACCOUNT column in the Quick Dimension Items group. Make sure that the column is defined as both a Key and an ID column.
Check that the view definition contains an ID column and a CODE (=key) column.
Use Create Dimension to publish the draft as an ordinary dimension.
A quick dimension only supports Online access but when connecting it to an Information Source where the Fact supports both Online and Data Mart access, it will be necessary to support Data Mart access. The new dimension must thus support Data Mart. This is handled by a configuration on the Dimension page in the Data Access Type Information group.
For more information, please lookup documentation about Quick Dimensions.
Create a Dimension Connection Configuration¶
Next we want to connect the dimension to an Information Source and we choose FACT_GL_BALANCE and this will be done as a configuration.
By doing this it is made sure that it is possible to create the necessary Access View for the new dimension. Access Views can only be created using an Information Source as the starting point.
Another benefit of connecting the new dimension is that it can be used in a reporting tool as IFS Business Reporter once the GL Balance Information Source is refreshed.
Create Access Views¶
An Access View related to the new quick dimension needs to be created.
It is assumed that the model has already been setup. During that process all necessary Access Views are created. What is missing is one new Access View.
The view creation is based on FACT_GL_BALANCE and Online access will be the selected access type.
Create a New Tabular 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,SQL Server Destination and SQL Server Source Views.
- Data Source ID can be anything but of course good to provide a name that is easy to recognize.
- In Oracle Source group, set View Name = QDIM_ACCOUNT_DISTINCT_IAS$_BI, the name of the view created via Quick Dimension draft functionalilty.
- Type is selected as Dimension. There is no support for incremental load. It is important to set the type as Dimension to make sure that creation of Access Views in the Setup Tabular Model assistant works properly.
- SQL Server Source group is not used in this case.
- In SQL Server Destination a destination table is defined in Table Name.
- In SQL Server Source Views
- Define one view with ID = QDIM_ACCOUNT_DISTINCT_BI Performs SELECT * FROM QDIM_ACCOUNT_DISTINCT_BI_TAB
- Define one TM view with ID = QDIM_ACCOUNT_DISTINCT_TM Selects from the QDIM_ACCOUNT_DISTINCT_BI view and makes sure that column aliases are readable.
The view definitions follow the core standard where there is 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.
The TM view QDIM_ACCOUNT_DISTINCT_TM has been defined as:
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 ACCOUNT_DISTINCT is added. Create a configuration of the model.
Now add the new table in the Tables in the model group.
Table ID = ACCOUNT_DISTINCT
Name = ACCOUNT DISTINCT
The table must have a partition that refers to the new Data Source with identity QDIM_ACCOUNT_DISTINCT.
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 ACCOUNT_DISTINCT with the core model table ACCOUNT. A relation to GL ANALYSIS table could have been possible if the table had a column representing the account code but that is not the case.
Relations are defined for the model, thus navigate to the Tabular Model details page.
Relations are defined in the Relations in the model group.
The ACCOUNT_CODE column in tale ACCOUNT is connected to column ID in table ACCOUNT_DISTINCT.
Note: There should be a many-to-one relationship between tables ACCOUNT and ACCOUNT_DISTINCT.
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.
If the objects related to the model has already been deployed as part of model setup, the best way is to use the command Deploy to SQL Server Db from either the Tabular Models overview page or the Tabular Model details page.
Toggle off the deploy flag for all listed objects and actions,
Focus on the new table ACCOUNT_DISTINCT (Data Source QDIM_ACCOUNT_DISTINCT)
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 Data Source QDIM_ACCOUNT_DISTINCT 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 QDIM_ACCOUNT_DISTINCT.
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 ACCOUNT DISTINCT is available with expected columns and that the source view is the expected (QDIM_ACCOUNT_DISTINCT_TM).
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. If the model has been previously processed, it will be enough to Process Type = Default.
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 ACCOUNT DISTINCT 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 Aurena) 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.