Power BI Teradata 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 Teradata, the method in M Language that defined the connection is Teradata.Database(...). In Snowflake, there is a connector that depends on some other parameters and the main connection is defined by Snowflake.Database(...) method. In addition, there is a limited support to ODBC.Query connector only for Teradata as a source languge in the migration. This means that the source connection parameters (of Teradata connections) will be redefine to point to the Snowflake migration database context.

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 Teradata 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, make sure that all the tables and views used by the repointed reports are already published in your 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. The DDLs must be added in the migration along with the Power BI reports in SnowConvert.
Notice that other connections to sources other than Teradata.Database and ODBC.Query connections will not be modified.

Considerations:

The source schema is used as the current repointed schema. If It changed during the migration, it must be changed.
The columns inside an embeded query are not being evaluated at the moment. Please, review the column renaming of queries carefully.
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 Teradata native connector from Power BI, so this cases are not supported.
All found connections related to an Teradata database (Teradata.Database or ODBC.Query connectors) 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.

Teradata Connection in the Power Query Editor

IN -> Teradata_01.sql

let
    Source = Teradata.Database("the_teradata_server", [HierarchicalNavigation=true]),
    databaseTest = Source{[Schema="databaseTest"]}[Data],
    employees1 = databaseTest{[Name="employees"]}[Data]
in
    employees1

Snowflake Connection in the Power Query Editor

OUT -> Teradata_01.sql

let
    Source = Snowflake.Databases(SF_SERVER_LINK, SF_WAREHOUSE_NAME),
    SourceSfDb = Source{[Name=SF_DB_NAME, Kind="Database"]}[Data],
    SourceSfSchema = SourceSfDb{[Name="databaseTest", Kind="Schema"]}[Data],
    SourceSfTbl = SourceSfSchema{[Name="EMPLOYEES", Kind="Table"]}[Data],
    Employees1 = Table.RenameColumns(SourceSfTbl, {{ "EMPLOYEEID", "EmployeeID"}, { "FIRSTNAME", "FirstName"}, { "LASTNAME", "LastName"}, { "HIREDATE", "HireDate"}, { "SALARY", "Salary"}, { "DEPARTMENTID", "DepartmentID"}})
in
    Employees1

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.

Teradata Connection in the Power Query Editor

IN -> Teradata_02.sql

let
    Source = Teradata.Database("the_teradata_server", [HierarchicalNavigation=true]),
    databaseTest = Source{[Schema="databaseTest"]}[Data],
    EmployeeSalaryBonusView1 = databaseTest{[Name="EmployeeSalaryBonusView"]}[Data]
in
    EmployeeSalaryBonusView1

Snowflake Connection in the Power Query Editor

OUT -> Teradata_02.sql

let
    Source = Snowflake.Databases(SF_SERVER_LINK, SF_WAREHOUSE_NAME),
    SourceSfDb = Source{[Name=SF_DB_NAME, Kind="Database"]}[Data],
    SourceSfSchema = SourceSfDb{[Name="databaseTest", Kind="Schema"]}[Data],
    SourceSfTbl = SourceSfSchema{[Name="EMPLOYEESALARYBONUSVIEW", Kind="Table"]}[Data],
    EmployeeSalaryBonusView1 = Table.RenameColumns(SourceSfTbl, {{ "FIRSTNAME", "FirstName"}, { "LASTNAME", "LastName"}, { "HIREDATE", "HireDate"}})
in
    EmployeeSalaryBonusView1

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.

Teradata Connection in the Power Query Editor

IN -> Teradata_03.sql

let
    Source = Teradata.Database("the_teradata_server", [HierarchicalNavigation=true, Query="SELECT *#(lf)FROM databaseTest.employees"])
in
    Source

Snowflake Connection in the Power Query Editor

OUT -> Teradata_03.sql

let
    SfSource = Value.NativeQuery(Snowflake.Databases(SF_SERVER_LINK,SF_WAREHOUSE_NAME,[Implementation="2.0"]){[Name=SF_DB_NAME]}[Data], "SELECT * FROM databaseTest.employees", null, [EnableFolding=true]),
    Source = Table.RenameColumns(SfSource, {{ "EMPLOYEEID", "EmployeeID"}, { "FIRSTNAME", "FirstName"}, { "LASTNAME", "LastName"}, { "HIREDATE", "HireDate"}, { "SALARY", "Salary"}, { "DEPARTMENTID", "DepartmentID"}})
in
    Source

ODBC.Query Case

At the moment it is supported only ODBC.Query connector. Other connectors as ODBC.DataSource are not supported.

This case refers to connections that contains embedded SQL inside os an ODBC.Query conenctor. Notice that all conenction with ODBC.Query will be taken as Teradata source when migrating Teradata. Please, be aware of your report connection definitions.

Teradata Connection in the Power Query Editor

IN -> Teradata_04.sql

let
  Source = Odbc.Query("dsn=TERADATA_TEST", "SELECT * FROM TEST_TABLE")
in
  Source

Snowflake Connection in the Power Query Editor

OUT -> Teradata_04.sql

let
   Source = Value.NativeQuery(Snowflake.Databases(SF_SERVER_LINK,SF_WAREHOUSE_NAME,[Implementation="2.0"]){[Name=SF_DB_NAME]}[Data], "SELECT * FROM TEST_TABLE", null, [EnableFolding=true])
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 does 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 NextOracle

Last updated 1 day ago