Setup of Reports Server

Introduction

Reports in r_keeper 7 manager station can only be viewed after the references server is licensed, and its connection to the external database under MS SQL Server 2012 control is configured. Therefore, a references server can function as a server for report generation or a reports server.

As the number of restaurants increases in the base up to dozens, the references server load increases as well. Therefore, r_keeper 7 allows creating intermediate (transit, subordinate) servers implemented via the reports server - rkReportServer.exe.

The reports server executable file is rkReportServer.exe, the default config file name is repserv.ini.

To install software from the distribution package (find the archive in ftp://ftp.ucs.ru/rk7/versions/7_05_03/STANDALONES/ folder), copy the REPSERV.INI file from \bin\win\ini\ folder to \bin\win\ working folder where rkReportServer.exe is located.

To install software via the installer, find a server with the config file in [installation path\instance name\Rk7Reports].

When is Reports Server Needed?

1. A reports server is needed when it is necessary to divide reports and references info into separate tasks.
2. A reports server is required to generate reports (with prior recalculation of cubes) and to view them in the manager station (the license is needed, connection to the internal SQL database is set up);

In this case, there may be a Central Reports Server collecting data from all restaurants (or from local reports servers), and a Local Reports Server receiving data directly from a specific cash server (if the customer has several restaurants with cash servers).

1. A reports server is needed to reduce the load on existing servers when the number of restaurants is growing fast.
2. A reports server can be used as intermediate/transit server (without the license and setup of connection to SQL). Useful when there are hundreds of restaurants.

The values for some parameters, given in this article, are default and are not mandatory to specify.

The connection between the servers is shown below:

Licensing

Reports server licensing procedure is identical to references server licensing procedure described in the article on the References server.

Briefly, the procedure is as follows:

• Open Options - OLAP reports - Reporting servers reference
• Select the server

• Disconnect an external DB if there is a connection (External Data Base Link — empty, Cubes Source Type — switch to recalculation via disk file: UDB database (exchange via file)
• Save the changes
• Select the protection key in the License Information — Protection Key Actual Number field
• Generate license request: right-click on the reports server icon — Actions — Generate License Request
• Generate license for this request on the licensing system site, and specify it in the License Information — License Key field
• Save the changes
• Configure the connection to an external DB if cube-based reports are required.

Launch

Launch the reports server as follows:

• • Create a server in the manager station in Options — OLAP reports — Reporting servers, assign a unique network ID to it, f. ex., REPSRV3001, change the status to Active, and save the changes
  • License the server (see paragraph above); Otherwise, the connection via the manager station will not be possible
  • In the Synchronization section, check all the boxes. Synchronization must be turned on.
  • Create an empty base in Management Studio. Configure the connection to this DB using instructions in the article on connection to MS SQL.

Specify the following parameters in repserv.ini, in the [Config] section:

Client = "REPSRV3001" ; reports server network IDRefServer = "RK7SRV3001" ; upper level server network ID (it can be both references server and reports server)RefsBasePath = ".\BaseData\refsdata.udb" ; relative or absolute (recommended) path to reference DBCheckBasePath = ".\BaseData\check_db.udb" ; relative or absolute (recommended) path to receipt (cumulative) DBWorkModsFile = ".\BaseData\wmodules.udb" ; relative or absolute (recommended) path to working modules DBReceivedPath = ".\Received\" ; relative or absolute (recommended) path to directory with received closed shiftsErrorLog = "repsserv.stk" ; relative or absolute (recommended) path to log fileUseSql = 0 ; do not connect to external DB under SQL Server 2012 control

• Launch the server
• Stop the server, change UseSql=1 parameter value in repserv.ini config file
• Launch the server.

The first launch should always be performed as an application (with the /desktop parameter). Also, make sure to perform the launch as administrator.

As well as references server, reports server can be launched as a service. For this purpose, use the /install parameter.

Upon server lunch as a service (rkReportsServer.exe/desktop) the following icon will appear in the tray:

Repsserv.ini File Description

Before launching the reports server, uncheck the Read only box in its parent folder attributes (otherwise, the reports server will not be able to create its database). Also, make sure the full path to the folder with the RKREPORTSSERVER.EXE file contains no spaces, and the total number of path symbols does not exceed 200.

Copy the repserv.ini file from the RK7/BIN/WIN/ini folder if you have not done this before, and place it beside the RKREPORTSSERVER.EXE file, i. e., into the RK7/BIN/WIN/ folder.

[CONFIG]

Client = "REPSRV3001"                                           ;; the report server network name, same as the corresponding report server in the manager station. See "Manager station settings" below
RefServer = "RK7SRV_3001"                               ;; the references server network name (or the upper report server)
RefsBasePath = ".\BaseData\refsdata.udb"         ;; a full path to the reference base, do not confuse with rk7.udb
CheckBasePath = ".\BaseData\check_db.udb"   ;; a full path to the receipt base, do not confuse with check.udb
WorkModsFile = ".\BaseData\wmodules.udb"    ;;  a full path to the working modules base
ReceivedPath = ".\Received\"                                   ;; a path to the data receiving folder
ErrorLog = "repsserv.stk"                                         ;; a log file name (add a path if necessary). By default, a log file is saved in the same folder where RKREPORTSSERVER.exe is placed
ErrorLogSize = 1048576                                          ;; error log size in bytes
OldRefsSyncMode = 0                                         ;; 1 - enable old synchronization mode, necessary if PollEvents=1
PollEvents = 0                                          ;; enable periodic references server polling mode (in the references server .ini file, fill in the EventCachePath= parameter), and set OldRefsSyncMode=1
RecalcAllCubesAt = 2015                                         ;; Time is specified without semicolon in "HHMM" format. Recalculate all cubes that would be recalculated by the "recalculate all cubes" item at the manager station at 20:15. For versions 7.4.20.60 or higher.
CubesRecalc = 0                                          ;; disable cubes recalculation, 1- enable cubes recalculation, 2 - only manual cubes recalculation (For versions7.6.1.2 or higher!)
NoReports = 0                                         ;; disabling menu with manager station reports (For versions 7.6.1.2 or higher!)
ServPingTimeout = 5000                                          ;; 7.4.17.78 or higher 
UseSql = 1                                          ;; 0 - if there is no connection to SQL, 1 - if connection to SQL is used
LoadThreadsCount = 4                                           ;; load closed shifts data to cumulative DB simultaneously, "4" - number of files processed simultaneously (recommended in case of big number of shift files arriving in parallel). For versions 7.5.2.328 or higher!!
cubesBuildTool = "bldcubex.exe"                                     ;; Enable bldcubex.exe utility for cubes generation at PC with 2+ GB memory (For versions7.6.2 or higher!)

[NETKERN]
Protocols = tcpsoc.dll    ;; protocols list (comma separated), you must make sure these DLL files are placed in the folder with the RKREPORTSSERVER.exe file
[TCPSOC]
PORT=3639    ;; port for server
 
[TCPDNS]
RK7SRV_3001=subway.rkeeper.com:3648
REP_CENTR4=subway.rkeeper.com:3644
RK7SRV=subway.rkeeper.com:3639
 
[UPDATE]
NoUpdate=0
 
 scheduler. only for 7.5.2.502+
[SYNCCONF]
 Enable scheduled synchronization
RefsSyncMode=1
 synchronization time in CRON format 50 10 * * * is equivalent to "always at 10:50", 45 13 * * * is equivalent to "always at 13:45". 
Format is as follows:


* * * * *
| | | | |
| | | | +----- Days of week (range: 1-7)
| | | +------- Months (range: 1-12)
| | +--------- Days of month (range: 1-31)
| +----------- Hours (range: 0-23)
+------------- Minutes (range: 0-59)



SyncSchedule=50 10 * * *         ;; synchronize references at report server loading
SyncOnReload=0

Reports Server Update

Right-click the icon to open the reports server in a browser. Default HTTP port can be set in the manager station by specifying a new port in reports server properties (HTTP Server group -> HTTP Data Port group).

After the reports server launch, check its connection to the upper server.

The reports server update is identical to the references server update.

Manager Station Connection to Reports Server

Use the following ways to connect the manager station to the reports server:

1. Create a separate instance of the manager station with a new INI file and specify the reports server network ID in the [REFEDIT] section therein.
   
   
   • Server= REPSRV3001 ;; Your reports server name
   • Client=MANST-%RANDOM% ;; the name of the manager station that will be connected to the reports server.
     
     Therefore, there will be two icons for RK7MAN.exe on the desktop - one for connection to the reports server and references editing, another for connection to the reports server and reports viewing.
     
     
2. Launch the manager station again, leaving the INI file unchanged. When the logging window appears, press Ctrl+Alt+S on the keyboard and enter the reports server ID in the poll in the bottom left corner.

The manager station will make necessary modifications in rk7man.ini, and you will have to select the server by pressing Ctrl+Alt+S at the next launch.

Cubes Recalculation

Cubes are placed in the Options — OLAP reports — Cubes and Reports reference.

The status of a cube and its data can be checked in the cube properties, Data Processing section. Each cube has a list of reports servers and cube recalculation info (periodicity, calculation time, last error, etc.).

You can recalculate a required cube manually (right-click -> Actions -> Recalculate).

Starting from version 7.06.04.041: if a server has over 2 GB of RAM, you can use a new utility for cubes generation – bldcubex.exe. To do this, add the following settings to repserv.ini:

[CONFIG]

cubesBuildTool = "bldcubex.exe"

and restart the reports server. Then, you will have a new rk7manex.exe manager station file. Use it for cube report generation.

It is desirable to create a new user (role) with "cubes and reports only" rights and hide all other elements in the manager station for this user (role). Then, create an icon for such a manager station with a name like Reports.

Starting from version 7.4.20.60 or higher, all cubes, that would be recalculated by the "recalculate all cubes" menu item at the manager station, can be recalculated automatically at the specified time by inserting the following command in the INI file:

RecalcAllCubesAt = "2015" (Time is specified without semicolon "HHMM") 
The value is easy to check via the web interface.

Setup of Reports Servers for Restaurant Chain

1. Example of reports servers connection in chain restaurants

Big restaurants chains (like SUBWAY, BURGER KING) use quite extensive nets consisting of multilevel connection trees of reports servers with a central reports server (numbering in hundreds sometimes). Since the data (meals, prices, composition) is the same within one chain, it is enough to make modifications in the manager station working with the upper-level server (references server), and the data synchronizes automatically with lower-level servers (intermediate), and then with local servers (in a restaurant).

Connection of reports server to an external DB is required if reports generation and viewing is needed when the manager station is connected to this reports server.

Find the connection diagram below:

In this case, the servers that will be used to view reports and process data, are subject to licensing. The quantity of stations means the number of different users that can generate reports at the same time.

Configuration example for the network of 2 restaurants and a head office

How it works:

After the common shift being closed at the cash server 1 (midserver1) of restaurant #1, the shift data is processed at the reports server 1 and the reports server of the head office.

The same is valid for the second restaurant. The closed common shift is processed by a local reports server 2, and further in the head office.

Framework

• Head Office
• primary SQL base
• manager station
• references server (= primary reports server)
• Restaurant 1
• reports server 1
• local SQL base 1
• cash server 1 (midserver1)
• Restaurant 2
• cash server 2 (midserver2)
• local SQL base 2
• reports server 2

SQL databases

• for Head office – ‘reports_HO’
• for restaurant 1 - ‘reports_rest1’
• for restaurant 2 - ‘reports_rest2’

Stations and devices

Create 2 restaurants and a cash server for each of them.

External DB settings

Create 3 settings as described in the article on connection to MS SQL, and configure the connection to corresponding reports servers (shall be created additionally). 

This shall result in 3 configurations:

1. For Head office (‘reports_HO’ database)
2. For restaurant 1 (‘reports_rest1’ database)
3. For restaurant 2 (‘reports_rest2’ database).

Reports Server Properties

INI files

For reports server 1:

[CONFIG]
Client = "repsrv_1"
RefServer = "RK7SRV_3001"
RefsBasePath = ".\BaseData1\refsdata.udb"
CheckBasePath = ".\BaseData1\check_db.udb"
ReceivedPath = ".\Received1\"
ErrorLog = "repsserv1.stk"


[NETKERN]
Protocols = tcpsoc.dll

For reports server 2:

[CONFIG]
Client = "repsrv_2"
RefServer = "RK7SRV_3001"
RefsBasePath = ".\BaseData2\refsdata.udb"
CheckBasePath = ".\BaseData2\check_db.udb"
ReceivedPath = ".\Received2\"
ErrorLog = "repsserv2.stk"

[NETKERN]
Protocols = tcpsoc.dll

The manager station connected to the references server (refserver):

[REFEDIT]
Server=RK7SRV_3001
Client=rk7man_ref

The manager station connected to reports server 1:

[REFEDIT]
Server=repsrv_1 //network name of reports server 1
Client=rk7man_rep1

Manager station connected to reports server 2:

[REFEDIT]
Server=repsrv_2 //network name of reports server 2
Client=rk7man_rep2

Employees and their rights

Add 2 employee groups:

• for reports server 1
• for reports server 2

Configure their right in properties:

• give all rights to view reports
• manager restrictions --> reports servers --> mark corresponding reports server

Set the following in employee properties:

• main --> current restaurant
• main --> account --> login and password

Using Valid SSL Certificates for Reports Server in Domain

The default certificate (for rk7.local) is placed in the BaseData\httpKeys\ folder, the default.pem file.
To use your own certificate, change its name to mylocal.pem, and place it into the same directory, then restart the reports server.

Certificate properties:

• Subj name – restaurant DNS name
• An alternative name is not needed
• A certificate shall support server authentication
• Key type – exchange or signature (default value is exchange). Exchange is a data encryption key
• A key size of 2048 is supported
• sha256 algorithm and sha256RSA hash algorithm are supported
• Key export is possible. The certificate itself shall be in PEM format. The server key shall be located there as well. See example in default .pem file
• No restrictions on crypto provider usage.

Troubleshooting

1. The error occurring while viewing a report: Cannot load cube data (cube data is empty or corrupted): Cannot open file "D:\UCS\RK7_5_1_34\bin\win\Cubes\00000022.blb". File not found.
   Solution: Find a cube corresponding to the report and recalculate it.
   
   
2. In Windows 7 and higher OS, when report generation is attempted at the manager station, the following error may occur: CubeDLL: Error reading Grid -> LICENSE_STR: Failed to set data for. The CubeDLL: error occurs while viewing the corresponding cube.
   Solution: Assign administrator rights to the Windows 7 user or run the manager station as administrator. Disable account control.
   
   
3. The error occurring during report generation: Item 0 was not found in external base config collection.
   Reason: this happens if the role has no connection to SQL, and the user attempts to run an existing report or to create a new one, generated via FR4 directly from SQL.
   
   
4. Connections limit exceeded.
   Reason: the pre-installed reports server is licensed in RK7, and you are trying to create one more reports server.
   Solution: reset the license data of an extra reports server, and create the "Setup of connection to external DB" empty string.
   
   
5. If the user login failure error occurs while implementing an internal or external base on your computer, go to Options > OLAP Reports > Reporting Servers and check if the following lines are filled correctly: 
   
   

Reason: the External Data Base Link block is filled, the Reference Logs block is filled automatically.

Solution: SQL username and password shall be the same in the External Data Base Link and Reference Logs blocks.