Skip to main content

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:

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 create 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 create 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 doesn't 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
  • This example includes WarehouseName, DatabaseName, SchemaName, and TableName as parameters, which aren't 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 doesn't 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
  • This example includes #"DatabaseName" as parameter name, which isn't supported in the query parser.

Variable names​

While using parameters in Power Query expressions, make sure that the variable names don't match the parameter names. For example, Atlan's query parser doesn't 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 this example, DatabaseName is used as both a parameter name and variable name, which isn't 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 aren't 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​

For column-level lineage generation, Atlan's custom query parser currently supports parsing expressions with the following Table Functions:

Power query functions​

Upstream lineage isn't supported when the data source expression involves the use of certain built-in Power Query functions. The following functions aren't supported:

  • Csv.Document
  • DateTime.LocalNow
  • Excel.Workbook
  • Folder.Files
  • Json.Document
  • List.Dates
  • SharePoint.Files
  • SharePoint.Tables
  • UsageMetricsDataConnector.GetMetricsData
  • Xml.Tables