IS1024 Project Team 7

Вид материалаДокументы

Содержание


Document History/
Table of Contents
Direct interactions with BevDOT
Requirements Workshop
List of use cases
Scope: BevDOT System Level
Scope: BevDOT Level
Scope: BevDOT System Level
1.3    References
3.1    Operating System Compliance
4.    Reliability
4.2    Mean Time Between Failures
5.2    Database Access Response Time
7. Design Constraints
8. Documentation Requirements
8.2 Online Help
9. Scheduled Maintenance
11. System Requirements
12. Licensing, Security, and Installation
13.1 Data and Technology Variations
...
Полное содержание
Подобный материал:
  1   2   3   4   5


IS1024
Project Team 7



David Azzolina

Philip Boggs

Brian Defranco

Bryce Foster

Mike Smolenski

Mark Wimer



The Beverage Company Incorporated Data Warehouse System


BEVDOT Database Warehouse System


14th, April 2009

Problem/Opportunity Statement #4


Your team works in the IT division of The Beverage Company, Inc. The company produces beverages for sale to other businesses. The financial department wants to track, analyze, and forecast the sales revenue across geographic regions on a periodic basis for all products sold. You already have standard queries of the sales data. However, these queries add to the load on your operational database. Also, users sometimes ask for additional ad hoc queries of the data, based on the results of the standard queries. As a result, senior management at the company has decided to create a data warehouse for the sales data. A data warehouse is a database that contains data that has been cleansed and transformed from an operational format (Create, Read, Update, Delete (CRUD)) into an informational format (user query and report-centric.) Your team’s responsibility is to come up with an overall software solution to address this business opportunity.


Document History/Revision Record

Revision #

Date

Author(s)

Revision Notes

1.0

2/24/09

David Azzolina, Philip Boggs, Bryan Defranco, Bryce Foster, Mike Smolenski, Mark Wimer

Initial draft

1.2

3/12/2009

B. DeFranco

Initial release – draft (Section 3)

1.3

3/15/2009

M. Smolenski

B. Foster

P. Boggs

Initial interviews

Use-case realization

Initial features listed

1.4

3/25/09

M. Wimer

New use cases

1.5

3/28/09

M. Wimer

Additional Use Cases

2.0

4/2/2009

M. Smolenski


P. Boggs

B. Foster

Add Workshop information

First draft of Actors and use cases

Additional features added

Develop Features for use-case




4/7/2009

M. Wimer

Initial Draft (Section 5)

2.1

4/10/2009

B. DeFranco

Add Table of contents.

Add Section 13

2.2

4/12/2009

M. Smolenski

B. Foster


M. Wimer

Rework of actors and use cases

Develop test-cases for use-cases

Completing Use Cases and Supp Specs

2.3

4/11/2009

M. Wimer

Finalizing Use Cases

2.4

4/13/2009

P. Boggs


M. Wimer

Rework of use cases and prioritization


Combined Both SS’s and Modified

2.5

4/14/09

D. Azzolina

Final draft



Table of Contents







Fishbone Diagram


Current System is overloaded!




Comments: The Fishbone Diagram shown above is the first of the Total Quality Management (TQM) tools we used to determine the underlying root cause of the problem statement given to us by senior management. We analyzed cause-and-effect and revealed the causes and key relationships from group brainstorming sessions. The “bones” of the diagram include problems arising among the system, from the lack of reports, from the lack of space, and from the inability to analyze sales data. This diagram gave us crucial insight into our system’s process behavior, and led us into early development of our problem statement. We determined that the main cause was overload on the system.


Pareto Bar Chart




Comments: The Pareto bar chart represents the main problem of overload on the system as defined in the fishbone diagram. We took this root problem of an overloaded system and used it to build a Pareto bar chart with the purpose of further determining the underlying root cause and as a stepping stone in coming up with our own problem definition, and also to highlight the most important cause, or the most frequent reasons for senior management’s complaints.

We found that the highest occurring defect was the limited amount of storage space available (50% of the problem). Then, it was obsolete hardware/software (30% of the problem). The next most common defect was data that had not been cleansed and transformed from an operational format into an informational format (25% of the problem). And finally, employees’ acting on the system whose performance was not meeting the required metrics made up only a small parcel of the overarching problem (5% of the problem).



Developing the Problem Statement






Comments: Finally, we put together all of the results of our evolving TQM techniques and used the results to come up with our very own problem definition as this chart shows. This chart illustrates the steps that we took as a group to logically step through our understanding of the root cause, and displays the intermediary problem going back to the original problem given to us by senior management in the problem statement.



Users and Stakeholders

Users Stakeholders



The Financial Department Senior Management




The Sales Department Our Customers





The Marketing Department All Beverage

Company Incorporated

Stock Holders






The Shipping Department





IT (Information Technology) Dept.







Problem Statement

Element Description



The problem of… Insufficient resources.

Currently the legacy

system delivers only

operational data that

does not track, analyze

and forecast sales

revenue.




Affects… Senior Management,

Financial, Sales,

Shipping, and

Marketing departments.



And results in… Time consuming

queries. Data that

is not generated into

reports.




Benefits of a solution… Productivity (Time

management/Fast

queries). Generation

of useful reports. Sales

forecast and targeting.





Comments: Our problem definition was agreed upon as a group and is summed in the problem of insufficient resources. This problem involves taking the current legacy database system and doing something to deliver informational data. These changes will affect all of our users and stakeholders. The results will pay off over time and furthermore we will be greatly benefitted by the new system in that we will be able to forecast and target the market based on our sales data.


Comments: This is our compiled list of users and stakeholders.



Defining Solution System Boundary






Direct interactions with BevDOT

-Legacy System - Sales Department

-Shipping Department - Financial Department

-Marketing Department



Identify Constraints on Solution

Users Stakeholders Rationale



Equipment Budget

Simple system cost, no new client hardware

Cost control. Maintenance.

Technology

Solution must be a Database Warehouse. Solution must use current Legacy database.


Increased productivity and profitability.



Security

Any sensitive customer or user data must be protected and secure.

Prevent information leak, secure company assets.



Operational

Backed up copies of sales revenue will remain on the Legacy database system for up to 6 months.

High risk of data loss.



Comments: Our constraints are all associated with variables such as Equipment and cost, environment and data security. And all of our reasoning was efficiently justified with critical thinking.


  1. Introduction

Purpose

The purpose of this section was to elicit and organize the needs of the stakeholders for the BevDot System. The interviews will show what each person and department wanted to see in this system and them will be organized into features and high level use cases. These documents will go though the step by step process; discussions, the workshop and the questionnaire.
  1. 2.1 Elicit Stakeholder Request
    1. The interview

The Following is the questions that were taken into each interview. While each interview started the same, it was expected that each interview would end in different directions based on stakeholder responses.

Questions*
  1. Name?
  2. Job title?
  3. What are your key responsibilities?
  4. How is success measured?
  5. For what problems do you lack solutions?
  6. Why does this problem exist?
  7. How would you like to solve it?
  8. Who uses the current system
  9. What is their educational and computer background?
  10. Are additional applications in use that is relevant to the application?
  11. What do you expect out of this product?
  12. What kind of user help do you need?
  13. What are your expectations of performance and reliability?



*References Managing Software Requirements, Second Edition, Dean Leffingwell and Don Widrig


The following people were interviewed From Beverage Company Inc.
  1. Dave Smith, CIO
  2. John Bell, CFO
  3. Michael Davis, Senior Manager of Marketing
  4. Jen Jones, Senior Manager of Sales
  5. Don King, IT Manager
  6. Bella Mathis, Head of Shipping



The following are the full interviews of each person.
        1. Dave Smith
        2. CIO
        3. “I oversee all information systems for the company”
        4. “I find that as long as all systems are running well the company runs like a well oiled machine”
        5. “Our current system Runs Slow”
        6. “The system is overloaded. There are too many users on it and it takes too long to get the information that our people want.”
        7. “We need a database warehouse that allows us to spread out the usage of both systems.”
        8. “Marketing, sales and the senior management some of the time.”
        9. “Most have a bachelor’s degree, but have only a basic understanding of computers.”
        10. “Just the standard database programs”
        11. “I expect to see reporting of the information inputted into the warehouse”
        12. “Standard online and written texts.”
        13. “I want to see 24 hour availability with only 1% downtime”
  1. John Bell
  2. CFO
  3. “I oversee all financial transactions, the sales and market departments”
  4. “Increases sales revenue”
  5. “I want to see sales forecasting reports and We need to be able to create our own reports”
  6. “Our current system is limited”
  7. “Increased functions in the new system”
  8. “Most of the people in both departments
  9. “bachelors’ degrees, basic computer knowledge”
  10. “Not that I am away of”
  11. “More reports”
  12. “Any training plus an instruction book”
  13. “As long as it works that what I want”
  1. Michael Davis
  2. Senior Manager of Marketing
  3. “I oversee the Marketing department”
  4. “Good response to our marketing campaigns”
  5. “We need to see sales reports based on a particular region’
  6. “Our current system does not allow us to see these kind of reports”
  7. “Add this as a function of a new system”
  8. “Most of my staff uses the current system”
  9. “Most of my people have a bachelors’ degree and basic computer knowledge”
  10. “No Idea”
  11. “Better reports”
  12. “Good training of the system”
  13. “As long as if runs during business hours I’m good”


  1. Jen Jones
  2. Senior Manager of Sales
  3. “I oversee the entire sales department”
  4. “Increasing sales”
  5. “We need to see monthly, quarterly or yearly reports”
  6. “Current system sucks”
  7. “More reporting”
  8. “most of my staff uses the Current system”
  9. “High school diploma or greater and have a typical understanding of computers”
  10. “ummm….no clue”
  11. “Better reporting of sales numbers”
  12. “I just need to know where to click”
  13. “Make it work”
  1. Don King
  2. IT Manager
  3. “I run to IT department”
  4. “I am happy as long as everything is running”
  5. “I would like to see how well the system is running”
  6. “The current system does not have the functionality that it needs”
  7. “Increased functionality in the database will allow us to check efficiency also it would be a benefit for us if we have an ETL program that will automatically retrieve the data from the old system and place it in the database warehouse”
  8. “All of my staff services the current system at some point or another”
  9. “bachelors or better with solid computer skills”
  10. “Just the usual Oracle based database program”
  11. “Efficiency”
  12. “I won’t need too much”
  13. “We will keep it running”
  1. Bella Mathis
  2. Head of shipping
  3. “I oversee all parcels in and out of the company”
  4. “Everything get where its goin’ on time”
  5. “I need to see where my parcels have gone”
  6. “The current system does not show this information to us’
  7. “Be able to print this kind of report when I need to”
  8. “None of my people do”
  9. “High school mostly with basic computer skills”
  10. “No idea”
  11. “Reports”
  12. “As much as possible”
  13. “I’m happy as long as it works”



After talking with each of these people the notes from the interviews were reviewed and the following statements were taken from them. These statements were found important in determining the system features for the BevDot Database warehouse.


Dave Smith

“Our current system runs too slow.”

John Bell

“I would like to see sales forecasting reports”

“We need to be able to create our own reports”

Michael Davis

“We need to see sales reports based on a particular region”

Jen Jones

“I need to see monthly, quarterly or yearly reports”

Don King

“It would be a benefit for us if we have an ETL program that will automatically retrieve the data from the old system and place it in the new database warehouse”

“I would like to see how well the system is running”

Bella Mathias

“I need to see where my parcels have gone”
    1. Requirements Workshop

After we met with some of the key people in different departments we put together a requirements workshop. We got together people from different departments put them together and discussed the task at hand. The workshop consisted of two days worth of meeting. It included brainstorming sessions, feature definitions and idea reduction and prioritization.

During this workshop we found that some ideas were the same as those that we got from the interview, however, we also got some other ideas that were new. After sorting through everything we found three new points that would lead to features.


Graphical representation of source data

Error handling

User-Friendly GUI


    1. Questionnaire

The questionnaire was sent out to people in different departments that were not able to attend the requirements workshop. It was an attempt to elicit and more features for the system. The questionnaire contained the same questions that the interviewer had with him. We received very few of the questionnaires back and the ones that we did contained the same ideas from sections a and b.


  1. Section 2.2 Determine System Features

After all the time was spent speaking with various people and getting responses to our questions we came up with a list of features that the system would need in order to satisfy all of the stakeholders for the system. The following is the features for BevDOT.


  1. Reports
    1. Ad-hoc
    2. Sales Forecasting
    3. Regional Sales
    4. Seasonal Sales
    5. Tracking
    6. Efficiency


  1. User-friendly GUI


  1. Database Warehouse



  1. Periodic Auto Uploads From Legacy System (ETL)
    1. Extract Data
    2. Auto Validation
    3. Transform Data
    4. Load Data


  1. Manual Validation of Data from Legacy System


  1. Error Handling


  1. Graphical representations of Reports


  1. Section 2.3 Finding Actors and Use cases


    1. There are four main actors for BevDOT


  • The Legacy System – it will be responsible for interfacing with BevDOT to upload new data into the warehouse. It will be the primary actor for the ETL process


  • The Financial Department – this includes two groups. Marketing and Sales. They will be the primary actors for most of the reports.


  • The IT Department – They will responsible for maintaining and updating BevDOT. They will print the System Efficiency Reports. They also will be responsible for manual validation of any data that is kicked out of the ETL process.



  • The Shipping Department – They are the primary actors for the Tracking Reports.
    1. Uses Cases

The following are the use cases that will be needed in order to complete the system.


  • Generate Reports
    • Ad-hoc
    • Sales Forecasting
    • Regional Sales
    • Seasonal Sales
    • Tracking
  • System Efficiency Reports
    • The DBA will be able to periodically check the amount of system errors, system load and other important system information.
  • Manual Validation
    • The IT Department will manually validate any information that is kicked out of the ETL process
  • Generate Graphs of Reports
    • This is for all reports including efficiency reports. The user will be able to have graphs made for presentational or other purposes.
  • Retrieve Data from Legacy System
    • ETL Process
      • Extract
      • Validate
      • Transform
      • Load Data



Use Case Diagram – First Iteration





Use Case Diagram – 2nd Iteration