CASE

Data Warehousing at Acme Inc.

After the senior management meeting, Mehta organised a team led by Aditi Patel, Head of IT, to initiate the data warehouse project. He also hired Neil Clarke, a SAP consultant specialising in data warehouse design and development, to help them in this initiative. Patel asked Clarke to explain the processes involved in building a successful data warehouse solution:

Patel: Good Morning Neil, How are you doing?

Clarke: Great. How is your new initiative on data warehouse coming along?

Patel: We are in the very early stages of this project and would like to seek your help on successfully driving this initiative. In this context, I have already shared with you our organisational business process details.

We are unclear on how to approach this project. Where should we start and how should we proceed? Can you share your thoughts on some of the best practices in the industry?

3 Gartner Reports. The state of data warehousing in 2012, 2013 and 2014.

Clarke: Yes. I did go through the documents you had shared. Based on my review, I prepared a data warehouse business matrix [refer to Exhibit 3] and emailed it to you this morning.

Patel: I did glance through the warehouse business matrix document. But how do I really interpret and use this business matrix for building a data warehouse?

Clarke: Let me explain. The business matrix contains information about your business processes the rows of the matrix, and their context or dimensions the columns. The individual cells are marked with an 'x' if there is a context associated with the specific business process row. For instance, the context for the retail product sales business process includes time, product, store, customer, promotion and geography.

While building a data warehouse, select a few business processes from the matrix and design a multi-dimensional data model. The model is then used to build a data warehouse. The above process is repeated for all of the business processes, based on your analytical requirements.

Patel: Okay. Is it a kind of bottom-up, iterative process for building a data warehouse?

Clarke: Yes. One needs to prioritise a few business processes based on the current business demands. A data mart (a mini warehouse) is then built for each of the selected business processes.

Patel: Great. How should we proceed after prioritising and selecting a few business processes for implementation?

Clarke: One of the industry best practices in this area is to apply a four-step process for designing and building a data mart/warehouse: (1) Select a business process, (2) declare the grain, (3) choose the dimensions, and (4) identify the facts. Let us assume that we wish to apply the four steps to one of your business processes.

As a first step, let us choose the 'sales order' business process. In the second step, we need to declare the grain of the business process. The grain is chosen based on your analytical requirements. For example, if you wish to analyse your sales data at the lowermost granularity, then the grain is declared as the transaction line item. On the other hand, if your analysis doesn't require transactional-level data, then the grain is declared at a weekly or monthly level.

Patel: Well, if the grain is defined at a higher level, will it not constrain my ability to analyse the data?

Clarke: You are right. One needs to make a trade-off between the size of the data stored in a data warehouse and one's analytical requirements. For example, if the business user rarely or never accesses the transactional- level data for analysis, then it will be an unnecessary waste of data warehouse space. Alternately, it is possible to create multiple data repositories with different levels of grains to meet different analysis requirements.

Patel: Hmm. Perhaps we should declare the transaction-level grain as the last 90 days of recent data. For historical data beyond 90 days, we could declare a weekly/monthly grain. This would allow the periodic archiving of the older data to a higher-level grain.

Clarke: You are spot on. This is precisely how organisations meet their analytical requirements and at the same time optimise warehouse storage space.

Let us move on to the third step of choosing the dimension, a fairly easy step if you have correctly declared the grain of the business process. For example, if the grain for the sales order process is declared as sales per line item, then the typical dimensions are the context of the sale such as item, store, customer, time and geography.

The final step in the dimensional design process is the identification of facts. These are the performance measures that one wishes to monitor. For the sales order business process, the facts are generally quantity of items sold, dollar value of sales, gross revenue and gross margin.

Patel: This is quite interesting. Now, I get a clear sense of how the dimensional modelling for a data mart/warehouse works. We will soon start work on prioritising our business processes and applying this framework to design the data mart/warehouse.

Neil, we shall connect with you again to understand the next steps. More specifically, we wish to understand the best practices in making the right architectural choices and building a data warehouse infrastructure.

Clarke: Sure. I'll certainly help your organisation in this data warehouse journey. Let us connect again next week for a deep dive on data warehouse design and development.

Patel and her team had a series of conversations with Clarke to better understand various aspects of data warehouse design and development. Through this process, the team gained a good understanding of data warehouse architecture and its components.

(A sample data warehouse architecture diagram is given in Exhibit 4. One of the components in the architecture is the online analytical processing (OLAP) engine. OLAP is an interactive data analysis tool that allows one to view data from a multi-dimensional perspective. Exhibit 5 provides an illustrative list of operations one can perform in OLAP. Exhibit 6 depicts some of the key architectural choices available for data warehouse implementation).

The team had to decide on which architecture would be the most appropriate for Acme.

question: What is the need for Data Warehousing at Acme?