ResultStack was recently presented with a problem from one of the largest fuel station and convenience retailers in the world. The client had multiple different systems that manage different parts of their business and no one system could give them insight into their whole business. Production systems were overloaded processing the day to day initiatives and needed to have historical data purged to remain solvent. Any new reporting requests against these systems only exacerbated the overloaded processing issue.
To reach a result, our team used Azure Data Factory pipelines to schedule data extractions from disparate existing core transactional and near real time systems. The pipelines transform the data into denormalized Star Schema structures before storing the data in an Azure Hyperscale Sql Server database. After careful projection analysis of the client's data storage needs, Hyperscale was chosen because it will allow decades of future storage growth without venturing into some of the other costly, complex, and more difficult to maintain systems.
Data aggregation steps can quickly lead to a hoarding of data which can be interpreted differently by different users. In an effort to provide unified “single source of truth” capabilities, we created Tabular Azure Analysis Service models (Microsoft's cloud based OLAP solution) which can then populate data for all of their developer reporting tools like PowerBI and SSRS. Another part of the solution is providing a complete self service option for business users comfortable with Excel. The end result is a single source of truth system which allows rapid ad-hoc analysis and business intelligence reporting without any concern for impacting the reliability or performance of the underlying transactional database. While their business systems are complex, we wanted to make sure our solution for them added as little cost and complexity on top.