Analysis Models - Deploy Necessary DW Objects¶
This page provides information on how to deploy necessary objects in the SQL Server database related to one Tabular Model. The deployment means that all objects defined in Tabular Data Sources required by one Tabular Model are deployed in the target SQL Server database. Once a successful deployment has been made, a Data Load Definition can be created and executed/scheduled to make sure that necessary data is transferred to the SQL Server database, data needed to be able to successfully process the tabular model.
Prerequisites¶
Before a DW Object Deployment is performed, make sure that:
- The SQL Server host has been set up properly and that necessary users have been defined with needed privileges.
Find more information in the installation section. - The environment parameters have been set up properly and the framwork environment in SQL Server has been set up succesfully.
Find more information in the installation section.
Architectual Overview¶
Tabular models are based on Information Sources that provide public read interfaces via Fact and Dimension specific views. Based on required Information Sources, so called Access Views are created. The Access Views and some other product specific views are all made available in the <IFSINFO> schema.
In the SQL Server database, necessary tables and views have to be created to make sure that data can be successfully transferred from the <IFSINFO> schema. The SQL Server database is used as the primary source for IFS delivered Tabular Models. The views in the SQL Server database serve as transformation view, in the image below named as TM views.
The DW object deployment focus on the SQL Server objects defined in the Tabular Data Sources.
DW Object Deployment¶
Deployment of necessary objects in the SQL Server database is performed using one specific Tabular Model. A Tabular Model references Tabular Data Sources and these sources define the objects to be created in the SQL Server database.
Start by selecting a tabular model in Tabular Models overview page or the Tabular Model detail page.
In the example, the standard model with Model ID = IFS_SALES has been selected. Note that the Configuration State is Base, meaning that this model is a standard IFS model without any configurations.
Use the command Deploy DW to launch the Data Warehouse Deployment Assistant.
Click Next.
The SQL Server <model_id> objects page gives the user the possibility to list all actions that will be performed on all objects related to Tabular Data Sources referenced by the current model. In most cases this is not needed, which is also the reason why the list is collapsed by default.
The below image shows the expanded list:
The list presents:
- Data Source ID
Identity of Tabular Data Source referenced by the IFS Model. - Object Name
Name of the object, defined in the Tabular Data Source, to be created in the SQL Server database - Deploy
Yes if the script command for this row should be deployed, otherwise No to not deploy the script command. - Deployment Order
Order of deployment. The generated order takes into account dependencies between Tabular Data Sources. Object creation will make sure that tables are executed before views but also that required SQL Server views are created before creating a table that requires these views. - Script Action
Action that the script for the actual row will perform.Typical values are DROP and CREATE. - Object Type
Type of object that that a row represents. Typical values are TABLE and VIEW. - Script
Presents the script that will be executed in the SQL Server database for a specific row.
One example; looking at the script to be executed for Data Source ID = DIM_VOUCHER_TYPE, Script Action = CREATE and Object Type = TABLE gives the following:
The described step also gives the possibility to configure what rows to be executed. This is typically a special case action,e.g. done only when one or a few objects are to be handled. At the lower end of the assistant there is a button named Toggle Deploy Flag that can be used to toggle all rows either to Yes or No. Then by editing the list, individual rows can be toggled to preferred value.
When ready with this step, click Next.
The user must now confirm the deploy actions ot take place in the SQL Server database.
When the deployment step has finalized, the last step of the assistant appears. If the execution was successful there will be a message:
Clicking on Finish will finalize the assistant and a verification message box will appear.
If the deployment fails for one or more actions, an error list named DW Deployment Errors is displayed.
To see the details for an error, click on the file/document icon. The opened dialog will show what was executed and the error reported.
In the above example, we can see that a table names DIM_SITE_BI_TAV is referenced and that is wrong, the table name should be DIM_SITE_BI_TAB.
If the error comes from a Tabular Data Source that has Configuration State set to Base then there is a core bug that needs to be corrected. A possible action to get the objects installed anyway, while waiting for a correction, would be to make a custom Configuration. For the example case it would be possible to make a custom configuration of the Tabular Data Source named and to correct the incorrect view definition. These custom actions has to be made with great care. Please refer to Analysis Models - Custom Configurations for more information.