BI Tools: Tableau, Power BI, and Looker Questions

Model changes

• Create Employee dimension with columns: EmployeeID, ManagerID, UserPrincipalName, and a Path column computed in ETL (e.g., PATH(EmployeeID, ManagerID) or build via recursive SQL). Maintain a flattened list of all manager-to-report paths.

DAX role filter example

• Use PATHCONTAINS to allow managers to see their reports:

[UserFilter] =
VAR currentUser = USERPRINCIPALNAME()
RETURN
PATHCONTAINS(Employee[Path], Employee[EmployeeID]) &&
(LOOKUPVALUE(Employee[UserPrincipalName], Employee[EmployeeID], Employee[ManagerID]) = currentUser || Employee[UserPrincipalName] = currentUser)

• Simpler pattern: create a table ManagerAccess with pairs (ManagerUPN, EmployeeID) in ETL and filter Employee[EmployeeID] IN VALUES from that table where ManagerUPN = USERPRINCIPALNAME().

Testing & validation

• Use 'View as Role' in Power BI Desktop for a sample manager account to verify row visibility.
• Automated testing: build a test harness that iterates through manager accounts, calls Power BI REST API or uses XMLA to run a validation query and compare expected vs actual accessible rows.
• Spot-check edge cases: circular manager references, contractors without managers.

This approach leverages precomputed hierarchical paths or mapping tables to keep RLS efficient and testable at scale.

Approach: Build sets of customers active in month M and month M-1 using DATE table. Churn = customers in M-1 but not in M divided by customers in M-1.

DAX measure:

Customer Churn % :=
VAR CurrentMonth = MAX('Date'[Date])
VAR M = EOMONTH(CurrentMonth,0)
VAR PrevMonthStart = STARTOFMONTH(EDATE(M,-1))
VAR PrevMonthEnd = ENDOFMONTH(EDATE(M,-1))
VAR PrevCustomers =
    CALCULATETABLE(
        VALUES(Orders[CustomerID]),
        DATESBETWEEN('Date'[Date], PrevMonthStart, PrevMonthEnd)
    )
VAR CurrCustomers =
    CALCULATETABLE(
        VALUES(Orders[CustomerID]),
        DATESBETWEEN('Date'[Date], STARTOFMONTH(M), ENDOFMONTH(M))
    )
VAR Churners = EXCEPT(PrevCustomers, CurrCustomers)
RETURN
DIVIDE(COUNTROWS(Churners), COUNTROWS(PrevCustomers), 0)

Notes: Ensure Orders has date relationship to Date table. This uses set operations (VALUES, EXCEPT) to derive counts and is robust to multiple orders per customer.

Scheduled refresh (Import mode)
Power BI Service runs scheduled dataset refreshes to reload imported data. For on-prem sources the On-premises Data Gateway brokers secure connections and executes queries from the service.

Gateway role

• Installs on network; authenticates to sources; required for SQL Server, Oracle, file shares.

Frequency differences

• Pro: up to 8 refreshes/day (as of latest tiers) and limited concurrency.
• Premium: higher frequency (up to 48/day or near-real-time with Premium Per User features) and larger resource quotas.

Incremental refresh

• Partitions data (range parameters), refreshes only recent partitions, reducing time and resource consumption. The initial full load still occurs; subsequent refreshes touch only incremental windows and optionally refresh historical reprocess.

Common failures & remediation

• Authentication/credential expiry: update credentials in service.
• Gateway offline: restart service or update network routes.
• Query timeouts/long-running queries: optimize source queries, add indexes, or implement incremental refresh.
• Schema changes: fix broken queries after column renames.
• Exceeded capacity limits: move to Premium or optimize model/refresh windows.

As a data engineer, monitor refresh logs, implement incremental patterns, and push heavy transforms upstream.

Dataset architecture for sub-second queries over 100M+ rows:
Model schema:

• Star schema: central fact at transactional grain, small denormalized dimensions (product, customer, date) with surrogate integer keys.
  Pre-aggregation strategy:
• Build aggregated fact tables at required grains (daily/product, hourly/region) in the warehouse and import them to Power BI as aggregation tables.
• Configure Power BI Aggregations mapping so visuals route to smaller imported tables instead of the detailed fact.
  Storage-mode choices:
• Use Import for aggregated tables and small dims for sub-second; keep detail fact in DirectQuery only if needed for rare deep-dive queries (composite model).
  Incremental refresh and partitions:
• Partition imported tables by date (monthly/daily) and use incremental refresh to load only new partitions.
  Performance and SLA:
• Measure query latency using synthetic workloads and Power BI Premium metrics; define SLA (e.g., 95th percentile <= 500ms).
• Enforce SLA via: aggregation coverage, cached tiles, pre-warmed capacity, and throttling non-critical reports.
  Operational considerations:
• Maintain ETL to update pre-aggregations daily; ensure near-real-time windows use a separate pipeline if needed.
• Monitor aggregation hit-rate and tune mappings; extend aggregations to cover more queries as needed.
  Why this works:
• Aggregations reduce scanned rows dramatically, import of small pre-aggregated tables ensures fast in-memory queries, while DirectQuery retains detailed access without inflating model memory.

Overview:
VertiPaq stores columnar data in compressed segments. Each column is split into segments (~1M rows) and encoded to reduce memory.
How columns are stored:

• Columnar pages (segments) keep values contiguous per column, enabling vectorized scan and high compression.
  Dictionary encoding:
• VertiPaq builds a dictionary of distinct values and replaces actual values with integer keys (value IDs). Low cardinality → small dictionary and smaller keys.
  Run-length encoding (RLE):
• If repeated value IDs appear consecutively, VertiPaq stores runs (valueID, runLength) rather than repeated keys.
  Segment-based compression:
• Each segment independently chooses encodings (dictionary, RLE, bit-packing) and may apply additional algorithms (e.g., delta, Huffman-like) to minimize size.
  Actionable steps to reduce model size:
• Reduce cardinality: bucket continuous columns, trim distinct values, remove GUIDs.
• Prefer integers: convert large-string keys to integer surrogate keys.
• Remove unused columns/measures and hide unnecessary columns.
• Use appropriate data types: dates as Date, booleans as bit flags.
• Pre-aggregate: push heavy detail to pre-aggregated tables when possible.
• Replace high-cardinality strings with lookup tables and surrogate keys; use numeric keys for joins.
  Validation and tools:
• Use VertiPaq Analyzer (or VertiPaq Analyzer integrated in DAX Studio) to inspect per-table and per-column compressed size, encoding types, and cardinality.
• In DAX Studio, run DMV queries (DISCOVER_STORAGE_TABLES, DISCOVER_STORAGE_COLUMNS) to get segment-level stats.
• Compare before/after: measure dataset size (.pbix or model) and observe reduced column sizes and changed encoding (dictionary->RLE) in analyzer output.
• Validate memory footprint using Power BI Premium metrics or SSAS DMV queries on memory consumption under realistic refresh and query load.
  Why these steps work:
• Lower cardinality and numeric keys improve dictionary efficiency and increase chance for RLE/bit-packing, reducing RAM usage and improving query throughput.
  Edge considerations:
• Maintain query semantics when bucketing or aggregating; test visuals and measures. Document transformations for lineage and debugging.