Warning:
This feature is currently in beta and is subject to change. If you would like to test it, please contact us at ask@celus.net.
For users who want to analyze the data stored in CELUS themselves, CELUS offers an export into a dedicated analytical database.
The data is exported regularly into a powerful dedicated analytical database ClickHouse running on our infrastructure. It is a suitable source for ingestion into Power BI, Tableau, Superset, Metabase and other analytical tools.
The export is updated weekly and contains all usage data and tag information from CELUS. Other data, such as information about harvesting success, configured credentials, etc. is not included.
Each report from your CELUS instance is stored in a separate table in the database. For example, if you are harvesting TR, DR and PR reports, you will have three tables in the database with the names TR, DR and PR. COUNTER 5.1 reports have their own tables with the 51 suffix, e.g. TR51. Non-COUNTER reports are exported as well.
Each table has the same basic structure, with some report-specific columns, you will find the detail in our documentation.
Once you receive a message from us that your database is ready, you will be able to connect your analytical database to Power BI and other BI tools.
In the left side menu / Analytics / Analytical database…
… you will find the database connection information.
The export updates weekly, but you can manually start an immediate synchronization by clicking the green button.
Tip: For easy testing and data exploration, click any report name to open the Clickhouse web interface. This displays the table structure and allows you to run queries directly on your data.
Once you receive a message from us that your database is ready, you can connect your analytical database to Power BI. In Power BI Desktop, select Get data from another source
Choose ClickHouse as a data source and click on Connect
Fill in the information from CELUS and choose Data Connectivity mode Import (data stored in PowerBI file) or DirectQuery (data source remains in Clickhouse).
Then you will be prompted to enter User name and Password, which you will retrieve in CELUS in Analytical database
…and enter in Power BI to Connect to your database.
Thereafter, you can work with the data in PowerBI, choosing which reports you want to work with, preview, Load or Transform Data to adjust the data in Power Query Editor, and create your visuals.
Please notice that your data will probably be large, and if so, it will not be effective to load it all directly into PowerBI. We recommend adjusting your data set in Power Query Editor before starting to create visualizations.
If you encounter problems with the connection between the analytical database and Power BI desktop, one of the most common problems is the absence of ClickHouse ODBC driver on your computer. It can be downloaded from Releases · ClickHouse/clickhouse-odbc.
