OnLine Analytical Processing (OLAP) for Sagan’s Marketing Department

It’s the end of the financial year, and the marketing and sales department at Sagan’s Sports Goods manufacturing company is heavily involved in formulating marketing strategies for the coming year. Before investing in market surveys and market research, the management wishes to make maximum use of the customer records contained in their information systems. A data warehouse expert has been assigned the task of consolidating customer information from several different data sources into a single data warehouse and developing an on-line analytical processing (OLAP) facility. Integrating all data sources into the data warehouse is not at all straightforward. The data warehouse has been created. It might also be available as a backup on Moodle. As the business analyst, you have to carry out the following tasks and report to the management. The key requirements of this assignment are: 1. Identify market segments for each of the following business measures: a. margin = (1 - TotalProductCost / TotalSalesAmount) x 100% b. penetration = (average TotalSalesAmount per customer) c. total profitability = (TotalSalesAmount – TotalProductCost) and do this for different market segments (e.g., French house owners), noting (e.g.) the segment with the largest amount of sales (items, dollars). 2. Generate reports using SQL Server Reporting Services that demonstrate for each of these segments the relative profitability, penetration and margin - and whether these values are high or low. 3. Analyze the relationship among average NoOfDaysToShip, TotalSalesAmount per customer and Number of Orders per customer by generating necessary reports. 4. Discuss some of the potential implications for marketing and sales of the results of your queries and reports. (Students can make reasonable assumptions at this point, talk to the lecturer or tutors if clarification/assurance required.) (Hint: Students should investigate the data from several dimensions – customer, product – categories/sub-categories, sales regions/personnel, geography, etc. to identify potential interesting areas for market research.) Report Your submission should be in the format of a report. The report should be a maximum of 2250 words (as far as words go) in addition to (e.g.) screen shots, etc. The report should contain the following: 1. Method. A description of the analysis that you undertook to identify the segments, etc. 2. Findings. Interesting (probabilistic) findings with evidence 3. Reports. Outputs of the reports that demonstrate your (probabilistic) findings 4. Summary. A business summary of what the analysis conveys