Overview
CBMA, is a leading supplier of materials to the construction industry throughout Australia, and operates in a high-velocity environment where accurate tracking of Front end, operations, and other key data metrics were vital for strategic growth. Over a two-year partnership, Report Simple helped CBMA transform its reporting from a fragile, manual workaround into an automated, enterprise-grade analytics ecosystem. By integrating their CRM with a Google Big Query data warehouse and layering an advanced Power BI architecture, Report Simple delivered a single source of truth that empowers national leadership and state-based teams with real-time, highly secure insights.
Challenge
As CBMA scaled, its legacy reporting infrastructure hit an operational ceiling. The previous system relied on scheduled CSV data exports throughout the day from the CRM system. While this file-based approach worked in CBMA's early stages, growth and data quickly created severe operational roadblocks:
Storage Bloat & Bottlenecks: High-frequency CSV exports created massive data storage accumulation and severely throttled backend processing speeds.
Deduplication Nightmares: Overlapping records across frequent extracts required complex, recurring data-cleaning cycles to prevent major metric inflation.
System Stability Failures: The sheer volume of processing raw, un-optimised files caused Power BI Dataflows to frequently crash or fail, resulting in un-reconciled data and frustrating reporting downtime.
Security & Governance Constraints: CBMA required a scalable mechanism to enforce strict data privacy, such as restricting sensitive data visibility or isolating data without building separate, siloed reports for individual teams.
Solution
Report Simple engineered a comprehensive data modernisation strategy that completely replaced the fragile CSV pipeline with a robust, modern cloud data warehouse architecture:
Automated Cloud Pipeline: Eradicated manual exports by developing an automated Python-based API data pipeline running via Google Cloud. The solution directly ingests raw JSON payloads from the CRM into a secure Google BigQuery data warehouse, using optimised delta syncs to capture updates seamlessly.
SQL Data Transformation & Modeling: Authored custom SQL views within BigQuery to flatten deeply nested, complex JSON arrays into clean, structured reporting tables and pages. These were modelled into an efficient, high-performance Star Schema in Power BI, distinctly separating Fact tables from core Dimensions.
Enterprise-Grade Row-Level Security (RLS): Developed a dynamic, matrix-driven RLS model governed by a centralised Security Matrix. This empowers administrators to manage user access to specific state-level data and sensitive data dynamically, without modifying backend database code.
Dual-Level Security Pattern: To balance macro and micro reporting needs, Report Simple architected a custom "duplicate bypass" Power Query pattern. This allows high-level, aggregated KPIs to remain visible to all users on the dashboard, while automatically locking granular drill-through rows to the user's explicit state permissions.
Advanced DAX & Cohort Analytics: Formulated complex, time-traveling DAX measures to conduct deep Cohort Analysis of key metrics and business requirements.
Result
The modernisation project fundamentally shifted how CBMA interacts with business data, delivering immediate operational breakthroughs:
100% Automated, Stable Infrastructure: Manual processes were entirely eradicated. The direct BigQuery API pipeline eliminated dataflow crashes, providing a seamless and highly resilient data refresh cycle.
Flawless Data Integrity: Meticulous row-by-row data reconciliation against the CRM front-end reports guaranteed a 100% accurate, single source of truth that stakeholders trust implicitly.
Secure Data Democratisation: National leaders can evaluate top-line performance while regional managers work within securely restricted dashboards. Future administrators can safely onboard new users via comprehensive, step-by-step Admin documentation without risking security bypasses.
Advanced Operational Visibility: Real-time tracking of key metrics and data allowed CBMA to optimise its sales funnel and respond to shifting market demands with unprecedented speed.
Conclusion
By collaborating with Report Simple, CBMA successfully moved from a reactive data posture to a proactive, enterprise-grade data strategy. The transition to a Google BigQuery warehouse and an advanced Power BI model has built a scalable data asset that protects sensitive data, guarantees compliance via Row-Level Security, and unlocks deep operational insights. This transformation proves that with the right data architecture, growing businesses can eliminate manual overhead and turn raw data into a powerful engine for growth.
What began as a battle against crashing reports and manual CSV files turned into a complete operational transformation. Report Simple didn't just build us a dashboard; they engineered a secure, scalable cloud data infrastructure that gives the business and team total confidence in our metrics.
Heath | Head of Sales & Marketing