Dimensional Modeling - Case Study

Introduction to the activity

Context

The Quiventou supermarket chain uses an information system capable of managing all operational aspects of its business. Your consulting firm has won a contract to design a decision-support solution enabling the generation of dashboards for managing the chain’s business.

Your department is in charge of the dimensional modeling for the data warehouse on which the dashboards will be based. An experienced consultant has already led several working meetings with different contacts at Quiventou in order to analyze its core business, i.e. the in-store sales of products. He used the BEAM✲ method to gather as much information as possible and develop a sound understanding of this business process.

Following these working sessions, the consultant produced an initial dimensional schema, but very recently, the client Quiventou contacted the consultant to mention a series of requirements that had emerged and seemed necessary in the light of their discussions. The consultant noted each of these requirements in order to assess their impact on the current dimensional schema.

As a recently hired junior consultant, you have inherited the daunting task of completing this study. You can refer to the dimensional schema as proposed by the senior consultant who is concentrating on another project. Nevertheless, he has left you a summary of his most recent discussions with the client.

Dimensional schema for product sales in Quiventou stores

The dimensional schema revolves around the company’s sales process, which is focused on the act of payment, at the checkout, for products purchased by customers in Quiventou supermarkets.

The consultant has implemented the BEAM✲ method, as presented in the book by Corr, Laurence, and Jim Stagnitto. Agile Data Warehouse Design: Collaborative Dimensional Modeling, from Whiteboard to Star Schema. DecisionOne Press, 2011.

The BEAM✲ method combines questioning techniques (the “7 Ws” method (Who, What, Where, When, hoW many, Why, How) with data-modeling techniques.

The consultant has used this method for two purposes. Firstly, in order to gather as much information as possible from the client Quiventou with a view to developing a sound understanding of its business process. And secondly, in order to design the dimensional schema. He has therefore added a certain number of BEAM✲ annotations to the dimensional schema, which should improve your understanding of his schema (the annotations are explained in the book Agile Data Warehouse Design, pages 287 - 292)

In this schema, the most detailed level corresponds to a type of product purchased by a customer at the checkout. However, from a business standpoint, the customer notion cannot be used because not all customers are identified when they pay at the checkout. Nevertheless, each checkout process leads to a payment; the grain is therefore defined by means of the PRODUIT (PRODUCT) dimension and the ID PAIEMENT (PAYMENT IDENTIFICATION) degenerate dimension. It could also have been defined in an equivalent manner by all of the PRODUIT, CALENDRIER (CALENDER), TEMPS (TIME), CAISSE (CHECKOUT) and MAGASIN (STORE) dimensions.

Analyzing the dimensional schema

We will start by analyzing the dimensional schema with assistance from the various BEAM✲ annotations that it contains.

The fact table

For assistance, use the acronym stated alongside the name of the fact table in the dimensional schema and refer to the book, Agile Data Warehouse Design, Event Story and Fact Table Types, page 287.

The dimensions

Certain dimensional attributes are associated with the following annotations: CV, PV and HV, whose meanings are set out in the book, Agile Data Warehouse Design, Dimensional Attribute Types, pages 290-291. If they are not mentioned, the attribute is considered as being of the FV type by default. The presence of an HV-type attribute leads to an HV-type dimension; by default, the presence of a CV-type attribute leads to a CV-type dimension (see Agile Data Warehouse Design, Dimension Table Types, pages 288).

Requests to be dealt with

For each of these requests, you will need to propose a solution expressed in the following forms:

• a modification to the bus matrix.
• a modification to the dimensional schema, which is correctly annotated: modification to the fact table, to the dimensions, or the addition of a new fact table with dimensions to be identified. You can start with this shared schema and duplicate it in order to add your modifications.
• an explanation which is required to help the client understand the proposed solution.

Multiple payment methods

The client Quiventou would like the proposed solution to take account of the fact that supermarket customers can use two different payment methods for a single transaction (cash, bank check, credit or debit card, or lunch/restaurant/holiday vouchers).

Special offers

The client Quiventou wants to analyze the effectiveness of its special offers. For this purpose, it needs to be able to analyze the products on special offer that have not been sold.

Customers

After reading the document, your manager shares his concerns about the Client (Customer) dimension with you. He believes that it is likely to be excessively large. Quiventou currently has 2 million customers (with a loyalty number) and predicts a 50% (linear) increase every three years. Annual campaigns are conducted, which enable the information for 15% of customers to be updated.

Deliveries

The client Quiventou is wondering whether the proposed solution could also be use to manage their Quiventou Online Shopping service, from the online ordering process through to the delivery of products.