cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Governance
Join discussions on data governance practices, compliance, and security within the Databricks Community. Exchange strategies and insights to ensure data integrity and regulatory compliance.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

System Tables not showing up in PowerBI

Amber-26
New Contributor III

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.

Amber26_0-1699456599708.png

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

4 REPLIES 4

karthik_p
Esteemed Contributor

@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
New Contributor III

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
Contributor

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
New Contributor III

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. 

Screenshot 2023-12-05 at 9.17.55โ€ฏAM.png

โ€ƒ

 

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group