Dashboard Performance and Publishing

Query Performance Optimization

Dashboard performance is not a luxury — it is a requirement. A dashboard that takes more than 5 seconds to load will be abandoned by users. Performance optimization is the process of identifying bottlenecks and eliminating them through a combination of data modeling, query optimization, and visualization design.

Identifying Performance Bottlenecks

The first step in optimization is identifying where the time is being spent. Both Tableau and Power BI provide tools for this.

In Tableau, use the Performance Recorder (Help > Settings and Performance > Start Performance Recording). It captures the time spent on each query, rendering, and data source connection. The results are displayed in a performance summary dashboard that shows the slowest operations.

In Power BI, use Performance Analyzer (View > Performance Analyzer). It captures the time spent on each visual's query, DAX calculation, and rendering. The results are displayed in a pane that shows the duration of each operation.

Common Performance Bottlenecks

1. Large data sources. Querying millions of rows is slow. The solution is to aggregate the data before it reaches the dashboard, or to use extracts that pre-compute aggregations.

2. Complex calculations. Calculated fields and DAX measures that iterate over large tables are slow. The solution is to simplify calculations, use variables, or pre-compute values in the data source.

3. Too many visuals. Each visual generates a separate query. A dashboard with 20 visuals generates 20 queries. The solution is to reduce the number of visuals, use combined charts, or use tooltips instead of separate visuals.

4. Inefficient data sources. A data source with unnecessary columns, rows, or joins is slow. The solution is to clean the data source, remove unused columns, and optimize joins.

5. Network latency. A dashboard that queries a remote data source over a slow network is slow. The solution is to use extracts, move the data source closer to the dashboard, or use a faster network connection.

Optimizing Data Sources

The most effective performance optimization is at the data source level. A well-designed data source can reduce query times by 90% or more.

1. Remove unused columns. Every column in the data source consumes memory and query time. Remove any column that is not used in the dashboard.

2. Filter unnecessary rows. If the dashboard only shows data for the last 2 years, filter out older data at the source.

3. Aggregate when possible. If the dashboard shows monthly sales, aggregate the data to the monthly level instead of querying daily data.

4. Optimize joins. Use inner joins instead of left joins when possible. Avoid joining on calculated fields.

5. Use appropriate data types. Use integers instead of strings for IDs. Use dates instead of strings for dates.

Optimizing Calculations

Calculations are the second most common performance bottleneck. A calculation that iterates over a million rows will be slow regardless of the data source.

1. Use simple aggregations. SUM, AVG, COUNT are fast. SUMX, AVERAGEX are slow because they iterate.

2. Avoid nested iterators. SUMX(VALUES(Table, SUMX(...))) is extremely slow. Use CALCULATE instead.

3. Use variables. Variables are evaluated once and reused. Repeating the same calculation multiple times is wasteful.

4. Pre-compute in the data source. If a calculation is used in multiple visuals, pre-compute it in the data source instead of in the dashboard.

5. Use DAX optimization techniques. In Power BI, use SUMMARIZE instead of ADDCOLUMNS when possible. Use CALCULATE with FILTER instead of ITERATE.

Optimizing Visualizations

The third performance bottleneck is the visualization itself. A visualization with too many data points, too many marks, or too many filters is slow.

1. Limit data points. A line chart with 10,000 data points is slow. Aggregate to a higher level or use sampling.

2. Reduce marks. A scatter plot with 100,000 marks is slow. Use clustering, sampling, or aggregation.

3. Simplify tooltips. A tooltip with 10 visuals generates 10 queries. Use a single tooltip visual or disable tooltips.

4. Use context filters. Context filters reduce the data before other filters are applied. This can significantly improve performance.

5. Avoid unnecessary interactions. Cross-filtering and drill-through generate additional queries. Use them judiciously.

Practical Example: Optimizing a Slow Dashboard

Suppose you have a sales dashboard that takes 30 seconds to load. The Performance Recorder shows that 25 seconds are spent on the data source query.

1. Identify the bottleneck. The data source query is slow because it queries 10 million rows.

2. Optimize the data source. Create an extract that aggregates the data to the monthly level. The extract has 12 rows per region instead of 365.

3. Optimize calculations. Replace SUMX(Sales, Sales[Revenue] * Sales[Quantity]) with SUM(Sales[Revenue]) if the data source already has a revenue column.

4. Optimize visualizations. Reduce the number of visuals from 15 to 8. Combine related charts into dual-axis charts.

5. Test the result. The dashboard now loads in 3 seconds.

Common Pitfalls

1. Optimizing without measuring. Always use the Performance Recorder or Performance Analyzer to identify bottlenecks before optimizing.
2. Over-optimizing. A dashboard that loads in 1 second is not necessarily better than one that loads in 3 seconds. Optimize until the performance is acceptable, then stop.
3. Ignoring the data source. The most effective optimizations are at the data source level. Dashboard-level optimizations have limited impact.
4. Forgetting to test after optimization. Always test the dashboard after making changes to ensure the optimizations actually improved performance.
5. Optimizing for the wrong metric. Query time is not the only metric. Rendering time and user experience are also important.

Data Extracts and Scheduling

A data extract is a snapshot of the data stored in a compressed, columnar format. Extracts are the primary mechanism for improving dashboard performance in Tableau. They are also used in Power BI for import mode and incremental refresh.

Why Extracts Improve Performance

Extracts improve performance for three reasons:

1. Columnar storage. Data is stored by column, not by row. This allows the query engine to read only the columns it needs, reducing I/O.

2. Compression. Extracts use compression to reduce the size of the data. A 1 GB data source might compress to 100 MB.

3. Pre-aggregation. Extracts can pre-compute aggregations, reducing the amount of data that needs to be queried at runtime.

Creating Extracts in Tableau

To create an extract in Tableau, connect to a data source, go to the Data Source tab, and select "Extract" instead of "Live." Tableau creates a .hyper file that contains the snapshot of the data.

You can configure the extract to:

1. Filter data. Include only the rows and columns needed for the dashboard.

2. Aggregate data. Pre-aggregate to a higher level (e.g., monthly instead of daily).

3. Compute calculations. Pre-compute calculated fields in the extract.

4. Hide unused fields. Hide fields that are not used in the dashboard to reduce the extract size.

Incremental Refresh

An incremental refresh updates only the new or changed data in the extract, instead of rebuilding the entire extract. This is essential for large datasets where a full refresh would take hours.

To configure incremental refresh:

1. Identify a column that indicates when a row was added or modified (e.g., a timestamp or an auto-incrementing ID).

2. Configure the extract to refresh only rows where that column is greater than the last refresh value.

3. Schedule the refresh to run at regular intervals (e.g., every hour, every day).

Scheduling Extracts in Tableau Server/Cloud

Tableau Server and Tableau Cloud allow you to schedule extract refreshes. You can configure:

1. Refresh frequency. Hourly, daily, weekly, or monthly.

2. Refresh time. The time of day when the refresh runs.

3. Priority. The priority of the refresh relative to other refreshes.

4. Notifications. Email notifications when a refresh succeeds or fails.

Power BI Import Mode and Incremental Refresh

In Power BI, the equivalent of a Tableau extract is import mode. In import mode, the data is loaded into Power BI's in-memory engine (VertiPaq), which uses columnar storage and compression.

Power BI also supports incremental refresh, which updates only the new or changed data. To configure incremental refresh:

1. Create a parameter for the start date and end date.

2. Filter the data using these parameters.

3. Configure the incremental refresh policy in Power BI Service (Dataset settings > Incremental refresh).

4. Specify the refresh range (e.g., refresh the last 5 days) and the archive range (e.g., keep 5 years of data).

Live Connections vs. Extracts

A live connection queries the data source in real time. An extract queries a snapshot of the data. The choice depends on the requirements.

Use a live connection when data freshness is critical and the data source is fast. Use an extract when performance is critical and data can be refreshed on a schedule.

Practical Example: Extract Strategy for a Sales Dashboard

Suppose you have a sales dashboard with 10 years of data. The dashboard shows daily sales for the current year and monthly sales for prior years.

1. Create an extract that includes only the last 3 years of data.

2. Aggregate prior years to the monthly level.

3. Keep daily data for the current year.

4. Schedule a daily refresh at 6 AM to update the current year's data.

5. Schedule a monthly refresh to update the prior years' data.

This strategy balances performance and data freshness. The extract is small (3 years of data), the refresh is fast (daily for current year, monthly for prior years), and the dashboard loads quickly.

Common Pitfalls

1. Forgetting to schedule refreshes. An extract that is not refreshed becomes stale. Always schedule refreshes.
2. Refreshing too frequently. Refreshing every minute puts unnecessary load on the data source. Refresh only as often as needed.
3. Refreshing too infrequently. An extract that is refreshed once a month may be too stale for operational dashboards. Match the refresh frequency to the business need.
4. Not monitoring refresh failures. A failed refresh means the dashboard shows stale data. Set up notifications for refresh failures.
5. Using extracts when live connections are needed. If the dashboard must show real-time data, use a live connection. Extracts are not suitable for real-time data.

Publishing to Tableau Server/Cloud and Power BI Service

Publishing is the process of deploying a dashboard from the desktop authoring tool to a server where users can access it. In Tableau, this means publishing to Tableau Server or Tableau Cloud. In Power BI, this means publishing to Power BI Service.

Publishing to Tableau Server/Cloud

To publish a Tableau workbook:

1. Server > Publish Workbook. Sign in to Tableau Server or Tableau Cloud.

2. Select the project. Projects organize workbooks and data sources.

3. Configure permissions. Set who can view, edit, and interact with the workbook.

4. Choose the data source. Publish the data source separately or embed it in the workbook.

5. Set refresh schedules. Configure extract refreshes for the published data source.

Publishing to Power BI Service

To publish a Power BI report:

1. Home > Publish. Sign in to Power BI Service.

2. Select the workspace. Workspaces organize reports and datasets.

3. Configure permissions. Set who can view, edit, and share the report.

4. Set up data source credentials. Power BI Service needs credentials to refresh the data source.

5. Configure refresh schedules. Set up scheduled refreshes for the dataset.

Row-Level Security

Row-level security (RLS) restricts the data that a user can see based on their identity. It is essential for dashboards that contain sensitive data.

In Tableau, RLS is implemented using user filters. A user filter maps a Tableau user to a data value (e.g., a region or department). When the user views the dashboard, Tableau applies the filter automatically.

In Power BI, RLS is implemented using roles. A role is a DAX expression that filters the data (e.g., Sales[Region] = USERNAME()). You assign users to roles in Power BI Service.

// Power BI RLS: users see only their region's data
Sales[Region] = USERNAME()

// Power BI RLS: users see only their department's data
Employee[Department] = LOOKUPVALUE(
    Employee[Department],
    Employee[Email],
    USERNAME()
)

Data Source Credentials

When you publish a dashboard, the server needs credentials to connect to the data source. There are two options:

1. Embedded credentials. The credentials are stored in the published workbook or dataset. This is simple but less secure.

2. OAuth or single sign-on. The user's credentials are passed through to the data source. This is more secure but requires configuration.

In Power BI Service, you configure data source credentials in the Dataset settings. You can use OAuth, Windows authentication, or a gateway for on-premises data sources.

Gateway Configuration

For on-premises data sources, you need a gateway. A gateway is a bridge between Power BI Service and the on-premises data source.

1. Install the gateway on a machine that has access to the data source.

2. Configure the data source in the gateway with the connection details and credentials.

3. Map the dataset to the gateway data source in Power BI Service.

4. Schedule refreshes through the gateway.

Version Control and Deployment

As dashboards evolve, you need a way to manage versions and deploy changes. Both Tableau and Power BI support deployment pipelines.

In Tableau, use projects and permissions to separate development, testing, and production. Publish workbooks to different projects for each environment.

In Power BI, use deployment pipelines (Premium feature) to move datasets and reports from development to testing to production. Deployment pipelines support automatic validation and rollback.

Practical Example: Publishing a Sales Dashboard

Suppose you have developed a sales dashboard in Power BI Desktop. The dashboard uses a SQL Server database as the data source.

1. Develop the dashboard in Power BI Desktop with sample data.

2. Test the dashboard with different users and scenarios.

3. Publish to Power BI Service in the development workspace.

4. Configure data source credentials using OAuth.

5. Set up row-level security with roles for each region.

6. Schedule refreshes every hour.

7. Test the published dashboard in Power BI Service.

8. Deploy to production using a deployment pipeline.

9. Share with users by granting access to the workspace or app.

Common Pitfalls

1. Forgetting to configure credentials. A published dashboard without credentials cannot refresh. Always configure credentials after publishing.
2. Not setting up row-level security. A dashboard without RLS may expose sensitive data. Always configure RLS before sharing.
3. Publishing to the wrong workspace. A dashboard published to a personal workspace is not visible to others. Publish to a shared workspace.
4. Not testing after publishing. A dashboard that works in Desktop may not work in Service. Always test after publishing.
5. Ignoring refresh failures. A failed refresh means the dashboard shows stale data. Monitor refresh history and set up notifications.