BI Tools: Tableau, Power BI, and Looker Questions

Step-by-step — Connect Power BI Desktop to an on‑prem SQL Server

1. Open Power BI Desktop → Home → Get Data → SQL Server.
2. Enter Server name (and optional Instance) and Database (or leave blank).
3. Select Data Connectivity mode: Import (recommended for reporting) or DirectQuery (real‑time).
4. Under Advanced options add SQL query if needed.
5. Click Connect → choose authentication:
   • Windows (Integrated): uses your AD credentials — common in corporate domains.
   • SQL Server auth: enter SQL username/password — used when Windows auth not available.
   • Microsoft account / OAuth: supported for Azure SQL; not typical for on‑prem SQL.
6. Load or Transform in Power Query; set Privacy Level per source (Organizational for internal).

Step-by-step — Connect to local Excel workbook

1. Home → Get Data → Excel, browse to .xlsx file.
2. Select sheets/tables, Load or Transform.
3. Excel is a File data source; set Privacy Level (Private or Organizational).

On‑premises Data Gateway setup for scheduled refresh

1. Install Gateway (standard mode) on a server with network access to SQL Server and internet.
2. Sign in with the service account tied to the Power BI tenant.
3. In Power BI Service → Settings → Manage Gateways, add data sources: choose SQL Server or File (for on‑prem files use a shared network path).
4. Configure authentication for that gateway data source: Windows or Basic (SQL auth). Save.
5. In dataset settings, map dataset to gateway datasource and enable scheduled refresh (set frequency, credentials).

Privacy levels & credentials

• Privacy Levels (Private/Organizational/Public) control query folding and data mixing in Power Query; set appropriately to avoid blocking merges.
• Ensure credentials in Power BI Service match gateway data source credentials (credential mismatch prevents refresh).

Common pitfalls

• Credential mismatches between Desktop and Service or gateway configuration.
• Firewall/network: gateway machine must reach SQL Server port (default 1433) and outbound ports for Power BI.
• Missing/incorrect database drivers (install latest ODBC/OLE DB/SQL Server Native Client).
• Using local file path in Desktop: service cannot access C:\ — use network share accessible by gateway or use OneDrive/SharePoint.
• Permission scopes: Windows auth requires gateway running under account with DB permissions or use Kerberos constrained delegation for identity pass‑through.

When to prefer which connection

• Use Import when you want fast visuals and scheduled refresh (smaller latency, offline snapshots).
• Use DirectQuery when data must be live and very large (watch performance and query limits).
• Prefer Windows Integrated auth for enterprise security; SQL auth if domain not available or for service accounts.
• Excel/local file is fine for ad‑hoc or departmental data; for production use move to shared network, database, or cloud storage for reliability and scheduled refresh via gateway.

This approach ensures secure, refreshable datasets while avoiding common deployment issues.

A star schema is a dimensional model with a central fact table (transactional, numeric measures) surrounded by denormalized dimension tables (descriptive attributes). Power BI’s VertiPaq favors this shape because its columnar, dictionary-encoded storage and in-memory engine get maximum compression and fastest scan/aggregation on wide, read-optimized tables.

Core components:

• Fact table: contains facts/measures and foreign keys to dimensions. Grain must be explicit (e.g., one row = one order line).
• Dimension tables: user-friendly attributes (Customer, Product, Date). Typically one row per business entity.
• Grain: the atomic level of analysis; determine which measures are valid and which dims join.
• Relationships: usually one-to-many (dimension 1 → fact *). Also many-to-many use bridging tables, and inactive relationships can be activated in DAX.

Why VertiPaq favors star:

• Compression: repeating attribute values in dimensions and low-cardinality columns compress extremely well with dictionary encoding.
• Query performance: queries scan only needed columns; simple 1:* joins across keys are implemented efficiently via in-memory storage and aggregate pushdown.
• DAX simplicity: measures written against a single fact with filter context from dimensions are straightforward (SUM, CALCULATE). Avoids complex joins and LOOKUPVALUE overhead.

Example mapping (commerce):

• Fact_OrderLine (grain: one order line) — OrderLineID, OrderID, CustomerID, ProductID, DateKey, Quantity, UnitPrice, LineTotal
• Dim_Customer — CustomerID, CustomerName, Region, Segment
• Dim_Product — ProductID, SKU, Category, Brand, Price
• Dim_Date — DateKey, Date, Year, Quarter, Month, DayOfWeek

Typical measure:

• Total Sales = SUM(Fact_OrderLine[LineTotal])
  With this star, slicers on Dim_Date or Dim_Product filter the fact efficiently, yielding fast visuals, small model size, and simple, maintainable DAX.

Overview: below are detailed migration patterns and a checklist for translating Tableau features to Power BI equivalents (focus: LOD expressions, table calculations, parameterized actions, device-specific layout). Each section lists conversion patterns, incompatibilities, and mitigation strategies.

1. LOD expressions (Tableau FIXED/INCLUDE/EXCLUDE)

• Conversion pattern:
  • Use DAX calculated columns / measures with CALCULATE + ALL / ALLEXCEPT / VALUES to replicate FIXED; use REMOVEFILTERS/KEEPFILTERS for behavior control.
  • Example: Tableau FIXED [Customer]: SUM([Sales]) → DAX: CustomerSales = CALCULATE(SUM(Sales), ALLEXCEPT(Customer[CustomerID], Customer[CustomerID]))
• Known incompatibilities:
  • Tableau implicit row-level context and table-scoped LODs can be tricky to mirror in DAX row context.
  • Performance: DAX with many ALL/ALLEXCEPT can be slower on large models.
• Mitigation:
  • Re-model: create summary tables in Power Query or on the data warehouse for heavy aggregations.
  • Validate with sample queries; add appropriate relationships and use USERELATIONSHIP for alternate keys.

2. Table calculations (WINDOW_SUM, INDEX, RUNNING_SUM, RANK)

• Conversion pattern:
  • Use DAX iterators and time-intelligence: SUMX, RANKX, EARLIER (legacy) / VAR with ORDERBY and TOPN patterns, and FILTER with ALLSELECTED to emulate window scope.
  • Example: Running total by date = CALCULATE(SUM(Sales), FILTER(ALLSELECTED(Date), Date[Date] <= MAX(Date[Date])))
• Known incompatibilities:
  • Table calc partitioning (pane/level of detail) and addressing directions require explicit DAX context; interactive pivot-like behavior in visuals differs.
• Mitigation:
  • Define explicit grouping keys in DAX, or pre-aggregate in Power Query. Use disconnected tables and slicer sync to mimic partitioning. Test visuals with different drill/sort states.

3. Parameterized actions (URL/action-driven filters, set actions)

• Conversion pattern:
  • Use Power BI Bookmarks + Buttons for navigation; use Report page tooltips, Drillthrough, and Selection pane. For parameter inputs, use "What-if" parameters or disconnected tables with measures reacting to selected slicer values.
  • Replicate set actions by creating a multi-row selection via a supporting table and measure state (e.g., SelectedIDs = CONCATENATEX(VALUES(...), ...)).
• Known incompatibilities:
  • Power BI lacks direct equivalent to Tableau set actions that modify mark-level sets on click; no native per-mark action sending arbitrary parameters except through drillthrough or URL.
• Mitigation:
  • Use Power BI’s Selection/Sync slicers, single/multi-select slicer visuals, or use custom visuals (e.g., Chiclet Slicer). For advanced behavior, use Power BI JavaScript API in embedded scenarios to capture clicks and change report state.

4. Device-specific layout (Tableau device designer)

• Conversion pattern:
  • Use Power BI phone and tablet layouts (mobile view) and responsive visuals; create separate report pages tuned for device form factors.
• Known incompatibilities:
  • Power BI mobile layout is page-level and more limited than Tableau’s device-specific per-dashboard design. No simultaneous canvas variations per device beyond mobile view.
• Mitigation:
  • Create dedicated pages for desktop/tablet/phone and link via buttons or bookmarks. Use conditional formatting and visual-level filters to surface simplified content on mobile. Test on Power BI Mobile app.

Migration checklist (practical steps)

• Inventory: list all workbooks, identify usage of LODs/table calc/parameters/device layouts.
• Prioritize: migrate high-impact dashboards first.
• Data model: replicate/rethink Tableau extracts → build star schema in Power Query/Data Model.
• Recreate metrics: convert LODs → DAX measures; document assumptions and test values row-by-row.
• Recreate table calcs: map to DAX window patterns; validate ordering and partitioning.
• Recreate interactions: map actions → bookmarks/drillthrough/what-if; use embedded APIs when needed.
• Layouts: design desktop and mobile pages; ensure visuals supported in Power BI.
• Performance: add aggregations, composite models, or materialized views where DAX is slow.
• QA: compare numbers vs Tableau, test edge cases, test on devices.
• Documentation: document conversion patterns, decisions, and owner for maintenance.

Key tips:

• Prefer modeling and pre-aggregation over complex DAX for performance.
• Keep a translation reference for recurring Tableau functions.
• Use automated testing (sample queries) to validate parity after migration.

Requirements / goals:

• Build a clean star schema optimized for Power BI (VertiPaq) to support fast filter propagation, accurate aggregations, and simple DAX.

High-level model:
FactOrders (OrderID PK, OrderDateKey, CustomerKey, ProductKey, Amount)
DimDate (DateKey PK, Date, Year, Month, Day)
DimCustomer (CustomerKey PK, CustomerID, Region, RepID)
DimProduct (ProductKey PK, ProductID, Category)

Cardinality and directions:

• FactOrders → DimDate: Many (FactOrders) to One (DimDate). Cross-filter direction: Single, from DimDate → FactOrders (date slicers filter facts). Cardinality: Many-to-one.
• FactOrders → DimCustomer: Many-to-one. Cross-filter: Single, from DimCustomer → FactOrders.
• FactOrders → DimProduct: Many-to-one. Cross-filter: Single, from DimProduct → FactOrders.

Keys and indexing:

• Use integer surrogate keys (DateKey as YYYYMMDD int, CustomerKey int identity, ProductKey int identity) on dimensions and store FK ints on fact. Keep original source IDs as attributes on dimensions for lookups.
• Add clustered index on FactOrders(OrderDateKey) and non-clustered on (CustomerKey, ProductKey) in the source DB to speed imports/refresh.
• Ensure DimDate has contiguous keys (no gaps) and cover Year/Month columns for drill-down.

Why this helps VertiPaq and DAX:

• Star schema minimizes relationships and keeps facts large and skinny (mostly numeric FKs + measures), ideal for columnar compression and dictionary encoding.
• Integer surrogate keys compress well; contiguous DateKey improves encoding and range scan performance.
• Single-direction relationships simplify filter flow and reduce ambiguous filter contexts for DAX; measures can use CALCULATE with explicit CROSSFILTER only when needed.
• Indexes accelerate refresh/ETL; dimension lookups avoid heavy joins during import. Overall results: smaller model size, faster query time, predictable DAX semantics.

Why it happens

• A FIXED LOD (e.g. {FIXED [Region] : COUNTD([Customer ID])}) is evaluated independently of most worksheet filters — Tableau computes FIXED LODs before regular dimension filters. Only context filters are applied before FIXED. If your dashboard applies filters (e.g. Product Category, Date) as normal filters, the FIXED expression will ignore them and return counts over the unfiltered data, producing inflated numbers.

Fix 1 — make filters affect the FIXED result: use context filters or switch to INCLUDE/EXCLUDE

• Option A — keep FIXED but make relevant filters context:

  • Formula stays: {FIXED [Region] : COUNTD([Customer ID])}
  • Action: right-click relevant filters → Add to Context. Context filters are applied before FIXED so the LOD respects them.
  • Performance: Context filters create a temporary data subset (a temp table) — good for correctness but can slow queries and reduce multi-filter optimization, especially with many distinct values or large datasets.

• Option B — replace FIXED with an LOD that is evaluated after filters (INCLUDE/EXCLUDE):

  • Example using INCLUDE (affected by filters on the view): {INCLUDE [Customer ID] : COUNTD([Customer ID])} or more practically simply {COUNTD([Customer ID])} at the view grain. If you need region-level: place [Region] on rows and use COUNTD([Customer ID]) directly (or {INCLUDE [Customer ID] : MIN(1)} as a count surrogate).
  • Performance: INCLUDE/EXCLUDE are computed after dimension filters so they obey dashboard filters. They may be more expensive if they force row-level calculations, but generally avoid the temp-table cost of context filters.

Fix 2 — use filtered aggregations or pre-aggregate in the data source

• In-Tableau conditional aggregation:

  • Formula: COUNTD(IF [Dashboard Filter Condition] THEN [Customer ID] END)
  • Example: COUNTD(IF [Order Date] >= [Start Date Filter] AND [Order Date] <= [End Date Filter] THEN [Customer ID] END)
  • Performance: Simple and intuitive; avoids LOD semantics. Efficient when filters can be expressed deterministically, but complex conditions can still be heavy.

• Preferred for large data: push aggregation to the database (SQL view or materialized table):

  • Create a pre-aggregated table: SELECT Region, COUNT(DISTINCT CustomerID) AS cust_cnt FROM orders WHERE <filterable columns> GROUP BY Region
  • Performance: Best scalability and predictable performance; Tableau just reads the aggregated results.

Trade-offs summary

• Context filters fix correctness for FIXED LODs but can hurt performance and complicate multi-filter interactions.
• INCLUDE/EXCLUDE LODs obey filters (better correctness without context) but may compute more row-level work.
• Conditional COUNTD or DB-side aggregation often gives the best balance: clearer semantics and better performance when pushed to the database. Choose based on data size, filter complexity, and whether you can modify the underlying data model.