IS1024Project Team 7David AzzolinaPhilip BoggsBrian DefrancoBryce FosterMike SmolenskiMark WimerThe Beverage Company Incorporated Data Warehouse SystemBEVDOT Database Warehouse System14th, April 2009Problem/Opportunity Statement #4Your 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. SmolenskiB. FosterP. Boggs Initial interviewsUse-case realizationInitial 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. SmolenskiP. BoggsB. Foster Add Workshop informationFirst draft of Actors and use casesAdditional features addedDevelop 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. SmolenskiB. FosterM. Wimer Rework of actors and use casesDevelop test-cases for use-casesCompleting Use Cases and Supp Specs 2.3 4/11/2009 M. Wimer Finalizing Use Cases 2.4 4/13/2009 P. BoggsM. Wimer Rework of use cases and prioritizationCombined 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 ChartComments: 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 RequestThe 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 WidrigThe 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 ShippingThe 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 dataError handlingUser-Friendly GUIQuestionnaire 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 EfficiencyUser-friendly GUIDatabase WarehousePeriodic Auto Uploads From Legacy System (ETL) Extract Data Auto Validation Transform Data Load DataManual Validation of Data from Legacy SystemError HandlingGraphical representations of ReportsSection 2.3 Finding Actors and Use casesThere are four main actors for BevDOTThe 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 processThe 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 TrackingSystem 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 processGenerate 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 SystemETL Process Extract Validate Transform Load DataUse Case Diagram – First IterationUse Case Diagram – 2nd Iteration^ List of use cases:UC #1 – Extract DataUC #2 – Validate DataUC #3 – Manual Validate Data UC #4 – Transform DataUC #5 – Load DataUC #6 – Error HandlingUC #7 – Generate ReportsUC #8 – Ad Hoc ReportUC #9 – Create GraphsUse Case #1.0Use Case Name: Extract DataDescription: Initiates the ETL process for transferring data from the operational format legacy system to the informational format BevDOT data warehouse.^ Scope: BevDOT SystemLevel: Batch ProcessPrimary Actor: Legacy SystemStakeholders and Interests: IT Department, DBAPreconditions: 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 Data1Main Scenario (Basic Flow): BevDOT system connects to Legacy System using ODBC over TCP/IP Legacy System authenticates BevDOT system3. BevDOT system generates SQL statements based on loaded rules, and the date the last extraction was performed.4. BevDOT System submits query to Legacy System5. Legacy system begins return of requested data6. BevDOT System performs ValidateData17. BevDOT System checks if more data is available from Legacy system8. BevDOT System disconnects from Legacy systemTechnology 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.0Use Case Name: Validate DataDescription: Ensures data integrity. Routes inconsistent data to be held in temporary tables for Manual ValidationScope: BevDOT SystemLevel: Sub FunctionPrimary Actor: Legacy SystemStakeholders and Interests: IT Department, DBAPreconditions: 1. BevDOT System is authenticated with Legacy System2. 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 DataMain Scenario (Basic Flow): BevDOT System applies rules from external configuration. BevDOT System performs Transform DataExtensions: 1a. Upon failure of validation algorithm BevDOT System saves the questionable data in temporary storage for manual review*a. Upon detection of system failure1. 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.0Use Case Name: Manual Validate DataDescription: DBA Corrects data anomalies received from Legacy DatabaseScope: BevDOT SystemLevel: User GoalPrimary Actor: DBAStakeholders and Interests: IT Department, DBAPreconditions: User is logged on to BevDBW system, and has entered Manual Validation screen Validation rules are loaded from external fileSuccess Guarantee (Postcondition): Data is successfully corrected and passed to Transform DataMain 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 DataExtensions: 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 failure1. BevDOT System performs Error HandlingTechnology and Data Variations: NoneSpecial 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.0Use Case Name: Transform DataDescription: Data is cleansed and transformed from an operational format, into an informational format consistent with BevDOT data warehouseScope: BevDOT SystemLevel: Sub FunctionPrimary Actor: Legacy SystemStakeholders and Interests: IT Department, DBAPreconditions: 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 DataMain Scenario (Basic Flow): BevDOT System applies transformation algorithm to data. BevDOT System performs Load DataExtensions: *a. Upon detection of system failure1. 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.0Use Case Name: Load DataDescription: Data is loaded into tables in the BevDOT data warehouse in a manner consistent with existing information data formatScope: BevDOT SystemLevel: Sub FunctionPrimary Actor: Legacy SystemStakeholders and Interests: IT Department, DBAPreconditions:1. BevDOT System is authenticated with Legacy System2. 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 tablesMain 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 changesTechnology 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.0Use Case Name: Error HandlingDescription: Error Handling use case will perform the appropriate actions if an abnormal error occurs within the system’s scope of performance.Scope: BevDOTLevel: System GoalPrimary Actor: Database Administrator, System AdministratorStakeholders and Interests: Senior Management, IT Department Preconditions: BevDOT is performing a system function System error occursSuccess 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 fileThe use case on this page was created by Mark Wimer (creator: Mark Wimer and Philip Boggs)Use Case # 7.0Use Case Name: Generate ReportsDescription: Serves as the initial reporting interface for users of the BevDOT system to generate reports based on existing BevDOT data.^ Scope: BevDOT Level: User GoalPrimary 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.0Use Case Name: Generate Ad-Hoc ReportDescription: Gives users ability to specify parameters to craft report output.Scope: BevDOT SystemLevel: Sub function of Generate ReportsPrimary Actor: Sales person, Marketing Agent, AccountantStakeholders 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, quitExtensions (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 49a. Upon selection to print System sends the report to the print spooler. System presents Reports menu9b. Upon the selection to save System writes the query results to an Excel compatible file System resumes at step 99c. 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 99d. Upon selection to generate graph System Performs Generate Graph System resumes at step 99e. 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 1Special Requirements: NoneTechnology and Data Variations: NoneFrequency of Occurrence: As needed(creator: Mark Wimer and Bryan Defranco)Use Case #9.0Use Case Name: Create GraphsDescription: 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 SystemLevel: Sub function of Generate ReportsPrimary Actor: Sales person, Marketing Agent, Accountant, System Administrator, Database AdministratorStakeholders and Interests: Sales, Marketing, Finance / Accounting, System Administrators, Database Administrators – will all have access to create graphsPreconditions: 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]Functionality2.1 System Error HandlingAll 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. Supportability6.1 Software Updates and MaintenanceTo 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 Constraints7.1 Legacy SystemThe 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 requirementsThe 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 ManualThe 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 HelpThe 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 MaintenancePeriodical 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 StandardsThe 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 RequirementsThe 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 InstallationAn 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 Variations13.1.1 Data ChunkA 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 RulesData 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.BevDOTLegacy SystemValidateTransform1. 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 CasesFeatures 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 GraphsSection 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 CaseAutomatic ValidationManual ValidationError HandlingGenerate ReportsGenerate 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.0Use Case Name: Extract DataDescription: Initiates the ETL process for transferring data from the operational format legacy system to the informational format BevDOT data warehouse.^ Scope: BevDOT SystemLevel: Batch ProcessPrimary Actor: Legacy SystemStakeholders and Interests: IT Department, DBAPreconditions: 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 Data1Main Scenario (Basic Flow): BevDOT system connects to Legacy System using ODBC over TCP/IP Legacy System authenticates BevDOT system3. BevDOT system generates SQL statements based on loaded rules, and the date the last extraction was performed.4. BevDOT System submits query to Legacy System5. Legacy system begins return of requested data6. BevDOT System performs ValidateData17. BevDOT System checks if more data is available from Legacy system8. BevDOT System disconnects from Legacy systemExtensions: 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 HandlingSpecial Requirements: NoneTechnology 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: Weekly5.1b (creator: Bryan Defranco)Use Case #2.0Use Case Name: Validate DataDescription: Ensures data integrity. Routes bad data to be held in temporary tables for Manual ValidationScope: BevDOT SystemLevel: Sub FunctionPrimary Actor: Legacy SystemStakeholders and Interests: IT Department, DBAPreconditions: 1. BevDOT System is authenticated with Legacy System2. 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 DataMain Scenario (Basic Flow): BevDOT System applies rules from external configuration. BevDOT System performs Transform DataExtensions: 1a. Upon failure of validation algorithm BevDOT System saves the questionable data in temporary storage for manual review*a. Upon detection of system failure1. 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.0Use Case Name: Manual Validate DataDescription: DBA Corrects data anomalies received from Legacy DatabaseScope: BevDOT SystemLevel: User GoalPrimary Actor: DBAStakeholders and Interests: IT Department, DBAPreconditions: User is logged on to BevDBW system, and has entered Manual Validation screenValidation rules are loaded from external fileSuccess Guarantee (Postcondition): Data is successfully corrected and passed to Transform DataMain Scenario (Basic Flow): BevDOT System displays questionable data from temporary storageUser corrects data abnormalitiesBevDOT System validates data using validation rulesBevDOT System performs Transform DataExtensions: 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 failure1. BevDOT System performs Error HandlingTechnology and Data Variations: NoneSpecial 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.0Use Case Name: Transform DataDescription: Data is cleansed and transformed from an operational format, into an informational format consistent with BevDOT data warehouseScope: BevDOT SystemLevel: Sub FunctionPrimary Actor: Legacy SystemStakeholders and Interests: IT Department, DBAPreconditions: 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 DataMain Scenario (Basic Flow): BevDOT System applies transformation algorithm to data.BevDOT System performs Load DataExtensions: *a. Upon detection of system failure:System displays error code System Performs Error Handling Return error code to Validate Data Special Requirements: NoneTechnology 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 process5.1e (creator: Bryan Defranco)Use Case #5.0Use Case Name: Load DataDescription: Data is loaded into tables in the BevDOT data warehouse in a manner consistent with existing information data formatScope: BevDOT SystemLevel: Sub FunctionPrimary Actor: Legacy SystemStakeholders and Interests: IT Department, DBAPreconditions:1. BevDOT System is authenticated with Legacy System2. 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 tablesMain 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 changesExtensions: *a. Upon detection of system failure: System displays error code System Performs Error HandlingSpecial Requirements: NoneTechnology 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 process5.1f (creator: Mark Wimer)Use Case #6.0Use Case Name: Error HandlingDescription: Error Handling use case will perform the appropriate actions if an abnormal error occurs within the system’s scope of performance.Scope: BevDOTLevel: System GoalPrimary Actor: Database Administrator, System AdministratorStakeholders and Interests: Senior Management, IT Department Preconditions: BevDOT is performing a system function System error occursSuccess 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 fileExtensions (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 processSpecial 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.0Use Case Name: Generate ReportsDescription: Serves as the initial reporting interface for users of the BevDOT system to generate reports based on existing BevDOT data.^ Scope: BevDOT Level: User GoalPrimary 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 1Special 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: NoneFrequency of Occurrence: As needed5.1h (creator: Bryan Defranco)Use Case #8.0Use Case Name: Generate Ad-Hoc ReportDescription: Gives users ability to specify parameters to craft report output.Scope: BevDOT SystemLevel: Sub function of Generate ReportsPrimary Actor: Sales person, Marketing Agent, AccountantStakeholders 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, quitExtensions (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 49a. 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 99d. Upon selection to generate graph System Performs Create Graph System resumes at step 99d. Upon selection to quit 1. System presents the Reports menu*a. Upon detection of system failure: System displays error code System Performs Error HandlingSpecial Requirements: NoneTechnology and Data Variations: NoneFrequency of Occurrence: As needed5.1i (creator: Mark Wimer and Bryan Defranco)Use Case #9.0Use Case Name: Create GraphsDescription: 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 SystemLevel: Sub function of Generate ReportsPrimary Actor: Sales person, Marketing Agent, Accountant, System Administrator, Database AdministratorStakeholders and Interests: Sales, Marketing, Finance /