Module 9: BI Tools (Power BI / Tableau / Looker)

Module 9: BI Tools (Power BI / Tableau / Looker)

1. Data Modeling Basics — Star Schema, Relationships, Calculated Fields/Measures

Star schema — the foundational data modeling pattern for BI, and the direct technical realization of the “fact/dimension table” concept first flagged back in the orientation and Excel modules.

                    ┌──────────────┐
                    │  Dim_Date    │
                    └──────┬───────┘
                           │
┌──────────────┐    ┌──────┴───────┐    ┌──────────────┐
│ Dim_Customer │────│  Fact_Sales  │────│ Dim_Product  │
└──────────────┘    └──────┬───────┘    └──────────────┘
                           │
                    ┌──────┴───────┐
                    │  Dim_Region  │
                    └──────────────┘
Table type Contains Example
Fact table Numeric measurements/events, usually high row count, foreign keys to dimensions Fact_Sales: order_id, customer_key, product_key, date_key, amount, quantity
Dimension table Descriptive attributes, lower row count, the “who/what/when/where” context Dim_Customer: customer_key, name, segment, region; Dim_Date: date_key, day, month, fiscal_quarter

Why star schema over a single flat table — the core architectural reasoning interviewers want: a flat, fully-denormalized table duplicates every dimension attribute on every fact row (every order row repeats the customer’s full name, region, segment, etc.), which bloats the model, slows queries, and creates the same data-consistency problem from the cleaning module — if a customer’s region needs correcting, you’d have to update it in thousands of duplicated fact rows instead of one dimension row. Star schema normalizes the dimensions while keeping the fact table lean and join-friendly, balancing the dbt-style normalization concept against query simplicity (it’s deliberately less normalized than strict 3NF, optimized for read-heavy BI queries rather than write-heavy transactional systems).

A dedicated Date dimension table is a specific, frequently-tested best practice — rather than relying on a raw date column scattered across fact tables, a proper Dim_Date table pre-computes fiscal year, fiscal quarter, is-weekend, is-holiday, etc., so every report can join to one canonical calendar logic instead of reimplementing date logic in every measure.

Relationships — cardinality and direction matter more than they seem to:

  • One-to-many (one customer → many orders) is the standard, expected star-schema relationship — direction of filter flow should go from the “one” side (dimension) to the “many” side (fact).
  • Many-to-many relationships (e.g., a product belongs to multiple categories, or a customer has multiple overlapping segments) require a bridge table — directly joining two many-side tables produces the same join-cardinality explosion problem flagged in both the SQL and pandas modules, just inside a BI semantic layer instead of a query.
  • Cross-filter direction (single vs. bidirectional) — bidirectional filtering feels convenient but can silently create circular ambiguity in larger models and unintentionally filter tables you didn’t mean to affect; the safer default is single-direction filtering (dimension → fact) unless there’s a specific need.

Calculated fields/columns vs. measures — this distinction is one of the most commonly tested BI-tool interview questions:

Calculated Column Measure
When evaluated Row-by-row, at data load/refresh time At query time, dynamically, based on current filter context
Storage Materialized — takes up memory in the model Not stored — computed on the fly
Responds to filters? No — fixed value per row once computed Yes — recalculates based on whatever’s currently filtered/sliced
Example Profit = Sales - Cost (a fixed per-row value) Total Profit = SUM(Sales) - SUM(Cost) (aggregates dynamically across whatever’s currently in view)

Talking point: “The mistake I see most often is building something as a calculated column when it should be a measure — a calculated column for total profit gets computed once per row and stored, so it can’t adapt when someone slices by region or month; a measure recalculates live based on whatever filter context is currently applied. As a rule of thumb: if the calculation needs to respond to how someone’s currently slicing the report, it has to be a measure, not a column.”

2. DAX (Power BI) Fundamentals

The concept that unlocks DAX — filter context vs. row context, the single highest-value thing to understand before any DAX syntax:

  • Row context — exists when a calculation evaluates one row at a time (calculated columns, iterator functions like SUMX).
  • Filter context — the set of filters currently applied to the model (from slicers, visual-level filters, or the rows/columns of the visual itself) — this is what makes a measure’s result change depending on what’s being viewed, and it’s the entire reason measures behave differently from calculated columns.
-- Basic measure
Total Sales = SUM(Fact_Sales[Amount])

-- CALCULATE — the single most important DAX function: modifies filter context explicitly
Sales This Year = CALCULATE([Total Sales], Dim_Date[Year] = 2026)

-- Time intelligence — built on a proper Dim_Date table (callback to section 1)
Sales YTD = TOTALYTD([Total Sales], Dim_Date[Date])
Sales PY = CALCULATE([Total Sales], SAMEPERIODLASTYEAR(Dim_Date[Date]))
YoY Growth % = DIVIDE([Total Sales] - [Sales PY], [Sales PY])

-- SUMX — row-context iterator, needed when the calc isn't a simple column sum
Total Profit = SUMX(Fact_Sales, Fact_Sales[Quantity] * (Fact_Sales[UnitPrice] - Fact_Sales[UnitCost]))

-- ALL — removes filters, useful for "% of total" calculations
Pct of Total Sales = DIVIDE([Total Sales], CALCULATE([Total Sales], ALL(Dim_Customer)))

Talking point on CALCULATE: “CALCULATE is the function that everything else in DAX is built around — it takes a measure and modifies the filter context it’s evaluated in. Once that clicks, time intelligence functions like SAMEPERIODLASTYEAR stop looking like magic — they’re just CALCULATE with a pre-built filter-context modification for date ranges.”

Tableau’s equivalent — Calculated Fields and LOD (Level of Detail) expressions:

// Basic calculated field
Profit Ratio = SUM([Profit]) / SUM([Sales])

// LOD expression — Tableau's answer to DAX's filter-context control,
// computing an aggregation at a DIFFERENT granularity than the view
{ FIXED [Customer ID] : SUM([Sales]) }      // total sales per customer, regardless of view-level filters
{ INCLUDE [Region] : AVG([Sales]) }          // average including a dimension not otherwise in the view
{ EXCLUDE [Region] : AVG([Sales]) }          // average excluding a dimension that IS in the view

Talking point connecting both tools: *”DAX’s CALCULATE and Tableau’s LOD expressions are solving the same underlying problem — letting an aggregation be computed at a different level of granularity than whatever the visual is currently sliced by. The syntax is completely different, but the conceptual question is identical: ‘at what level should this calculation happen, independent of how the report happens to be filtered right now.'” * This kind of tool-agnostic conceptual framing is exactly what signals architect-level thinking rather than memorized tool syntax.

3. Dashboard Performance Considerations

This is where BI tooling connects back to the query optimization module — same underlying principles, applied at the semantic-model layer instead of raw SQL.

Data model size and structure:

  • Import vs. DirectQuery (Power BI) / Extract vs. Live (Tableau) — Import/Extract loads a compressed snapshot into memory (fast queries, but data is only as fresh as the last refresh); DirectQuery/Live sends queries to the source database live (always fresh, but every visual interaction round-trips to the source — slow if the source isn’t well-indexed). This is a direct trade-off between timeliness and performance, echoing the data quality framework from the cleaning module.
  • Reduce cardinality — high-cardinality columns (a raw transaction ID, a free-text comment field) bloat the in-memory model far more than low-cardinality columns; remove unused high-cardinality columns from the model rather than importing “everything just in case.”
  • Aggregation tables — pre-aggregating to a coarser grain (daily/monthly summary table) for high-level dashboard views, with drill-through to detail-level data only when someone actually needs it — directly mirrors the progressive disclosure principle from the visualization module.

Query/measure efficiency:

  • Avoid excessive bidirectional relationships and overly complex calculated columns — both inflate model size/refresh time, as flagged in section 1.
  • Minimize visuals per page — each visual is a separate query against the model; a dashboard with 20 visuals on one page issues 20 queries on load, compounding any per-query inefficiency. This is the performance argument underneath the “6-8 tiles per dashboard” design heuristic from the visualization module — it’s not just a visual-clarity rule, it’s also a load-time rule.
  • Filter early, filter often — apply filters at the data-source/query level (similar to pushing WHERE clauses early in SQL) rather than importing everything and filtering visually afterward.

Talking point: “Dashboard performance problems are almost always a data-modeling problem wearing a UI costume — a slow dashboard usually traces back to importing more granular data than the dashboard actually needs, too many bidirectional relationships forcing the engine to evaluate filter propagation in multiple directions, or DirectQuery hitting an unindexed source table. I’d profile the model and the underlying query plan before touching the visuals themselves, the same instinct as checking EXPLAIN before optimizing a slow SQL query.”

4. Row-Level Security (RLS) and Governance Basics

Row-Level Security (RLS) — restricting which rows a given user can see in the same report, based on their identity, rather than building separate reports per audience.

Power BI RLS pattern:

-- A role-based filter applied to a table, e.g., on Dim_Region:
[RegionManager] = USERPRINCIPALNAME()

This filters Dim_Region (and everything downstream via the star schema’s relationships) to only the rows matching the logged-in user’s identity — a regional sales manager logging into the same published dashboard only sees their own region’s data, automatically.

Tableau’s equivalent — typically implemented via a user filter joined against a permissions table mapping usernames to allowed dimension values, or via Tableau Server’s row-level security features tied to its permission model.

Static vs. dynamic RLS:

  • Static — explicit roles manually mapped to specific filter values; simple, but doesn’t scale and requires manual maintenance as org structure changes.
  • Dynamic — RLS rules reference the logged-in user’s identity directly (as in the DAX example above) against a permissions table, so access updates automatically as a permissions table changes, without redeploying the report.

Talking point connecting back to the prohibited-actions theme of trustworthy data work: “RLS is the BI-layer equivalent of column/row-level access control in a database — the goal is one governed semantic model serving every audience, rather than maintaining N nearly-identical copies of a report with manually different filters, which is its own data-consistency risk: if the underlying logic changes, you now have to remember to update every copy.”

Governance basics — the broader practice RLS sits inside:

  • Single source of truth for measures — centralizing core business-metric definitions (e.g., “what counts as Active Revenue”) in the shared model/semantic layer, rather than letting every report author redefine the metric slightly differently in their own calculated field — this is the BI-tool manifestation of the consistency dimension from the data quality framework module.
  • Certified/endorsed datasets — most modern BI platforms support marking a dataset as “certified” so report builders know which upstream source is the trusted, governed one vs. an ad hoc personal workbook — directly mirroring the “Excel version control” problem (Sales_Final_v3.xlsx) flagged in the Excel module, just solved at the platform level instead of the file-naming level.
  • Access auditing — who can see what, and a record of who accessed what — increasingly a compliance requirement (this connects to the accuracy/auditability governance themes already raised when comparing Excel vs. proper BI tooling).

Closing synthesis talking point: “Everything in this module — star schema, measures vs. columns, RLS, certified datasets — is really one underlying idea: move the business logic and access control out of individual files and reports and into a single governed semantic layer, so that ‘what does revenue mean’ and ‘who can see this customer’s data’ each have exactly one authoritative answer instead of N slightly-different copies. That’s the direct technical answer to the ‘when does Excel stop being the right tool’ question from the Excel module — this is what you migrate to.”