A customer places orders for parts and belongs to a particular industry group. An individual order is taken by a particular sales representative on a specific date and the sales representative is employed by a specific department and works in a given region. Parts are assigned to a unique category.
Tables
tblCustomer( CustomerNO, Name, Street, Suburb, Postcode, IndustryNOfk, Balance)
tblOrder( OrderNO, OrdDate, CustomerNOfk, SalesPersonNOfk )
tblOrderLine( OrderNO, PartNO, QtyPurchased, UnitSalePrice,)
tblPart( PartNO, PartDescription, QtyOnHand, UnitPrice, CategoryNOfk)
tblIndustry( IndustryNO, IndustryName)
tblCategory ( CategoryNO, CategoryName)
tblSalesPerson( SalesPersonNO, SalesPersonName, DepartmentNOfk, RegionNOfk)
tblDepartment( DepartmentNO, DepartmentName,)
tblRegion( RegionNO, RegionName)
Instructions
The company wants to improve its decision making processes by creating an OLAP system that satisfies the following requirements. The company wants to analyse its sales (revenue) information. It would to analyse its sales by the following criteria; Industry, PartNo and PartDescription, Category, SalesPerson, Region, Department and Customer. It needs to analyse this information on a yearly, monthly and daily basis
(a) Students are required to design a standard star scheme to meet the above requirements
(b) Students are required to transform their design in part (a) to match SAP's extended star schema model.
(c) A sales person over time can move to different regions and the company would like to record this fact. Indicate two ways this situation can be modelled in you design. You may need to redesign your model.