Our organization has been using AuditBoard for over two years, and while the platform excels at its core audit, risk, and compliance workflows, extracting clean, analysis-ready data for our Tableau executive dashboards has been a persistent and non-trivial challenge. The out-of-box reporting is sufficient for operational teams, but for product analytics and tracking key risk indicators (KRIs) alongside business metrics, we needed a methodical approach to data extraction and transformation.
The primary obstacles we encountered were:
* **API Limitations:** The REST API is robust for specific objects but lacks comprehensive endpoints for joined data across modules (e.g., connecting issue details with full control test histories).
* **Data Model Complexity:** The relational structure is not directly exposed, requiring an understanding of how entities like `Audits`, `Workpapers`, `Controls`, `Issues`, and `Actions` link through various junction tables or specific fields.
* **Data Hygiene:** Inconsistent user entry in free-text fields and the use of multiple custom field types necessitated significant cleansing.
We developed a two-stage process: extraction via the API followed by a transformation layer. Below is the core Python script we use for the initial data pull, focusing on the `Issues` module due to its executive importance.
```python
import requests
import pandas as pd
from datetime import datetime
# Configuration for API Authentication (using Service Account)
BASE_URL = 'https://yourcompany.auditboard.com/api/v2'
HEADERS = {
'Authorization': 'Bearer YOUR_ACCESS_TOKEN',
'Accept': 'application/json'
}
def fetch_paginated_data(endpoint, params=None):
"""Handles pagination for AuditBoard API endpoints."""
all_data = []
page = 1
while True:
if params:
params['page'] = page
else:
params = {'page': page}
response = requests.get(f"{BASE_URL}/{endpoint}", headers=HEADERS, params=params)
response.raise_for_status()
data = response.json()
all_data.extend(data.get('data', []))
# Check if it's the last page
if data.get('currentPage', 0) >= data.get('totalPages', 1):
break
page += 1
return all_data
# Fetch core Issues data
issues_data = fetch_paginated_data('issues')
issues_df = pd.DataFrame(issues_data)
# Enrich with custom fields (requires a separate call or joins in transformation)
custom_fields_data = fetch_paginated_data('issues/customFields')
custom_fields_df = pd.DataFrame(custom_fields_data)
# Merge and begin structuring
combined_issues_df = pd.merge(issues_df, custom_fields_df, how='left', on='id')
```
The transformation phase is critical. We use a series of SQL-like operations (via pandas or a dedicated ETL tool) to:
* **Flatten hierarchical data:** For example, unpacking the `owner` object within an issue to create distinct `owner_name` and `owner_department` columns.
* **Standardize status values:** Mapping various user-entered status phrases to a controlled set: `Open`, `In Progress`, `Remediated`, `Closed`.
* **Create calculated timelines:** Computing days in each status from the `activityLog` array.
* **Join with control data:** Using the `controlId` to append control categories and testing frequencies from a separately extracted Controls dataset.
The final schema for our dashboard fact table includes the following dimensions and measures:
| Dimension Group | Example Fields | Transformation Note |
| :--- | :--- | :--- |
| **Issue Core** | `issue_id`, `title`, `description_cleaned` | Free-text description stripped of PII and standardized nulls. |
| **Status & Dates** | `current_status`, `reported_date`, `remediated_date`, `days_to_remediate` | Derived from `statusHistory` array. |
| **Ownership** | `business_unit`, `process_owner`, `risk_rating` | Joined from `User` and `Custom Field` lookups. |
| **Related Objects** | `associated_audit`, `linked_control`, `control_category` | Resolved via separate API calls to `/audits` and `/controls`. |
| **Measures** | `count_of_open_issues`, `avg_remediation_days`, `sla_breach_count` | Aggregated at the business unit level weekly. |
Lessons learned:
* Schedule incremental extracts daily rather than full historical pulls weekly to avoid API timeouts.
* Build a dedicated mapping table for custom field IDs to human-readable names, as these can differ between AuditBoard instances.
* Validate data completeness by comparing record counts for key entities (e.g., total open issues) between the API and the AuditBoard UI weekly.
This process, while requiring upfront investment, has provided a reliable pipeline. Our executive dashboards now reflect near real-time risk postures integrated with operational data, enabling more nuanced cohort analysis of issue recurrence and control effectiveness over time.
— Amanda
Data > opinions
You stopped mid sentence there, but I'm already nodding. The API limitation for joined data is the real killer. We hit that exact wall and ended up building a separate "orchestration" layer in Python just to sequence API calls and perform the joins ourselves. It adds latency and complexity, but it was the only way to get a unified view of an issue through its entire lifecycle, from control test to action plan.
The data hygiene point is also critical, especially for free-text risk assessments. We implemented a rule based tagging system on our end to categorize those entries, because expecting consistency at the point of entry was a lost cause.
Benchmarks or bust.
We also built a separate orchestration layer. The latency hit was significant enough that we had to move from daily to near real time batch jobs, pulling only changed records via timestamp fields to keep it manageable.
The rule based tagging for free text is a good mitigation. We found it necessary to also apply a confidence score to those tags, which we expose in the dashboard. Executives need to see the data quality alongside the metric.
The move to near real time batch jobs is a clever workaround, but that operational cost adds up. Every incremental pull has a compute charge. We had to implement aggressive cost monitoring on our orchestration layer's cloud functions because the shift from daily to hourly increased our data pipeline spend by about 300% initially.
Exposing a confidence score for derived tags is an excellent practice. It builds trust in the dashboard. We took it a step further and set different dashboard visual thresholds based on that score; low-confidence tagged items don't trigger a red status, they flag for data steward review instead.
Have you quantified the total cost of maintaining this orchestration layer versus the perceived value of the near real time data? Sometimes showing that math can justify a push for a native API feature request.
CloudCostHawk
You've hit on the crucial tradeoff that so many cloud data pipelines ignore: the true operational cost of orchestration versus the value of freshness. A 300% cost increase for hourly pulls is significant but not surprising; cloud function pricing is deceptively linear at scale.
We took a hybrid approach to control that cost. The orchestration layer runs in Kubernetes, not as managed cloud functions. We then implemented a two tier polling strategy using the API's timestamp fields. High priority entities (like open issues above a certain risk score) are polled every 15 minutes via a dedicated, scaled down worker pool. Everything else falls back to a consolidated daily batch job. This required more complex state tracking but cut our incremental compute cost by nearly 60% compared to a naive hourly pull of everything.
The cost justification you mention is vital. We built a simple TCO model that included not just compute, but also the engineering hours for maintenance and break fix. Presenting that alongside the business value of near real time data for, say, KRIs helped us secure a permanent platform budget rather than fighting for project funding every quarter. It also made the case for the API feature request more compelling, as we could attach a concrete operational cost saving to its implementation.
Boring is beautiful
The TCO model is a non negotiable step, but it's often built too narrowly. You have to factor in the cost of downstream impact, not just pipeline maintenance. If a poorly tagged issue due to stale data leads to an incorrect KRI status, what's the cost of that decision? We map our data freshness tiers directly to a calculated "risk of error" metric in the model.
Your two tier polling is smart, but it introduces a new complexity: state management across those tiers. We found we needed to add a reconciliation job that runs weekly to catch any entities that might have fallen through the cracks, like a high priority item that was missed during a worker pool scaling event. It adds a bit of overhead but maintains data integrity.
That permanent platform budget is the real win. Once you have it, you can shift from justifying existence to optimizing for value.
Show me the data
Your two-stage process, extraction then transformation, feels like the right approach given these obstacles. I'm actually in a similar boat right now, evaluating AuditBoard for this exact purpose.
You mentioned the data model complexity and the junction tables. Is there any internal mapping or documentation from AuditBoard that helped you figure out how those entities link, or was it purely trial and error through the API? I'm worried about building a transformation layer on a model I might misunderstand.
Also, on the data hygiene point: did you have to push for new required field validations on the AuditBoard side to clean up the free text entry at the source, or was the cleansing purely done in your transformation stage after extraction? That's a big cost we're trying to model.