The Google BigQuery (Microsoft Entra ID) connector allows you to query BigQuery data with Power BI using your Microsoft credential. The connector relies on Google Workforce Identity Federation to federate your Microsoft credential into a Google credential.

This blog shows how to setup the connector using Terraform. Hereby, it complements the console-based setup guide.

Find the full example on GitHub

Query BigQuery with Power BI

Your user signs in to Power BI and opens a report with a BigQuery-based model on it. Power BI automatically exchanges your users Microsoft credential for a Google Cloud credential. Finally, Power BI queries the model and your user sees the data.

Power BI knows how to do this, because the Semantic model is associated with the Google Workforce Identity Federation pool provider, the Googla quota project and the BigQuery dataset.

Create the supporting infrastructure

First, the Workforce Identity Pool is associated with your Microsoft Entra ID tenant.

resource "google_iam_workforce_pool" "example_power_bi" {
  parent            = var.organization_id
  location          = "global"
  workforce_pool_id = "example-pbi"
}

resource "google_iam_workforce_pool_provider" "example_power_bi" {
  workforce_pool_id = google_iam_workforce_pool.example_power_bi.workforce_pool_id
  location          = "global"
  provider_id       = "aad"

  attribute_mapping = {
    "google.subject"      = "assertion.sub"    # Use 'sub' to bind to id composed of application and directory
    "google.groups"       = "assertion.groups" # Store 'groups' for groups IAM expressions
  }

  oidc {
    issuer_uri = "https://sts.windows.net/${var.tenant_id}/"
    client_id  = "https://analysis.windows.net/powerbi/connector/GoogleBigQuery"
    web_sso_config {
      response_type             = "ID_TOKEN"
      assertion_claims_behavior = "ONLY_ID_TOKEN_CLAIMS"
    }
  }

  extra_attributes_oauth2_client {
    issuer_uri = "https://login.microsoftonline.com/${var.tenant_id}/v2.0/"
    client_id  = azuread_application.power_bi_connector_bigquery_aad.client_id
    client_secret {
      value {
        plain_text = azuread_application_password.power_bi_connector_bigquery_aad.value
      }
    }
    attributes_type = "AZURE_AD_GROUPS_MAIL"
  }
}

Second, an Entra ID Application is registrered to allow Google Cloud to read (mail-enabled security-) Group memberships.

resource "azuread_application" "power_bi_connector_bigquery_aad" {
  display_name     = "PowerBI BigQuery SSO connector"
  owners           = [data.azuread_client_config.current.object_id]
  ..
}

resource "azuread_service_principal" "power_bi_connector_bigquery_aad" {
  client_id                    = azuread_application.power_bi_connector_bigquery_aad.client_id
  app_role_assignment_required = false
  owners                       = [data.azuread_client_config.current.object_id]
}

resource "azuread_app_role_assignment" "power_bi_connector_bigquery_aad" {
  for_each = toset([
    "GroupMember.Read.All",
    "User.ReadBasic.All"
  ])

  principal_object_id = azuread_service_principal.power_bi_connector_bigquery_aad.object_id
  resource_object_id  = data.azuread_service_principal.msgraph.object_id
  app_role_id         = data.azuread_service_principal.msgraph.app_role_ids[each.key]
}

Third, the federated credentials are granted permissions to run queries.

resource "google_project_iam_member" "example_power_bi_serviceusage_serviceusageconsumer" {
  project = var.project_id
  role    = "roles/serviceusage.serviceUsageConsumer"
  member  = "principalSet://iam.googleapis.com/${google_iam_workforce_pool.example_power_bi.name}/*"
}

resource "google_project_iam_member" "example_power_bi_bigquery_jobuser" {
  project = var.project_id
  role    = "roles/bigquery.jobUser"
  member  = "principalSet://iam.googleapis.com/${google_iam_workforce_pool.example_power_bi.name}/*"
}

Finally, ensure the required APIs are enabled to run BigQuery queries.

resource "google_project_service" "power_bi_connector_bigquery_aad" {
  for_each = toset([
    "bigquery.googleapis.com",
    "cloudresourcemanager.googleapis.com",
    "bigquerystorage.googleapis.com"
  ])

  project = var.project_id
  service = each.key
}

Expose the required datasets

Grant the federated users acess to the appropriate datasets, tables and so forth.

To allow all federated users to view a dataset, use the following grant.

resource "google_bigquery_dataset_iam_member" "example1_viewer_all_federated_users" {
  dataset_id = google_bigquery_dataset.example_power_bi1.dataset_id
  role       = "roles/bigquery.dataViewer"
  member     = "principalSet://iam.googleapis.com/${google_iam_workforce_pool.example_power_bi.name}/*"
}

To allow the management@xebia.com group to view a dataset, use the following grant.

resource "google_bigquery_dataset_iam_member" "example2_viewer_group_users" {
  dataset_id = google_bigquery_dataset.example_power_bi2.dataset_id
  role       = "roles/bigquery.dataViewer"
  member     = "principalSet://iam.googleapis.com/${google_iam_workforce_pool.example_power_bi.name}/group/management@xebia.com"
}

Configure the Semantic model

Use the Get Data-button to create a Google BigQuery (Microsoft Entra ID)-based model.

First, configure the Google Quota project as billing project and the Workforce Identity Federation Pool Provider id as audience.

project: your-project-id
audience: //iam.googleapis.com/locations/global/workforcePools/example-pbi/providers/aad

Finally, select the appropriate BigQuery dataset to expose your data.

Conclusion

Google BigQuery data has become easier to consume from Power BI. Users simply publish the relevant Semantic models and the connector does the rest.

Kick-start your usage of the connector using our example

Image by Peter H from Pixabay