Power BI Setup Guide

Free Desktop — connect to Gold KPI tables

NoteWhen you need this

Power BI is optional and self-paced — not required for the main workshop day.

Who When
Trainees After Module 4 / dbt exercise when all 12 kpi_* Gold tables exist
Trainer Module 7 optional demo — trainer prepares before class

Hands-on lab: Exercise: Power BI Dashboard (five pages, all 12 KPIs) · DAX/theme reference: Power BI DAX & theme reference

Start on: Prerequisites · Module 7

What you need (free tier)

Per Get Power BI Desktop:

Requirement Notes
Windows PC Desktop is Windows only (system requirements)
Power BI Desktop Free download — Microsoft Store or Download Center
Gold KPI tables From Databricks, Snowflake, or dbt exercises — 12 kpi_* tables
Warehouse credentials Snowflake key-pair auth (MFA trial accounts) or Databricks PAT
Microsoft account Optional — only if you publish to My Workspace

You do not need Power BI Pro, Premium, or an on-premises data gateway for the self-paced lab (Import mode).

Install Power BI Desktop

  1. Download from Get Power BI Desktop
  2. Run the installer (or install from Microsoft Store for automatic updates)
  3. Launch the app — confirm you see Home, Insert, Modeling on the ribbon (getting started)

Connect to your Gold tables

Use one data platform — Snowflake is simplest if you completed the dbt exercise ({attendee_id}_DBT_GOLD on Snowflake).

Option A — Snowflake (recommended)

Official steps: Power Query Snowflake connector

Snowflake trial accounts enforce MFA — Power BI cannot use username/password. Set up key-pair authentication once, then connect:

ImportantSnowflake trial accounts — use key-pair auth in Power BI

Snowflake trial accounts enforce multi-factor authentication (MFA) for password logins. Power BI Desktop cannot complete an MFA prompt, so username/password fails with errors such as:

  • 390197: Multi-factor authentication is required for this account
  • Failed to parse PEM block containing the private key (if Key Pair was selected without a valid key)

Workshop path: set up key-pair authentication once, then connect from Power BI with Key Pair Auth. Both Snowflake and the Power BI Snowflake connector support this method.

Programmatic access tokens (PATs) in Snowsight are not supported for Power BI Desktop — see Snowflake partner authentication support.

Before you start

Requirement Notes
MFA enrolled Log in to Snowsight and complete MFA enrollment if prompted
OpenSSL Needed to generate keys — included with Git for Windows
ACCOUNTADMIN On a trial account you are admin — needed to run ALTER USER … SET RSA_PUBLIC_KEY
Private key file Store rsa_key.p8 on your PC only — never commit to git or share in chat

Official references: Snowflake key-pair auth · Power BI Snowflake connector · Snowflake MFA

Step 1 — Generate a key pair

Open PowerShell or Git Bash on your Windows PC. Choose a folder outside the repo (e.g. Documents\snowflake-keys).

Simplest for the workshop — no passphrase in Power BI.

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Creates:

  • rsa_key.p8 — private key (keep secret; used in Power BI)
  • rsa_key.pub — public key (registered in Snowflake)

If you prefer a passphrase on the private key:

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Enter a passphrase when prompted. You will need the same passphrase in Power BI’s Key Pair Auth dialog.

Verify the private key format — open rsa_key.p8 in Notepad. It must start with:

-----BEGIN PRIVATE KEY-----

(or -----BEGIN ENCRYPTED PRIVATE KEY----- for the encrypted option). If you see BEGIN RSA PRIVATE KEY, re-run the commands above — Power BI expects PKCS#8 format.

Step 2 — Register the public key in Snowflake

  1. Open rsa_key.pub in Notepad.
  2. Copy only the base64 text between the header and footer lines — exclude -----BEGIN PUBLIC KEY----- and -----END PUBLIC KEY-----.
  3. In Snowsight, set role to ACCOUNTADMIN and run (replace placeholders):
ALTER USER <your_snowflake_username> SET RSA_PUBLIC_KEY='MIIBIjANBgkqh...';

Paste your single-line public key string inside the quotes.

  1. Verify:
DESC USER <your_snowflake_username>;

Confirm RSA_PUBLIC_KEY_FP is populated.

Step 3 — Connect from Power BI Desktop

Official connector steps: Power Query Snowflake connector

  1. File → Options and settings → Data source settings — remove any stale Snowflake entries for your server (avoids cached username/password).

  2. Home → Get data → More… → Database → Snowflake → Connect

  3. Enter:

    • Server: full account host from Snowsight View account details (e.g. el30551.west-europe.azure.snowflakecomputing.com) — not the short .env form (el30551.west-europe.azure)
    • Warehouse: DE_WORKSHOP_WH

  4. Select Connect → choose Key Pair Auth (not username/password)

  5. Enter:

    • Username: your Snowflake username (same as Snowsight login)
    • Private key: browse to rsa_key.p8, or paste the full PEM contents including BEGIN / END lines
    • Passphrase: leave blank for unencrypted keys; enter it if you used the encrypted option

  6. In Navigator, expand DE_MASTERCLASS → open the Gold schema for your path:

    Path Schema
    dbt (recommended) {ATTENDEE_ID}_DBT_GOLD
    Snowflake SQL only {ATTENDEE_ID}_SQL_GOLD
    Snowpark only {ATTENDEE_ID}_SP_GOLD

    Select all 12 kpi_* tables (or KPI_* — same data, different casing).

  7. Choose Load (not Transform Data) → Import when prompted (Import vs DirectQuery)

TipAdvanced options (optional)

In the Snowflake connection dialog, open Advanced options and set Role name to DE_WORKSHOP_ROLE if Navigator shows no schemas or access is denied.

Snowflake connection troubleshooting

Issue What to try
390197 MFA required Use Key Pair Auth, not username/password
Failed to parse PEM block containing the private key Regenerate with the OpenSSL commands above (PKCS#8); browse to the .p8 file; clear Data source settings
Power BI June 2026 (2.155.x) auth failures Known regression — install May 2026 Desktop (2.154.x) from Get Power BI Desktop
Navigator empty / access denied Resume DE_WORKSHOP_WH; set Role name to DE_WORKSHOP_ROLE; confirm Gold schema (_DBT_GOLD, _SQL_GOLD, or _SP_GOLD — not plain _GOLD); confirm all 12 kpi_* tables exist
Wrong server format Use full *.snowflakecomputing.com host — not the short .env form (el30551.west-europe.azure)

When prompted, choose Import (Import vs DirectQuery).

Option B — Azure Databricks

Official steps: Azure Databricks connector

  1. Home → Get data → search Azure Databricks
  2. Server hostname + HTTP path from SQL Warehouses → Connection details
  3. Expand Advanced options → set Catalog to mhpdeworkshop_databricks_2026
  4. Personal Access Token (same PAT as in .env)
  5. Navigator → {attendee_id}_gold → kpi_* tables → Load

Verify the connection

In the Data pane you should see 12 tables:

kpi_borough_analysis          kpi_popular_routes
kpi_data_quality_metrics      kpi_revenue_by_hour
kpi_distance_bands            kpi_time_of_day_analysis
kpi_passenger_count_analysis  kpi_top_pickup_zones
kpi_payment_type_analysis     kpi_trip_efficiency
kpi_trips_by_day              kpi_trips_by_hour

Quick visual check: drag kpi_trips_by_hour[pickup_hour] and [total_trips] onto a Line chart — values should be non-empty.

Next steps

  1. Complete Exercise: Power BI Dashboard (~50–70 min — five pages, all 12 KPIs)
  2. Optional DAX measures: DAX reference
  3. Optional theme: copy JSON from MHP theme → save as mhp-theme.json → View → Themes → Browse for themes
Troubleshooting + official docs
Issue Solution
No kpi_* tables in Navigator Finish Databricks, Snowflake, and dbt exercises first; open the correct Gold schema (_DBT_GOLD, _SQL_GOLD, or _SP_GOLD)
Snowflake MFA / PEM / 390197 errors Use key-pair auth — Snowflake key-pair setup
Snowflake login fails (other) Resume warehouse DE_WORKSHOP_WH; confirm role DE_WORKSHOP_ROLE; set Role name in connector advanced options
Connector error after Desktop update See Snowflake connector 2.0 notes; use latest Desktop build
Databricks catalog error in Navigator Advanced options → Catalog = mhpdeworkshop_databricks_2026
Databricks connection fails SQL warehouse running; correct HTTP path; use Azure Databricks connector
On macOS / Linux Desktop not available — read exercise steps or use a Windows VM for hands-on
Shape map not in Visualizations pane File → Options → Preview features → enable Shape map visual

Official documentation