Power BI Oracle Repointing

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 Oracle, the method in M Language that defined the connection is Oracle.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 save 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.
1. Power BI Oracle Repointing in SnowConvert AI
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_output" 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:
1. Run your migrated queries.
2. Open your Power BI report.
3. Add the Power BI parameters required: SF_SERVER_LINK, SF_DB_NAME and SF_WAREHOUSE_NAME. For more information, please review the following Power BI parameters documentation.
  Parameters in Power BI
4. Click on load and wait the report to retrieve the information.
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.

Support Insights

This version supports:

Repointing of tables, view, and embedded SQL queries.
Maintain the remaining logic steps after the connection steps in the M Language (multiple lines).
Provides parameters inside Power BI to handle information correctly for Snowflake server link, warehouse and database name.
Covers queries saved as expression (when "Enable load" property has been disable).
Renaming of columns based on related DDLs on the migration or by Power BI report references if DDLs are not provided.
Identification of views, if related DDLs are provided in the migration.
Notice that other connections from other sources rather than Oracle connections are not being edited.

Considerations:

The schema name of Oracle source connections are being used as the schema in the repointed connection. It is assumed that the Snowflake database objects were created under the same schema.
The database objects must be deployed in Snowflake before trying to open the repointed report.
If the column renaming step in the M Language is empty, it means that there were no information found in the migration context or Power BI project references to create it.
Functions and procedures are not supported in the Oracle connector from Power BI, so this cases are not supported.
All found connections related to an Oracle database will be repointed and parameters will be added.

Source Pattern Samples

Entity Repointing Case: Table

This case refers to connections that do not contain embedded SQL. This means that the user has established a connection from Power BI to a table.

Oracle Connection in the Power Query Editor

IN -> Oracle_01.sql

let
    Source = Oracle.Database("the_oracle_server", [HierarchicalNavigation=true]),
    #"C##POWERBI_USER" = Source{[Schema="C##POWERBI_USER"]}[Data],
    EMPLOYEES_B1 = #"C##POWERBI_USER"{[Name="EMPLOYEES_B"]}[Data]
in
    EMPLOYEES_B1

Snowflake Connection in the Power Query Editor

OUT -> Oracle_01.sql

let
    Source = Snowflake.Databases(SF_SERVER_LINK, SF_WAREHOUSE_NAME),
    SourceSfDb = Source{[Name=SF_DB_NAME, Kind="Database"]}[Data],
    SourceSfSchema = SourceSfDb{[Name="C##POWERBI_USER", Kind="Schema"]}[Data],
    SourceSfTbl = SourceSfSchema{[Name="EMPLOYEES_B", Kind="Table"]}[Data],
    EMPLOYEES_B1 = Table.RenameColumns(SourceSfTbl, {{ "EMPLOYEE_ID", "EMPLOYEE_ID"}, { "FIRST_NAME", "FIRST_NAME"}, { "LAST_NAME", "LAST_NAME"}, { "DEPARTMENT_ID", "DEPARTMENT_ID"}})
in
    EMPLOYEES_B1

Entity Repointing Case: View

This case refers to connections that do not contain embedded SQL. This means that the user has established a connection from Power BI to a view.

Oracle Connection in the Power Query Editor

IN -> Oracle_02.sql

let
    Source = Oracle.Database("the_oracle_server", [HierarchicalNavigation=true]),
    #"C##POWERBI_USER" = Source{[Schema="C##POWERBI_USER"]}[Data],
    DEPARTMENTS_V1 = #"C##POWERBI_USER"{[Name="DEPARTMENTS_V"]}[Data]
in
    DEPARTMENTS_V1

Snowflake Connection in the Power Query Editor

OUT -> Oracle_02.sql

let
    Source = Snowflake.Databases(SF_SERVER_LINK, SF_WAREHOUSE_NAME),
    SourceSfDb = Source{[Name=SF_DB_NAME, Kind="Database"]}[Data],
    SourceSfSchema = SourceSfDb{[Name="C##POWERBI_USER", Kind="Schema"]}[Data],
    SourceSfTbl = SourceSfSchema{[Name="DEPARTMENTS_V", Kind="View"]}[Data],
    DEPARTMENTS_V1 = Table.RenameColumns(SourceSfTbl, {{ "DEPARTMENT_ID", "DEPARTMENT_ID"}, { "DEPARTMENT_NAME", "DEPARTMENT_NAME"}})
in
    DEPARTMENTS_V1

Embedded SQL Case

This case refers to connections that contains embedded SQL inside of them. This sample show a simple query but SnowConvert covers a range of more larger scenarios. Besides, there may be warning messages knows as EWI- PRF - FDM depending on the migrated query. This will help the user identifies patterns that needs extra attention.

Oracle Connection in the Power Query Editor

IN -> Oracle_03.sql

let
    Source = Oracle.Database("the_oracle_server", [HierarchicalNavigation=true, Query="SELECT * FROM DEPARTMENTS_V"])
in
    Source

Snowflake Connection in the Power Query Editor

OUT -> Oracle_03.sql

let
    SfSource = Value.NativeQuery(Snowflake.Databases(SF_SERVER_LINK,SF_WAREHOUSE_NAME,[Implementation="2.0"]){[Name=SF_DB_NAME]}[Data], "SELECT * FROM
DEPARTMENTS_V", null, [EnableFolding=true]),
    Source = Table.RenameColumns(SfSource, {{ "DEPARTMENT_ID", "DEPARTMENT_ID"}, { "DEPARTMENT_NAME", "DEPARTMENT_NAME"}})
in
    Source

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 (EWI - PRF - FDM).

PreviousETL-BI Repointing NextTransact-SQL

Last updated 6 days ago