Snowflake integration with Power BI tool

Reading Time: 4 minutes

Snowflake is a cloud-based Data Warehouse solution provided as a Saas (Software-as-a-Service) It also has a unique architecture that enables users to just create tables and start querying data with very less administration or DBA activities needed.

Advantages:
  • There’s no virtual or physical hardware you need to take care of. There’s no software to install, and the Snowflake team takes care of maintaining the system. You also get updates of the latest version of the software. 
What is Power BI ?

Power BI is a Data Visualization and Business Intelligence tool that converts data from different data sources to interactive dashboards and BI reports. Power BI gives you a platform to be productive and creative with the reports and analytics. 

Working with Power BI:

  • Connecting to your data
  • The preformatting of data
  • Modelling of data
  • Data visualization
  • Publishing data reports
Power BI Desktop

Power BI Desktop, you can access a wide range of data sources, consolidate data from multiple sources, transform and enhance the data, and build reports that utilize the data.

The features in Power BI Desktop are organized into three views that you access from the navigation pane at the left side of the main window:

  • Report view
  • Data view
  • Relationship view

Report view: A canvas for building and viewing reports based on the datasets defined in Data view.

Data view:  Defined datasets based on data retrieved from one or more data sources. Data view offers limited transformation features, with many more capabilities available through Query Editor, which opens in a separate window.

Relationships view: Identified relationships between the datasets defined in Data view. When possible, Power BI Desktop is able to find the relationships automatically, but you can also define them manually.

Connecting to Data in Power BI Desktop

In Power BI Desktop there are wide range of data sources which are as follows:

All: Every data source type available through Power BI

File: Source files eg. Excel, CSV, XML, or JSON.

Database: Database systems eg. SQL Server

Azure: Azure services such as SQL Database,Snowflake

Online Services: Non-Azure services such as Google Analytics.

Integration of Power BI with Snowflake:

Snowflake is a cloud-native elastic data warehouse service that makes it easy to bring together data from disparate sources and make it available to all users and systems that need to analyse it. With the new Power BI, users can import data from Snowflake, combine it with data from other sources, and build rich data visuals as part of their Power BI reports.

Steps to integrate Power BI with snowflake:
  1. Download ODBC driver in snowflake.

https://docs.snowflake.com/en/user-guide/odbc-download.html

2. Open Power BI and select Get data ,choose snowflake.  

3. Provide the server and warehouse name.

 4. Choosing the Connection mode:

Direct Query is a type of connection in Power BI which does not load data into the Power BI model. Direct Query means Power BI is directly connected to the data source.

Import Data loads data into memory. 

Advantages of using Direct query:

  • Large Scale Dataset
  • No need for data refresh

Disadvantages:

  • Single Data Source only
  • Power Query transformations are limited
  • Modeling is limited
  • Lower speed of the report

5.Provide the credentials for Snowflake and click on connect.

6. Choose the database, we want to work on and can choose to directly open the query editor to perform queries and select transform data.

7. You will have the query editor with the data base and now you can perform all the queries.

And we can perform different queries and we can check its status in history of snowflake.

Different queries being run and the comparison between running directly on snowflake and running through Power BI is given below:

We can also run the same queries on snowflake query workspace and can compare the query run time.

References:

https://docs.microsoft.com/en-us/power-bi/connect-data/service-connect-snowflake


Knoldus-blog-footer-image

Written by 

Bhavya is CTO at Knoldus Inc. with 16+ years of experience. He is a Java & Scala expert and experienced in managing large customers. He is currently focused on Bigdata and Reactive Stack. Technology and process improvements have been a forte of Bhavya and he has worked on varied technology stack starting from COBOL, Mainframe, JAVA, Scala, Dataware House, Oracle, PL/SQL Salesforce, JMS - Active MQ etc. His hobbies include reading and playing badminton.

Leave a Reply