Covers knowledge and hands on skills using enterprise business intelligence tools such as Power BI and Tableau. Candidates should demonstrate the end to end workflow: connecting to diverse data sources including spreadsheets, relational databases, data warehouses, and cloud services; exploring and profiling data to understand schema and quality; and performing data transformation and cleaning using extract transform load processes or built in tool features. Includes building efficient data models with appropriate relationships, hierarchies, and performance minded design, and understanding when to use extracts versus live connections and aggregation strategies. Candidates should be able to create visualizations and interactive dashboards by mapping fields to charts, selecting appropriate chart types, applying filters and parameters, configuring drill down and drill through interactions, and assembling visuals into coherent reports. Covers calculated fields and custom metric creation using expression languages such as Data Analysis Expressions and Tableau table calculations, and awareness of performance implications of complex calculations. Also includes familiarity with differences between paginated reports and interactive dashboards, publishing and sharing workflows, deployment and distribution strategies, governance and access controls including row level security and workspace organization, versioning and refresh scheduling, and basic troubleshooting and optimization techniques. Candidates should be prepared to discuss real projects where they chose visualizations, resolved data quality or performance challenges, iterated on stakeholder feedback, and measured adoption and business impact.
MediumTechnical
49 practiced
Explain how you would set up and troubleshoot scheduled refresh for Power BI datasets that connect to an on-premises SQL Server via the On-premises Data Gateway. Discuss credential types (OAuth, Windows, SQL), gateway modes, clustering/high-availability, common error messages you might see in refresh history, and steps to resolve frequent refresh failures.
Sample Answer
Setup approach:- Install and register the On-premises Data Gateway (personal vs standard). For shared team use, install the standard gateway in “Enterprise” mode on a reliable server, logged in with a service account.- In Power BI Service, add the gateway and map data sources to your on-prem SQL Server (server name, database).Credential types (when configuring the data source in the gateway):- Windows (Kerberos/NTLM): used for Windows-authenticated SQL Server. Choose when you need integrated domain credentials. For delegation across services require Kerberos constrained delegation and SPNs.- SQL Server (username/password): simple SQL auth; useful when Windows auth not available or service account lacks rights.- OAuth2: used primarily for cloud services (not typical for on-prem SQL). Not applicable for native SQL Server connections.Gateway modes & HA:- Standard (default) gateway runs as service; can be clustered for high availability by installing additional gateways and adding them to the same gateway cluster. Power BI automatically balances requests across nodes; ensures failover if one node is down.- Personal mode is single-user, not clusterable; avoid for team datasets.Common refresh errors & troubleshooting steps:- “Data source credentials are invalid” — verify stored credentials in dataset settings and gateway data source; test connection in gateway UI; ensure password not expired.- “Unable to connect to the data source” / network timeout — check firewall, VPN, DNS, server reachable from gateway host; test telnet to SQL port; validate gateway service is running and latest version.- “Gateways offline” or “No gateway available” — ensure gateway machine up, service running, internet outbound ports allowed (TCP 443), gateway logged into same tenant; check gateway version and update.- “The user does not have permission” — ensure the account used has database read permissions and, if pulling metadata, VIEW DEFINITION; for Windows auth verify domain trust and SPN/Kerberos if impersonation needed.- “Query timeout / refresh taking too long” — optimize SQL queries, increase timeout settings, schedule during off-peak, or partition incremental refresh.- “MashupException” / “DataFormat.Error” — inspect Power Query steps, apply error handling, test query in Power Query Editor.Frequent resolution steps:1. Reproduce locally: run the same query from gateway server (SSMS) using the credentials to isolate network vs credential vs query issues.2. Check gateway logs (C:\ProgramData\On-premises data gateway\GatewayLogs) and refresh history details in Power BI for error codes.3. Update gateway to latest version and restart service.4. If using Windows auth and row-level security or impersonation, ensure service account can delegate (Kerberos/SPN).5. For scale/availability, add additional gateway nodes to cluster and distribute load; monitor gateway performance (CPU/memory).6. Use incremental refresh to reduce full-refresh failures and duration.Best practices:- Use a service account with least privilege required, keep credentials centralized and rotated securely.- Run gateway on a stable, patched server with good network connectivity.- Enable logging and monitor refresh failure patterns; document mapping of dataset → gateway node → data source for faster triage.
HardTechnical
53 practiced
Propose a monitoring and alerting architecture for a BI platform that tracks dataset refresh failures, gateway latency, report load-time SLA breaches, and workspace permission drift. Include what telemetry sources to collect, suggested alert thresholds, dashboards for SRE/BI teams, and possible automated remediation actions to reduce manual toil.
Sample Answer
Requirements / constraints:- Track dataset refresh failures, gateway (data connector) latency, report load-time SLA breaches (e.g., 95th percentile), and workspace permission drift. Low false positives, actionable alerts for SRE/BI, automated remediation where safe.High-level architecture:- Instrumentation → Telemetry ingest (Prometheus + Pushgateway or metrics API, plus logs to ELK/Datadog) → Stream processing (Alertmanager / Datadog monitors) → Notification & Runbooks (PagerDuty/Slack) → Dashboarding (Grafana/Power BI) → Automation layer (CI/CD hooks, orchestration via Airflow/Lambda).Telemetry to collect:- Dataset refresh: job_id, dataset_id, source, start/end timestamps, success/fail, error codes, row counts, data freshness lag.- Gateway latency: request_id, backend_duration, queue_time, retry_count, status, host.- Report load time: client_render_time, server_query_time, cache_hit, user_country, report_id, browser.- Workspace permissions: periodic snapshot of ACLs (user, role, permission), last-modified, source of change (UI/API).Suggested alert thresholds (tunable):- Dataset failure rate: >3 failures for same dataset within 1 hour OR failure rate >10% across datasets in 30m.- Gateway latency: p95 > 2s for >10 minutes OR p99 > 5s immediate.- Report SLA: p95 load-time >3s for >15 minutes or >5% of requests in 1h exceed SLA.- Permission drift: any removal of admin role, or new external-user added to prod workspace OR diff detected vs last daily snapshot.Dashboards:- SRE dashboard: cluster health, gateway latency heatmaps, p50/p95/p99, failed request logs, error codes, host-level metrics.- BI dashboard: dataset refresh history (success/fail trend), top failing datasets, data freshness distribution, report load-time by report and user cohort, permission change timeline and diff viewer.- Incident triage view: correlated traces (logs + metrics), affected reports/datasets list, recent deploys.Automated remediation:- Retry dataset refresh with backoff + alert if retries exhausted (Airflow/Lambda).- Failover gateway: route traffic to healthy cluster if host-level error rate > threshold.- Cache warming: proactively refresh cached queries for slow reports after dataset refresh succeeds.- Permission auto-rollback: when drift is detected from approved policy, revert via API unless flagged for manual override; create a ticket and notify owners.- Self-healing playbooks exposed as runbooks in alerts (one-click remediation from PagerDuty/Slack).Operational practices:- Use synthetic tests (canary report loads, scheduled dataset refreshes) to detect regressions.- Add runbooks and ownership metadata to every monitor (who to call, steps).- Start with conservative thresholds, iterate using noise reduction (alert fatigue)—track mean time to detect/resolve.- Ensure telemetry retention long enough for RCA (30–90 days).
EasyTechnical
44 practiced
Describe five simple accessibility and usability practices you apply when building dashboards in Power BI or Tableau (for example: color contrast, keyboard navigation, alt text for images, font scaling, and clear focus order). For each practice explain why it matters and provide a quick method to validate that the rule is satisfied.
Sample Answer
1) High color contrast (text/foreground vs background)Why: Improves readability for low-vision users and in bright environments; avoids reliance on color alone to convey meaning.Validation: Use contrast tools (WebAIM Contrast Checker or Power BI’s built-in accessibility checker) to ensure text and important visuals meet WCAG AA (4.5:1 for normal text) or AAA if possible. Also test with a grayscale filter to confirm information isn’t color-dependent.2) Keyboard navigation and focus orderWhy: Some users can’t use a mouse; logical tab order and visible focus let keyboard users explore the dashboard effectively.Validation: Tab through the dashboard (Tab / Shift+Tab) to ensure focus moves in a meaningful reading order, interactive items are reachable, and focus outlines are visible. In Tableau/Power BI, verify bookmarks/actions are keyboard-accessible and test with only keyboard.3) Alt text and meaningful titles for images and chartsWhy: Screen readers rely on alt text to describe visual content; concise titles explain chart takeaway for everyone.Validation: Add descriptive alt text to images and ARIA-like captions to charts. Use a screen reader (NVDA, VoiceOver) to listen to alt text and ensure it conveys the insight or purpose.4) Scalable fonts and responsive layoutWhy: Users may increase text size or view on different devices; scalable fonts prevent truncation and preserve readability.Validation: Increase browser/OS zoom or change text size (e.g., 200% zoom) and check layout, labels, and tooltips remain readable and don’t overlap. In Power BI, test mobile view.5) Clear visual hierarchy and descriptive labelsWhy: Helps all users scan and understand priority metrics quickly; reduces cognitive load and misinterpretation.Validation: Ensure each chart has a concise title (answering "what" and "insight"), axis labels, legends with clear naming, and consistent use of font sizes and weights. Do a quick 5-second glance test: a colleague should be able to state the dashboard’s top 2 insights in under 10 seconds.Bonus quick checks: run automated accessibility checker in Power BI/Tableau, test in grayscale, keyboard-only, and with a screen reader.
HardTechnical
45 practiced
A scheduled dataset refresh in Power BI intermittently fails and when it completes some visuals in the report are extremely slow to render. Walk through a structured debugging plan across ETL jobs, the data warehouse, network/gateway, Power BI dataset, and frontend visuals to isolate root causes. Include what logs/metrics to collect, tests to perform, and temporary mitigations you might apply while investigating.
Sample Answer
Start with goals: isolate whether failures/slowness originate in upstream ETL, DW, network/gateway, dataset refresh, or visuals; collect evidence; apply quick mitigations to restore service.1) ETL jobs- Logs/metrics: job run history, step-level durations, error messages, row counts, source latency, CPU/memory on ETL host, retry counts.- Tests: re-run failing job manually with subset; validate row counts/hash checksums vs. last successful run.- Mitigations: schedule a rerun out-of-hours; fall back to last known-good snapshot; throttle parallel extractions.2) Data warehouse- Logs/metrics: query performance (query plans, long-running queries), blocking/locks, table/index fragmentation, growth metrics, disk I/O, recent schema changes, statistics age.- Tests: run representative queries used by report (LIMIT sample) and full aggregated queries; check resource contention (sp_who2, sys.dm_*).- Mitigations: rebuild stats/indexes, increase resource class for ETL, move expensive indices offline, restore partition to yesterday.3) Network / Gateway- Logs/metrics: gateway logs (On-prem gateway), network latency, packet loss, throughput, TLS errors, concurrent connection count.- Tests: ping/traceroute from gateway to DW and sources; reproduce refresh from gateway diagnostic tool; test from a different gateway region/node.- Mitigations: switch to alternate gateway, increase gateway cluster nodes, retry with direct cloud connection if available.4) Power BI dataset refresh- Logs/metrics: refresh history (duration, success/failure step), query diagnostics (performance analyzer, Query Folding info), Mashup engine memory/CPU, incremental refresh logs.- Tests: refresh only specific tables; run “Refresh” in Desktop with diagnostics enabled; enable/disable privacy levels to test folding.- Mitigations: disable non-critical queries, split dataset into smaller datasets, enable incremental refresh, reduce parallelism.5) Frontend visuals- Logs/metrics: Performance Analyzer timings, DAX Query Plan, timeline of visual rendering, browser devtools network and CPU, dataset size in model view.- Tests: open report with single visual, reproduce slowness; test with optimized visual (simpler chart) or with filters; test with Aggregations/Materialized views.- Mitigations: replace heavy visuals (high-cardinality slicers) with alternatives, add pre-aggregated tables, enable query caching, pin slow visuals to dashboard with cached tiles.Investigation workflow:- Correlate timestamps across logs to find where latency spikes/failures begin.- Prioritize reproducible tests: manual ETL -> direct DW queries -> gateway checks -> Power BI Desktop refresh -> report load with Performance Analyzer.- Communicate interim status and use temporary mitigations (rollback snapshot, incremental refresh, alternate gateway) to restore stable reporting while fixing root cause.Key stop conditions: persistent ETL failures indicate upstream fix; DW contention suggests indexing/partitioning; gateway/network issues point to infra; long DAX/visual times point to model/visual redesign.
HardSystem Design
50 practiced
Architect an enterprise BI platform that supports both governed central datasets and self-service authoring. Requirements: 5,000 users (500 concurrent authors/consumers), sub-second dashboard queries for aggregated metrics, daily ETL from OLTP sources plus near-real-time feeds for some operational reports, secure distribution, and compliance with access controls. Describe components (data warehouse, ETL/streaming, semantic layer, BI tools), dataset publishing model, caching/aggregation strategy, security architecture, and how to balance self-service with governance.
Sample Answer
Requirements clarification:- 5,000 total users (≈500 concurrent authors/consumers), sub-second aggregated dashboard queries, daily ETL from OLTP + near-real-time (seconds–minutes) for some reports, secure distribution and row/column-level access controls, governed central datasets plus self-service authoring.High-level architecture:- Ingest: CDC streams (Debezium/Kafka) for near-real-time feeds; scheduled batch jobs (Airflow) for daily ETL.- Data lakehouse: Cloud object store (S3/ADLS) with a table format (Delta/Apache Iceberg) to store raw and curated data.- Enterprise DW (columnar MPP): Snowflake/BigQuery/Redshift Spectrum for fast analytics and materialized aggregates.- Semantic layer / governed datasets: A central metrics layer (dbt + a metrics store / semantic layer like Cube.js, Atlan/Alation-managed metrics, or Power BI Tabular/Analysis Services) publishing certified datasets/models.- BI tools: Support both governed visuals (Power BI/ Tableau connected to semantic layer) and self-service tools that can consume certified datasets or sandboxed datasets.- Serving/cache layer: Materialized views and pre-aggregations in the DW, plus an in-memory cache (Redis or BI tool caching) and CDN for static assets.Dataset publishing model:- Dev → Staging → Certified workflow using dbt for transformations and tests. Certified datasets (star schemas, documented metrics) are registered in a catalog with lineage, SLA, and owners. Analysts can fork certified models into sandbox schemas; any promoted fork must pass tests and governance checks.Caching & aggregation strategy:- Precompute heavy aggregates and time-buckets as materialized views; use query acceleration (Snowflake result cache / BigQuery BI Engine) for sub-second response.- For ultra-low-latency operational reports, stream into a real-time aggregate store (Kafka Streams → Materialized view in ClickHouse / Druid) with TTL.- Use adaptive TTL caching at BI layer; incremental refreshes for dashboards.Security & compliance:- Centralized IAM (Azure AD/Okta) + SSO. Row-level and column-level security enforced at DW/semantic layer. Use attribute-based access control (ABAC) mapped from identity provider groups.- Data masking/encryption for PII, audit logs for all data access (Cloud SIEM), and certified dataset access controls managed in the catalog.- Periodic compliance scans and automated policy enforcement (e.g., sensitive-data detectors in CI).Balancing self-service with governance:- Provide high-quality, discoverable certified datasets to cover common needs so analysts don’t reinvent logic.- Offer sandbox spaces with resource quotas and automatic lineage capture; require tests and documentation to promote to certified.- Governance guardrails: automated testing (dbt), CI pipelines, approval workflows, and quarterly reviews with data stewards.- Training and templates for analysts to encourage reuse and metric consistency.Operational concerns:- Monitor query patterns, scale compute separately from storage, cost controls (auto-suspend warehouses), and SLOs for data freshness. Assign data stewards and SLAs for certified datasets.
Unlock Full Question Bank
Get access to hundreds of Business Intelligence Tool Proficiency interview questions and detailed answers.
