BI Tools: Tableau, Power BI, and Looker Questions

Start with a prioritized troubleshooting checklist (quick triage → deeper profiling → fixes), then actions grouped by quick wins and longer-term changes.

Checklist (prioritized)

1. Reproduce & baseline

• Open report with F12 network & note load time; run Performance Analyzer inside Power BI Desktop to capture visual/query durations.

2. Quick-profile heavy queries

• Export queries from Performance Analyzer; run in DAX Studio (Server Timings / Query Plan) to see CPU, cache hits, and bottlenecks.

3. Inspect model size & cardinality

• In Power BI Model view check table sizes, row counts, column cardinality (high-cardinality string keys escalate memory/processing).

4. Visuals & interactions

• Identify visuals with long DAX queries (from Perf Analyzer). Simplify or replace complex visuals (multi-measure matrix, high-mark/high-cardinality scatter).

5. Query reduction techniques

• Enable Query reduction settings (single visual selection, disable auto page refresh), turn on "Load report page with reduced visuals" patterns.

6. Backend checks

• Verify import vs DirectQuery, refresh timing, and data types; confirm Vertipaq memory available.

Quick wins

• Disable unnecessary visuals and excessive slicers; reduce interactions (edit interactions) so visuals don’t cross-filter unnecessarily.
• Replace cardinals large text keys with integer surrogate keys; remove unused columns and reduce column precision.
• Turn off auto-highlight and unnecessary animations; set visuals to aggregate at model level (pre-aggregated columns).
• Use Import mode where feasible; increase model compression by changing string columns to categories and using integer keys.

Deeper fixes / long-term

• Rewrite expensive measures: prefer SUMX alternatives, avoid iterator over large tables; use variables to avoid repeated calculations.
• Create calculated tables or pre-aggregations (aggregation tables) to serve high-level visuals; implement composite model with DirectQuery to large historical tables.
• Partition data refreshes, incremental refresh for large fact tables.
• Optimize relationships: single-direction where possible, star schema normalization, remove bi-directional filters unless needed.
• Use DAX Studio for tuning: examine Query Plan, force storage engine (EVALUATE) vs. formula engine operations; rewrite measures to push work to storage engine.
• Monitor telemetry in Power BI Service (Premium metrics if available) for dataset memory pressure and refresh durations.

Key principles to justify changes

• Reduce cardinality and row footprint first (lowest friction, biggest gains).
• Move computation to model/ETL (pre-aggregate) so visuals query small, efficient tables.
• Profile iteratively: measure before/after with Performance Analyzer and DAX Studio to validate improvements.

To compute a rolling 12‑month average of SalesAmount (including current month) while handling missing months, use the Date table (dim_date) as the time axis and use DAX time-intelligence with a 12-month window anchored to the current row context.

Rolling12MoAvg_Sales :=
VAR CurrentDate = MAX(dim_date[Date])
VAR StartDate = EDATE(CurrentDate, -11)  -- include current month + previous 11 months
VAR DateRange =
    FILTER(
        ALL(dim_date),
        dim_date[Date] >= StartDate
        && dim_date[Date] <= CurrentDate
    )
VAR TotalSales =
    CALCULATE(
        SUM(FactSales[SalesAmount]),
        DateRange,
        REMOVEFILTERS(dim_date)  -- ensure full range is considered even if model filters applied elsewhere
    )
VAR MonthsWithData =
    DISTINCTCOUNT(
        FILTER(
            ADDCOLUMNS(
                VALUES(dim_date[Date]),
                "@Sales", CALCULATE(SUM(FactSales[SalesAmount]))
            ),
            [@Sales] <> BLANK()
        )
    )
-- If you want true average across 12 months regardless of missing data, use 12. 
-- Here we average over months present in the 12-month window (ignores completely missing months).
RETURN
IF(
    MonthsWithData = 0,
    BLANK(),
    TotalSales / MonthsWithData
)

Assumptions:

• dim_date[Date] is a continuous date key (daily granularity).
• FactSales has SalesAmount and is related to dim_date by Date.
• This measure averages over months that have any sales; if you want to force dividing by 12 even when sales are missing, replace MonthsWithData with 12.
• REMOVEFILTERS/ALL ensure missing months are included in the window calculation rather than being excluded by other filters.

Notes:

• For month-level visuals, consider using dim_date[MonthStart] or grouping by month in the visual; the measure will still compute correctly because it uses date windowing.
• Performance: use calculated columns for MonthStart or pre-aggregated monthly table if dataset is large.

Approach summary: reduce virtual-table size, eliminate unnecessary row-by-row iteration, push calculations to aggregated grain, and reuse intermediate results via variables. Benchmark before/after with DAX Studio (Server Timings + Query Plan) and inspect model storage with VertiPaq Analyzer.

Concrete rewrite strategies

1. Capture intermediate tables as variables

• Avoid recomputing the same FILTER/SUMX multiple times.
• Example:

VAR FactFiltered =
    FILTER( FactTable, FactTable[Date] >= MIN(DimDate[Date]) && FactTable[Category] = "X" )
VAR Result =
    SUMX( FactFiltered, FactFiltered[Amount] * FactFiltered[Rate] )
RETURN Result

(But prefer aggregating FactFiltered first — see #2.)

2. Replace row-by-row SUMX over large detail with SUMMARIZECOLUMNS pre-aggregation

• Aggregate to the needed grain (few columns) then perform scalar math across smaller result.

VAR Agg =
    SUMMARIZECOLUMNS(
      DimProduct[ProductKey],
      "TotalAmt", SUM(FactTable[Amount]),
      "AvgRate", AVERAGE(FactTable[Rate])
    )
RETURN
SUMX( Agg, [TotalAmt] * [AvgRate] )

This turns many rows into a compact aggregated table (much faster and memory-friendly).

3. Use filter-push / Boolean filters and CALCULATE instead of FILTER iterators

• CALCULATE with simple table filters is faster than FILTER over entire table.
• Prefer expressions like CALCULATE(SUM(Fact[Amount]), Fact[Category]="X", Dates[Year]=2024)

4. Pre-aggregate at model or ETL level when possible

• If the visualization never needs row-level detail, create summarized tables (by day/product/category) in Power Query or source SQL. This reduces VertiPaq size and query work.

5. Avoid EARLIER, nested FILTERs, and excessive use of RELATED in iterators

• Use relationships with CALCULATE context transition or use TREATAS where appropriate.

6. Reduce cardinality and storage overhead

• Change data types (integers vs strings), remove unused columns, and create surrogate keys. High cardinality columns slow scans.

Benchmarking and validation

1. Baseline with DAX Studio

• Run the slow measure inside DAX Studio with Server Timings on.
• Capture: Duration, CPU, VertiPaq Read, Number of Storage Engine queries.
• Use "Query Plan" to see expensive operators (EVALUATE, RowByRow, etc.).
• Save the baseline trace.

2. Implement changes iteratively

• After each refactor, rerun same query and compare timings & resource metrics.
• Look for big drops in VertiPaq reads and storage engine time — those indicate successful push-down/aggregation.

3. Use VertiPaq Analyzer / Power BI Desktop memory metrics

• Export the model from DAX Studio ( DMV DISCOVER_STORAGE_TABLE_COLUMNS ) and inspect with VertiPaq Analyzer.
• Check largest tables and columns, column cardinality, and compression sizes. After pre-agg or removing columns you should see model size decrease and better compression.

4. Interpret results

• Target: reduce query duration, CPU, and VertiPaq reads. A large reduction in Storage Engine (SE) reads or number of rows scanned is a clear win.
• If CPU still high, inspect Query Plan for expensive iterator operators — convert them to aggregated calculations.

Edge checks

• Ensure semantic correctness after changing grain (e.g., distinct counts or ratio calculations require careful aggregation).
• Validate results vs original for multiple slices and filters.

Outcome: iterative refactoring (variables + SUMMARIZECOLUMNS + pre-aggregation + CALCULATE filters) usually converts heavy row-by-row work into compact storage-engine work — measurable in DAX Studio as much lower duration, lower SE reads, and improved interactivity.

Requirements & constraints:

• Low interactive latency for EMEA and APAC users (<200ms perceived for dashboards)
• Data sovereignty: some datasets must remain/processed in-region
• Minimize data duplication and ops overhead; support failover and DR
• Tooling: support Power BI (Premium) and Tableau Server options

High-level solution:

1. Hybrid regional architecture — regional read/analytics zones + a governed global layer.

• Regional analytics clusters in EMEA and APAC host regional copies of production data that are required to remain in-region (PII, regulated datasets). These contain transformed, de-identified marts where needed.
• A central global analytics workspace (in a compliant region or dedicated tenant) holds non-sensitive, enterprise-wide datasets for cross-region BI.

2. Data replication / read replicas

• Use transactional-change capture (CDC) into regional data lakes/warehouses (e.g., managed replication via cloud provider: Azure Data Factory + Azure Synapse, or AWS DMS + Redshift Spectrum).
• For regulated datasets, do full replication only to the designated region. For non-sensitive dimension & aggregated datasets, maintain a single global master and promote read replicas in regions via asynchronous replication to lower latency.

3. Refresh scheduling (local vs global)

• Local visual refresh: schedule regional dataset refreshes in each region (power BI dataset/refresh or Tableau extract refresh) aligned to business hours in that region for lower latency.
• Global datasets: refresh centrally during low-traffic windows and push delta/summary extracts to regions. Use incremental refresh to minimize load.
• Use near-real-time streaming for operational dashboards (Kafka/Event Hubs -> materialized views), with TTLs to avoid storing raw PII outside region.

4. Centralize vs regionalize datasets

• Regionalize source-of-truth for regulated data + aggregated marts needed by local stakeholders.
• Centralize global non-sensitive metrics and reference tables to avoid unnecessary duplication and to simplify governance.

5. BI deployment choices

• Power BI: provision Premium capacities per region (P SKUs or regional capacities) to ensure capacity isolation, local GPU/compute for large models, and meet residency. Use multi-geo capacities if tenant supports it.
• Tableau: run Tableau Server clusters per region behind a load balancer; use a central Tableau Prep/online for global assets. Use single sign-on federation and content sync tools (Tableau Content Migration Tool) to push approved extracts.

6. Failover & DR

• Data: maintain cross-region backup snapshots and storage-tiered replicas (cold backups globally, hot read-replicas where allowed). For regions with strict sovereignty, keep encrypted backups in-region and a metadata-only replica centrally for orchestration.
• BI layers: configure automated failover of report endpoints to secondary region through DNS with health checks; for Power BI, have capacity failover plans and export PBIX/metadata to central repo to redeploy; for Tableau, keep image/backup sync and bootstrap scripts for fast cluster restore.
• Test runbooks quarterly with RTO/RPO targets (e.g., RTO <2 hours for dashboards; RPO <15 minutes for critical operational views).

7. Governance, security, compliance

• Row-level security and column masking applied at dataset/warehouse level; encryption at rest and in transit.
• Data catalog and lineage (e.g., Purview/Collibra) to prove residency and transformation steps.
• Automated policies to prevent accidental copy of regulated tables to global area.

Trade-offs & rationale:

• Regionalization increases storage/ops but meets sovereignty and reduces latency. Minimizing duplication handled via centralizing non-sensitive data and using incremental/summary pushes.
• Asynchronous replication keeps latency low and availability high but accepts small eventual consistency windows—acceptable for BI reporting (not for transactional systems).
• Maintaining multiple BI clusters increases maintenance but isolates failures and complies with residency — mitigated via IaC, automation, and shared governance.

Operational recommendations:

• Use IaC to deploy and maintain BI clusters and replication pipelines.
• Define clear dataset classification (sensitive vs non-sensitive) to automate placement.
• Monitor user performance and adapt caching and refresh cadences per region.

Power BI workspaces: there are My Workspace (personal sandbox) and app workspaces (collaborative containers). Historically app workspaces were “classic” (backed by Office 365 Groups) — creating an O365 group, less granular roles. The new workspace experience (V2) decouples from O365 groups, adds finer role types (Admin, Member, Contributor, Viewer), better lifecycle controls, dataset permissions (Build), and supports large-scale distribution and governance.

How app workspaces relate to apps: a workspace is where authors collaborate (reports, datasets, dashboards). An app is a curated, packaged, read-only surface built from a workspace that you publish to consumers. You can update workspace content, then republish the app to push changes to users.

End-to-end publishing flow:

• Author in Power BI Desktop (model, visuals, RLS, sensitivity labels).
• Publish to a target app workspace (or save pbix to workspace).
• Configure dataset settings: refresh, credentials, parameters, gateway.
• In the workspace, assemble content, set navigation.
• Promote/certify content (Promoted or Certified) for discoverability.
• Create and publish the app: choose included items, navigation, branding, permission scope (specific users, AAD groups, entire org).
• Consumers access the app (read-only); creators retain workspace access for updates.

Permission models:

• Workspace roles control who can edit/manage content (Admin/Member/Contributor/Viewer).
• App permissions control who can consume the published app.
• Dataset Build permission allows users to create new reports on the dataset.
• Row-level security, sensitivity labels, and Azure AD group membership add data-level controls.

Content promotion and governance:

• Mark datasets/reports as Promoted or Certified to signal trust.
• Use endorsement + documentation to guide users to sanctioned sources.
• Use sensitivity labels and tenant settings to control sharing/export.

When to use apps vs sharing workspaces directly:

• Use apps when you need controlled, versioned distribution to many consumers, want a read-only curated experience, and need clear separation between authors and viewers. Share workspaces directly only for small coauthoring teams where multiple users need edit access or to collaborate iteratively.