BI Tools: Tableau, Power BI, and Looker Questions

Concrete example: a "Regional Sales" report built on an on-prem SQL Server database, published to a workspace called "Sales Analytics", shared with the internal sales team plus one external vendor contact.

Steps

1. In Power BI Desktop: validate the model, mark a Date table, save the PBIX file (Power BI Desktop's project file, it holds both the data model and the report visuals).
2. Publish: Home tab, Publish, choose the "Sales Analytics" workspace in Power BI Service.
3. In the Service, open the dataset's Settings, Data source credentials, and sign in. If the source supports it, use OAuth (a login flow where you sign in with your Microsoft/Google/etc. account and Power BI receives a token to access data on your behalf, instead of you typing a password into Power BI directly); otherwise use a SQL login.
4. Because SQL Server here sits inside the company network (on-prem), install an On-premises Data Gateway (a small app installed on a machine inside that network that acts as a secure bridge, letting Power BI Service reach data behind the firewall). In the dataset settings, map the data source to that gateway and test the connection.
5. Scheduled refresh: in Dataset settings, set Refresh frequency (e.g., daily at 2am), time zone, and failure email alerts. If the report instead used DirectQuery (a connection mode where Power BI does not copy the data at all; every time someone interacts with a visual, it sends a live query straight to SQL Server), there's no data refresh to schedule, refresh just re-checks the connection.
6. Sharing: internal sales team gets access via workspace roles or a published App. For the external vendor contact, add them as a guest B2B user in Azure AD (a way to invite someone outside the company by email; they get a limited guest account without needing a full company license) and share the report or app with that guest account. Avoid "Publish to web", it makes the report public on the internet with no login required, wrong for anything containing real customer or sales data.

Cloud-only vs hybrid/on-prem

• Cloud-only (e.g., source is an Azure SQL Database or a SaaS connector): no gateway needed, the Service connects directly, credentials are OAuth or stored keys in the Service.
• Hybrid/on-prem (our SQL Server example): requires the gateway step above; every scheduled refresh and every DirectQuery request is routed through that gateway machine, so it needs stay-on network access and the right database drivers installed.

Security notes
Use a dedicated low-privilege service account for the gateway's data source login rather than a personal account. For the external vendor, conditional access policies (rules IT sets in Azure AD that restrict sign-in by device, location, or requiring multi-factor authentication) can be applied to guest accounts so external access is still controlled even though it's outside the company.

Troubleshooting steps

1. Reproduce and baseline: open the report in Desktop and enable Performance Analyzer (View tab > Performance Analyzer > Start Recording).
2. Interact with the slow visual and read the breakdown Performance Analyzer gives per visual: 'DAX Query' (time spent getting data), 'Visual Display' (time spent drawing it), and 'Other' (waiting on other visuals). For example, a typical trace might show: Visual Display 90ms, DAX Query 2,400ms, Other 30ms. That gap makes the culprit obvious: here, almost all the time is in the DAX query, so start there rather than touching the visual.
3. Copy the DAX query from Performance Analyzer and paste it into DAX Studio, then run it with 'Server Timings' turned on.
   • 'Server Timings' is a DAX Studio feature that splits the query's execution time into two engines: the Storage Engine (SE), the fast, multi-threaded engine that scans Power BI's compressed in-memory data (this compressed in-memory format is called VertiPaq, Power BI's columnar storage engine for Import-mode tables), and the Formula Engine (FE), a single-threaded engine that handles row-by-row logic DAX can't push down to storage (iterators, complex FILTER logic, or DirectQuery translation).
   • Worked example: Server Timings might report SE = 2,100ms, FE = 300ms out of the 2,400ms total. Because SE dominates, the query is storage-engine bound, meaning the volume of data being scanned is the problem, not the DAX logic itself. Conversely, if FE dominated (say FE = 1,900ms, SE = 200ms), that points to an expensive, non-foldable calculation (nested iterators, context transition) as the bottleneck.
4. Check server-side signals: for Premium/Fabric capacities, 'Query Diagnostics' (a built-in Power BI feature that records detailed timing/trace events for a refresh or query, similar to what Performance Analyzer shows but capturable at scale) and gateway performance counters (CPU, memory, network latency on the on-premises gateway machine) can reveal whether slowness is local to your machine or systemic across all users.

Differentiate data retrieval vs rendering

• Long 'DAX Query' time in Performance Analyzer, confirmed by high SE or FE time in DAX Studio, means the problem is data retrieval: the query itself is expensive.
• If the DAX query comes back fast (say, under 200ms) but 'Visual Display' is still slow (say, 2 seconds), the problem is rendering: too many data points, a heavy custom visual, or complex conditional formatting the client has to draw.

When to optimize DAX vs simplify visuals vs change storage mode

• Optimize DAX when SE or FE time dominates: rewrite iterators with variables, replace row-by-row FILTER with SUMMARIZE/pre-aggregated logic, or add explicit measures instead of implicit ones.
• Simplify visuals when 'Visual Display' dominates: reduce the number of data points (Top N, binning), replace an expensive custom visual with a native one, or split a busy visual into a drillthrough page.
• Change storage mode when the query is DirectQuery and SE time is dominated by slow round-trips to the source database (not VertiPaq): consider Import mode, a composite model, or aggregation tables so common queries are answered from cached, in-memory data instead of the live source.

Outcome
Collect the metrics above, fix the highest-cost layer first (usually the query/data layer), then re-run Performance Analyzer to confirm the improvement before touching anything else.

Goal: reliable incremental refresh with multi-timezone event timestamps and late arrivals.

Power Query approach (convert to UTC then filter by RangeStart/RangeEnd):

let
  Source = Csv.Document(File.Contents(...)),
  ChangedType = Table.TransformColumnTypes(Source,{{"EventLocalTime", type datetime}, {"TimeZoneOffsetMinutes", Int64.Type}}),
  EventUtc = Table.AddColumn(ChangedType, "EventUTC", each DateTimeZone.ToUtc(DateTime.AddZone([EventLocalTime], 0, [TimeZoneOffsetMinutes]))),
  Filtered = Table.SelectRows(EventUtc, each [EventUTC] >= RangeStart and [EventUTC] < RangeEnd)
in
  Filtered

Notes:

• RangeStart/RangeEnd are parameters created by Power BI incremental refresh.
• DateTime.AddZone takes numeric (hours, minutes) to attach a zone offset to a local datetime, producing a datetimezone value; it does not take a #duration argument. DateTimeZone.ToUtc then converts that zoned value to UTC by applying the offset.
• Use DateTimeZone functions to handle offsets and DST.

Late-arriving events and buffer window:

• Apply a buffer by subtracting buffer minutes from RangeStart in query or configure partition refresh policy: for example use RangeStartBuffered = RangeStart - #duration(0,0,bufferMinutes,0) to include late events.
• Configure policy: keep recent partitions (e.g., last 5 days) as overlapping and perform full refresh for those partitions on every scheduled run.

Service-side policy and backfill:

• Incremental refresh in the service creates partitions per period (day/month). To backfill, adjust RangeStart/RangeEnd or run a full refresh for historical partitions via XMLA APIs or use the Power BI REST API to trigger refreshes on partitions.
• For large backfills, use partition-level management (XMLA) to load historical data efficiently.

As a data scientist I ensure timestamps are normalized to UTC upstream, set a realistic buffer window based on SLA for late arrivals, and use partition-level backfill for cost-effective historical loads.

Requirements & constraints
Embed Power BI reports inside a multi-tenant web app, securely, with tenant isolation enforced through RLS, at a scale of thousands of concurrent users, without ever exposing Power BI credentials to the browser.

High-level architecture

• A backend API (your service) is the only thing that talks to Power BI's REST API. It authenticates as itself (not as any human user), fetches a short-lived embed token, and hands that token to the frontend, which uses it to render the report in an iframe/SDK component.
• One Power BI workspace per environment (dev/stage/prod); each dataset has RLS roles defined so the same report can show different data per tenant.

Auth & app registration

• Register an application in Azure AD (Microsoft's identity platform) and grant it Application permissions to the Power BI service, with admin consent.
• Your backend calls Azure AD using the OAuth 'client credentials' flow: the OAuth mode where the app authenticates as itself, using a client ID and secret, with no human user signing in at that moment. It's the right fit here because the backend, not an end user, is the thing requesting tokens from Power BI.

Service principal vs master user

• A service principal is an Azure AD identity that represents your application, not a person. Using one for token requests is non-interactive and auditable (every request ties back to the app, not a shared login), and it's the standard choice for production multi-tenant embedding.
• A 'master user' approach means signing in as one shared Power BI user account and using its session to generate tokens. It's simpler to set up but risky (that one account's credentials become a single point of failure, and every action shows up as the same user in the audit log), so treat it as dev/prototype-only.

Effective identity & RLS mapping
An 'effective identity' is a small JSON object attached to the embed token request that tells Power BI 'render this report as if this specific user were signed in', even though it's really your backend calling on their behalf. Example:

{
  "username": "tenant42-user@contoso.com",
  "roles": ["TenantRLS"],
  "datasets": ["<datasetId>"],
  "customData": "TenantID=42"
}

Your backend builds this from the logged-in web-app user's tenant ID, and the dataset's RLS DAX role filter reads USERNAME() and/or the customData value to restrict rows to that tenant. This lets RLS work even for tenants that have no real Azure AD account of their own.

Token lifetimes & refresh

• Embed tokens are short-lived (on the order of an hour). The frontend should call a small backend 'refresh embed token' endpoint before expiry (or on a 401 from Power BI), rather than the frontend ever holding the Azure AD client secret.

Scaling & caching

• Requesting a brand-new embed token from Power BI on every page load doesn't scale to thousands of concurrent users; cache issued tokens keyed by (tenant, report, role) in a distributed cache like Redis (a shared, external, in-memory key-value store all your backend instances can read from) for a TTL a bit shorter than the token's real lifetime, so most requests are served from cache instead of calling Power BI again.
• Wrap calls to the Power BI REST API in a circuit breaker (a safety pattern that stops sending requests to a failing dependency for a cooldown period, instead of retrying it repeatedly and making an outage worse), so a Power BI API slowdown doesn't cascade into your whole app timing out.

Security & multi-tenant isolation

• Never trust the frontend's claimed tenant ID; the backend must derive tenant identity from its own authenticated session and reject any embed request where the requested report/dataset doesn't belong to that tenant.
• Log every token issuance (who, which tenant, which report, when) via Azure Activity Logs or your own audit table, so a suspicious embed request is traceable after the fact.

Why this suits a Data Scientist
This pattern gives me confidence that an embedded report is only ever showing one tenant's data, which matters when that same dataset is feeding model feature validation or stakeholder-facing metrics, not just casual browsing.

Design summary
Create a star schema: one fact table for Orders and one event-fact for WebEvents; Customers, Products, and Date as dimensions.

Fact and dimension tables & grain

• Fact_Orders (grain: one row per order_line): order_id, customer_id, product_id, date_key, quantity, price, currency, revenue_local
• Fact_WebEvents (grain: one row per event): event_time, session_id, product_id, event_type, date_key
• Dim_Customers: customer_id, name, region, current_flag, effective_date, expiry_date (for SCD)
• Dim_Products: product_id, category, brand
• Dim_Date: date_key, date, year, month, fiscal flags

Relationships

• Fact_Orders.customer_id -> Dim_Customers.customer_id (many-to-one)
• Fact_Orders.product_id -> Dim_Products.product_id
• Fact_Orders.date_key -> Dim_Date.date_key; Fact_WebEvents.date_key -> Dim_Date

Date table requirements

• Single marked Date table with continuous range covering 10+ years, set as Mark as Date Table. Include fiscal attributes and flags for business periods.

Key measures

• Revenue = SUM(Fact_Orders[revenue_local])
• Conversion Rate = DIVIDE( COUNTROWS( FILTER(Fact_WebEvents, event_type = "purchase")), DISTINCTCOUNT(Fact_WebEvents[session_id]), 0)
• CLTV (simple cohort approach): group customers by acquisition month (their cohort) and average how much revenue that cohort has generated so far. As a DAX measure:

CLTV (Cohort Avg) =
VAR CohortRevenue =
    CALCULATE(
        SUM(Fact_Orders[revenue_local]),
        ALLEXCEPT(Dim_Customers, Dim_Customers[acquisition_month])
    )
VAR CohortCustomers =
    CALCULATE(
        DISTINCTCOUNT(Fact_Orders[customer_id]),
        ALLEXCEPT(Dim_Customers, Dim_Customers[acquisition_month])
    )
RETURN
    DIVIDE(CohortRevenue, CohortCustomers)

Worked example: if the customers acquired in January had 500 people in that cohort, and together they've generated $150,000 in revenue since, this measure returns $150,000 / 500 = $300 average revenue per customer for that cohort so far. This is a simplified, non-discounted proxy for CLTV (average revenue per customer to date), not a true predictive lifetime value; a more rigorous version would project forward using retention/churn rates rather than only looking backward at revenue booked so far.

Handling slowly changing customer attributes

• Use SCD Type 2 in Dim_Customers: keep effective_date, expiry_date, and current_flag. Capture customer changes at ETL/dataflow level; ensure Fact_Orders links to customer_id surrogate. For short-term needs, SCD Type 1 may suffice for overwrites, but prefer SCD2 for analytics and CLTV accuracy.

This layout supports marketing funnels (events->orders), finance revenue reporting, and retention/LTV analysis with clear grains and time dimension for time intelligence.