Analysis Models - Custom Configurations
This page provides information about custom specific configuration options related to Tabular Models, an essential part of IFS Analysis Models.
The Tabular Models framework is based on a layered architecture. The core/base definitions are defined in a core/base layer, while custom specific definitions are defined in a specific custom layer that will not interfere with the base layer.
Customization of a Tabular Model can be made in two ways:
- Using Marble
- For a model that is represented by a IFS Marble model, which applies for all delivered core/base models, IFS Developer Studio can be used to change the Marble definition of the model by adding a custom layer.
- All custom specific definitions are defined in separate Marble model files.
- During model deployment, the core/base model and the custom model will be merged, thus creating a database representation of the customized model.
- Once the model is deployed, all functionality in the Tabular Models framework can be used to handle actions like DW deployment, model deployment, data loads etc.
- Using Aurena Configuration Support
- The different Tabular Models specific Aurena pages support customization through configuration.
- Aurena specific configurations do not require any Marble model handling.
The information in this page only relates to Aurena specific configuration support.
The basic idea behind the custom configuration support is to provide a way to define a custom specific layer that does not interfere with the base layer definition. Thus, if e.g. an attribute in a tabular model table is to be modified, this is done by creating a configuration and then doing the change as a configuration. The configuration definition will be stored separately. The tabular table will after the configuration consist of base definitions and one custom configuration. When the table is used, all sub parts as e.g. columns, measures and hierarchies that are supposed to be included will be considered and the custom configuration will be prioritized over the base layer.
A created configuration can easily be removed to make sure that the the base definition applies again.
There are different scenarios related to custom configurations. Two examples will be given.
- A new custom attribute is added to a Dimension or Fact (using configuration support for Information Sources).
- The attribute has the data type Integer but in the Access View related to the changed source it is represented by a Number column.
- In the Tabular Data Source that uses the modified (Dimension/Fact) related Access View, it will be necessary to create a column override for the new view column, to make sure that it is not interpreted as being of SQL Server data type Float but instead being of data type Int or Bigint.
To handle this and assuming that the data type should be Int in the SQL Server target table, a configuration is created for the Tabular Data Source and a column override is added.
- When the Tabular Data Source is used further, e.g. when creating the Tabular Data Source objects in the SQL Server database, the made configuration will be considered.
- After DW deployment it will also be necessary to load the newly created table in the SQL Server database, that will contain one more column than when using the base version of the Tabular Data Source.
- How the column is is not described in this example.
- An existing core/base Tabular Model, already deployed in SSAS, is supposed to be customized by adding one new source represented by a dimension.
- The enhancement of the base model is handled in SQL Server Data Tools, typically on a VM/server where SQL Server is installed. To be able to update and process the model a new input/read source is needed.
- It is assumed that the necessary Dimension can be created using the Quick Dimension functionality in IFS Cloud, i.e. by creating the Dimension through configuration functionality available in Aurena. The Quick Dimension draft is first created, then modified if needed and then used to create a new Dimension.
- The Dimension now needs to be added to an existing Information Source. This can be done by using the Custom Dimension Connections functionality.
- Once the Dimension is connected, the Information Source it was connected to is used for creation of Access Views. This means that all necessary Access Views are created and also that these views are made available in the <IFSINFO> schema.
- Next a Data Source needs to be created. That can be done in Aurena client using the Data Source page. The data source should refer to the new access view as the Oracle Source and a SQL Server Destination table should be defined. Also create a new view that reads from the SQL Server table. Check the Data Source page documentation for more info.
- A new table needs to be added to the tabular model. This can be done in the Tabular Model detail page in the Tables in the model group.
- Next select the table and go to the Tabular Table details page. In the Partitions in the table group, add a new partition where the identity of the created Data Source is defined. Use the same Partition ID and Name as for other tables in the model.
- Next the new SQL Server objects, defined in the Data Source, has to be deployed. This is done by using the Deploy DW command in the Tabular Models page. In the assistant make sure that the new data source appears and toggle off all other data sources and make sure that only actions related to the new data source are active. Perform the deployment. If there are errors, necessary corrections has to be made. Once the deployment is successful, make sure to check in the SQL Server database that the objects have been correctly created.
- Create a new data load definition that contains only the new data source and run the load to make sure that the source view is found, that data can be transferred correctly to the SQL Server table.
- Adding the new SQL Server view, defined in the new data source as a SQL Server source view, to the model is the next step and is done using e.g. Data Tools. The development might lead to that extra transformation views are needed. If so the Data Source should be updated with the view definitions and then a new Deploy DW action is needed to create all new objects. Also run a new data load to transfer data.
- When the development is done and quality assured (deployment from Data Tools to SSAS and testing), the model should be imported back to IFS Cloud to ensure that custom configurations are correctly defined, thus ensuring that the Tabular Models framework can be used for data loads etc.
- When the model changes have been made, perform an import of the modified model by using the Import command in the Tabular Models page. The import should lead to that the core/base model is defined as configured and all deviations from base version will be defined as New or Configured. For more details refer to the Configuration State section.
The available configuration states are described by the following image.
- Models defined in the Marble language needs to be deployed during installation and will end up having the state Base. Thus all IFS delivered models will have this state.
- A model where the model itself and all configurable parts have the state Base will, when creating a configuration on the model level, get the state ConfiguredBase, displayed as Configured.
- If a new model is created in Aurena client, it will get the state ConfiguredNew, displayed as New.
- If a detail of a Base model is configured, the detail will get the state ConfiguredBase, displayed as Configured, the parent entity will get the state ConfiguredDependent, viewed as Dependent, and the model itself will get the state ConfiguredDependent viewed as Dependent.
- If a Base model is configured, e.g. by first configuring a table and then configuring a table column, then all these levels will get the state ConfiguredBase, viewed as Configured.
- Configuring a detail in state Base leads to that the detail gets state ConfiguredBase, viewed as Configured, and the parent will get the state ConfiguredDependent, viewed as Dependent.
- If a Base model is configured, the state will be set to ConfiguredBase ( Configured ) and it will be possible to edit on the model level. If the edit operation changes something that deviates from the base layer, save leads to the state ConfiguredBase ( Configured ). However, if a new edit is done and reverting the change, save leads to the state ConfiguredDependent ( Dependent ) even of no details have yet been configured. The configuration is removed by using Remove Configuration or by performing Exclude and then Include.
The same pattern is there for model details, if e.g. a parent like a table is configured and edited without changing/configuring any details (e.g. columns).
- If a Base model is configured and then a new detail is added, the the model gets the state ConfiguredBase, displayed as Configured, while the detail gets the state ConfiguredNew, displayed as New.
Note: The status handling works in a similar way for e.g. a Tabular Data Source as for a Tabular Model.
Note: The Exclude command, that sets state to Excluded can be used to e.g. exclude a model detail as a column, measure, table etc. from DW deployment and from being added to the .bim file that is deployed in a SSAS database. Howerver, excluding parts of a model must be done with great care. Use command Include to go back to previous state.
Example - Configured Tabular Data Source
The following example shows one simple case where a new custom attribute is added to an existing dimension, a dimension that is also used as source for a tabular model.
The first step adds a new custom attribute of data type integer to dimension DIM_SITE, named INT_COL which wll also be the column name in the dimension specific source views.
Next step will be to recreate the Access View, DIM_SITE_BI, to make sure that the new column will be available.
After recreating the Access View, we find the new column among the Column Details.
Also investing the view definition to make sure that everything is ok.
Next step is to add a configuration to the Tabular Data Source with Data Source ID = DIM_SITE.
In this case a configuration was added in the top level and then a new entry was added in the override group where a data type override is made to make sure that the column will be treated as an integer and not a float.
Since the source view DIM_SITE_BI has changed we need to re-deploy the associated Tabular Data Source.
This is done by using the Deploy DW command in e,g, the Tabular Model page, running the assistant but making sure to only perform deploy actions related to the source.
After a sucessful deployment, the target database is also investigated to make sure that the target table DIM_SITE_BI_TAB has been updated.
Creating a Data Load Definition that only includes Tabular Data Source DIM_SITE_BI and the running the definition to transfer data to the SQL Server table.
After a successful load, the target table is checked to make sure that data has been correctly transferred.
Next step will be to modify the standard base model and to update it properly to consider the new column. This work is done using SQL Server Data Tools.
After testing and re-deployment, the model can be imported, either using the standard model as target leading to a configured base model or importing it as a new model.
For more information about development/configuration scenarios, please refer to the Analysis Models Development.
Examples on State Changes
As a first example we will create a configuration on a base model and then modify an attribute on the highest level.
The IFS_SALES model is selected and then command Create Configuration is used to create a configuration,
Configuration Status has changed to Configured.
Navigate to the details page.
The model is in state Configured on the highest level while the details, e.g. expressions and tables, still have the state Base.
Now, if we edit the model, group Model Details, and e.g. change the Description and save, the state will still be Configured.
However, if a new edit is done where we undo the changes, the state will be set to Dependent. Still there are no status changes on the model details. The reason for this behavior is that editing affects a configured record and during save it is difficult to remove the configuration and go back to state Base.
To remove the configuration, use the Remove Configuration command button. The model will the go back to state Base.
One more example will be provided where a detail of a model is configured.
Also in this example the IFS_SALES model is used.
No changes are made on the highest, model, level.
The model table with identity SALES_MARKET is selected and navigation to table details is made.
A configuration is created on the ID column.
The table will get the state Dependent, in this case meaning that there is a dependent configuration on a detail level.
The ID column has the state Configured.
On the model level the state is also Dependent.
Removing the configuration on the ID column, the state of the column will go back to Base.
The table has still state set to Dependent.
Use Remove Configuration to remove it.
The table SALES_MARKET now has state Base again but the model has state Dependent. Also in this case we need to Remove Configuration on the model level to set the state of the complete model to Base.