Transcript Apex Supermarket Data Warehouse

Presented By:
Muhammad Rizvi
Raghuram Vempali
Surekha Vemuri
Background
 Apex Supermarket is a supermarket chain with 50
stores in the West Coast, East Coast, Midwest, and
Southwest
 They have stores in:
 West Coast: California
 East Coast: Georgia, New York, Virginia
 Midwest: Illinois, Kansas
 Southwest: Texas
OLTP System Limitations
 Currently, there is an inability to make strategic
decisions
 Cannot make drill-down or roll-up type reports
 Cannot support multi-dimensional analysis and
decision making
Business Need
 Help executive staff study the sale of products by
Categories, Regions, States, Cities, Stores, and Time
 Executive Staff will be able to figure out sales revenue
through the different dimension combinations
 Give strategic information for the Executive Staff to
make decisions on optimizing prices of products
Why A Data Warehouse?
 Allows Decision Support
 Provide An Informational System For Executives To
Make Strategic Decisions like the following:


Which is the period of time where school products are sold
most?
Which are the racks that the school products have to be
moved to in order to attract customers during “school
season”?
Why A Data Warehouse?
 Allows Decision Support
 Provide An Informational System For Executives To
Make Strategic Decisions like the following:


Which are the store locations where perishable items like
fruits and vegetables need to moved off fast?
What is the period of time for which meat foods could stay on
rack at the Illinois location of Apex?
 Central Repository
 Roll-up and Drill-Down
 Gives Sales by Category, Region, Store, etc.
Potential Starting Dimensions
 Time
 Year
 Quarter
 Month
 Day
 Area
 Regions
 States
 Cities
 Categories
 Perishables



Fruits/Vegetables
Dairy
Meats
 Non-Perishables
 Drinks
 Snacks
 School Supplies
 Cigarettes
Proposed Data Warehouse
 Extract data from the Data
Warehouse and





populate one
or more Data Marts for
use by groups
Decision makers
Production Department
Personnel Department
Data in data mart is
organized using Star
schema or Snow flake
Uses Dimensional Tables
Relational Vs Dimentional

Relational databases are stable, flexible and work well for online transaction processing.

A Dimensional Model is a database structure that is optimized for online queries and Data Warehousing
tools. It is comprised of "fact" and "dimension" tables.

Dimensional Models are designed for reading, summarizing and analyzing numeric information, whereas
Relational Models are optimized for adding and maintaining data using real-time operational systems.
Database and Tools
 Online Transaction Processing (OLTP)
 Microsoft SQL Server 2008 is used as the RDBMS
 Online Analytical Processing (OLAP)
 Microsoft SQL Server 2008 Analysis Services
Questions???