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.
- connection string — for example,
- 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
, andTableName
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.