BI Tools: Tableau, Power BI, and Looker Questions

Import/Extract (Power BI Import / Tableau Extract)

• Data freshness: Snapshot-based; refresh schedule (minutes→daily). Good for near-real-time not required.
• Query latency: Fast — queries hit in-memory or optimized extract files, low dashboard responsiveness.
• Concurrency: Scales well because queries serve from cached extract; fewer DB connections.
• Row-level security (RLS): Supported but must be implemented in the extract (static or dynamic via user filters); complexity if user attributes change frequently.
• Performance: Excellent for complex aggregations, high-cardinality joins, and heavy visual calculations.
• Cost (compute): Shifts compute to BI server or scheduled refresh infra — cheaper if you avoid constant DB compute; extract refreshes can be costly if large/frequent.
• Reliability/failure modes: Fewer runtime failures; failures occur at refresh time (ETL failures) which are predictable.
• Typical scenarios: Executive dashboards, high-concurrency consumer reports, complex transformations, and when source load must be limited.

Live/Direct (Power BI DirectQuery / Tableau Live)

• Data freshness: Real-time or near-real-time — queries go to source on each view or interaction.
• Query latency: Higher and variable — depends on source DB performance and network.
• Concurrency: Can stress source DB under many users; needs strong DB scaling/connection pooling.
• RLS: Enforced at source (better for dynamic security); row-level policies in DB are most robust.
• Performance: Limited by source database; complex visual queries can be slow unless you push down aggregates or use indexed/materialized views.
• Cost (compute): Higher ongoing DB compute costs; pay-per-query cloud DB costs can rise with usage.
• Reliability/failure modes: Runtime failures if source is slow/down; interactive experience impacted immediately.
• Typical scenarios: Operational reports, real-time monitoring, small user base, when data must be live (transactions, inventory).

Trade-offs summary:

• Choose extracts when you need speed, concurrency, complex calculations, and to protect source load.
• Choose live when freshness and centralized security are critical and the source can support query load or when real-time decisions are required.
• Hybrid: use extracts for heavy/consumptive dashboards and live connections for a few real-time operational views.

To compute the YTD ratio (current YTD Sales / prior-year YTD Sales) reliably, define explicit measures for current YTD and prior YTD, then use DIVIDE to safely handle division-by-zero. Use TOTALYTD or CALCULATE with DATESYTD for current YTD; use DATEADD or SAMEPERIODLASTYEAR to shift the period for the prior YTD. DATEADD is more flexible for non-contiguous calendars; SAMEPERIODLASTYEAR is simple and efficient for contiguous daily calendars.

Code:

-- Current Year-to-Date Sales
Current YTD Sales :=
TOTALYTD(
    SUM( Sales[Amount] ),
    'DimDate'[Date]
)

-- Prior Year-to-Date Sales (using DATEADD)
Prior YTD Sales :=
CALCULATE(
    [Current YTD Sales],
    DATEADD( 'DimDate'[Date], -1, YEAR )
)

-- YTD Ratio (handles divide-by-zero)
YTD Sales Ratio :=
DIVIDE( [Current YTD Sales], [Prior YTD Sales], BLANK() )

Key points:

• TOTALYTD (or DATESYTD) scopes dates to year-to-date based on DimDate.
• DATEADD shifts the entire YTD set back one year, preserving the same range (good for irregular calendars). SAMEPERIODLASTYEAR('[DimDate'[Date]) returns the exact period last year and is fine when DimDate has continuous daily entries and a standard fiscal year.
• DIVIDE safely avoids divide-by-zero and lets you return BLANK() or 0 as desired.
  Edge cases:
• Ensure 'DimDate' is a proper contiguous date table marked as a Date table.
• If fiscal year != calendar year, use TOTALYTD with a year-end parameter or DATESYTD with fiscal year handling.

Start by clarifying correctness vs performance: correctness = expected row-level semantics; performance = model simplicity and query speed. There are three common options.

1. Bridge (junction) table (recommended default)

• Pattern: normalize many-to-many into two one-to-many relationships via a bridge table (e.g., Products ← ProductCategoryBridge → Categories).
• Pros: explicit lineage, predictable filter flow, fine-grained control, works well with star schemas and large models.
• Cons: extra table and joins; need to maintain bridge keys and cardinality.
• DAX tip: write measures using explicit filter/context functions to avoid implicit cross-filtering surprises. Example:
  SalesByCategory :=
  CALCULATE( SUM(Sales[Amount]),
  FILTER(VALUES(ProductCategoryBridge[CategoryID]),
  COUNTROWS(
  FILTER(RELATEDTABLE(Products), Products[ProductID] = ProductCategoryBridge[ProductID])
  )>0))

2. Built-in many-to-many (composite models / DirectQuery storage relationships)

• Pattern: create a direct many-to-many cardinality between tables.
• Pros: simpler model, less ETL.
• Cons: can be ambiguous for filter propagation; performance may degrade for large cardinalities; harder to debug.
• DAX tip: avoid relying on implicit behavior; use TREATAS or CROSSFILTER to control propagation.

3. Bi-directional filtering

• Pattern: enable both directions on relationships.
• Pros: convenient for slicers and natural reporting (filters flow both ways).
• Cons: can create ambiguous relationships, slow queries, and incorrect totals if multiple bidirectional paths exist.
• DAX tip: prefer one-directional relationships and use CROSSFILTER or USERELATIONSHIP locally in CALCULATE when you need reverse flow. Example safe pattern:
  TotalSales_Filtered :=
  CALCULATE( SUM(Sales[Amount]),
  CROSSFILTER(Products[ProductID], Bridge[ProductID], Both),
  Bridge[CategoryID]=SELECTEDVALUE(Categories[CategoryID])
  )

Best practices

• Prefer bridge tables for correctness and scale; use many-to-many cardinality only for small lookup sets or prototypes.
• Keep relationships single-direction by default; enable bi-directional only on isolated, well-understood paths.
• Use DAX functions (TREATAS, CROSSFILTER, USERELATIONSHIP, ALL/REMOVEFILTERS) to make filter intent explicit.
• Test measure correctness with small datasets and use Performance Analyzer; materialize intermediate tables (calculated tables/indexes) if queries are heavy.
• Document model choices so report consumers understand behavior.

Requirements & constraints:

• Manage Power BI, Tableau, LookML artifacts across dev/test/prod.
• Version control, automated data & visualization tests, deploy & rollback, integrate with Git, secure secrets/service accounts, minimal disruption to business users.

High-level architecture:
Developer Git workflow (feature branches → PR → main) → CI server (GitHub Actions/GitLab CI/Bitbucket Pipelines) → automated tests → CD orchestrator (Argo CD/Octopus/ custom scripts) → environment-specific artifact stores (Power BI workspace, Tableau Server/Cloud projects, Looker projects) → monitoring & rollback.

Core components and responsibilities:

1. Git repo(s): store pbix/parameters, Tableau TWB/TDSX or workbook XML exports, LookML files. Use one mono-repo or per-tool repos with consistent branching policy and protected main branches.
2. CI (GitHub Actions): on PR run:
   • Lint LookML, validate Tableau XML schema, check pbix metadata.
   • Run unit/data tests: dbt tests or SQL-based row-counts, schema checks against dev database.
   • Visualization tests: use Snapshot testing (Selenium/Playwright + image diffs) or Assertive tests (check presence of fields, filters, no broken queries).
3. CD: on merge to main:
   • Deploy to dev workspace automatically; run integration tests against dev data.
   • Manual approval gates for test → prod (use pull-request or environment approvals in CD tool).
   • Deployment steps: push LookML via Looker API/git integration; publish Tableau workbook via Tabcmd/REST API to target project; publish Power BI via Power BI REST API/PowerShell (upload dataset, set parameters, refresh).
4. Rollback:
   • Maintain release tags and artifact snapshots in Git and artifact storage.
   • CD tool supports "promote previous tag" or re-deploy earlier commit; for Power BI/Tableau, use API to republish previous file and trigger refresh; for Looker, git revert & deploy.
5. Secrets & service accounts:
   • Use vault (HashiCorp Vault, AWS Secrets Manager, Azure Key Vault) integrated with CI/CD. CI uses short-lived tokens; never store creds in repo.
   • Create least-privilege service accounts for APIs with scoped permissions per environment. Rotate keys regularly and use OIDC where supported (GitHub Actions OIDC to Azure AD).
6. Observability & testing feedback:
   • Log deployments, test results, dataset refresh history.
   • Alerts for failed refreshes, schema drift, visual regressions.
     Trade-offs:

• Image diff visual tests catch layout regressions but are brittle; combine with DOM/assertion tests.
• Full automation to prod increases speed but requires strong approval/gating for exec dashboards.

This pipeline balances reproducibility, test coverage, and safe deployments for BI artifacts.

Measures are numeric, aggregatable facts (what you analyze); dimensions are categorical or descriptive attributes (how you slice the measures).

Examples:

• Measures: revenue, order_count, profit_margin, session_duration
• Dimensions: order_id, customer_id, product_category, order_date, country

Aggregation behavior:

• Measures aggregate (SUM, AVG, COUNT, MIN, MAX). Aggregation semantics depend on grain: SUM(revenue) makes sense at order or day level; AVG(session_duration) vs SUM(session_duration) differ in meaning. COUNT(distinct customer_id) is expensive and sensitive to grain.
• Dimensions are not aggregated; they define grouping. Incorrect grouping causes double-counting or over-aggregation (e.g., summing revenue at product_category without respecting order-level discounts).

Modeling in a semantic layer to avoid ambiguous aggregations:

• Define base grain for each fact table (e.g., orders at order_line_id). Explicitly expose pre-aggregated measures (daily_revenue, monthly_active_users) and their aggregation rules.
• In Looker: create explores with primary keys and set measure types (sum, avg, count_distinct) and use sql_distinct_key or group_label to enforce grain.
• In Tableau/Power BI: create a semantic model (data source or Power BI dataset) that defines measures with DAX/Calc fields and mark keys/relationships so tool-level aggregations are predictable.
• Provide derived measures for different grains (revenue_per_order, revenue_per_customer) and use role-playing dimensions or context-aware calculations (window functions, FIXED LOD in Tableau) to lock aggregation level.
• Document each measure’s grain and aggregation rule in the layer and prevent users from changing aggregation where it would be invalid.

Result: clear, documented measures and controlled aggregations prevent double-counting and ensure dashboards produce accurate business metrics.