enterprise data warehouse

Enterprise Data Warehouse Delivers Single Source of Truth and Streamlines KPIs

Business Situation

The client's processes for serving KPIs to the business were comprised of individual reports written against an ERP database, as well as manual on-demand extracts of data from source systems and mashup in Tableau and Excel.

Each report and data extract were created to meet specific needs, resulting in inconsistent implementation of business terminology and KPIs across the business. Similarly named terms and KPIs returned different results, which often led to confusion.

To eliminate this confusion and standardize terminology, the client strived to implement an Enterprise Data Warehouse that provides a single source of truth and streamlines the effort to gain insights from their data.

Project Objectives

  • Improve access to sales information
  • Provide business users (analysts, sales managers, sales people) the ability to answer their own data questions using familiar business terms – move from IT-responsible to business-responsible (with as-needed support from IT)
  • Enhance business-decision processes with single source-of-truth analysis and metrics that align with goals
  • Measure and drive sales against quotas
  • Analyze sales by customer demographics
  • Simplify data summarization and reduce dependency on IT for maintenance of summary tables
  • Allow for the ability to analyze multiple layers of a hierarchy (markets, for example)
  • Create capability to track slowly changing dimensions – the “Customers move all the time” scenario
  • Incorporate security model so that users have access to the appropriate data
  • Support scaling for performance, data volume, and additional data sources
  • Improve overall business decision-making capabilities

Our Solution

To accomplish the client’s objectives, we leveraged an agile approach. Prioritizing the work allowed us to focus on the highest value requirements and ensured we met the key project objectives. Pairing an agile approach with our metadata-driven ETL framework allowed us to set the baseline architecture early in the project and continuously build upon and refactor the solution as requirements were refined and feedback received.

The Results

  • The completed Phase 1 provides business and data analysts with the ability to easily report many sales KPIs by any level within their Customer, Date, Time of Day, Product, and Sales Representative business entities.
  • By consolidating data into an Enterprise Data Warehouse, KPIs and business terms were standardized to provide a single source of truth.
  • The solution can be accessed from rich visualization tools such as Power BI and Tableau.
  • Data is refreshed nightly, eliminating the need for manual data extracts.
  • Future phases are planned to expand the EDW to include other processes within the business.

Client Profile

This client is a national distributor of quality maintenance products. They have built their reputation by offering customers highly efficient methods to order and monitor purchases.

Technologies Used

Microsoft Power BI

Bion

SQL Server 2017 Standard

SQL Server Analysis Services (Tabular)

SQL Server Database Engine

SQL Server Integration Services

Tableau