|
| Looking for any advice/feedback. My database background is more in OLTP
systems, but I am studying to get familiar with OLAP. The layout below is
my best effort to date.
Purpose
I am working on building a data mart at my company with the purpose of
comparing current sales against budget and prior year.
Data available from OLTP systems
Budgeting system - Monthly sales, Cases, and Margin per customer. As part
of the ETL process I will break the numbers down by the week.
Ordering system - Shipped cases, Net Sales, and Margin per product per
order.
Budget tables
Fact: Budget (Sales $, Cases, Margin $)
Dimensions: Warehouse, Account (including sales category and salesperson),
Time
Budget Dimensions
Customers - Country, State, City, Name
Category - Category, Customer Name
Salesperson - Salesperson, Customer Name
Warehouse - Country, State, City, Name
Time - Year, Quarter, Month, Week
Sales tables
Fact: Sales (shipped cases, Sales $, Margin $)
Dimensions: Product, Warehouse, Account (including sales category and
salesperson), Time
Sales Dimensions
Product - Type, Commodity, Description
Customers - Country, State, City, Name
Category - Category, Customer Name
Salesperson - Salesperson, Customer Name
Warehouse - Country, State, City, Name
Time - Year, Quarter, Month, Week
The Warehouse, Account, and Time dimensions will be shared between both
cubes.
Thanks,
Greg
|
|