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 |
1. How to configure http - redirection
- The following instructions are created for the Comarch BI Point program for instances that are set up on a port other than the default port (80).
To ensure the configuration proceeds correctly, there are two solutions if the program is installed on port 80 (default):
- Move the BI Point program to another port (e.g., 81) if it is installed on port 80.
- Create a "site" on a port other than 80 during the subsequent configuration stage (however, in this solution, after logging into the site, it is required to enter www.website.com:port)
Required components when configuring using IIS
(Internet Information Services):
In order to redirect the newly installed BI Point to another port, it is possible to use the IIS functionality. For this purpose, install the Microsoft Application Request Routing 3.0 add-on. Next, configure the config.json file available under: C:\Program Files (x86)\Comarch\BI Point\instances\Instance_name\bipoint\config.json as shown in the figure below:
If you want to allow connections to BI Point through a specific domain/subdomain, you must appropriately modify the
"AllowedHosts" entry.
Windows
"AllowedHosts": "bipoint.example.com"
Linux
bipoint:
(...)
environment:
- AllowedHosts=bipoint.example.com;another.example.com
(...)
If you want to define more than one host, separate them with a semicolon:
;
To ensure the analytics engine functions correctly, you must add to
AllowedHosts the host specified in the engine's
ApiUrl. The default container name
bipoint is automatically added, but if the BI Point container name has been changed in
docker-compose.yml on Linux, you must appropriately update
AllowedHosts.
The change must also be applied in BI Point database in the table
dbo.settings for BIPointUrl. In SET_CustomValue, enter a website name that matches the website address by which BI Point will be hosted.
For instances that use the analytical engine, enter the address (same as the one provided in
dbo.Settings) in the appsettings.json available under:
C:\Program Files (x86)\Comarch\BI Point\instances\
Instance_name\engine\appsettings.json
For "ApiUrl": "" entry
Next, add a website in IIS. To do so, after starting the
Internet Information Services Manager, go to the Sites folder and then select the option
Add Website… .
Depending on the address on which you want to host BI Point, complete the website name (in the presented case, BI Point must be available at the address bipoint101.pl) and the host name. The physical path is discretionary. It is recommended to specify a physical path to an empty folder.
Save the entered settings with the
OK button.
Next, add a server farm. This option becomes available upon installing first the ARR add-on and restarting the IIS. In order to create a farm server, select the option
Create Server Farm as shown in the figure below.
In the
Server Farm Name field, enter any name (a typed name should clearly indicate the application/website to which it applies).
In the advanced settings of the server farm, select the port (in the
httpPort field) on which the
BI Point you want to rewrite is installed. In the presented case, BI Point is installed on the port 81, therefore, this port is entered in the
httpPort field. Type localhost in the
Host Name field and 127.0.0.1 in the
Server Address IP.
Once all the fields are completed, select the button
Add to confirm and then the button
Finish.
After specifying the server, you will be asked whether to create a rule. Confirm the displayed message:
The message confirmation status is presented in the
IIS home > Url rewrite tab. The added rewrite rule should be displayed in the selected location after starting the URL Rewrite, as shown in the figure below:
In the rule edition window, the following view is displayed:
Tag a pattern in the
Match URL tab with “*” symbol unless a named instance is configured (see
How to configure HTTP with URL prefix)
In the
Conditions tab, select the option
Add. The window below will then open:
Set the
condition input to {HTTP_HOST} and the
pattern to the address at which BI Point is to be available (you can also use * indicating any address). You can use the option
Test pattern to verify whether the entered pattern covers the target URL address of BI Point:
After saving the address configuration, it may be necessary to restart IIS / BI Point or refresh the website several times. The BI Point instance should then be available at the configured address. If the selected website is not available from an external network (there is no public address specified), add the website address also into the host file
(C:\Windows\System32\drivers\etc\hosts). In case of the described installation, complete the host file as shown in the figure below:
After the website is added, BI Point should be available at the added website link:
2. How to configure HTTPS
In order to configure HTTPS, you need to configure the above and select a SSL certificate. Next, right-click on the created website for which you want to configure https and then select
Edit Links.
In the opened window, define a new link with https configuration. Select also an appropriate https certificate.
Next, go to the website
home, select
SSL Settings
and then the parameter
Require SSL. Back in the IIS
home, select
URL Rewrite and then the option
View Server Variables:
Select
Add and type
HTTP_X_FORWARDED_PROTO as the server variable name. Next, go back to the rule added in chapter 1:
And we add in the
'Server Variables' tab:
After restarting again the IIS, BI Point should be available under https.
HSTS header settings
When using HTTPS connection, enable the HSTS header setting. This option forces the browser to use HTTPS. In the application configuration, set the
UseHsts flag to
true and specify also the https port by setting https_port.
Settings in config.json
To properly view the page, set the
UseForwardHeaders flag to
true in the config.json file under C:\Program Files (x86)\Comarch\BI Point\instances\
Instance_name\bipoint\.
3. How to configure HTTP with URL prefix
If other websites are configured in the IIS as Default Web Site, then it is possible to configure BI Point with a URL prefix (i.e., the URL address has then the following form: demo-xl.comarch.pl/bipoint). For this purpose, create an application under Default Web Site website as part of configuration.
First, modify the config.json file that can be found at:
C:\Program Files (x86)\Comarch\BI Point\instances\
Instance_name\bipoint\config.json
In the file, add the instance name as shown below (the instance in the described example is bipoint).
Next, in the IIS, add the application as shown in the figure below:
When adding the application, type the instance name (in the example, it is bipoint) and select the path to the empty folder.
Then, add a server farm. This feature becomes available after installing first the ARR add-on and restarting the IIS. In order to create a farm server, select the option
Create Server Farm as shown in the figure below:
In the
Server Farm Name, type any name (a typed name should clearly indicate the application/website to which it applies).
In the advanced settings of the server farm, select the port (in the
httpPort field) on which the
BI Point you want to rewrite is installed. In the presented example, BI Point is installed on the port 81, therefore, this port is entered in the
httpPort field. Type localhost in the
Host Name field and 127.0.0.1 in the
Server Address IP.
Once all the fields are completed, select the button
Add to confirm and then the button
Finish.
After specifying the server, you will be asked whether to create a rule. Confirm the displayed message:
The message confirmation status is presented in the
IIS home > Url rewrite tab. The added rewrite rule should be displayed in the selected location after starting the URL Rewrite, as shown in the figure below:
In the rule edition window, the following view will be displayed:
In the
Pattern field, type an expression that will cover only the BI Point instance and not the other websites. In the above example, the pattern has the form *bipoint*.
Below is a description of the certificate parameters according to Microsoft documentation:
Note: In the case of other distributions, it is necessary to get a relevant command as part of package installation.
In order to install the curl tool, execute the following command (the exemplary command below is running on Debian and Ubuntu distributions):
In order to install the curl tool, execute the following command (the exemplary command below is running on Debian and Ubuntu distributions):
The next step is the installation of a Docket-compose component:
Finally, the images are installed:
You will be asked to select the path to the package containing 
After the images are loaded, it will be possible to run the Key Manager and install BI Point application. Upon confirmation, the Key Manager application will be installed on Linux system.
Note: You only need one instance of the Key Manager per network. If you installed the Key Manager with a license for BI Point on another server (Windows or Linux), then you can skip this step (by typing n) and in the latter installation step you will be asked to provide appropriate parameters (server and key number).
If you added the Key Manager to the docker-compose.yml file while executing the script, then after running the Key Manager for the first time, you need to add your license key via the manager tool from the key manager image, e.g.:
Prior to executing the commands, make sure to include the container names of the Key Manager:
After that, you will be asked to specify the configuration parameters of the application.
Setup parameters:
After the configuration parameters are specified, execute the following command:
After the installation is completed, the BI Point website can be run via a web browser. The default user is admin (password – admin).
Next, execute the last fragment of the log of the selected container by using this command:
The above message indicates that the import process has completed.
Next, select the 2023.2 release image location. If it was already changed in the same folder that contains the update-images.sh script, then press Enter to confirm the default location.
After loading individual images:
The additional scripts will be updated:
The docker-compose.yml will also be updated as shown in the message below, which is displayed during execution of the updating script:
The above update step has overwritten the following:
Next step, delete the ae_db container (after executing again the docker-compose command, a data volume will be created in the version compatible with Postgres 12). The command in this case is as follows:
The final step is to migrate the BI Point database:
To perform the migration, we first remove the containers:
After the backup copy is created, modify the docker-compose.yml and overwrite the following values as shown in the figure below (sample command of the text editor):
After the docker-compose.yml is changed, execute the update-images.sh script.
Once the script is executed, execute the command below:
Finally, run the containers using the command below:
The button Options opens new window. In order to change the setup path, select a location on disk, where BI Point must be installed. The default path is always the same as on the screen below:
If the available disk space is not sufficient, it will not be possible to continue the installation. A relevant message will then be displayed.
Before starting the installation, close all running programs.
Selecting the button Install starts the installation.
After launching the Comarch 
The figure below presents the META database of BI Point configuration tool.
Note: In case of updating a BI Point database, it is required that the user updating the database has the sysadmin or db_owner privileges on the server on which the database is stored.
After selecting the update option, a window presenting the progress of BI Point update process will open.
After the update is finished, the following progress screen will be displayed.
In the configuration tool start screen, it is necessary to:
1. Select a product to install from among the available products:
The following must be completed:
An OLAP database can additionally be configured for XL BI Point, Altum BI Point, CEE BI Point and BI Point Standalone products:
The following must be completed:
BI Point (Website)
Bi Point Database
The following must be completed in order to use an existing instance:
Configuration of BI Point component services
In this tab, you can create a database and a premium analytics engine service if you own the appropriate license. For a description of this functionality, see the article "
Geographical Database
Key Manager
Mail Server
Standard reports
Option ‘Import standard reports’ is used to import standard reports (depending on the product selected in the first step of the configuration tool) into the BI Point repository.
Logon method
After selecting the option ‘Login/Password’, credentials of the first user must be provided. A ‘Domain Account’, that will be associated with a given login, is completed automatically with credentials of a currently logged-in user.
If the option ‘Domain Accounts’ is selected, a user will be logging on via domain accounts. In this case, the login and password will also be completed automatically with credentials of a currently logged-in user.
