topic Re: System Tables not showing up in PowerBI in Data Governance

System Tables not showing up in PowerBI

Amber-26 — Wed, 08 Nov 2023 15:18:51 GMT

Hello,

I am currently working with System Tables on Unity Catalogue part. I have loaded all the schemas in the catalogue and I am using PowerBI to directly access these tables. But while connecting PowerBI to Databricks, I am not able to see System Tables.

Is there something else which needs to be configured while doing this?

Re: System Tables not showing up in PowerBI

karthik_p — Wed, 08 Nov 2023 18:06:33 GMT

@Amber-26 have you enabled them in your workspace, if some one from admin team enabled for you they should provide access to you, untill them you can not see them.

if you are admin, are you able to see systems schema --> tables (billing etc..) under catalog in your workspace

Re: System Tables not showing up in PowerBI

Amber-26 — Thu, 09 Nov 2023 08:05:52 GMT

Hi @karthik_p , yes they are already enabled in workspace. I am connecting PowerBI to Databricks workspace in Azure VM otherwise it is giving proxy issue.

Re: System Tables not showing up in PowerBI

SSundaram — Fri, 10 Nov 2023 19:57:11 GMT

You can query system tables from Power BI. If you modify the PowerQuery to use the system catalog it works. Here’s an example:
let
Source = Databricks.Catalogs("XXXX.cloud.databricks.com ", "/sql/1.0/warehouses/XXXXX", [Catalog="system", Database="billing", EnableAutomaticProxyDiscovery=null]),
#"Expanded Data" = Table.ExpandTableColumn(Source, "Data", {"account_id", "workspace_id", "record_id", "sku_name", "cloud", "usage_start_time", "usage_end_time", "usage_date", "custom_tags", "usage_unit", "usage_quantity", "usage_metadata"}, {"account_id", "workspace_id", "record_id", "sku_name", "cloud", "usage_start_time", "usage_end_time", "usage_date", "custom_tags", "usage_unit", "usage_quantity", "usage_metadata"}),
#"Removed Columns" = Table.RemoveColumns(#"Expanded Data",{"Name", "Item", "Schema", "Catalog", "Description"})
in
#"Removed Columns"

You'll need an access token or OAuth to connect.

Re: System Tables not showing up in PowerBI

Chinu — Tue, 05 Dec 2023 17:21:11 GMT

I had/have the same issue with the Tableau desktop. I'm not able to select the "Billing" schema because I don't see the "System" catalog. However, I found a workaround to resolve this issue. In databricks console, go to the "Catalog Explore" and select the schema. The upper right screen you will see "Use with BI tools". Select Open in Power BI. This will download a connection file. Open Power BI by clicking the downloaded file. This will open Power BI tool with a preselected "Billing" schema. This was what I did for Tableau. I hope this works for Power BI as well.