This topic provides a quick step-by-step guide on how to extend the data model. It is intended for advanced users who are familiar with certain operations in the system, such as creating queries and configuring integration tasks. However, these operations are also documented in this manual, which can be consulted for instructions.
1. Create a query with the data you want to use in Microsoft Power View. This query can be from the Organization, Risk, Compliance, or Workflow modules, or from the ERM solution.
2. Access the Administration module -> Integrations -> Integration Tasks. Create an integration task to export queries to SQL tables. For details, see Chapter 17: Administration -> Integrations -> Integration Tasks -> Creating a Task to Export Queries to SQL Tables. When configuring the task, select the query you want to use in Power View. Make sure to mark the option to execute the task after saving it.
3. Once the task is executed, go to its history and write down the name of the table created for the exported query in the system's SQL database.
4. To edit the extraction script for the Intelligence solution and include the new table, locate the integration task for the Intelligence solution in the system.
5. Add the script below to the end of the extraction script in the integration task for the Intelligence solution.
-- ************************ Custom Extraction *******************
-- Select Intelligence SQL database as default database to query
USE [{etlbase}]; -- [{etlbase}] is the predefined reference that Modulo Risk Manager uses for the Intelligence SQL database
-- drop the target table before recreating it
IF EXISTS (SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'[dbo].[name of table created in step 3]') AND type in (N'U')
)
DROP TABLE [dbo].[RM_workflow_Administrator_AllWorkflowEvents];
-- copy the database from the system SQL database to the Intelligence SQL database
select * into [dbo].[name of table created in step 3]
from [{etlreader}].[dbo].[name of table created in step 3]
6. The result should be similar to the figure below. Be sure to replace the highlighted text with the name of the exported table.
7. Run the integration task to extract and process data for the Intelligence solution. Next, go to its history and make sure it was executed successfully. If so, a success message indicating that the data was stored in the Analysis Services database will be displayed. However, the table will not yet appear for users because it was not yet included in the analytical database from Analysis Services.
8. To include the table in the analytical database, the name of this database must first be identified. To do so, access Administration -> Settings -> Intelligence Solution -> Analysis Services Database tab. Write down the name of the database in the Database field (see figure below).
9. To edit the analytical database, the server where Microsoft Analysis Services is installed must be accessed. To do so, open SQL Server Data Tools, which is the tool from the SQL Server suite used to edit analytical BI models from Analysis Services (see figure below).
10. After configuring SQL Server Data Tools to access the system database, a new business intelligence project must be created in Microsoft Visual Studio by importing the current structure of this database (see figure below).
IMPORTANT: When editing the model, always recreate the project as detailed here to ensure you use the latest published model, even if it was edited by someone else.
11. To proceed, enter the analytical database that you want to import to Microsoft Visual Studio in order to make the modifications (see figures below).
12. Once the project is registered, the new table can be created in the analytical model using the data connection with the SQL database for the Intelligence solution that is already configured in the model. This table will receive the data from the table exported from Modulo Risk Manager. To begin, click Existing Connections (see figure below).
13. In the Existing Connections window, select the data source connection and click Open (see figure below).
14. You may need to provide credentials to access the SQL database for the Intelligence solution (see figure below).
15. To proceed, the analytical database that will be imported for editing must be provided. To do so, select the table previously created in the SQL database for the Intelligence solution (see figure below).
16. The Preview & Filter option (see figure below) allows you to exclude any unnecessary columns from being imported to the analytical model. These may include tables with larger quantities of text, such as description fields, which take up space in the database and are not used in metrics or consolidations.
17. After selecting the columns and clicking Finish, the import process should start and finish successfully (see figure below).
18. Once imported, it should be possible to view the tab for the imported table at the bottom of the screen (see below). To customize it, click the icon to open the diagram view of the model.
19. Locate the new table in the model and rename it, meanwhile renaming any other fields with unclear names (see figure below). Next, check for other fields that should be removed or hidden. Any field that should not be displayed to users should be hidden, as this will not prevent it from being used in calculations or references throughout the model.
Note: These steps are important because removing or renaming the fields afterwards may invalidate reports that are already using these fields.
20. To finish and transfer the changes to Analysis Services, the project must be deployed. To do so, right-click the root of the project in the Solution Explorer section and select Deploy (see figure below). The new model will be transferred to Analysis Services together with the updated data, and the new table should be available for use in the Intelligence solution.
The tables are displayed in the Field List window in Power View (see figure below).
