Data Warehouse for a Mid-Sized E-Commerce Company
Why a Company Needs Its First Data Warehouse
Running a company can be seen as a continuous process of making business decisions. Consider a mid-sized e-commerce company. It naturally has a number of departments and processes, and, as you would expect, no single application covers all of the organization’s needs. In this article, I want to show how to bring data together into a single source with conformed data elements, so that BI applications can present the organization’s data efficiently and decisions can be based on it. That is the story behind building a mature data warehouse (DWH) in any company.
Companies start building a DWH for several reasons. As a company grows, it generates more and more data in new areas of the business. The main driver then is separating day-to-day operations from analytics, which relieves core systems of heavy reporting workloads. Right after business hours, the DWH runs its main ETL (Extract, Transform, Load) process to load data into its own storage, without affecting the performance of the operational systems while employees are using them. Once loading is complete, the data warehouse has the whole night to prepare data for analytics and reporting. This lets developers slice and dice the data and analyse it in depth. With facts and dimensions properly modelled, BI applications can run even the most complex reports without long processing times. A growing company also introduces new operational systems as new processes emerge, so the data warehouse must be ready to add further sources to the ETL process. That is why the architect’s job is to choose a suitable model (Kimball vs Inmon, star vs snowflake schema, etc.) that balances flexibility and scalability.
A DWH can also serve as a backup of the company’s operational data, but I won’t cover that here. Another challenge is integrating multiple data sources and conforming them into a single source of truth. I’ll skip these topics for now and cover them in future articles.
An e-commerce company focuses mainly on sales, whether retail (B2C) or B2B. Sales is at the core of the business, but many other areas add value or even provide a competitive advantage. Here is a sample structure of an e-commerce sales company:
ERP vs Data Warehouse
A company could consider implementing a comprehensive ERP system. In practice, ERP is usually introduced when manufacturing is part of the business. When products are bought from other companies, a CRM takes the place of ERP (since suppliers can be managed much like customers). So in our example, the CRM is the first source system. Next come logistics and the physical warehouse for products. We assume that some products are imported and that it pays to keep some of them in stock, which gives us more flexibility and credibility as a B2B supplier. This process runs in a separate, smaller, custom-built system designed to keep stock rotation and inventory costs low. Then there is accounting and finance, where accounting has its own structures; it is connected to the CRM to generate invoices and send them to clients. Finally, there is analytics, which builds the data warehouse for the BI system and delivers data to finance, marketing and management (both top management and operational leaders). Each of these parties acts as either a client or a supplier of the enterprise DWH.
The simple diagram below shows the data flow and its main integration points.
Conclusion and Next Steps
Building a DWH for the company structure in our example will inevitably raise the challenge of combining data from different sources. We have three systems, but the DWH should present a single dimensional model across all of them. This is where the conformance process begins - a topic for a separate article. There is also the question of data quality, which I describe in Business Case: Data Quality - The Missing Process. Since this article only outlines the idea of a DWH, I’ll stop here - a more technical post is coming soon.