Customer Analytics in Valeur: OLAP Queries and Data Warehouse Design

This report details the implementation of OLAP cubes and queries within a retail data warehouse environment to enhance customer analytics. It addresses the need for a more comprehensive understanding of customer behavior through both transactional data and demographic analysis. The report includes an improved dimensional model designed to capture relevant customer information, along with OLAP queries used to analyze customer purchasing patterns, product pricing, store performance, and customer feedback. Key improvements to the dimensional model include the incorporation of transaction fact tables and dimension surrogate keys. The analysis provides actionable insights for targeted marketing strategies and improved customer relationship management, aiming to convert low-value customers into high-value ones by identifying potential sales and marketing opportunities. The document also discusses the strategic advantages of analyzing customer flow and strategically upselling or downselling products. Desklib provides access to this report and other solved assignments for students.

Assignment 02: OLAP and Cubes

Table of Contents
New dimensional model that is improved...................................................................................................2
Tables Description.......................................................................................................................................3
Create cubes and OLAP queries...................................................................................................................8
Recommendation......................................................................................................................................12
References.................................................................................................................................................15

New dimensional model that is improved
Relationship for supermarket
The above figure shows the improved version dimensional model of the old one consisting of ten
relations which are all related with the retails dimensional model to get all transaction details.

Description of the relations
Customer details
Feedback relation

Mailshot Campaigns relation
Product_Promotion relation

Product_In_Warehouses relation
Ref_Calendar relation

Ref_Invoice_Status_Codes relation
Ref_Payment_Methods relation

Retail_Dimensional_Model relation
Store_Details

Creating cubes and queries
The underneath OLAP inquiry is utilized to discover the which client purchased more number of
items from store so we can give the a few offers to who got more number of products and also
we can analysis the more sales at which store. Based on our database store ID S_100 has more
sales and customer C1000 got 300 products. Likewise we can analysis for the rest of the stores
who sold very less number of products such as store _id 105. We can also analysis total number
of products sold in all store by sum of all products in all stores is 950.
This query is used to analysis the product price of the product. Here we got price of the product
100 rs from warehouse 1 and 3 which is selling milk and battery.

This query is used to analysis the product price of the product. Here we got price of the product
200 rs from warehouse 1 which is selling story books.
The below query is used to check what are all the stores are available in US which has store1 and
4.

The below query is used to check what are all the stores are available in UK which has store1,3
and 4.
Analysis the feedback of the customer by using below query here which product has highest
price , got good feedback and values are 5.

The below query result states that customer C1000 only purchase details and total cost of the
product is 155000.