Business Intelligence and Analytics Design and Implementation
Pick a user role as listed in the Case Study’s Business Information Requirements section.
Stationary Supplier was established in 2010 and has grown into a global company supplying stationary supplies to branches all over the world. In order to be competitive in the global economy, the company needs to analyze and monitor its sales and marketing performance in relation to its products, retailers, inventory and forecast closely against its targets and plans. Decision is made to build a data warehouse or data marts to support the following business requirements. The company wants to hire you, an IT consultant, to help them discover more knowledge from the data they have collected from their operational databases over the years.
A meeting was held in the company with the key stake holders of the project. Their business requirements are summarized in the table below:
|Users||Business Information Requirements|
|A report showing the actual sales amount by year (with drill-down to quarter) and by region (with drill-down to country and retailer).
A report which allows user to select a Year and then a Quarter to show the planned and actual sales revenue with achievement % (use green color to show at least 100% achievement and red when otherwise), generated product category, drill down to sub-product category.
|3.||A report showing the proportion of profit made from sales by regions and its respective countries, for a chosen product category by year and quarter.|
|4.||A report showing the changes in rank (by actual sales amount) of the customer segment across the all the quarter based on actual sales amount.|
|5.||A dashboard which contains reports, useful slide filters and select value filters to help the Sales Director to explore and view the information.|
|A report showing the inventory movement on order quantity by year (with drill-down to month) and by Product Category (with drill-down to product sub-category).
A report which allows user to select a year and then a month to show the target Sales Quantity and actual order quantity (use blue color when actual met target sales quantity and use orange when otherwise) generated by products category and summarized by product sub-category.
|3.||A report showing the target and actual sales revenue generated from the chosen product category over the years and months and the proportion composition of the retailer country for each product category.|
|4.||A report showing the changes in rank (by actual sales quantity) of the retailer countries across the years based on actual sales quantity.|
|5.||A dashboard which contains reports, and useful slide filters and select value filters to help the Inventory Manager to explore and view the information.|
Give appropriate title, header or footer. Apply conditional formatting and calculations wherever applicable to enhance your reports.
The table structures of some of the operational systems database are given as follows:
Such a cheap price for your free time and healthy sleep
All online transactions are done using all major Credit Cards or Electronic Check through PayPal. These are safe, secure, and efficient online payment methods.