The process of designing the new dimensional models
Individual Assignment
Shasha Siyambalapitiya 18031219
Table of Contents
Data Warehouse Re-designing
Introduction
Valeur is a well-known supermarket retail chain that provides day to day goods to customers all over the country. Recently, the management of Valeur started a customer loyalty program to strengthen the consumer relationship with the Valeur supermarket chain and also to increase the sales by encouraging customers to choose Valeur over other competitive business organizations.
The lacking data in previous dimensional model
Attributes of product dimension was incomplete. As an business organization that sells consumer goods, it is necessary to collect in-depth detail regarding the products.
The Process of designing the new dimensional models
In order to design redesign an existing dimensional model, it is important to analysis the required data that has not been included in the existing model. Also understanding organizational decision making requirements is vital. A business is an organization that fulfil consumer needs and wants while maximizing the profit goals. Every business organization has involved in many business processes. Recognition of core business processes is prerequisite in designing of dimensional model.
Bus Matrix
Data Mart: Retail sales
Data Mart: Loyalty Program
Data Mart: Procurement Transactions
Cube creation and analysis
![]() |
The tables below show the link between consumer age and the total sales. According to these tables youngest aged consumers are aged 18 and oldest consumers are aged 99. The highest amount of purchases has been made by the customers who are in age 51 which the total sales valued to $164 285.17, the minimum sales have made by the age group 90-99 and age group 18. Interestingly, consumers who are aged 61-70 are reflecting lesser values in total sales. For Instance aged 70 consumer’s total sales value is $18 628.61.
![]() |
Figure 2 |
According to figure 2, female consumers have higher number of sales than male consumers. In year 2012, female consumers have contributed to sales by $551 560.86 and male consumer has contributed by only $519 837. But in year 2014 males consumers have contributed more than female members to the total sales.
Figure 4 |
Figure 6 |
According to figure 6, consumers who purchase the highest number of quantity have been studied up to high-school.
Figure 8 |
The above report summarizes
the number of the quantity sold in each quarters. According these
reports, in every year maximum number of quantity has been sold in the
4th quarter.
The Recommendations
Seasonality is a definite factor that affects the sales levels fluctuates. One of the interesting findings of the analysis is that the, month December has the highest number of sales comparatively to the other months since consumers tend to shop more during the Christmas celebrations. There are few months such as April which had the lowest level of sales. In situations like this Velour can encourage the consumers to shop in the months which have lower level of sales by giving high level (2X) points on their loyalty cards.
Additionally, Consumers those who are coming under the age group 60-70 have contributes the total sales in very less percentage. Velour can attract these consumers by providing a pensioner’s promotion program. Velour can design special pricing strategies which are targeting the age group 60-70.