Intelligent System Solutions Project Overview

1
Intelligent System Solutions Project Overview

• Reporting Database
• Woodstock Percussion, Inc.

2
Project Goals

• Provide a user-friendly reporting mechanism
  allowing users to create reports quickly and
  easily
• Design the database such that data in disparate
  sources could be accessed easily and made
  available for reporting purposes

3
Background

• Woodstock Percussion, Inc is a company which
  designs, builds, and sells wind chimes. They also
  have re-seller lines of percussion instruments,
  and musical instruments specifically marketed for
  children. Their headquarters are located in
  Shokan, NY not far from the town of Woodstock
  where they draw their name. The manufacturing
  facilities and business offices are located in
  the same building.
• The company uses a software product called SBT
  Pro Series to keep track of several different
  business components. The SBT is written in FoxPro
  and has modules for inventory control,
  accounting, sales order tracking, product line
  management, accounts payable etc. Woodstocks
  in-house development team has added components to
  SBT to extend its functionality. One area where
  extensive ad-hoc development has taken place is
  with user-defined reports. The SBT product ships
  with several canned reports, however, they are
  limited and not easily modified or extended. If a
  user had a business need for a new or modified
  report, they would have to submit a work order to
  the development team, and the report would have
  to be created in code.

4
Background - continued

• The turnaround time for this process was long and
  in many cases unnecessary. Essentially, the user
  could have created many of these reports
  themselves if they were provided with a
  user-friendly system. As a band-aid or
  workaround, users would often copy data manually
  into Excel and then perform calculations and
  group data to create something like a report. The
  data was not linked to SBT in any way, and it
  often took a long time to create these
  spreadsheets.
• In addition to the SBT program, there were
  additional programs that were built from scratch
  by the development team to handle things that the
  SBT program did not address. One such program was
  called MRP (Manufacturers Resource Planning)
  which helped control what products were built and
  when, and was dependent on things like inventory
  levels, sales, etc. Another program was called
  POTS (Priority Order Tracking System) that
  managed priority orders for upper tier customers
  like Lowes and World of Discovery. These programs
  could read SBT data, and they could store data of
  their own in separate tables, however they were
  not linked together in any way.

5
Phase 1 Data Gathering

• Our first step was to meet with the Woodstock
  employees. We wanted to better understand the
  overall work process flow for the company, find
  out which reports users were currently using, and
  what data elements were important to them. Over
  the course of several meetings, the key people
  from all lines of business provided sample
  reports containing the data elements that they
  were reporting on. A majority of these reports
  were the SBT reports created by the development
  team as well as some of the canned reports.
  Sample workaround Excel reports were also
  provided. A record was kept of all the reports
  presented, what line of business they were from,
  and the specific data elements on the reports. In
  addition, a record of what application or data
  store the data elements were coming from was
  kept. Meaning, is this an SBT data element, MRP,
  POTS, excel spreadsheet, etc. This process helped
  identify which data elements were to be created
  in the new reporting database.

6
Phase II Database Design

• At this point, all the data elements had been
  gathered. It was then determined which SBT tables
  the data elements were coming from. Data elements
  NOT found in the user reports that qualified as
  good candidates to include were also identified.
  These elements would eventually populate the
  tables in the reporting database. The new
  database was designed such that linking of
  relevant tables could take place, and data
  elements from SBT tables were combined into
  merged reporting database tables. This would help
  by reducing the number of total tables and links,
  making it easier for users to report.
  Furthermore, historical data was incorporated
  within with the tables making it even easier for
  users to access relevant data. Finally, the data
  was given meaningful, English language table and
  field names. Sales order data from the SOMAST
  table could be accessed in the newly designed
  Sales Order table.

7
Phase III Data Importing

• Once the database was designed, a system that
  would effectively shadow the data from the
  disparate data stores, and coalesce it into one
  coherent database was built. The reporting
  database itself was built on Microsoft SQL Server
  2000, and was installed by the Intelligent System
  Solutions network services team. Because the data
  to be imported was residing on a different
  server, a remote link to the data was
  implemented. A series of stored procedures were
  implemented that would utilize the remote link to
  import/insert the data into the reporting
  database on a regularly scheduled basis (this
  usually occurred once a day but could be done
  more frequently to update critical data). SQL
  Agent was employed to control the import job
  scheduling. In addition, a Visual Basic
  application was built that would allow an
  administrator to manually import data from
  specific tables at any time and to track system
  responses.

8
Phase IV Installation/Training

• The users interface to the reporting database
  through Microsoft Access. Access was chosen for
  its ease of use and its reporting capabilities.
  The ISS network services team installed Access on
  the users machine and created the links that
  tied to the SQL Server reporting database.
• The final phase involved training the users on
  the new reporting system. ISS created a custom
  manual with business examples and lesson plans
  geared specifically to Woodstock Percussion. The
  training took place at Woodstock and typically
  had a classroom size of 3-4 people from varying
  lines of business. The primary goals were to
  create a familiarity with the Access application
  and the capabilities of the new reporting
  database. Empowering the users to create their
  own reports was the training philosophy. The
  users were shown how to capture the specific data
  they were looking for. The user-friendly features
  of Access allowed users to could create complex
  queries and calculations. These queries have the
  capability to capture the data they desire, make
  calculations, group, sort, etc.

9
Phase IV - continued

• In addition the users were shown how to create
  and design reports that display the information
  in a meaningful, aesthetic format. After looking
  at green bar reports for years, the users were
  truly impressed with the creative features
  available to them. Perhaps one of the most
  impressive features (to the users) was the
  ability to link tables together, such that sales
  order information could be tied to inventory
  levels, or accounting, or salesperson quotas.
  This could be accomplished before, but it was the
  domain of the Woodstock development team, and it
  would typically take them days or weeks to create
  the reports. The users could now create these
  reports instantly! Furthermore, this new design
  fosters and encourages an atmosphere of free and
  creative business analysis. Because they can
  actually see the data and relate to it in a
  meaningful fashion, users could now imagine and
  create new reports to help drive the business.

10
For More Information

• Intelligent System Solutions
• 116 Everett Road Suite 1C
• Albany, New York 12205
• 518-435-1133
• WWW.INTELLSYSTEMS.COM