Data Analytics

Multicloud reporting and analytics using Google Cloud SQL and Power BI

After migrating databases to Google Cloud,  Cloud SQL developers and business users can use familiar business intelligence tools and services like Microsoft Power BI to connect to and report from Cloud SQL MySQL, PostgreSQL, and SQL Server databases.  

The ability to quickly migrate databases to GCP without having to worry about refactoring or developing new reporting and BI tools is a key capability for businesses migrating to CloudSQL. Organizations can migrate today, and then replatform databases and refactor reporting in subsequent project phases.

000_Image.jpg

The following guide demonstrates key steps to configure Power BI reporting from Cloud SQL. While your environment and requirements may vary, the design remains the same. 

To begin, create three Cloud SQL Instances, each with a Private IP address.

001_Image.jpg

After creating the database instances, create a Windows VM in the same VPC as the Cloud SQL instances. Install and configure the Power BI Gateway on this VM along with the required ODBC connectors.

Download and Install ODBC Connectors for PostgreSQL and MySQL.

 Configure System DSNs for each Database connection. Examples follow. 

SQL Server

002_Image.jpg
003_Image.jpg
004_Image.jpg

PostgreSQL

005_Image.jpg

MySQL

006_Image.jpg

The traffic between the CloudSQL instance and the VM hosting the data gateway stays inside the Google VPC and is encrypted via Encryption in Transit in Google Cloud. To add an additional layer of SSL encryption for the data inside the Google VPC, configure each System DSN to use CloudSQL SSL/TLS certificates

Next, download, install, and configure the Power BI Gateway. Note that the gateway may be installed in an HA configuration. The screenshot below shows a single standalone gateway. 

On-premises data gateway configuration: Create a new on-premises data gateway

007_Image.jpg

On-premises data gateway configuration: Validate Gateway Configuration

008_Image.jpg

On-premises data gateway configuration: Review logging settings

009_Image.jpg

On-premises data gateway configuration: Review HTTPS mode

Make sure that outgoing HTTPS traffic is allowed to exit from the VPC.

010_Image.jpg

Next, download and open Power BI Desktop. Log into Power BI and select “Manage gateways” to configure data sources.

011_Image.jpg

Add data sources for each instance, and then test the data source connections. In the example below a data source is added for each CloudSQL instance.

012_Image.jpg

Load test data into each database instance (optional). In the example below a simple table containing demo data is created in each source database.

013_Image.jpg

Launch Power BI desktop and log in. Next, add data sources and create a report. Select “Get data” and add ODBC connections for CloudSQL SQL Server, PostgreSQL and MySQL, then create a sample report with data from each instance.

014_Image.jpg

Using the Power BI publish feature, publish the report to the Power BI service. Once the report and data sources are published, update the data sources in the Power BI workspace to point to the data gateway data sources.

015_Image.jpg

Map the datasets to the CloudSQL database gateway connections.

016_Image.jpg

Optional: Schedule a refresh time.

017_Image.jpg

To perform an end-to-end test, update the test data and refresh the reports to view the changes.

018_Image.jpg

Use the Publish to - Power BI Service to publish Power BI reports that were developed with Power BI Report Builder to a workspace (Power BI Premium Capacity is required).

019_Image.jpg

Conclusion

Hopefully this blog was helpful in demonstrating how Power BI reports and dashboards can connect to Google Cloud SQL Databases using the Power BI Gateway. You can also use the Power BI Gateway to connect to your Big Query datasets and databases running on GCE VMs. For more information on Cloud SQL, please visit Google Cloud Platform Cloud SQL.