Related pages: 🇫🇷 Reporting BI

1. BI reports

Pwic.wiki's standard user interface offers basic listings and pivots in the special area of your projects. However, you might be interested to access directly to the internal tables of Pwic.wiki to make use of the extended capabilities of any external BI tool.

1.1. API

The first possibility to access the internal data is to use the API (Application Programming Interface). It is a collection of end-points that you can reach by HTTP requests to perform operations in read or write mode. The website interacts with it when you write a page or configure an option for example. The API is not used everywhere by design, for example when a page is rendered.

The API is a custom implementation with arbitrary names, parameters and response codes. You need to read the online documentation to know more about the available interfaces. First, you must connect through /api/login and read the value of the returned encrypted cookie. Then you can call the other end-points to perform a particular action. Some code samples are available in the path static/api/.

The API is safe in the sense that all the logic is managed: you just have to map the right values with the right fields. The end-point /api/project/get is certainly the most useful to analyze the data.

1.2. ODBC

Open Database Connectivity (ODBC) is a wrapper to integrate a database with another application at the level of... the tables! The logic is then not managed.

First, the database file pwic.sqlite MUST NEVER BE SHARED, more especially if you use the option keep_sessions. By not respecting this rule, as per the current design of the dependent module aiohttp-session, you would compromise the integrity of the entire application.

Second, Pwic.wiki is running the database SQLite. It is a flat file that cannot be read remotely. The file is available locally and its access is protected by the file system. You must not put the database in a shared folder (refer to the first rule).

Third, you need an SQLite ODBC driver to read the database. The one available at ch-werner.de is good.

Fourth, you define a DSN source by running the command %windir%\syswow64\odbcad32.exe (32 bits) or %windir%\system32\odbcad32.exe (64 bits). You add an entry and provide the file name of the database in the parameters:

Finally, in your reporting tool, you select this connection in the menu related to ODBC: the data from Pwic.wiki become visible in raw format and you can report on it.

Remind that this procedure is strongly discouraged because it can put your data at an unbearable risk. This is no way to prevent the use of an ODBC driver on the database. Alternatively, you can use SQLiteBrowser.org that requires no particular setup.

1.3. OData

Open Data Protocol (OData) is the most recent way to report into BI tools. Schematically, it is a safe mix of API and ODBC: it offers a classical way to access the tables remotely but it implements many features to secure the data and deliver them appropriately.

Pwic.wiki implements a partial version of OData V4 because there are some restrictions:

• the data are in read only
• you can't filter/limit the data with a dollar argument
• the service is not integrated well with Power BI Desktop for an unknown technical reason
• the authentication is done at the lowest path via a Basic challenge (no OAuth)
• some columns of the tables are not displayed on purpose

First, you must define the option odata by running the command: ./pa set-env odata X (Linux) or pa set-env odata X (Windows). The activation of the following end-points is immediate:

• /api/odata
• /api/odata/$metadata
• /api/odata/table (replace table by its real name)

Then, add the paths to your BI tool. We will use Power BI Desktop that is free under some conditions.

You need to authenticate with your credentials (OAuth is not supported). Create a separate user account with the profile Reader if you need a password, but remind that the credentials may be saved in the PBIX file! Make sure that you select the longest URL address in the drop-down list box.

An overview of the selected table is displayed, and you can import/transform it. Repeat the operation as many times as there are tables to load. Their list is given in the description of the OData interface.

Power BI is not able to load the service from its root path, stating that the encoding is not supported. If you have any idea, feel free to contact us to get rid of this annoying error.

At the end, you should be able to model the tables and report on it with your own criteria. Hit the button Refresh at the top of the application to collect the entire data.

Attached documents

Revision #1 was last modified by gitbra
on 2023-12-11 at 00:00:00 — 03e7dfb3e82bd091