Business Intelligence and Analytics Design and Implementation

Assignment
Business Intelligence and Analytics Design and Implementation
 Objectives:

  1. Understand the process and requirements of creating an OLAP application.
  2. Create analysis model (Star Schema) using dimensional data modeling techniques.
  3. Create reports and dashboard to fulfill case study’s business information requirements.

Instructions:
 Pick a user role as listed in the Case Study’s Business Information Requirements section.
 

  1. Data Warehouse / Data Mart Design Content:
    • User role assigned
    • Business measures with based or derived indicator
    • Business dimensions with corresponding hierarchical information
    • Granularity statement
    • Star schema design for the data warehouse / data mart, with business measures and dimensions with relevant primary keys and attributes
  2. Assignment Report Content:
    • User role as picked
    • Every business information requirement description and its corresponding screen captures of reports and/or dashboard to fulfill the requirement.

Company Background

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.

Business Information Requirements

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.
Sales
Director
1.
2.
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.
B.
Inventory
Manager
1.
2.
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.

Data Sources

The table structures of some of the operational systems database are given as follows:
Order Now

Calculate a fair price for your paper

Such a cheap price for your free time and healthy sleep

1650 words
-
-
Place an order within a couple of minutes.
Get guaranteed assistance and 100% confidentiality.
Total price: $78
WeCreativez WhatsApp Support
Our customer support team is here to answer your questions. Ask us anything!
👋 Hi, how can I help?