Chapter 9 covers dashboards built in Quarto, where the entire product is a text file under version control. Many public health teams publish through Tableau or Power BI instead. The license is already paid, often at the enterprise level; program staff already know the interface; and in agencies where the BI platform is the sanctioned publishing route, a Quarto dashboard is not an accepted substitute regardless of its technical merits.
This chapter covers how to keep an analysis reproducible when the presentation layer is a proprietary application. The organizing principle is that shared analytical rules belong in version-controlled preparation code, and the BI tool reads the prepared data. Calculations that depend on interactive selections may remain in the BI model, with their definitions and checks under version control where the format supports it.
There is a longer-term argument for the same arrangement. R skills and reusable analysis code transfer across projects, including those using different BI platforms. The same holds for the work itself: an R pipeline survives an agency replacing Tableau with Power BI. Keep the analysis portable when deciding where to implement it.
10.1 Dividing the Work
Keep the R preparation code in version control, test it, and verify that it reruns from raw data. Use the BI application to display the prepared extract.
It can be convenient to recode categories, exclude records, or define rates inside a workbook. Those rules then need their own documentation, tests, and review. If another report uses the same rules, implement them in shared preparation code to avoid maintaining two versions.
For a specified set of filters, check that an independent calculation reproduces the dashboard’s numbers. Include any BI measures in that reconciliation, with the same denominator, grouping, and suppression rules.
Tip
Choose the prepared table’s level of detail to support the intended views. Keep the components needed for recalculation, including compatible rate numerators and denominators. Document each column’s meaning.
10.2 Pre-Aggregating Before Import
BI dashboards rarely display individual records. They display counts by county and week, rates by age group, trends by month. Importing line-level data into a dashboard that only shows aggregates means the BI engine recomputes the same aggregation on every interaction, over a table orders of magnitude larger than the result.
Performance degrades as the source data grows, and the usual remedy is to aggregate in R before import. Consider a simulated line-level influenza case file:
set.seed(42)counties<-c("Fairfax","Arlington","Loudoun","Prince William","Alexandria","Chesterfield","Henrico","Richmond","Roanoke","Virginia Beach")n<-250000cases<-data.frame( case_id =seq_len(n), county =sample(counties, n, replace =TRUE), onset_date =as.Date("2021-01-03")+sample(0:1090, n, replace =TRUE), age_group =sample(c("0-17", "18-49", "50-64", "65+"),n, replace =TRUE), hospitalized =sample(c(TRUE, FALSE), n, replace =TRUE, prob =c(0.08, 0.92)))nrow(cases)
Two hundred fifty thousand rows become about six thousand, and the imported file shrinks accordingly. Nothing the dashboard displays is lost.
The cost is that the aggregation grain fixes what the dashboard can show. Drilling down to individual cases or slicing by an unretained variable is no longer possible. For a surveillance dashboard this constraint is usually appropriate, since individual records should not be exposed, but choose the aggregation level before building the dashboard.
Warning
Aggregation grain interacts with disclosure rules. A view that permits filtering to a single county, a single month, and a single age group can produce cells below the suppression threshold. Quarterly aggregation may reduce the number of small cells, but still requires disclosure checks. Choose the grain with the thresholds in Section 25.6 in mind, and check suppression before publishing and across all permitted filter combinations.
10.2.1 Default Aggregation
BI tools sum measures by default. Check whether that default makes sense for each measure.
Two cases recur. A measure that should be broken out by a dimension is totaled across it, so a view intended to show the current year’s burden silently reports a multi-year total. And a rate or percentage is averaged when it should be recomputed from summed numerators and denominators; the mean of ten county rates is not the state rate.
For an interactive crude rate, retain compatible numerators and denominators and calculate the ratio for the selected group. Ten cases among 1,000 people and 45 among 9,000 yield different rates; a dashboard combining them should calculate (10 + 45) / (1000 + 9000) * 100000, or 550 per 100,000. Do not sum the displayed rates. Summing denominators is valid only when their units, periods, and populations support that operation; repeated population values across weeks, for example, cannot be added to produce an annual population.
Define and review this calculation in the BI model, or limit the dashboard to precomputed groups. Distinct-person counts across overlapping groups and age-adjusted rates also need their own aggregation rules. Reconcile representative filter combinations against independent calculations, including empty selections and zero denominators.
Suppression must apply to the released data and all supported views. Hiding a mark while leaving its count in a downloadable extract does not protect it. Check whether totals, adjacent periods, or combinations of filters reveal a suppressed count by subtraction. Apply complementary suppression, restrict the available views, or release a separately reviewed aggregate when needed (Section 25.6).
10.3 Publishing Programmatically
The manual publishing loop runs the R script, writes a file, opens the workbook, refreshes the data source, saves, and publishes. A missed refresh leaves the published dashboard out of date.
Both major platforms expose REST APIs that close the loop from code, using the httr2 patterns covered in Chapter 18. Tableau Server and Tableau Cloud authenticate with a personal access token and return a credentials token for subsequent calls:
For an existing published data source, set datasource_id to its Tableau ID and request a refresh. The server must be able to reach the source data; this request does not upload a CSV created on your laptop. A successful request starts an asynchronous job, whose completion must also be checked.
request(paste0(server,"/api/",api,"/sites/",site_id,"/datasources/",datasource_id,"/refresh"))|>req_headers(`X-Tableau-Auth` =token, Accept ="application/json")|>req_body_raw("<tsRequest></tsRequest>", type ="text/xml")|>req_perform()
Power BI offers a dataset refresh endpoint in its REST API, with authentication through Microsoft Entra ID. The specific endpoints change on the vendor’s schedule, so the vendor documentation is the only reliable reference; the pattern is stable.
Note
Before adopting a Tableau API wrapper such as vvtableau, check its current maintenance status and API coverage. Direct httr2 calls are another option, but your team must maintain them when the vendor changes the API. Chapter 13 covers how to weigh this class of decision.
10.3.1 Obtaining Permissions
Access approval can take longer than implementation. Issuing a personal access token or provisioning a service account requires a permission change, and requests of this kind routinely stall.
Ask early and specifically (Section 24.4). What the pipeline needs is a token or service account scoped to publish to a single project or workspace, not administrator rights, and saying so plainly makes the request substantially easier to approve. Name the specific datasource or dataset. State that the alternative is a staff member manually refreshing an extract on a schedule, which carries both a labor cost and a reliability risk. If the agency requires a service account, request one: a token tied to an individual becomes a broken pipeline when that individual leaves (Section 23.8).
Read credentials from environment variables, following the pattern in Section 16.5.
10.4 Power BI and SharePoint
In agencies standardized on Microsoft, a common arrangement has the R pipeline write a prepared file to a SharePoint document library, a Power BI report read from it, and a scheduled refresh run nightly. Control who can modify the file.
A file in a SharePoint library is a shared, mutable object. Anyone with write access can open it, sort a column, and save. The scheduled refresh then runs against a file that no longer matches what the pipeline produced, and nothing announces the change.
Restrict write access. Write pipeline output to a location where only the pipeline writes, and set permissions accordingly. Never have the pipeline overwrite a file that a person is expected to edit. Include a generated-at timestamp in the extract, or a small metadata file, so readers can see when the pipeline last produced the data. Monitor the scheduled refresh (Section 11.6).
10.5 Version Control for BI Artifacts
Git can store binary files, but its line-based review tools work best with text. A .pbix file is a compressed archive containing the data model, the queries, the visuals, and a cached copy of the data. Committing it yields a history of opaque snapshots: you can restore a prior version, but you cannot see what changed between two of them or merge concurrent edits.
Power BI Desktop projects (.pbip) save report and semantic model definitions as text files suitable for Git. Check availability and limitations in your deployment before adopting this format.
Tableau supports both packaged and plain-text workbooks. A .twbx is a packaged workbook including the data and is opaque for the same reasons. A .twb is XML and will produce a diff, though the generated XML can make changes difficult to review. Preferring .twb with a separately managed data source is still worthwhile, because the diff is occasionally readable and the repository stays smaller.
For binary workbooks, keep a readable change history alongside the file. Version control the R preparation code properly, with meaningful commits, branches, and review, and document any measures implemented in the BI model so they can also be audited. Keep the workbook in the repository or a controlled location but record its changes separately, marking it binary in .gitattributes so Git stops attempting to diff it. Maintain a written change log for the workbook, in the repository, updated in the same commit that updates the workbook: two lines per change recording what changed in the view and why. The log is manual, and should accompany the platform’s version history where available.
Tip
Large BI files do not belong in the main repository history. A workbook committed weekly for two years produces a repository that is slow to clone. Git LFS addresses this, as does the simpler approach of storing workbooks in a versioned shared location and committing only the change log (Section 1.5).
10.6 Concurrent Editing
Because the artifact does not merge, two people editing the same binary workbook can overwrite each other’s work. Agree on how to coordinate edits. Name one owner per workbook and record it in the team handbook (Section 23.2). Anyone else requiring a change requests it from the owner or takes explicit temporary ownership. Where the platform supports checkout or locking, use it.
Keeping preparation in the pipeline reduces the number of changes that require editing the workbook. Two analysts can then work simultaneously on the R side, with branches and review, and neither opens the binary file. For text-based project formats, use branches and review where supported, then open and test the merged project in the BI application; a clean text merge does not establish that the dashboard works.
10.7 Metric Definitions
A dashboard tooltip, a data dictionary in the repository, and a footnote in the quarterly report can diverge when they are maintained by different people at different times.
Define each metric once in a machine-readable file that the pipeline reads, and generate the rest from it. A small YAML or CSV in the repository with one row per metric, recording the name, definition, numerator, denominator, units, and any suppression rule, is sufficient:
dict<-readr::read_csv("metadata/metrics.csv")# The pipeline uses it to label and annotate the extractweekly_labeled<-weekly|>tidyr::pivot_longer( cols =c(cases, hospitalizations), names_to ="metric", values_to ="value")|>left_join(dict, by ="metric")# And writes the same definitions out for the BI tool to readreadr::write_csv(dict, "extracts/metric_definitions.csv")
The BI tool imports the definitions file alongside the data and uses it for tooltips and axis labels. A changed definition changes in one place, in version control, with a commit message recording the reason. Section 21.4 covers the data dictionary as a documentation artifact; the addition here is using that dictionary as an input to the pipeline.
This also removes the apparent competition between BI and reproducible reporting. The same prepared extract and the same definitions can feed a Power BI report for interactive exploration and a Quarto document for the quarterly narrative (Section 6.3): one input, several outputs, one definition of what a case is.
10.8 Checks Before Publishing
Confirm that the prepared data supports the permitted views, that rates and totals reconcile, and that disclosure rules hold across filters and downloads. Review both preparation code and BI measures. Coordinate edits according to the file format, and verify the refreshed published result after deployment.