Business Analytics

 

TASK

Your task is to complete the following activities:

a) design a relational data model to enable everyday transactions,

b) develop a Dimensional Model (oM) using a star schema representation for OLAP,

c) justify your fact and dimension tables (including the facts and attributes) on your GM,

d) build OLAP cubes from the star schema using appropriate SOL commands,

e) identify different types of questions that can be answered using the OLAP data cubes,

f) develop data marts for decision-making at different departments, and

g) design dashboards and scorecards for different stakeholders.

CASE DESCRIPTION

Ted’s Threads is a business specializing in a limited set of youth-oriented apparel. They were
founded in 2000 and have enjoyed rapid expansion and now have 175 retail outlets in 50 states.
The states are organized into 5 regions for their operations: East Coast, West Coast, Midwest,
Central, and South. They also sell their products via the Internet, which are shipped directly to
customers from 3 different warehouses through third-party logistics providers. Stores are
replenished from the same 3 warehouses using their own fleet. They obtain their products from
brand-name clothing manufacturers.

They sell 3 categories of clothing: hats, t-shirts, and hoodies. They carry: 2 lines of t-shirts:
Metro (aimed at the urban/ modem market) and Retro (general 605 throwback line); 3 lines of
hats: Wooly (knit hats aimed at the snowboarder), Bully (gangsta-style ballcaps aimed at the
gang crowd), and Unruly (unconventional hats aimed at the general MTV crowd); and 2 lines of
hoodies (hooded sweatshirts): Dppy (zippered) and Slippy (slipover). Each product line comes in
a number of sizes and colors.

The inventory at each store and the Internet store are maintained on a transaction processing
system that also handles various internal activities (e.g., distribution, scheduling, restocking)
and external activities (e.g., store sales, Internet orders, purchase orders).

Ted’s Threads is interested in obtaining management insights into their operations including
store operations, sales, profitability, customer segments, logistics, etc.