What lineage does Atlan extract from Microsoft Power BI?

Atlan currently supports the following lineage for Microsoft Power BI:

• Lineage between Microsoft Power BI assets crawled in Atlan
• Upstream lineage to SQL warehouse assets, includes table- and column-level lineage for the following supported SQL sources:
  • Amazon Redshift
  • Databricks
  • Google BigQuery
  • Microsoft Azure Synapse Analytics
  • Microsoft SQL Server
  • MySQL
  • Oracle — Atlan generates lineage for the following methods of Oracle connectivity:
    • connection string — for example, <host_name>:<port>/<service_name>
    • connect descriptor — for example, (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<host_name>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=<service_name>)))
    • Lineage generation for TNS Alias connectivity is currently not supported.
  • SAP HANA
  • Snowflake
  • Teradata

This document helps you understand how Atlan generates lineage to upstream SQL sources for your Microsoft Power BI assets using a custom query parser, and the steps you can take while developing reports and dashboards in Microsoft Power BI to ensure seamless lineage generation.

Lineage generation

Atlan generates lineage for your Microsoft Power BI assets as follows:

• You connect to a SQL data source such as Snowflake and extract relevant SQL tables to create a table in Microsoft Power BI for analysis.
• Once the data has been loaded, you can perform Microsoft Power BI native operations as required.
• Each table created in Microsoft Power BI and part of a dataset has a Power Query expression associated with it. For example:

  let
  Source = Snowflake.Databases("example.snowflakecomputing.com","EXAMPLE_WAREHOUSE",[Role="EXAMPLE_ROLE"]),
  EXAMPLE_DB = Source{[Name="EXAMPLE_DATABASE_NAME",Kind="Database"]}[Data],
  EXAMPLE_Sch = EXAMPLE_DB{[Name="EXAMPLE_SCHEMA_NAME",Kind="Schema"]}[Data],
  EXAMPLE_Table_Var = EXAMPLE_Sch{[Name="EXAMPLE_TABLE_NAME",Kind="Table"]}[Data]
  in
  EXAMPLE_Table_Var

• Atlan retrieves the Power Query expression as a plain string from the Microsoft Power BI API response.
• Atlan's custom query parser then parses the Power Query expression to derive lineage between the upstream SQL tables and Microsoft Power BI table asset.

However, note that the Power Query expression can be modified in the Power Query Editor of the Power BI Desktop application. These modifications may involve using parameter substitutes and variable naming patterns in the Power Query expression.

These modifications may lead to inconsistent behavior in Atlan's query parser. This is because the latter is built on the standard format of a Power Query expression, without any modifications.

Limitations of query parser

To ensure seamless lineage generation, Atlan recommends the following when building tables in Microsoft Power BI.

Using parameters

The Power Query expression associated with a table can be manually modified to serve different use cases. For example, if you're creating multiple tables using data from the same database and schema at source, you may want to use dynamic M query parameters to substitute common values in Power Query expressions.

Atlan recommends the following:

• Avoid using the following words to define your parameter names:
  • Database
  • Schema
  • Table
  • View
  • Warehouse
  • Role
• Avoid including any spaces in your parameter names — for example, ( Example : Example DB )

For example, Atlan's query parser does not support the following:

let
Source = Snowflake.Databases("example.snowflakecomputing.com",WarehouseName,[Role="EXAMPLE_ROLE"]),
DatabaseName = Source{[Name=DatabaseName,Kind="Database"]}[Data],
EXAMPLE_Sch = DatabaseName{[Name=SchemaName,Kind="Schema"]}[Data],
EXAMPLE_Table_Var = EXAMPLE_Sch{[Name=TableName,Kind="Table"]}[Data]
in
EXAMPLE_Table_Var

• The above example includes WarehouseName, DatabaseName, SchemaName, and TableName as parameters, which are not supported in the query parser.

Parameter syntax

There are different formats for the syntax used in parameter names for Power Query expressions. For example, param_name, #”param_name”, or #"param name”.

Atlan recommends the following for parameter names:

• Use plain text format
• Avoid any special characters — for example, #, ", and more

For example, Atlan's query parser does not support the following:

let
Source = Snowflake.Databases("example.snowflakecomputing.com","EXAMPLE_WAREHOUSE",[Role="EXAMPLE_ROLE"]),
DatabaseName = Source{[Name=#"DatabaseName",Kind="Database"]}[Data],
EXAMPLE_Sch = DatabaseName{[Name="EXAMPLE_SCHEMA_NAME",Kind="Schema"]}[Data],
EXAMPLE_Table_Var = EXAMPLE_Sch{[Name="EXAMPLE_TABLE_NAME",Kind="Table"]}[Data]
in
EXAMPLE_Table_Var

• The above example includes #"DatabaseName" as parameter name, which is not supported in the query parser.

Variable names

While using parameters in Power Query expressions, ensure that the variable names do not match the parameter names. For example, Atlan's query parser does not support the following:

let
Source = Snowflake.Databases("example.snowflakecomputing.com","EXAMPLE_WAREHOUSE",[Role="EXAMPLE_ROLE"]),
DatabaseName = Source{[Name=DatabaseName,Kind="Database"]}[Data],
EXAMPLE_Sch = DatabaseName{[Name="EXAMPLE_SCHEMA_NAME",Kind="Schema"]}[Data],
EXAMPLE_Table_Var = EXAMPLE_Sch{[Name="EXAMPLE_TABLE_NAME",Kind="Table"]}[Data]
in
EXAMPLE_Table_Var

• In the above example, DatabaseName is used as both a parameter name and variable name, which is not supported in the query parser.

User-defined expressions

Parts of a Power Query expression can be parameterized and cross-referenced in other Power Query expressions. Atlan's query parser currently only parses standard forms of Power Query expressions, hence these user-defined expressions are not supported.

Example of a supported Power Query expression:

let
Source = Snowflake.Databases("example.snowflakecomputing.com","EXAMPLE_WAREHOUSE",[Role="EXAMPLE_ROLE"]),
EXAMPLE_DB = Source{[Name="EXAMPLE_DATABASE_NAME",Kind="Database"]}[Data],
EXAMPLE_Sch = EXAMPLE_DB{[Name="EXAMPLE_SCHEMA_NAME",Kind="Schema"]}[Data],
EXAMPLE_Table_Var = EXAMPLE_Sch{[Name="TBL_AGG_SALES_HT_POS_BEER",Kind="Table"]}[Data]
in
EXAMPLE_Table_Var

Example of an unsupported Power Query expression:

let
Source = db_source,
EXAMPLE_Sch = db_source{[Name="EXAMPLE_SCHEMA_NAME",Kind="Schema"]}[Data],
EXAMPLE_Table_Var = EXAMPLE_Sch{[Name="EXAMPLE_TABLE_NAME",Kind="Table"]}[Data]
in
EXAMPLE_Table_Var

Example of a reference expression, parameterized as db_source:

let
Source = Snowflake.Databases("example.snowflakecomputing.com","EXAMPLE_WAREHOUSE",[Role="EXAMPLE_ROLE"]),
EXAMPLE_DB = Source{[Name="EXAMPLE_DATABASE_NAME",Kind="Database"]}[Data]
in
EXAMPLE_DB

Table functions

Atlan's custom query parser currently does not support parsing expressions with table functions.