Using Microsoft Power BI for ClickHouse
Power BI doesn't support direct connection with ClickHouse, so installing ODBC Driver is an ideal solution to bring data to Power BI from ClickHouse database server within no time.
1. About Power BI
Microsoft's Power BI is a business analytics service that enables us to connect to single or multiple data sources, transform the connected raw data into eye-catching visuals, and share insights throughout an organization. It also allows us to embed the report into our application or website and an Adhoc report generation feature, which helps analyze the data. See our introduction document about Power BI
2. Connect ClickHouse to Power BI
1. Installing ClickHouse ODBC Driver
Download and install the latest installation package from their GitHub repository
2. Run ODBC and configure the connection with ClickHouse
ODBC uses a per-configured connection to databases called Data sources. You need to set up all the information to locate the ClickHouse server and authenticate. The data source is the name you can refer to in Power BI when connecting to the server.
3. Connecting to ClickHouse from Power BI
The final step is connecting ClickHouse User DSN from Power BI and fetching data. You need to run Power BI from Home tab, click Get Data, and search for ODBC source. Then select Data Source Name created in the previous step.
Press the load button, and the data will be pulled into Power BI. Now you can enjoy all Power BI features with BI for ClickHouse.
3. Power BI as a BI Tool for ClickHouse
Allow users to create interactive visuals from 30 different chart types ranging from basic to advanced.
Copy all formatting features across similar visualizations.
Uniform interface with Microsoft Office, which is familiar to users using Office tools like Excel.
Keep up with the latest real-time update with data-driven alerting and full report-sharing options.
View personalized dashboards and reports from anywhere with Power BI Mobile, offering optimized apps for Windows Mobile, iOS, and Google Play.
Provide a Natural Language Q & A feature for users to ask questions in natural language, and answers get data and information in the Power BI system.
Some possible limitations:
Still require a lot of mouse-clicking when users edit and config charts through icons and drop-down menus.
Unable to handle complex relationships between tables, like more than one link between tables, Power BI might not handle them well.
Not provide many options to configure your visualizations per your requirements.
Crowded and bulky user interface
Take a little more than usual time or even hangs while processing millions of rows and columns of data.
4. Build a visualization with Power BI
1. Create a visualization by selecting a field from the Fields pane.
Start with a numeric field like SalesFact > Sales $. Power BI creates a column chart with a single column.
2. Change the type of visualization
Change the visualization to a column chart by selecting the Stacked column chart icon.
Looking for another BI tool for ClickHouse?
Rocket.BI is a powerful business intelligence tool for data discovery, exploration, and visualization. Since is natively integrated with ClickHouse, now you can easily perform BI for ClickHouse within some simple steps. Discover more here
Some notable features of Rocket.BI include:
Ad hoc Analysis
Drag-n-drop Chart builder
Deep-dive on Reporting
Monitor users' activities
Sharing & Collaboration
Did you know? Data Insider Rocket.BI is providing free trials with unlimited features, integrations, and visualisation capabilities. Visit https://www.datainsider.co/register to sign up for a free trial and experience full accessibility.