Реферат: IS1024 Project Team 7

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.



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.

2.1 Elicit Stakeholder Request

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*

Name?

Job title?

What are your key responsibilities?

How is success measured?

For what problems do you lack solutions?

Why does this problem exist?

How would you like to solve it?

Who uses the current system

What is their educational and computer background?

Are additional applications in use that is relevant to the application?

What do you expect out of this product?

What kind of user help do you need?

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.

Dave Smith, CIO

John Bell, CFO

Michael Davis, Senior Manager of Marketing

Jen Jones, Senior Manager of Sales

Don King, IT Manager

Bella Mathis, Head of Shipping



The following are the full interviews of each person.

Dave Smith

CIO

“I oversee all information systems for the company”

“I find that as long as all systems are running well the company runs like a well oiled machine”

“Our current system Runs Slow”

“The system is overloaded. There are too many users on it and it takes too long to get the information that our people want.”

“We need a database warehouse that allows us to spread out the usage of both systems.”

“Marketing, sales and the senior management some of the time.”

“Most have a bachelor’s degree, but have only a basic understanding of computers.”

“Just the standard database programs”

“I expect to see reporting of the information inputted into the warehouse”

“Standard online and written texts.”

“I want to see 24 hour availability with only 1% downtime”

John Bell

CFO

“I oversee all financial transactions, the sales and market departments”

“Increases sales revenue”

“I want to see sales forecasting reports and We need to be able to create our own reports”

“Our current system is limited”

“Increased functions in the new system”

“Most of the people in both departments

“bachelors’ degrees, basic computer knowledge”

“Not that I am away of”

“More reports”

“Any training plus an instruction book”

“As long as it works that what I want”

Michael Davis

Senior Manager of Marketing

“I oversee the Marketing department”

“Good response to our marketing campaigns”

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

“Our current system does not allow us to see these kind of reports”

“Add this as a function of a new system”

“Most of my staff uses the current system”

“Most of my people have a bachelors’ degree and basic computer knowledge”

“No Idea”

“Better reports”

“Good training of the system”

“As long as if runs during business hours I’m good”



Jen Jones

Senior Manager of Sales

“I oversee the entire sales department”

“Increasing sales”

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

“Current system sucks”

“More reporting”

“most of my staff uses the Current system”

“High school diploma or greater and have a typical understanding of computers”

“ummm….no clue”

“Better reporting of sales numbers”

“I just need to know where to click”

“Make it work”

Don King

IT Manager

“I run to IT department”

“I am happy as long as everything is running”

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

“The current system does not have the functionality that it needs”

“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”

“All of my staff services the current system at some point or another”

“bachelors or better with solid computer skills”

“Just the usual Oracle based database program”

“Efficiency”

“I won’t need too much”

“We will keep it running”

Bella Mathis

Head of shipping

“I oversee all parcels in and out of the company”

“Everything get where its goin’ on time”

“I need to see where my parcels have gone”

“The current system does not show this information to us’

“Be able to print this kind of report when I need to”

“None of my people do”

“High school mostly with basic computer skills”

“No idea”

“Reports”

“As much as possible”

“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”

^ 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



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.



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.



Reports

Ad-hoc

Sales Forecasting

Regional Sales

Seasonal Sales

Tracking

Efficiency



User-friendly GUI



Database Warehouse




Periodic Auto Uploads From Legacy System (ETL)

Extract Data

Auto Validation

Transform Data

Load Data



Manual Validation of Data from Legacy System



Error Handling



Graphical representations of Reports



Section 2.3 Finding Actors and Use cases



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.

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





^ List of use cases:


UC #1 – Extract Data


UC #2 – Validate Data


UC #3 – Manual Validate Data


UC #4 – Transform Data


UC #5 – Load Data


UC #6 – Error Handling


UC #7 – Generate Reports


UC #8 – Ad Hoc Report


UC #9 – Create Graphs


Use Case #1.0

Use Case Name: Extract Data

Description: Initiates the ETL process for transferring data from the operational format legacy system to the informational format BevDOT data warehouse.

^ Scope: BevDOT System

Level: Batch Process

Primary Actor: Legacy System

Stakeholders and Interests: IT Department, DBA

Preconditions: BevDOT system has loaded the data acquisition and extraction rules from external file.

Success Guarantee (Postcondition): Required data is retrieved from Legacy System and passed to Validate Data1

Main Scenario (Basic Flow):

BevDOT system connects to Legacy System using ODBC over TCP/IP

Legacy System authenticates BevDOT system

3. BevDOT system generates SQL statements based on loaded rules, and the date the last extraction was performed.

4. BevDOT System submits query to Legacy System

5. Legacy system begins return of requested data

6. BevDOT System performs ValidateData1

7. BevDOT System checks if more data is available from Legacy system

8. BevDOT System disconnects from Legacy system


Technology and Data Variations: Data extraction rules are specified outside of BevDOT, and define the data to retrieve. The rules file will also define table formats of Legacy system to facilitate ability to reconfigure if Legacy database structure changes in the future.


Use Case #2.0

Use Case Name: Validate Data

Description: Ensures data integrity. Routes inconsistent data to be held in temporary tables for Manual Validation

Scope: BevDOT System

Level: Sub Function

Primary Actor: Legacy System

Stakeholders and Interests: IT Department, DBA

Preconditions:

1. BevDOT System is authenticated with Legacy System

2. A data chunk has been extracted.

3. Validation rules have been loaded from external file.


Success Guarantee (Postcondition): Required data chunk is validated and passed to Transform Data


Main Scenario (Basic Flow):

BevDOT System applies rules from external configuration.

BevDOT System performs Transform Data



Extensions:

1a. Upon failure of validation algorithm

BevDOT System saves the questionable data in temporary storage for manual review



*a. Upon detection of system failure

1. BevDOT System performs Error Handling

Return error code to Extract Data



Technology and Data Variations:

A data chunk is a sufficient amount of data to perform batch validation, transformation, and storage in data warehouse.

Special Requirements:

Validation rules will contain checks for null values, dates out of range, orphaned records, and any other items the DBA requires for successful transformation. This external configuration can be changed as needed.

Use Case #3.0

Use Case Name: Manual Validate Data

Description: DBA Corrects data anomalies received from Legacy Database

Scope: BevDOT System

Level: User Goal

Primary Actor: DBA

Stakeholders and Interests: IT Department, DBA

Preconditions:

User is logged on to BevDBW system, and has entered Manual Validation screen

Validation rules are loaded from external file



Success Guarantee (Postcondition): Data is successfully corrected and passed to Transform Data

Main Scenario (Basic Flow):

BevDOT System displays questionable data from temporary storage

User corrects data abnormalities

BevDOT System validates data using validation rules

BevDOT System performs Transform Data



Extensions:

3a. Upon failure of validation algorithm

1. BevDOT System displays data that did not pass validation

BevDOT System resumes at step 1



*a. Upon detection of system failure

1. BevDOT System performs Error Handling


Technology and Data Variations: None


Special Requirements:

Validation rules will contain checks for null values, dates out of range, orphaned records, and any other items the DBA requires for successful transformation. This external configuration can be changed as needed.


Use Case #4.0

Use Case Name: Transform Data

Description: Data is cleansed and transformed from an operational format, into an informational format consistent with BevDOT data warehouse

Scope: BevDOT System

Level: Sub Function

Primary Actor: Legacy System

Stakeholders and Interests: IT Department, DBA

Preconditions:

BevDOT System is authenticated with Legacy System

A data chunk has been Extracted and Validated.

Transformation Rules have been loaded from external file.



Success Guarantee (Postcondition): Required data chunk is cleansed and transformed and passed to Load Data

Main Scenario (Basic Flow):

BevDOT System applies transformation algorithm to data.

BevDOT System performs Load Data



Extensions:

*a. Upon detection of system failure

1. System performs Error Handling

Return error code to Validate Data



Technology and Data Variations:

A data chunk is a sufficient amount of data to perform batch validation, transformation, and storage in data warehouse.

Transformation algorithm is based on loaded rules defined external to the BevDOT system. These rules may be changed for future needs.


Use Case #5.0

Use Case Name: Load Data

Description: Data is loaded into tables in the BevDOT data warehouse in a manner consistent with existing information data format

Scope: BevDOT System

Level: Sub Function

Primary Actor: Legacy System

Stakeholders and Interests: IT Department, DBA

Preconditions:

1. BevDOT System is authenticated with Legacy System

2. A data chunk has been Extracted, Validated, and Transformed.

3. BevDOT system has loaded the configuration rules from external file.


Success Guarantee (Postcondition): Required data is stored in data warehouse tables

Main Scenario (Basic Flow):

BevDOT System generates SQL statements based on configuration file

BevDOT System initiates transaction

BevDOT System writes data to data warehouse tables using SQL

BevDOT System commits changes



Technology and Data Variations: Data Loading rules are specified outside of BevDOT. The rules file will define table formats of BevDOT system to facilitate ability to reconfigure if BevDOT database structure changes in the future.


(creator: Mark Wimer)

Use Case #6.0


Use Case Name: Error Handling

Description: Error Handling use case will perform the appropriate actions if an abnormal error occurs within the system’s scope of performance.

Scope: BevDOT

Level: System Goal

Primary Actor: Database Administrator, System Administrator

Stakeholders and Interests: Senior Management, IT Department

Preconditions:

BevDOT is performing a system function

System error occurs



Success Guarantee (Postcondition):

BevDOT successfully receives, codes, and logs system errors for later retrieval.



Main Scenario (Basic Flow):

Legacy system sends handshake request to BevDOT

BevDOT connects to Legacy System

Legacy system sends error report(s) to BevDOT

BevDOT disconnects from Legacy System

BevDOT assigns incremental error code to each error report

BevDOT logs errors in a flat file



The use case on this page was created by Mark Wimer

(creator: Mark Wimer and Philip Boggs)

Use Case # 7.0

Use Case Name: Generate Reports

Description: Serves as the initial reporting interface for users of the BevDOT system to generate reports based on existing BevDOT data.

^ Scope: BevDOT

Level: User Goal

Primary Actor: Sales, Marketing, Finance, Shipping, and IT departments.

Stakeholders and Interests: Sales, Marketing, Finance, Shipping, and IT departments – will use this to generate various reports for data contained within BevDOT.

Senior management - will use reports to set and track goals at the company, division, and country level to anticipate trends and changes in the market over time to improve forecasting, and to make executive decisions based on product demand.

Preconditions: User is logged into the system. Database must be established with minimum of one (1) record.

Success Guarantee (Postcondition): A report is generated.

Main Scenario (Basic Flow):

1. System displays operations options on the GUI interface.

2. User chooses the generate report option.

3. System displays a report type variant screen.

4. User selects a report type from the menu.

5. System queries BevDOT for report-specific data.

7. BevDOT returns data to System.

8. System displays data in spreadsheet format.

9. System presents options to print results or export results.


Use Case #8.0

Use Case Name: Generate Ad-Hoc Report

Description: Gives users ability to specify parameters to craft report output.

Scope: BevDOT System

Level: Sub function of Generate Reports

Primary Actor: Sales person, Marketing Agent, Accountant

Stakeholders and Interests: Senior management - will use reports to set and track goals at the company, division, and country level to anticipate trends and changes in the market over time to improve forecasting, and to make executive decisions based on product demand.

Preconditions: User is logged into the system. Reports menu is displayed.

Success Guarantee (Postcondition): Report is generated, critiqued to the users’ needs.

Main Scenario (Basic Flow):

User chooses ‘Custom Reporting’ from the menu.

System presents options for the user to choose a saved configuration, or create a new one

User chooses ‘create new’

System presents options screen for the user to select and/or key-in values

User selects desired options and enters needed criteria.

System checks for incomplete or improper specification

System generates query from the users configuration.

System queries data warehouse and displays results

System presents options to print, save, save configuration, generate graph, quit



Extensions (Alternative Flows):

2a. Upon selection to use saved configuration,

System presents user with list of saved configurations

User chooses the desired configuration from the menu

Skip to step 7.



6a. Upon detection of bad spec:

System informs user of the fields improperly or not filled

System resumes at step 4



9a. Upon selection to print

System sends the report to the print spooler.

System presents Reports menu



9b. Upon the selection to save

System writes the query results to an Excel compatible file

System resumes at step 9



9c. Upon selection to save configuration

System prompts for input of a name for the custom configuration

User enters a name for the custom configuration

System saves the query criteria in the database

System resumes at step 9



9d. Upon selection to generate graph

System Performs Generate Graph

System resumes at step 9



9e. Upon selection to quit

1. System presents the Reports menu


*a. Upon detection of system failure:

Present failure message to user.

Generate error message and send to IT dept error logger

Restart main menu, step 1



Special Requirements: None


Technology and Data Variations: None


Frequency of Occurrence: As needed


(creator: Mark Wimer and Bryan Defranco)

Use Case #9.0


Use Case Name: Create Graphs

Description: Gives users ability to create graphs in conjunction with existing BevDOT reports and previously saved ad-hoc reports. Users will use a 3rd party graphing tool to interpret BevDOT data for graph generation

^ Scope: BevDOT System

Level: Sub function of Generate Reports

Primary Actor: Sales person, Marketing Agent, Accountant, System Administrator, Database Administrator

Stakeholders and Interests: Sales, Marketing, Finance / Accounting, System Administrators, Database Administrators – will all have access to create graphs

Preconditions:

User is logged into the system.

Reports menu has been accessed and is displayed.



Success Guarantee (Postcondition): Graph is generated based on existing BevDOT reports, or previously saved ad-hoc reports.

Main Scenario (Basic Flow):

1. User chooses the ‘Graph Data’ option.

2. System displays a ‘Graph Type’ selection screen.

3. User selects a graph type from the available options.

4. System presents options to print results, export results, or quit.


[Bryan DeFranco]

Introduction



1.1    Purpose

The purpose of this document is to define requirements of the BevDOT system. This Supplementary Specification lists the requirements that are not readily captured in the use cases of the use-case model. The Supplementary Specifications and the use-case model together capture a complete set of requirements on the system.

1.2    Scope

This Supplementary Specification applies to the BevDOT System which will be developed by the Beverage Company, Inc. IT department. The IT department will develop this client-server system to interface with the existing legacy database system.

BevDOT system will give users needed information previously not redily available. BevDOT system will reduce the load on the existing Legacy system.

This specification defines the non-functional requirements of the system; such as reliability, usability, performance, and supportability as well as …

^ 1.3    References

[References to be added in final supplementary specification]


 [Philip Boggs]

Functionality



2.1 System Error Handling


All system errors shall be logged. Non-fatal errors will cause the session to restart at the best previous point. Fatal system errors shall result first in a system halt, then an orderly shutdown of the system.


The system error messages shall include a text description of the error, the operating system error code (if applicable), the module detecting the error, and a date/time stamp. All system errors shall be retained in the Error Log Database.

[Philip Boggs]

3.    Usability

This section lists all of those requirements that relate to, or affect, the usability of the system.

^ 3.1    Operating System Compliance

The desktop user-interface shall be platform independent. The minimum operating system requirements shall be Microsoft Windows XP, Linux kernel 5.1, or Mac OS X 10.3.

3.2    Design for Ease-of-Use

The user interface of the data warehouse shall be designed for ease-of-use and shall be appropriate for a computer-literate user community with a minimal level of training on the System.

[Philip Boggs]

^ 4.    Reliability

This section lists all reliability requirements.

4.1    Availability

The data warehouse shall be available 99% during normal business hours. It will also be available outside of business hours subject to SS item #9

^ 4.2    Mean Time Between Failures

      Mean Time Between Failures shall exceed 500 hours.

 [Philip Boggs]

  5.    Performance

The performance characteristics of the system are outlined in this section.

5.1    Simultaneous Users

The system shall support up to 500 simultaneous users against the central database at any given time, and up to 200 simultaneous users against the local servers at any one time.


^ 5.2    Database Access Response Time

The BevDOT system shall respond in a time of no longer than 2 seconds during normal transactional operations.


[Mark Wimer]

6. Supportability


6.1    Software Updates and Maintenance

To provide as much security as possible,. The database itself will not be connected to any external connections except for occasional software updates and maintenance, at which point a system admin will enable such a connection. Any off-site access must be done through a VPN to the legacy system.


[Bryan DeFranco]

^ 7. Design Constraints


7.1 Legacy System


The system shall integrate with the existing legacy database system.


The system shall clean and transform data from an operational format (Create, Read, Update, Delete) into an informational format, and store it in a data warehouse.



Software platform requirements



The system shall use a modern, industry-standard data warehousing scheme. Refer to supplementary specification item #10

[Philip Boggs]

^ 8. Documentation Requirements


This section will outline the documentation that will be included for a successful deployment of the product.


8.1 User Manual

The user manual will provide a purpose, table of contents, and glossary as well as detailed instructions on operating the product and troubleshooting any problems that may be encountered.


^ 8.2 Online Help

The online help section will have a download of the user manual a frequently asked questions page.


8.1    Online Help

Each feature of the data warehouse shall have built-in online help for the user. Online Help shall include step by step instructions on using the System. Online Help shall include definitions for terms and acronyms


[Bryan DeFranco]

^ 9. Scheduled Maintenance


Periodical loading / ETL batch transformations from legacy database will take place during evening/night (non-business hours)


[Section 10,11,12,by Michael Smolenski]

10. Applicable Standards

The Database Warehouse system must run an Oracle based Unix system and compatible with Windows XP or greater. It needs an easy to use GUI interfaces will allow easy to use access to the necessary data. The interface must be compatible with a TCP/IP protocol accessed on a company controlled WAN. Firewalls must be in place to avoid outside users accessing the WAN. The system should also be compatible with Norton Antivirus software.


^ 11. System Requirements

The hosting server will be a Windows based or Unix operating system with at least 4 GB of memory and each PC that interfaces with the sever must have at least 1 GB of memory in order to run the necessary queries. The hosting server will have at least 2 TB or storage with bays available to expand as needed.


^ 12. Licensing, Security, and Installation

An Oracle or Microsoft licenses will be needed for the server. Microsoft licenses will be needed for the interfacing computers. Norton Antivirus software licenses will be needed for all computers in the network. Only authorized employees of Beverage Inc. will be allowed to access the system and updates will handled by the maintenance staff.


[Bryan DeFranco]

Technical Specification



^ 13.1 Data and Technology Variations


13.1.1 Data Chunk

A data chunk is a sufficient amount of data to perform batch validation, transformation, and storage in data warehouse for a DB-unit of information. This typically will be less than 12 records, or 100K Bytes per chunk, but each chunk will vary is size.


A DB-unit of information is that which sufficient to create a meaningful storage in Data Warehouse informational format. This is bound by the external rules file.


^ 13.1.2 Extraction, Transformation and Validation Rules

Data extraction rules are specified outside of BevDOT, and define the data to retrieve. The rules file will also define table formats of Legacy system to facilitate ability to reconfigure if Legacy database structure changes in the future.

BevDOT

Legacy System

Validate

Transform


1. Introduction

Purpose

The purpose of this section was to prioritize the features and the use cases for the BevDot System and then identify the use cases for the first iteration. The prioritization of the use cases is related to the needs as specified in section two.



^ 4.1 Prioritize Use Cases

Features Table, Prioritized

ID

Feature

Effort

Risk

Comment

1

Extract Data

Med

High

Download data

2

Auto validate data

Med

High

Auto validation

3

Manually validate data

Med

High

Manual validation

4

Transform data to BevDOT

High

High

Transform data

5

Load data

Med

High

Load validated data

6

Error handling

High

High

Handle critical errors

7

Generate reports(high level)

Med

Med

High level reports

8

Generate ad-hoc reports

Med

Med

Ad-Hoc reports

9

Generate sales forecasting reports

Med

Med

Sales forecasting reports

10

Generate regional revenue reports

Med

Med

Regional revenue reports

11

Generate tracking reports

Med

Med

Tracking reports

12

Generate seasonal sales reports

Med

Med

Seasonal sales reports

13

Create system efficiency reports

Med

Low

System efficiency reports for IT

14

Create graphical representations of data

Med

Low

Make graphs based on report data




Use Cases, Prioritized

The use cases have been derived from the features as shown above and put in order of importance from highest to lowest.

ETL Use Case (Extract, Transform, Load)

Automatic Validation

Manual Validation

Error Handling

Generate Reports

Generate Ad-Hoc Reports

Create Graphs



Section 4.2 Identify Use Cases for Initial Iteration

After discussing the necessary use cases for the successful operation of the system, the use cases have been narrowed down to six for the initial iteration of BevDOT. They are as follows:

ETL Use Case

Automatic Validation

Manual Validation

Error Handling

Generate Reports

Generate Ad-Hoc Reports

These six (6) use cases will fulfill the needs of the company in the initial iteration. Graphical representations of data from user-generated reports as well as additional reports will be added in future iterations.



























1. Introduction

Purpose

The purpose of this section is to further refine the system by finalizing preliminary use cases into fully dressed use cases. This section is to also refine the initial supplementary specification into the Final Supplementary Specification for the BevDOT data Warehouse.

5.1a (creator: Bryan Defranco)

Use Case #1.0


Use Case Name: Extract Data


Description: Initiates the ETL process for transferring data from the operational format legacy system to the informational format BevDOT data warehouse.


^ Scope: BevDOT System


Level: Batch Process


Primary Actor: Legacy System


Stakeholders and Interests: IT Department, DBA


Preconditions: BevDOT system has loaded the data acquisition and extraction rules from external file.


Success Guarantee (Postcondition): Required data is retrieved from Legacy System and passed to Validate Data1


Main Scenario (Basic Flow):

BevDOT system connects to Legacy System using ODBC over TCP/IP

Legacy System authenticates BevDOT system

3. BevDOT system generates SQL statements based on loaded rules, and the date the last extraction was performed.

4. BevDOT System submits query to Legacy System

5. Legacy system begins return of requested data

6. BevDOT System performs ValidateData1

7. BevDOT System checks if more data is available from Legacy system

8. BevDOT System disconnects from Legacy system


Extensions:

7a. Upon additional data available

BevDOT restarts process at step 4.



7b. Upon no additional data available

BevDOT resumes process at step 8



*a. Upon detection of system failure

1. BevDOT System performs Error Handling


Special Requirements: None


Technology and Data Variations: Data extraction rules are specified outside of BevDOT, and define the data to retrieve. The rules file will also define table formats of Legacy system to facilitate ability to reconfigure if Legacy database structure changes in the future.


^ Frequency of Occurrence: Weekly


5.1b (creator: Bryan Defranco)

Use Case #2.0


Use Case Name: Validate Data


Description: Ensures data integrity. Routes bad data to be held in temporary tables for Manual Validation


Scope: BevDOT System


Level: Sub Function


Primary Actor: Legacy System


Stakeholders and Interests: IT Department, DBA


Preconditions:

1. BevDOT System is authenticated with Legacy System

2. A data chunk has been extracted.

3. Validation rules have been loaded from external file.


Success Guarantee (Postcondition): Required data chunk is validated and passed to Transform Data


Main Scenario (Basic Flow):



BevDOT System applies rules from external configuration.

BevDOT System performs Transform Data



Extensions:

1a. Upon failure of validation algorithm

BevDOT System saves the questionable data in temporary storage for manual review



*a. Upon detection of system failure

1. BevDOT System performs Error Handling

Return error code to Extract Data



Special Requirements:

Validation rules will contain checks for null values, dates out of range, orphaned records, and any other items the DBA requires for successful transformation. This external configuration can be changed as needed.


Technology and Data Variations:

A data chunk is a sufficient amount of data to perform batch validation, transformation, and storage in data warehouse.


^ Frequency of Occurrence: As needed by parent process

5.1c (creator: Bryan Defranco)

Use Case #3.0


Use Case Name: Manual Validate Data


Description: DBA Corrects data anomalies received from Legacy Database


Scope: BevDOT System


Level: User Goal


Primary Actor: DBA


Stakeholders and Interests: IT Department, DBA


Preconditions:

User is logged on to BevDBW system, and has entered Manual Validation screen

Validation rules are loaded from external file



Success Guarantee (Postcondition): Data is successfully corrected and passed to Transform Data


Main Scenario (Basic Flow):



BevDOT System displays questionable data from temporary storage

User corrects data abnormalities

BevDOT System validates data using validation rules

BevDOT System performs Transform Data



Extensions:

3a. Upon failure of validation algorithm

1. BevDOT System displays data that did not pass validation

BevDOT System resumes at step 1



*a. Upon detection of system failure

1. BevDOT System performs Error Handling


Technology and Data Variations: None


Special Requirements:

Validation rules will contain checks for null values, dates out of range, orphaned records, and any other items the DBA requires for successful transformation. This external configuration can be changed as needed.

5.1d (creator: Bryan Defranco)

Use Case #4.0


Use Case Name: Transform Data


Description: Data is cleansed and transformed from an operational format, into an informational format consistent with BevDOT data warehouse


Scope: BevDOT System


Level: Sub Function


Primary Actor: Legacy System


Stakeholders and Interests: IT Department, DBA


Preconditions:

BevDOT System is authenticated with Legacy System

A data chunk has been Extracted and Validated.

Transformation Rules have been loaded from external file.



Success Guarantee (Postcondition): Required data chunk is cleansed and transformed and passed to Load Data


Main Scenario (Basic Flow):



BevDOT System applies transformation algorithm to data.

BevDOT System performs Load Data



Extensions:

*a. Upon detection of system failure:

System displays error code

System Performs Error Handling

Return error code to Validate Data



Special Requirements: None


Technology and Data Variations:

A data chunk is a sufficient amount of data to perform batch validation, transformation, and storage in data warehouse.


Transformation algorithm is based on loaded rules defined external to the BevDOT system. These rules may be changed for future needs.


^ Frequency of Occurrence: As needed by parent process


5.1e (creator: Bryan Defranco)

Use Case #5.0


Use Case Name: Load Data


Description: Data is loaded into tables in the BevDOT data warehouse in a manner consistent with existing information data format


Scope: BevDOT System


Level: Sub Function


Primary Actor: Legacy System


Stakeholders and Interests: IT Department, DBA


Preconditions:

1. BevDOT System is authenticated with Legacy System

2. A data chunk has been Extracted, Validated, and Transformed.

3. BevDOT system has loaded the configuration rules from external file.


Success Guarantee (Postcondition): Required data is stored in data warehouse tables


Main Scenario (Basic Flow):

BevDOT System generates SQL statements based on configuration file

BevDOT System initiates transaction

BevDOT System writes data to data warehouse tables using SQL

BevDOT System commits changes



Extensions:

*a. Upon detection of system failure:

System displays error code

System Performs Error Handling



Special Requirements: None


Technology and Data Variations: Data Loading rules are specified outside of BevDOT. The rules file will define table formats of BevDOT system to facilitate ability to reconfigure if BevDOT database structure changes in the future.


Frequency of Occurrence: As needed by parent process


5.1f (creator: Mark Wimer)

Use Case #6.0


Use Case Name: Error Handling


Description: Error Handling use case will perform the appropriate actions if an abnormal error occurs within the system’s scope of performance.


Scope: BevDOT


Level: System Goal


Primary Actor: Database Administrator, System Administrator


Stakeholders and Interests: Senior Management, IT Department


Preconditions:

BevDOT is performing a system function

System error occurs



Success Guarantee (Postcondition):

BevDOT successfully receives, codes, and logs system errors for later retrieval.



Main Scenario (Basic Flow):

Legacy system sends handshake request to BevDOT

BevDOT connects to Legacy System

Legacy system sends error report(s) to BevDOT

BevDOT disconnects from Legacy System

BevDOT assigns incremental error code to each error report

BevDOT logs errors in a flat file



Extensions (Alternative Flows):

6a. Export Error log

User opens log file

BevDOT displays all errors

User chooses, ‘Export Data’ from file options

BevDOT presents user with file extensions for exporting

User selects file extensions and chooses, “Export”

BevDOT creates export file.

6b. Delete Error Log

User opens log file

BevDOT displays all errors

User chooses specific or all error logs

User selects, ‘Delete’

BevDOT displays warning prompt, “Are you sure?”

User confirms ‘Delete’

BevDOT removes selected error logs

*a. Upon detection of system failure:

BevDOT halts process

BevDOT dumps temporary memory to file

BevDOT terminates process



Special Requirements: Error reports are stored until an IT user clears them from the BevDOT.


Technology and Data Variations: BevDOT retains a small portion of disk space for error logs.


Frequency of Occurrence: Daily or as needed.

5.1g (creator: David Azzolina; contributor(s): Mark Wimer )

Use Case # 7.0


Use Case Name: Generate Reports


Description: Serves as the initial reporting interface for users of the BevDOT system to generate reports based on existing BevDOT data.


^ Scope: BevDOT


Level: User Goal


Primary Actor: Sales, Marketing, Finance, Shipping, and IT departments.


Stakeholders and Interests: Sales, Marketing, Finance, Shipping, and IT departments – will use this to generate various reports for data contained within BevDOT.

Senior management - will use reports to set and track goals at the company, division, and country level to anticipate trends and changes in the market over time to improve forecasting, and to make executive decisions based on product demand.


Preconditions: User is logged into the system. Database must be established with minimum of one (1) record.


^ Success Guarantee (Postcondition): A report is generated.

Main Scenario (Basic Flow):

1. System displays operations options on the GUI interface.

2. User chooses the generate report option.

3. System displays a report type screen.

4. User selects a report type from the menu.

5. System queries BevDOT for report-specific data.

7. BevDOT returns data to System.

8. System displays data in spreadsheet format.

9. System presents options to print results or export results.


Extensions (Alternative Flows):

4a. User chooses ‘Custom Reporting’ from the menu.

System performs Generate Ad-Hoc Report.

4b. User chooses ‘Sales Forecasting Reporting’ from the menu.

System generates Sales Forecasting Report.

4c. User chooses ‘Regional Reporting’ from the menu.

System generates Regional Sales Report .

4d. User chooses ‘Seasonal Reporting’ from the menu.

System Generates Seasonal Sales Report .

4e. User chooses ‘Tracking Reporting’ from the menu.

System Generates Tracking Report .

4f. User chooses ‘System Reporting’ from the menu.

System Generates System Efficiency Report .

4g. User chooses ‘Statistical Graphics Reporting’ from the menu.

System Performs Create Graphs.

9a. Upon selection to print:

System sends the query results to the print spooler.

System sends print instructions to printer when queued job is called.

9b. Upon selection to export:

System prompts user to name file and select file type

User enters filename and selects type, selects export option

System creates new file from report data.

*a. Upon detection of system failure:

System displays error code

System Performs Error Handling

Restart step 1



Special Requirements: The Create Graphs use case will be dependent on data from other reports. It will generate graphics in accordance with other BevDOT reports, but will utilize templates specific to each report type.


Technology and Data Variations: None


Frequency of Occurrence: As needed


5.1h (creator: Bryan Defranco)

Use Case #8.0


Use Case Name: Generate Ad-Hoc Report


Description: Gives users ability to specify parameters to craft report output.


Scope: BevDOT System


Level: Sub function of Generate Reports


Primary Actor: Sales person, Marketing Agent, Accountant


Stakeholders and Interests: Senior management - will use reports to set and track goals at the company, division, and country level to anticipate trends and changes in the market over time to improve forecasting, and to make executive decisions based on product demand.


Preconditions: User is logged into the system. Reports menu is displayed.


Success Guarantee (Postcondition): Report is generated, critiqued to the users’ needs.


Main Scenario (Basic Flow):

User chooses ‘Custom Reporting’ from the menu.

System presents options for the user to choose a saved configuration, or create a new one

User chooses ‘create new’

System presents options screen for the user to select and/or key-in values

User selects desired options and enters needed criteria.

System checks for incomplete or improper specification

System generates query from the users configuration.

System queries data warehouse and displays results

System presents options to print, save, save configuration, quit



Extensions (Alternative Flows):

2a. Upon selection to use saved configuration,

System presents user with list of saved configurations

User chooses the desired configuration from the menu

Skip to step 7.



6a. Upon detection of bad specification:

System informs user of the fields improperly or not filled

System resumes at step 4



9a. Upon selection to print

System sends the query results to the print spooler.

System sends print instructions to printer when queued job is called.



9b. Upon the selection to export

System prompts user to name file and select file type

User enters filename and selects type, selects export option

System creates new file from report data.

System resumes at Step 9.



9c. Upon selection to save configuration

System prompts for input of a name for the custom configuration

User enters a name for the custom configuration

System saves the query criteria in the database

System resumes at step 9



9d. Upon selection to generate graph

System Performs Create Graph

System resumes at step 9



9d. Upon selection to quit

1. System presents the Reports menu


*a. Upon detection of system failure:

System displays error code

System Performs Error Handling



Special Requirements: None


Technology and Data Variations: None


Frequency of Occurrence: As needed


5.1i (creator: Mark Wimer and Bryan Defranco)

Use Case #9.0


Use Case Name: Create Graphs


Description: Gives users ability to create graphs in conjunction with existing BevDOT reports and previously saved ad-hoc reports. Users will use a 3rd party graphing tool to interpret BevDOT data for graph generation


^ Scope: BevDOT System


Level: Sub function of Generate Reports


Primary Actor: Sales person, Marketing Agent, Accountant, System Administrator, Database Administrator


Stakeholders and Interests: Sales, Marketing, Finance /