Pine Valley Furniture wants you to help design a data mart for analysis of sales. The subjects of the f data mart are as follows:
Salesperson……………………..Attributes: Salespersons, Years with PVFC, SalespersonName,
and SupervisorRating.
Product…………………………Attributes: ProductID, Category, Weight, and
YearReleasedToMarket.
Customer………………………..Attributes: CustomerlD, CustomerName,
CustomerSize, and Location. Location is also a hierarchy over which they want to be able to aggregate data. Each Location has attributes LocationID, Averagelncome, PopulationSize, and NumberOfRetailers. For any given customer, there is an arbitrary number of levels in the Location hierarchy.
Period……………………….Attributes: DaylD, FullDate, WeekdayFlag,
and LastDay of MonthFlag.
Data for this data mart come from an enterprise data warehouse, but there are many systems of record that feed this data to the data warehouse. The only fact that is to be recorded in the fact table is Dollar Sales.
a. Design a typical multidimensional schema to represent this data mart.
b. Among the various dimensions that change is Customer information. In particular, over time, customers may change their location and size. Redesign your answer to part a to accommodate keeping the history of these changes so that the history of DollarSales can be matched with the precise customer characteristics at the time of the sales.
c. As was stated, a characteristic of Product is its category. It turns out that there is a hierarchy of product categories, and management would like to be able to summarize sales at any level of category. Change the design of the data mart to accommodate product hierarchies.