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.
Locate the Power BI reports in a folder and apply restrictively the extension of a template .pbit.
In the SnowConvert app, add the path of the Power BI projects in the ETL-BI section.
Continue the migration steps as normally.
Reports: In the output folder you can review a ETL-BI report about the repointing transformation.
Access: In the output folder, you can review the "repointing_folder" to access the Power BI repointing reports.
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:
Run your migrated queries.
Open your Power BI report.
Add the Power BI parameters required: SF_SNOWFLAKE_SERVER and SF_WAREHOUSE_NAME. For more information, please review the following Power BI paramters documentation.
Click on load and wait the report to retrieve the infromation.
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.
Review the ETL-BI report and fix every data entity with issues.
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
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.
