As the client’s reporting requirements changed, the existing reporting solution was falling short in the following areas:

• Data Integrity. Both external consumers and internal audiences questioned the data reported. Some of the data used for reporting was manually maintained and at times were incorrect because the owner did not make the required changes.

• Maintainability. The reporting solution was custom-built using an outdated programming language. The company had to hire a contractor to maintain it because they were lacking an internal resource who had this expertise. Data was stored in multiple databases, which led to additional challenges, including extra time spent trying to track down data issues.

• Flexibility. The available canned reports did not allow modifications to users to customize their analyses and reports. Additional reports required costly development.

Archetype partnered with the client to facilitate requirements and design sessions that informed the development and delivery of a reporting solution that fit the client’s business needs. The solution used Microsoft SQL Server for the database and SQL Server Integration Services for the ETL (extract, transform, and load) pipelines. Archetype performed a reporting tool comparison for the client and they opted to utilize Tableau for all reports and dashboards. The solution design included the following:

• Raw data sources were loaded into a staging area allowing it to be in one centralized location. This reduced the performance hit on the transaction systems during the transformation process.

• Data from the staging area was transformed and loaded into a dimensional data model. During this transformation process, predefined business logic was used to identify which source system was the source of truth. Similar data from different sources was merged to create a unique row of data from the multiple data sources.

• Archetype and the client partnered to develop an error checking process to help identify data inconsistencies from the source data. If the system discovered errors, those errors were reported to the client so they can resolve the data issues at the source.

• Archetype created a dashboard for the client’s existing and new reports in Tableau. If updates are needed, the client can build their own reports and dashboards.

The client benefited from the following results:

• By storing data in a central location, the client can easily maintain the data. This reduces time spent on troubleshooting data issues because the client no longer searches where the data is located. Also, as part of the implementation, Archetype delivered a detailed system design document to the client, which was not available with their previous reporting solution.

• Data integrity improved since data used for reporting is pulled from the source systems. This allowed the manual process of maintaining data to be removed since the data in the reports matched the source.

• The client is able to maintain the reporting solution internally which reduced their cost for external contractors.

• By using modern technology for the solution, the client no longer needed to support outdated programming languages and technology.

• Self-service reporting is now easy with the implementation of Tableau. Business users are no longer reliant on IT or contractors to develop reports and dashboards for them.

Archetype delivered the new reporting solution on-time and under budget. As part of the solution hand-off process, the client received proper training on the solution so they can be self-reliant on maintaining the solution going forward.