Interconnecting GaussDB(DWS) with Power BI
Power BI is a self-service business intelligence tool. It extracts, cleans, integrates, summarizes, analyzes, and displays data visually from various systems. Power BI handles all data analysis steps, from getting data, to cleaning, modeling, and visualizing it.
This guide shows how to install Power BI on a Windows ECS and connect it to GaussDB(DWS) using an on-premises data gateway.
The process takes about 120 minutes. The steps include:
- Making Preparations: Set up a Windows ECS and buy GaussDB(DWS).
- Installing the Power BI Environment and Software: Install the .NET Framework, Npgsql driver, and Power BI.
- Preparing GaussDB(DWS) Sample Data: On GaussDB(DWS), create a database, schema, table, and user, and grant table access to the user.
- Interconnecting GaussDB(DWS) with Power BI to Generate and Publish Reports: Connect Power BI to GaussDB(DWS), then create and publish reports.
- Adding the GaussDB(DWS) Connection Information to the Gateway List of Power BI: Synchronize data in Power BI reports with GaussDB(DWS) data in real time.
Making Preparations
- You have purchased a Windows ECS and the Windows Server is running properly. (If the existing ECS or local PC meets the requirements, you do not need to purchase an ECS separately.)
- You have purchased a GaussDB(DWS) cluster.
- The GaussDB(DWS) cluster can communicate with the ECS over the internal network. If using the public network, bind a public IP address to the GaussDB(DWS) cluster.
Installing the Power BI Environment and Software
- Log in to an ECS.
- Install the .NET Framework in the Windows operating system. This document uses .NET Framework 4.8 as an example.
After the installation is complete, the following information is displayed.
Figure 1 Installing the .NET Framework - Install the PostgreSQL driver in Windows.
- Obtain the Npgsql driver (version 4.0.10 is used as an example) installation package.
Figure 2 Npgsql v4.0.10
- During the installation, select Npgsql GAC Installation, as shown in the following figure.
Figure 3 Installing the Npgsql
After the installation is complete, the following figure is displayed.
Figure 4 Npgsql installed
- Obtain the Npgsql driver (version 4.0.10 is used as an example) installation package.
- Install the Power BI Gateway standard edition.
- Visit Power BI to download the installation program of the standard edition.
- In the gateway installation program, retain the default installation path, accept the terms of use, and click Install.
Figure 5 Installing Power BI
- Enter the email address of your Office 365 account and click Sign in.
Figure 6 Entering an email address
If you are registered with Office 365, your email might look like xxx@contoso.onmicrosoft.com.
- Select Register a new gateway on this computer. and click Next.
Figure 7 Registering a new gateway
- Enter the gateway name, which must be unique in the tenant. Do not select Add to an existing gateway cluster.
- Enter the recovery key, which is required in the recovery or mobile gateway scenario, and click Configure.
Figure 8 Entering the gateway nameAfter the installation is complete, the following figure is displayed.Figure 9 Installing Power BI
- Configure the NAT gateway.
- Log in to the NAT Gateway management console, and choose Network Connectivity > NAT Gateway > Public NAT Gateways.
- Buy a public NAT gateway. For details, see NAT Gateway Documentation.
- Click Add Rule on the right of the purchased public network NAT, click the DNAT Rules tab, click Add DNAT Rule, and set the parameters shown in the following figure.
- Click OK.
Preparing GaussDB(DWS) Sample Data
- Connect to the default GaussDB(DWS) database gaussdb and create a database named dws_test.
1
CREATE DATABASE dws_test;
- Connect to the newly created database dws_test, create a schema named dws_data, create a table named rpg_order in the schema, and insert four data records.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
CREATE SCHEMA dws_data; CREATE TABLE dws_data.dws_order ( order_id VARCHAR, order_channel VARCHAR, order_time VARCHAR, cust_code VARCHAR, pay_amount DOUBLE PRECISION, real_pay DOUBLE PRECISION ) DISTRIBUTE BY HASH (order_id); INSERT INTO dws_data.dws_order VALUES ('202306270001', 'webShop', '2023-06-27 10:00:00', 'CUST1', 1000, 1000); INSERT INTO dws_data.dws_order VALUES ('202306270002', 'webShop', '2023-06-27 11:00:00', 'CUST2', 5000, 5000); INSERT INTO dws_data.dws_order VALUES ('202307100003', 'webShop', '2023-07-10 13:00:00', 'CUST1', 3000, 3000); INSERT INTO dws_data.dws_order VALUES ('202307200004', 'webShop', '2023-07-20 14:00:00', 'CUST2', 4000, 4000);
- Check whether data is inserted into the table.
1
SELECT * FROM dws_data.dws_order;
- Create a Power BI database user. The password needs to be customized.
1
CREATE USER dws_thiru PASSWORD '{password}';
- Grant the user the permission to access the corresponding schema and table.
GRANT USAGE ON SCHEMA dws_data TO dws_thiru; GRANT SELECT ON dws_data.dws_order TO dws_thiru;
Interconnecting GaussDB(DWS) with Power BI to Generate and Publish Reports
- Ensure that you have obtained the internal network IP address and port number of GaussDB (DWS).
- Log in to the GaussDB(DWS) console. In the navigation pane, choose Dedicated Clusters > Clusters. Click the name of a cluster to go to the Cluster Information page.
- In the Connection area on the right, record the private IP address and port number.
- Log in to the Windows desktop of the ECS, double-click Power BI Desktop, and click Get Data.
- Choose Database > PostgreSQL Database and click Connect.
Figure 10 Connecting to a database
- Enter the GaussDB(DWS) connection information.
- Click OK.
- Enter the following information:
- User name: Enter the user name for connecting to the database, for example, dws_thiru.
- Password: Enter the password of dws_thiru, which is defined during creation.
Figure 12 Username and password - Click Connect to test the connection.
The connection fails, showing this error message. The connection is encrypted by default. To switch to a non-encrypted connection, see the following steps.
Figure 13 Encrypted connection error information - Click Cancel to exit.
- Return to the Power BI homepage, choose File > Options and settings, and click Data source settings.
Figure 14 Setting data sources
- Select the GaussDB(DWS) connection information, click Edit Permissions, deselect Encrypt connections, and click OK.
Figure 15 Switching to a non-encrypted connection
- Close the window for setting the data source.
- Return to the Power BI homepage and perform 3 to 7 again.
- Select the data table to be loaded after the connection is established and click Load.
Figure 16 Selecting the table to be loaded
- Go to the report creation page, click File, and click Save to save the report to the local host.
Figure 17 Saving a report
- Publish the report to the Power BI online page.
- Return to the Power BI homepage and choose File > Publish > Publish to Power BI.
- If you are not logged in, you will need to enter your email and password.
- After the login is successful, the release page is displayed. Select a workspace and click Select.
If the following information is displayed, the report is published.
Adding the GaussDB(DWS) Connection Information to the Gateway List of Power BI
GaussDB(DWS) must not be directly connected to the public network for security. This means Power BI cannot always update live report data from GaussDB(DWS). To fix this, follow these steps to add the GaussDB(DWS) connection details to Power BI's gateway list.
- Visit here, log in to the Power BI online page, enter your email address and password, and click Submit.
- On the Power BI online page, click Workspaces on the left and click the workspace where the report has been saved.
Figure 18 Selecting a workspace
- Click
in the upper right corner of the report to be set and click Settings.
Figure 19 Setting a report - Toggle Use an On-premises or VNet data gateway on, click
under Actions, and click Add to gateway.
Figure 20 Clicking Add to gateway - Enter the GaussDB(DWS) connection information, as shown in Table 1.
Table 1 Parameters for adding gateway information Parameter
Description
Example
Connection name
Name of a connection.
Enter a name.
Server
GaussDB(DWS) connection address and port
192.168.0.196:8000
Database
Name of the GaussDB(DWS) database to be connected
dws_test
Authentication method
Authentication method
Basic
Username
Username of the database
dws_thiru
Password
Database user password
Enter a password.
Encrypted connection
Whether to encrypt the connection.
Not encrypted
Privacy level
Privacy level
Organizational
Figure 21 Adding gateway information - Confirm the parameter settings and click Create. The newly created connection is displayed in the gateway list.
Figure 22 Gateway information added
- Select the new connection name pbogw_dws_cn1 and click Apply.
The gateway mapping is successful. The report has been interconnected with GaussDB(DWS).
Figure 23 Mapping succeeded
Reference
- For details about Power BI, visit the official website.
- For details about the installation process, visit the official website.
Feedback
Was this page helpful?
Provide feedbackThank you very much for your feedback. We will continue working to improve the documentation.See the reply and handling status in My Cloud VOC.
For any further questions, feel free to contact us through the chatbot.
Chatbot