Transcript Cube Implementation

Data Warehouse
Design
GSK FMCG industry
Pavan Kumar Mantha
Vinod Tati
Shourya Konda
nd December2014
210
October 2014
1
UK based GSK Plc, maker of popular FMCG products
like Horlicks and Sensodyne, has been planning to raise
its stake in its Indian unit GlaxoSmithKline Consumer
Healthcare to 75 per cent.
2
Business needs
User wants to know….
What is the most
effective distribution
channel?
What product prom-otions have the biggest
impact on revenue?
Which are our
lowest/highest margin
customers ?
Who are my customers
and what products
are they buying?
Which customers
are most likely to go
to the competition ?
What impact will
new products/services
have on revenue
and margins?
3
Business needs
continued…
 Daily, Monthly, Quarterly, Yearly reports of sales and
manufactured goods.
 Strategic information to assess the products demand
 Which type of consumers to target
 Which products to promote
 Distributor wise reports
 Retailer wise reports
 Product wise reports
 And many more…
4
Fact constellation Schema
• Two fact tables and for each dimension one
dimension table has been chosen
• Fact Tables
• CompanySalesFact
• Distributor SalesFact
• Dimension tables
• Date
• Product
• Promotion
• Retailer
• Distributor
5
Date
Product
DateTime_ID
Time_Month
Time_Day
Time_Year
Time_Quarter
Product_ID
Product_Name
Product_Category
Product_brand
Price_Per_Unit
Distributor_SalesFact
Distributor_ID
Distributor_Name
Distributor_addrs
Distributor_PhNum
Distributor_region
DateTime_ID
Product_ID
Promotion_ID
Retailer_ID
Distributor_ID
Quantity sold
Sale amount
Discount_amount
Retailer
Company_SalesFact
Promotion
Promotion_ID
Product_ID
Promotion_Name
Promotion_Category
Promotion_brand
Promotion_region
Promotion_discount
Promotion_Desc
DateTime_ID
Product_ID
Promotion_ID
Distributor_ID
Distribution_type
Distribution_desc
Distribution_Qty
Distribution_Amount
Retailer_ID
Retailer_Name
Retailer_Region
Retailer_PhNum
Retailer_Address
Retailer_Type
Fact Constellation schema
6
Database import from MS Access
using
SQL Server Management Studio
7
Creation of Multi Dimensional Data Model using
MS SQL Server – SQL Server Data Tools
8
Multi-dimensional cube
Fact Constellation Schema
with two fact tables
9
After deploying the database we have generated few reports like:
 Monthly sales report
 Quarterly sales report
 Product wise sales report
 Distributor wise sales report…
10
Distributor & Product wise monthly sales report
for the month of January 2014
(Goods sold to distributor by company)
Grand total of sales = Rs.80295
Quantity sold = 1405 units
11
Retailer & Product wise monthly sales report
for the month of January 2014
(Goods sold to retailer by distributor)
Grand total of sales = Rs.18285
Quantity sold = 385 units
12
Product wise quarterly sales report
for the second quarter 2014
(Goods sold to distributor by company)
Grand total of sales = Rs.240135
Quantity sold = 4115 units
13
Product wise quarterly sales report
for the second quarter of 2014
(Goods sold to retailer by distributor)
Grand total of sales = Rs.170485
Quantity sold = 3620 units
14
Product wise overall promotion report
15
Distributor & Product wise overall promotion report
16
Thus by generating various reports and analyzing the trends in
sale of different products in different regions and by different
retailers, we can forecast and accordingly target a particular
sector.
17
18
19