Configure Power BI lineage mapping

Create a YAML file to map data sources for lineage harvesting.

Before you begin

Role required: admin

About this task

This is an optional task for harvesting lineage information. Create a YAML file and pass it using the Datasource name mapping file option when running the collector.

Set up a YAML file in the following scenarios:

Note: You can use environment variables in the file for sensitive information such as passwords.

Procedure

1. Create a YAML file named datasources.yml.

2. Configure the YAML file based on your scenario.

   • For multiple server name aliases:

     Add the following to map a host alias:

     datasources:
      - datasourceKey: "<host or data source key in Power BI source>"
       host: <my-datasource-host>
     

     For example, if your Power BI table source is:

     let Source = Snowflake.Database("host-alias.snowflakecomputing.com", "KOS_TEST"),
     PowerBiTest_Test_Table = Source{[Schema="POWERBI_TEST",Item="TEST_TABLE"]}[Data]
     in PowerBiTest_Test_Table
     

     Then the datasourceKey will be host-alias.snowflakecomputing.com. Your datasources.yml file will look like:

     datasources:
     - datasourceKey: host-alias.snowflakecomputing.com
     host: host-actual.snowflakecomputing.com
     

   • For custom SQL statements:

     Add the following for databases specified in custom SQL statements. Environment variables are supported:

     datasources:
       - datasourceKey: "<host or data source key in Power BI source>"
       OR
      - name: <data source name>
      host: <my-datasource-host>
      databaseUsername: <username> # recommend setting up env variable
      databasePassword: <password> # recommend setting up env variable
     

     Use the following option to specify which databases a datasource configuration applies to. If not provided, it’s assumed that the datasource settings apply to all databases on the given host:

     applicableDatabases:
       - <database_name>
     

     For example:

     datasources:
     - datasourceKey: "example.cpcnqsn422gx.us-east-1.rds.amazonaws.com, 1433"
      host: example.cpcnqsn422gx.us-east-1.rds.amazonaws.com
      databaseUsername: ${DB_USERNAME}
      databasePassword: ${DB_PASSWORD}
      applicableDatabases:
      - 8bank_database
     

   • For ODBC connections:

     Map the DSN to a specific database host and type. For ODBC connections, list data sources with their corresponding host and database type. If ODBC connections use Odbc.Query, specify the user name and password. If ODBC connections specify the database name, include defaultDatabaseName:

     datasources:
     - name: "Name-for-datasource"
     host: <my-datasource-host>
     databaseType: <type-of-database>
     databaseUsername: <username> # optional
     databasePassword: <password> # optional
     defaultDatabaseName: <database name> # optional
     

     The list of possible databaseTypes are: postgres, redshift, bigquery, oracle, mysql, netezza, snowflake, sqlanywhere, sqlserver, databricks, denodo. Types aren’t case-sensitive but should be a single word with no spaces.

     For example:

     datasources:
     - name: "SQL Server DSN Production"
     databaseType: sqlserver
     host: 8bank-sqlserver.cpetgx.us-east-1.rds.amazonaws.com
     

3. Add database-specific configuration options if necessary.

   JDBC properties options:

</td></tr></tbody>
</table>

    Snowflake database credentials options:

    |Option|Description|
    |------|-----------|
    |databaseUsername: $\{DB\_USERNAME\}|Required if custom SQL queries are used in Power BI database sources|
    |databasePassword: $\{DB\_PASSWORD\}|Required if SQL queries are used and if a private key isn’t used for authentication to Snowflake|
    |snowflakePrivateKeyFile: privateKeyFile|Required if SQL queries are used and if a private key is used for authentication to Snowflake|
    |snowflakePrivateKeyFilePassword: $\{privateKeyFilePassword\}|Required if SQL queries are used and if a private key is used for authentication to Snowflake|
    |snowflakeRole: role|Required if SQL queries are used|
    |snowflakeWarehouse: warehouse|Use to override warehouses used in Power BI expressions in the database connection|

    Databricks database credentials options:

    |Option|Description|
    |------|-----------|
    |databricksHttpPath|Required if the source database is Databricks|

    Oracle Autonomous Database options:

<table id="table_oracle"><thead><tr><th>

Option

</th><th>

Description

</th></tr></thead><tbody><tr><td>

oracleAutonomousDbConnectionString

</td><td>

Required if the source is an Oracle Autonomous Database. The connection string should be in the format:

BigQuery options:

|Option|Description|
|------|-----------|
|project|Required if the source is a BigQuery table|
|bigQueryCredentialJsonString|Required for supplying BigQuery credentials|

Example for BigQuery database:

```
datasources:
 - project: "project-name"
databaseType: bigquery
bigQueryCredentialJsonString: '{"key": "value","key": "value"}'

```