How to transfer reports from the RB to BI Point
In order to transfer the reports from the Report Book to BI Point, use a report migration tool. The application is called a Reports Exporter and can be found in the folder bin\rbexporter:
The application must be connected to a META database. To do so, select the button ‘Connect to META’. In the opened window, type a server name and select a META database from the drop-down list. An authentication is by default set to Windows authentication that uses the account of a currently logged-in user. For ‘SQL Server Authentication’ it is necessary to provide the credentials of SQL user with access permissions to a given database.
The application connected to the META database will display the same structure of the report tree as that available in the Report Book. Here it is necessary to select the reports that must be exported to a file and then select the button ‘Export’. A standard window to select a file location will open.
Note: It is possible to export the following reports: OLAP, SQL and Table. A Table report will be converted to a SQL report.
Note: MDK dimensions and additional dimensions as well as SQL functions cannot be exported.
After selecting the file location, a window for typing a password for protecting a file will open. That password will be required when importing the reports into BI Point application. After typing the password and selecting the button ‘Export’, the report definitions will be saved in a .RBD file.
In order to import the reports into BI Point, log on to the application, go to tab Administrator Panel and select the option ‘Import from Report Book’.
In the opened window, drag the created .RBD file onto the area with an arrow or indicate its location on disk.
After the file is loaded, the file name will be displayed and the button ‘Import’ will activate. Select that button to proceed to the selection of a destination folder in BI Point repository and finish the import process.
If the selected .RBD file is incorrect, you can return to the previous screen using the icon “X”.
In each of the three repository sections: Connections, Data Models and Reports, a folder ‘Imported from Report Book’ with appropriate objects will be created. The number of created connections and data models depends on the number of the data sources that were used by the exported reports.
Note: After the import operation is finished, it is necessary to complete the credentials in each created connection!
Note: In created data models, it is recommended to verify the correctness of parameters (tab Query) as well as the mapped measures/dimensions (tab Headers).
Reports with an additional measure or a MDX measure
The Reports Exporter application exports neither additional measures nor MDX measures. Therefore, such measures need to added to BI Point application manually.
The script below, when executed on the REPO database, displays the list of reports to which an additional measure has been added.
| SELECT [RDA_ReportId] as “Report ID” ,[LIN_Name] as “Report Name” ,[LIN_FolderId] as “Folder ID” ,[FOL_Name] as “Folder Name” ,[RDA_DefinitionType] as “Report Definition Type” ,[RDA_TextDefinition]as “Report Definition” ,[LIN_CreatedOn] “Report Date” ,[LIN_ModifiedOn] “Report’s Last Modification Date” FROM [RB].[ReportsData] RD join [RB].[Reports] R on RD.RDA_ReportId=R.REP_Id join [RB].[Links] L on L.LIN_ReportId=RD.RDA_ReportId join [RB].[Folders] F on L.LIN_FolderId=F.FOL_Id where RDA_TextDefinition like ‘%whatifMeasures%’ AND RDA_TextDefinition like ‘%data fieldName=”[[]Measures].[[]%-%-%-%-%]%’ |
| To search for the reports with additional MDX measures, the condition RDA_TextDefinition like “%whatifMeasures%” must be changed to RDA_TextDefinition like ‘%mdxQuery=%’ |
| The last condition RDA_TextDefinition like ‘%data fieldName=”[[]Measures].[[]%-%-%-%-%]%’ means that a given measure is used in a report (has been dragged onto the workspace). This condition can also be added to the script 2. |
Last report opening date
After executing the script 1 you can check when the reports with the additional measure or MDX measure were opened the last time. To do so, execute the following script also on the REPO database.
| SELECT [LAC_Id] as “Action ID” ,[LAC_ActionName] as “Action” ,[LAC_ObjectId] as “Report ID” ,[LIN_Name] as “Report Name” ,[LAC_SessionId] as “Session ID” ,[SES_UserLogin] as “User Name” ,[LAC_DateTime] as “Date” FROM [LOG_DATABASE_NAME].[dbo].[LogActions] LA join [LOG_DATABASE_NAME].[dbo].[Sessions] S on LA.LAC_SessionId=S.SES_ID join [REPO_DATABASE_NAME].[rb].[Links] L on LAC_ObjectId=L.LIN_ReportId join [REPO_DATABASE_NAME].[rb].[ReportsData] RD on L.LIN_ReportId=RD.RDA_ReportId where LAC_ActionName like ‘Open Report’ and RDA_TextDefinition like ‘%whatifMeasures%’ and [LAC_Id] in (select max(lac_id) from [LOG_DATABASE_NAME].[dbo].[LogActions] la join [LOG_DATABASE_NAME].[dbo].[Sessions] S on LA.LAC_SessionId=S.SES_ID join [REPO_DATABASE_NAME].[rb].[Links] L on LAC_ObjectId=L.LIN_ReportId join [REPO_DATABASE_NAME].[rb].[ReportsData] RD on L.LIN_ReportId=RD.RDA_ReportId where [LAC_ActionName] like’Open Report’ and RDA_TextDefinition like ‘%whatifMeasures%’ group by [LAC_ObjectId] ) |
| In this script, you can also specify whether to search for the reports with additional measures or MDX measures. Additional measures are searched for using the condition RDA_TextDefinition like “%whatifMeasures%“(which is already added in the above script), whereas MDX measures are searched for using the condition RDA_TextDefinition like ‘%mdxQuery=%’ |
| In the condition LAC_ActionName like ‘Open Report’ you can also type ‘UpdateReport’ to similarly search for the update dates of particular reports. |
Reports using the same model
After the reports are exported from the Report Book and then imported to BI Point, duplicated data models can be created. If two SQL reports with the same query are exported, then two identical data models will be added to each of the reports in BI Point. The scripts below can be used to find the reports with data models that use the same query. Such reports can next be linked to one model and the other reports can be removed.
Note: The scripts work only for SQL reports transferred from the Report Book with the report exporter. The scripts must be executed on the BI Point database and its backup copy needs to be made prior to making any changes.
The first script will display the report list, the data models on which they are generated as well as an ID of the data model that has the same query as the report model. In case there are more identical models, the smallest ID from the group of identical models will be displayed.
| SELECT r.”OBJ_Id” “Report object ID”, “REP_Id” “Report ID”, r.”OBJ_Name” “Report name”, m.”OBJ_Id” “Model object ID”, “DMO_Id” “Model ID”, m.”OBJ_Name” “Model name”, smq.ModelId “Identical model ID” –, * FROM rb.”RepositoryObjects” r LEFT JOIN rb.”Reports” ON “REP_ObjId” = r.”OBJ_Id” LEFT JOIN rb.”DataModelsReports” ON “DMR_REPID” = “REP_Id” LEFT JOIN rb.”DataModels” ON “DMR_DMID” = “DMO_Id” LEFT JOIN rb.”RepositoryObjects” m ON “DMO_ObjId” = m.”OBJ_Id” LEFT JOIN rb.”DataSources” ON “DS_Id” = “DMO_DataSourceId” LEFT JOIN rb.”DataSourceDefinitions” ON “DSD_Id” = “DS_DefinitionId” LEFT JOIN rb.”DataModelsQueries” mq ON mq.”DMQ_ModelId” = “DMO_Id” LEFT JOIN (SELECT MIN(“DMQ_ModelId”) ModelId, “DMQ_Query” FROM rb.”DataModelsQueries” GROUP BY “DMQ_Query”) smq ON mq.”DMQ_Query” = smq.”DMQ_Query” WHERE r.”OBJ_Type” = 4 AND “DSD_Definition” is null AND m.”OBJ_DestinationElementType” = ‘DataSetMSSQL’ ORDER BY 7,4; |
The example below illustrates the query results on the database with 6 imported reports:
The figure shows 4 models created for 6 reports. 3 reports (874,875,876) have the same model because in the Report Book, they were the standard reports linked to the same source report. In case identical reports are linked to one source report in the Report Book, they will be linked to one model in BI Point.Despite different models, five reports have the same query in these models. This is presented in the Identical Model ID column. The reports (871,872, 874,875,876) can thus be linked to one model. In this case, the identical model ID is 170, however it is possible to use any model from this group (models 170, 171, 173). Value 170 is a grouping value that shows which elements have a common query. The report 873 has a different model and a query than the other reports, thus, it cannot be reassigned and must remain as it is. After defining which reports have a common model, it is necessary to execute an update to link them to the common model. The update is as follows for the above-described example:
| UPDATE rb.”DataModelsReports” SET “DMR_DMID” = 173 WHERE “DMR_REPID” IN (871,872) |
Since this update, all sample reports will use the same model. The last non-mandatory step is the deletion of unused models. They can be removed manually from the BI Point application. This can be done with the help of the script below, which will list the models not used in any report (the query will also display the models that have been used in a dashboard but have not been used in the report).
| SELECT m.”OBJ_Id” “Model object ID”, “DMO_Id” “Model ID”, m.”OBJ_Name” “Model name” FROM rb.”RepositoryObjects” m LEFT JOIN rb.”DataModels” ON “DMO_ObjId” = m.”OBJ_Id” LEFT JOIN rb.”DataModelsReports” ON “DMR_DMID” = “DMO_Id” WHERE “DMR_REPID” IS NULL |