Analysis Models - Custom Config - Add Cust Attr Example¶
This page provides one example how the Tabular framework in the IFS Aurena client can be used to add a custom attribute to a table in a tabular model. Some other functionality/tools except for the Tabular framework will also be used.
A custom attribute will be added to an existing Dimension. The new attribute will be visualized via a Tabular Model referencing the dimension. The chosen dimension is DIM_CODE_B and the tabular model is IFS_GENERAL_LEDGER. Functionality related to Information Sources as well as the Tabular Model framework will be used and there will be no need to use external tools as MS Visual Studio or Tabular Editor.
Adding a Custom Attribute to DIM_CODE_B¶
A new attribute called CODE_COMPANY will be added to dimension DIM_CODE_B.
The value will be defined as a concatenation of the CODE column, the text “ – Company=> " and the COMPANY column. For more information, read how to add a custom attribute to a Dimension.
Relation needed for the new attribute:
Definition of the new dimension attribute:
When publishing the new a custom attribute, there is a message:
NOTE: The message means that even if the attribute has been added, the existing Access View associated with DIM_CODE_B has NOT been modified.
Recreating Access View Related to DIM_CODE_B¶
To be able to transfer info tp the target SQL database related to the customized DIM_CODE_B, the access view needs to be recreated. This can be done in the following ways:
- Navigate to the Access Views page.
- Search for Access View Name = DIM_CODE_B_BI
- Use Recreate From Source command
- If the Access View does not yet exist, navigate to Information Sources page and use the command Create Access Views that starts an assistant. Important to know:
- What Information Source references DIM_CODE_B? (e.g. FACT_GL_BALANCE). (Can be found in the Access Views page or via Information Source – Dimension Overview page.)
- Data Access Type
Should normally be Online for a dimension.
- Enable Recreate Existing Access Views
After successful recreate, the access view should contain the custom attribute related column. Can be checked in the Access View details page; the Column Details group should have the column CODE_COMPANY.
Note: Since the Access View now has a new column, any transfer related to DIM_CODE_B to the target SQL database will from now lead to error until modified objects have been defined in the target database.
Adding Configuration to Data Source DIM_CODE_B and DIM_CODE_B_FINANCIALS¶
The dimension DIM_CODE_B is represented by the Data Source with Data Source ID=DIM_CODE_B.
The Data Source defines the IFS Cloud source view, DIM_CODE_B_BI, as well as the target objects in target SQL database.
The core Data sources DO NOT by default select all columns from a source. The main reasons are:
- Selecting as few as possible column in the last view, called the TM view, is good for read performance.
- Each selected column in the TM view is given a user-friendly alias that normally does not have to be modified in the Tabular Model.
In this case it is necessary to modify two Data Sources.
Looking in the Tabular Model details page for model IFS_GENERAL_LEDGER, in the Tables in the model group, a table with Table ID = CODE_B can be found.
The detail page shows that the referenced Data Source is DIM_CODE_B_FINANCIALS.
We can also see that there are more than one Data Source containing DIM_CODE_B.
DIM_CODE_B_FINANCIALS is used in the IFS_GENERAL_LEDGER model. That Data Source has a TM view definition that refers to Data Source DIM_CODE_B. Can also be seen in the dependency section in the details of DIM_CODE_B_FINANCIALS.
Start by creating a configuration for DIM_CODE_B.
What we need to do is to add the new column to the so-called TM view, in this case DIM_CODE_B_TM, the view definition with the highest Order.
Edit the view definition and add the new column.
NOTE: Don’t forget comma to separate column definitions.
Do a similar configuration on Data Source DIM_CODE_B_FINANCIALS.
Since we are referencing DIM_CODE_B_TM that we previously configured, we need to use the correct column name i.e., the alias we used when modifying the TM view related to DIM_CODE_B.
NOTE: Instead of using the Aurena client to do the modifications, the changes can be done by exporting the Data Source, changing it and then importing it.
Making sure that DIM_CODE_B Objects are modified in target Db¶
It will be necessary to recreate database objects related to the two changed Data Sources.
As mentioned before, the model IFS_GENERAL_LEDGER refers to Data Source DIM_CODE_B_FINANCIALS that requires Data Source DIM_CODE_B. Also, DIM_CODE_B references the access view DIM_CODE_B_BI.
In the Tabular Models page, select IFS_GENERAL_LEDGER and use the command Deploy to SQL Server Db.
In the 2nd step, start by toggling of all data sources. Use the Toggle Deploy Flag at the bottom of the assistant page.
Next, filter on Data Source ID = DIM_CODE_B
All data sources starting with DIM_CODE_B are listed. All listed objects and actions are needed.
Update Deploy to Yes for the listed rows.
Continue with Next step that will deploy the listed objects in the target SQL database. Deployment means that all objects like tables and views related to Data Sources DIM_CODE_B and DIM_CODE_B_FINANCIALS will be re-created, and no data will be available after the deploy step.
New Data Load Definition for DIM_CODE_B¶
Since objects have been recreated it is necessary to transfer data related to dimension DIM_CODE_B to the target database.
Create a new Data Load Definition like in the image below.
Go to details and in the Data Load Definition Items group and add Data Source DIM_CODE_B. It is not needed to add DIM_CODE_B_FINANCIALS since that data source definition only contained view definitions, views that have already been deployed.
Run the Load Definition¶
Once the Data Load Definition has been created, run the load by using the command Run.
Use Fetch Status command in the Data Load Runs group to check the load run is ready
By using a tool like Management Studio we can check that the storage table DIM_CODE_B_BI_TAB has the expected values.
Add New Column to Target Model¶
So far, no changes have been made to the IFS_GENERAL_LEDGER model.
There are different ways to handle this. In this example, IFS Aurena will be used to add the new column to the table with Table ID = CODE_B and Name=CODE B.
First look up the model in the Tabular Models page and go to the details page.
In the Tables in the model group, find table with Table ID=CODE_B and go to details.
In the header, use command Create Configuration to initiate the configuration of the table.
In the Columns in the table group, add a new column with Column ID = CODE_COMPANY.
Important to note that table CODE_B in IFS_GENERAL_LEDGER model refers to Data Source DIM_CODE_B_FINANCIALS that has a TM view named DIM_CODE_B_FINANCIALS_TM. The Source Column must be defined according to the column name in this view.
Deploy the Tabular Model¶
The changed model needs to be deployed to Analysis Services.
Select the model in the Tabular Models page.
In this example, the SSAS Database ID and Name will be modified using the Change SSAS Database ID command. This is not needed but can be an idea when testing made changes.
Next use the Deploy Tabular Model command.
As an optional step, to make sure that the generated model contains the new column, use the Download .bim command to download the model file and open it preferably in Tabular Editor.
That the file can be opened in Tabular Editor, is a confirmation that the model is probably ok.
Continue with the next steps in the assistant to deploy the model to Analysis Services.
Process the Tabular Model¶
Now the model needs to be processed.
Select the model in the Tabular Models page.
Use the Process Tabular Model command.
Use Process Mode = Database and Process Type = Full
Continue with next steps to process the model.
View the Tabular Model¶
When the processing is done, test the model. In this example Power BI Desktop will be used.
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.