System Tables not showing up in PowerBI

[Amber-26]

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?

[karthik_p]

@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

[Amber-26]

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.

[SSundaram]

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. 

[Chinu]

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.