Welcome to the Power BI for Snowflake CodeLab. The main purpose of this lab is to learn how to convert a Power BI report pointing to an SQL database to a Snowflake database using the SnowConvert for SQL tool and do some manual changes on the Power BI report.

What you'll learn

In this code lab you will learn:

Prerequisites

Sample Report

Download the file located on this repo.

Open Report

When you open the report, you'll be prompted with a message stating that the file might be a potential security risk. Click Ok on this message to continue. This message appears when you're opening a report that contains connections to multiple sources.

Initial Security Prompt

Afterwards, you'll be prompted to input database credentials. Since the connections used on this report were used using local resources, you don't have access to them. Simply click Cancel on both prompts and we'll update these connections later according to your local SQL Server connection.

Source Credentials Prompt

In this part, we're going to configure the datasets. This sample report contains 2 datasets and 2 visualization sheets.

alt-text-here

Dataset Sales(View)

In this section we're going to configure the Sales Table to connect to your local SQL Server.

  1. Right click on the dataset and select the option Edit query.

alt-text-here

  1. In the new window you will be able to see the option Advanced Editor. Click that option to check all the configuration settings for this dataset.

alt-text-here

  1. On the Advanced Editor option you can confirm the SQL server name, the database, the name of the view and the schema for the view.

alt-text-here

NOTE for this example:

  1. Don't close the Window.

Dataset Employees (Stored procedure)

Now we're going to modify the Employees table.

  1. Click the Employees button on the left panel to change table.

Employee Table

  1. Click the Advanced Editor button.
  2. On the Advanced Editor option you can confirm the SQL server name, the database, the name of the store procedure with corresponding schema. Modify the values according to your SQL Server instance.

alt-text-here

NOTE for this example:

  1. Click Done to finish.
  2. Click the Close & Apply button to refresh the data on the report.

Close & Apply SQL

  1. You should now see the charts on the report.

Employees

Employee Charts

Sales

Sales Charts

After the sql queries and views have been converted using SnowConvert tool, we will update some of the connections and data sources configurations in Power BI. For more information regarding the SnowConvert tool, please visit SnowConvert information.

For this next section, we're going to migrate the report to connect to Snowflake using an ODBC connection.

Snowflake ODBC Connection

Our next step is to create an ODBC connection for Snowflake. To do this you first need to install the ODBC driver on your computer.

  1. Please visit this link and download the drivers. Follow the instructions there for more details.

Please take into consideration that Report Builder only works with 32-bit ODBC connections, and Power BI only works with 64-bit connections, so make sure to install the version that best fits your need. If you want to complete both Power BI and SSRS labs, install both versions. There's no issues by having both installations.

  1. After the driver has been installed, create the new driver connection. To achieve this, click the Windows button and then search for ODBC. Make sure to choose the bits version that best fits your needs. You can identify them by the parenthesis at the end. If you installed both versions, it doesn't matter which one you chose, ODBC should automatically create it for both.

ODBC Drivers

  1. Click Add... to create a new connection. In here, select the Snowflake driver, as per the following image.

alt-text-here

  1. Add the connection parameters.

alt-text-here

  1. Click the Test... button. If your connection was successful, you should get a message similar to this one.

alt-text-here

Update the Power BI to connect to Snowflake

Once you created the ODBC connection to the Snowflake server and all the tables, views and stored procedures required are migrated in the database, we will be able to update the Power BI report and connect to the new objects in Snowflake.

Dataset Sales(View)

Check the Dataset sales to update it and connect to the new view in Snowflake.

  1. Right click on the dataset and select the option Edit query

alt-text-here

  1. In the new window you will be able to see the option Advanced Editor click in that option to check all the configuration settings for this dataset.

alt-text-here

  1. Now On the Advanced Editor you can update the settings with the following code. Make sure to change the values to your ODBC connection name and the database name you're using.
let
    Source = Odbc.DataSource("dsn=<your_odbc_connection_name>", [HierarchicalNavigation=true]),
    ADVENTUREWORKS_OLTP_Database = Source{[Name="<your_database>",Kind="Database"]}[Data],
    HUMANRESOURCES_Schema = ADVENTUREWORKS_OLTP_Database{[Name="HUMANRESOURCES",Kind="Schema"]}[Data],
    VPOWERBISALES_View = HUMANRESOURCES_Schema{[Name="VPOWERBISALES",Kind="View"]}[Data]
in
    VPOWERBISALES_View

alt-text-here

What we're doing here is changing the source connection from SQL Server to ODBC. The important here to note here is that we're not changing the table name, so all charts and visualizations won't be affected.

  1. Click Done button.
  2. You will be prompted to Edit credentials on the upper part of the screen. Click this button and input your user and password.

alt-text-here

  1. Confirm the data was loaded.

Dataset Employees(Stored procedure)

To run a Snowflake stored procedure in Power BI we need to modify the Power Query code to call a stored procedure we migrated previously.

  1. Select the Employees table on the left side panel.
  2. Go to the Advanced Editor option one more time and type the following code.
let
    SP_Source = Odbc.Query("dsn=<your_odbc_connection>", "CALL  HumanResources.SP_PowerBIEmployee()"),
    sp_result = SP_Source[SP_POWERBIEMPLOYEE],
    Source = 
    if Text.From(List.First(sp_result)) <> null then 
        Odbc.Query(
            "dsn=SnowFlake"
            ,"SELECT * FROM " & Text.From(List.First(sp_result)) &";"
        ) 
    else 
        null
in
    Source

What this code does is execute the Stored Procedure and gets the value return from it, which in this case it would be the temporary table that was created in the Stored Procedure. Afterwards, we build the query using the temporary table from the return value and query it.

alt-text-here

  1. Click Done and validate that the data loaded correctly.
  2. Click Close and Apply and refresh the report.
  3. Your report should look like this.

alt-text-here

  1. Finally save the report with all the changes.