SnowflakeDocumentation Home

Power BI Repointing

Applies to

Description

The Power BI repointing is a feature that provides an easy way to redefine the connections from the M language in the Power Query Editor. This means that the connection parameters will be redefined to point to the Snowflake migration database context. For SQL Server, the method in M Language that defined the conenction is Sql.Database(...). In Snowflake, there is a connector that depends on some other parameters and the main connection is defined by Snowflake.Database(...) method.

How to use it

Notice that this feature only supports Power BI reports with the extension .pbit. Before starting, please transform your reports to .pbit extension.

  1. Locate the Power BI reports in a folder and apply restrictively the extension of a template .pbit.

  2. In the SnowConvert app, add the path of the Power BI projects in the ETL-BI section.

    Sample of adding a path for Power BI repointing

  3. Continue the migration steps as normally.

  4. Reports: In the output folder you can review a ETL-BI report about the repointing transformation.

  5. Access: In the output folder, you can review the "repointing_folder" to access the Power BI repointing reports.

  6. Execution: Before opening your reports, it is important to run all your migrated files in you Snowflake account. Otherwise, the object will not be retrieve because do not exist in the Snowflake account. So, follow the next steps:

    1. Run your migrated queries.

    2. Open your Power BI report.

    3. Add the Power BI parameters required: SF_SNOWFLAKE_SERVER and SF_WAREHOUSE_NAME. For more information, please review the following Power BI paramters documentation.

    4. Click on load and wait the report to retrieve the infromation.

    5. Provide your account credential to Power BI app. In the case that you have Two-factor authentication, you may be asked to accept every request of connection from Power BI. Be aware that, there may be several pop-ups for authorization.

    6. Review the ETL-BI report and fix every data entity with issues.

    7. Refresh the data.

Source Pattern Samples

This section will explain the cases currently addressed by SnowConvert.

Simple Entity Repointing Case

Even for a simple connection to a table from Power BI requires a lot of transformation to be used with the implicit Power BI connector fro Snowflake. In thsi case notice how SnowConvert adds new information variables as database, schema, and call the source table with the implicit type (It can also be a view).

Also, SnowConvert generates a mapping between the columns to match the text case with the database migration context or, if this is not possible, with the Power BI report internal information.

Transact-SQL Connection in the Power Query Editor

IN -> SqlServer_01.sql
let
    Source = Sql.Database("111.11.111.111\BIFROST", "LibraryDatabase"),
    dbo_Authors = Source{[Schema="dbo",Item="Authors"]}[Data]
in
    dbo_Authors

Snowflake SQL Connection in the Power Query Editor

OUT -> SqlServer_01.sql
let
    Source = Snowflake.Databases(SF_SERVER_LINK, SF_WAREHOUSE_NAME),
    SourceSfDb = Source{[Name="LIBRARYDATABASE", Kind="Database"]}[Data],
    SourceSfSchema = SourceSfDb{[Name="DBO", Kind="Schema"]}[Data],
    SourceSfTbl = SourceSfSchema{[Name="BOOKS", Kind="Table"]}[Data],
    dbo_Books = Table.RenameColumns(SourceSfTbl, {{ "BOOKID", "BookID"}, { "TITLE", "Title"}, { "AUTHORID", "AuthorID"}, { "PUBLICATIONYEAR", "PublicationYear"}})
in
    dbo_Books

Simple Entity With Multiple Lines Repointing Case

In this case "Filtered Rows" is an aditional step into the logic of the query. In the repointing version, the additional logic is preserved as it is.

Transact-SQL Connection in the Power Query Editor

IN -> SqlServer_01.sql
let
  Source = Sql.Database("11.111.11.1", "mytestdb"),
  dbo_Employee = Source{[Schema="dbo",
  Item="Employee"]}[Data],
  #"Filtered Rows" = Table.SelectRows(dbo_Employee, each Text.StartsWith([name], "John"))
in
  #"Filtered Rows"

Snowflake SQL Connection in the Power Query Editor

OUT -> SqlServer_01.sql
let
  Source = Snowflake.Databases(SF_SERVER_LINK, SF_WAREHOUSE_NAME),
  SourceSfDb = Source{[Name="MYTESTDB", Kind="Database"]}[Data],
  SourceSfSchema = SourceSfDb{[Name="DBO", Kind="Schema"]}[Data],
  SourceSfTbl = SourceSfSchema{[Name="EMPLOYEE", Kind="Table"]}[Data],
  dbo_Employee = SourceSfTbl,
  #"Filtered Rows" = Table.SelectRows(dbo_Employee, each Text.StartsWith([name], "John"))
in
  #"Filtered Rows"

Embedded SQL Query Repointing Case

For the SQL queries embedded inside the entities, SnowConvert will extract, migrate and re-insert this queries. There may be warning messages that may require extra atention. In this case the warning message do not stop the query to be run in the Snowflake database.

Transact-SQL Connection in the Power Query Editor

IN -> SqlServer_01.sql
let
    Source = Sql.Database("111.11.111.111\BIFROST", "LibraryDatabase", [Query="SELECT DISTINCT#(lf)    B.Title#(lf)FROM#(lf)    DBO.Books AS B#(lf)JOIN#(lf)    DBO.Authors AS A ON B.AuthorID = A.AuthorID#(lf)JOIN#(lf)    DBO.BookGenres AS BG ON B.BookID = BG.BookID#(lf)JOIN#(lf)    DBO.Genres AS G ON BG.GenreID = G.GenreID#(lf)WHERE#(lf)    A.Nationality = 'American' AND G.Origin = 'USA'#(lf)ORDER BY#(lf)    B.Title;", CreateNavigationProperties=false])
in
    Source

Snowflake SQL Connection in the Power Query Editor

OUT -> SqlServer_01.sql
let
    SfSource = Value.NativeQuery(Snowflake.Databases(SF_SERVER_LINK,SF_WAREHOUSE_NAME,[Implementation="2.0"]){[Name="LIBRARYDATABASE"]}[Data], "--** SSC-FDM-0007 - MISSING DEPENDENT OBJECTS ""DBO.Books"", ""DBO.Authors"", ""DBO.BookGenres"", ""DBO.Genres"" **
SELECT DISTINCT
    B.Title
FROM
    DBO.Books AS B
    JOIN
        DBO.Authors AS A
        ON B.AuthorID = A.AuthorID
    JOIN
        DBO.BookGenres AS BG
        ON B.BookID = BG.BookID
    JOIN
        DBO.Genres AS G
        ON BG.GenreID = G.GenreID
WHERE
    A.Nationality = 'American' AND G.Origin = 'USA'
ORDER BY B.Title", null, [EnableFolding=true]),
    Source = Table.RenameColumns(SfSource, {{ "TITLE", "Title"}})
in
    Source

Embedded SQL Query With Multiple Lines Repointing Case

This case showcase the connection with SQL queries and multiple lines of logic after the connection logic.

Transact-SQL Connection in the Power Query Editor

IN -> SqlServer_01.sql
let
  Source = Sql.Database("11.111.11.1", "mytestdb", [Query="SELECT DISTINCT#(lf)    P.ProductName,#(lf)    P.Category,#(lf)    P.StockQuantity#(lf)FROM#(lf)    Products AS P#(lf)WHERE#(lf)    P.StockQuantity > 0#(lf)ORDER BY#(lf)    P.Category ASC;"]),
  #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Cards"))
in
 #"Filtered Rows"

Snowflake SQL Connection in the Power Query Editor

OUT -> SqlServer_01.sql
let
  Source = Value.NativeQuery(Snowflake.Databases(SF_SERVER_LINK,SF_WAREHOUSE_NAME,[Implementation="2.0"]){[Name="MYTESTDB"]}[Data], "--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT ""Products"" **
SELECT DISTINCT
    P.ProductName,
    P.Category,
    P.StockQuantity
FROM
    Products AS P
WHERE
    P.StockQuantity > 0
ORDER BY P.Category ASC", null, [EnableFolding=true]),
  #"Filtered Rows" = Table.SelectRows(Source, each Text.StartsWith([Name], "Cards"))
in
  #"Filtered Rows"

Embedded SQL Query With Column Renaming Repointing Case

At the moment, column renaing for SQL queries cases are only applied if the internal infromation of the provided Power BI report contains this information.

Transact-SQL Connection in the Power Query Editor

IN -> SqlServer_01.sql
let
    Source = Sql.Database("111.11.111.111\BIFROST", "SalesSampleDB", [Query="SELECT DISTINCT#(lf)    P.ProductName,#(lf)    P.Category,#(lf)    P.StockQuantity#(lf)FROM#(lf)    Products AS P#(lf)WHERE#(lf)    P.StockQuantity > 0#(lf)ORDER BY#(lf)    P.Category ASC;"])
in
    Source

Snowflake SQL Connection in the Power Query Editor

OUT -> SqlServer_01.sql
let
    SfSource = Value.NativeQuery(Snowflake.Databases(SF_SERVER_LINK,SF_WAREHOUSE_NAME,[Implementation="2.0"]){[Name="SALESSAMPLEDB"]}[Data], "--** SSC-FDM-0007 - MISSING DEPENDENT OBJECT ""Products"" **
SELECT DISTINCT
    P.ProductName,
    P.Category,
    P.StockQuantity
FROM
    Products AS P
WHERE
    P.StockQuantity > 0
ORDER BY P.Category ASC", null, [EnableFolding=true]),
    Source = Table.RenameColumns(SfSource, {{ "PRODUCTNAME", "ProductName"}, { "CATEGORY", "Category"}, { "STOCKQUANTITY", "StockQuantity"}})
in
    Source

Function For Entity Case Repointing Case

Currently, the functions are only supported for entities import case.

Transact-SQL Connection in the Power Query Editor

IN -> SqlServer_01.sql
let
  Source = Sql.Database("11.111.11.1", "mytestdb"),
  dbo_MultiParam = Source{[Schema="dbo",Item="MultiParam"]}[Data],
  #"Invoked Functiondbo_MultiParam1" = dbo_MultiParam(1,"HELLO")
in
  #"Invoked Functiondbo_MultiParam1"

Snowflake SQL Connection in the Power Query Editor

OUT -> SqlServer_01.sql
let
  Source = Snowflake.Databases(SF_SERVER_LINK, SF_WAREHOUSE_NAME),
  SourceSfDb = Source{[Name="MYTESTDB", Kind="Database"]}[Data],
  SourceSfFunc = (x, y) => Value.NativeQuery(SourceSfDb, "SELECT DBO.MultiParam(" & Text.From(x) & "," &  (if y = null then null else ("'" & y & "'"))  & ")"),
  dbo_MultiParam = SourceSfFunc,
  #"Invoked Functiondbo_MultiParam1" = dbo_MultiParam(1,"HELLO")
in
  #"Invoked Functiondbo_MultiParam1"

Troubleshooting

  • If the user do not enter the requested global parameters after repointing, the load of objects is not triggered by Power BI, so make sure to add the paramerer information. If the user click on cancel and the reports do not load, then it is recommended to close and open again the report.

  • If a visualization dos not load, it may be because a column definition do not match the text case. Notice that the Snowflake connector from Power BI retrieve the entities and columns always in uppercase.

  • If you have issues with the credential cache, you can go to settings in Power BI and clear the connection to enter new credentials.

  • There may be problems with complex SQL queries after migration. This cases may require more extra work to solve warning messages from migration process.

PreviousETL-BI RepointingNextSSIS Repointing

Last updated