Mark Penny, B.S., Siemens Medical Group
Selecting the Information Solution. Initially, Siemens Medical Group (SMG) created sales datamarts using UDMS to extract data from MANMAN/OMAR databases. The data extracts proved to be extremely unreliable. Data was extracted each night, took many hours to complete, and special care was needed to ensure that no new transactions were entered while the extraction took place. At times, we had to restore databases and re-run extracts-time was lost and the users were frustrated.
Implementing the Data Warehouse. We selected the eNVy Systems data warehouse solution which was implemented on a two-processor DELL Pentium II with 512 MB of memory, and was deployed on SQL Server 6.5. ENVy provided a turnkey implementation, trained end-users and the MIS staff on the application software, and tutored us on "SQL for Beginners."
Initially, the reporting tool was Microsoft ACCESS. Although MS-Access was extremely easy to connect to the database and generate almost any report, MS Access was limited when creating reports using complex table joins (performing a query against multiple tables connected by common fields). We converted these applications to Visual Basic (VB) and found it, too, was limited. VB programs had to be installed on each PC; version control was an issue, as was the location of source code, etc. We reviewed the technologies that were available and settled on Microsoft Information Server (IIS) and Active Server Pages (ASP).
Using IIS and ASP, we found we could generate complex reports easily, and users could execute them using Internet Explorer and MS Excel. Beyond configuring the browser security and configuring the proxy information and login name on the SQL Server, there was nothing else to do. We found that users who would otherwise have gone to MANMAN to execute a list command were checking data on the data warehouse. We were eventually relying on the data warehouse quite heavily to run the business. The next step was going to be data validation: Is the data correct? Does it match the MANMAN lists and reports?
Quality Control and the Data Warehouse. SMG (and the medical equipment industry) is regulated by the Food and Drug Administration (FDA). Our internal policies require regular audits of the MANMAN and eNVy warehouse data. As part of the eNVy Warehouse Suite, an application is executed each evening to report MANMAN database record and SQL table
counts. The program calculates a checksum (SUM) and counts the number of occurrences of the record in the MANMAN database. The same procedure is executed against the SQL warehouse. The resulting values from these databases are compared. The data warehouse is in-sync with the MANMAN database if the SUM and COUNT of a given numeric field of a given table/record is an exact match in the SQL Server database and the MANMAN database. The audit program executes as a nightly exclusive job when no other jobs are executing, ensuring a valid count/checksum of the MANMAN data. We also routinely verify the warehouse data using MANMAN list commands. The eNVy warehouse data consistently remains synchronized with the MANMAN database.
Administering the Data Warehouse. Administration of the data warehouse for 8 databases (over 25 million records) is automatic. For each database, a VAX batch job is executed to check the database After Image Journal file for transactions. If transactions are found, they are applied to the data warehouse on the VAX. This process is extremely fast, lasting only a few minutes. Early in the morning, these AIJ transaction records are moved from the warehouse on the VAX to the SQL Server database using the eNVy Data Replicator, a Windows NT program. This process also takes only a few minutes. To ensure the database is performing optimally, indexes are created for link-fields on all tables. Warehouse tables contain foreign key (FK) fields from parent records, e.g. CSTREC is the child record of PRTREC (check the database guide). The PRTNO field exists on the CSTREC record even though the PRTNO does not exist in the MANMAN database record. These FKs permit the link/join of related tables in a query. Using the above example, the user can easily join the PRTNO from the PRTREC table and PRTNO from the CSTREC table to report a part's cost information. An index is added for any field for which users have special query selection requirements, e.g. fiscal period, entry date or miscellaneous field.
Where are we now? The eNVy warehouse provides us static relational data that can be reported throughout the day. At times, however, certain applications require real-time access to the MANMAN data. We acquired a direct access ODBC gateway to enable direct access to the MANMAN database. We have re-written critical data warehouse queries "OMAR Sales Backlog" using ODBC gateways, and found their performance to be about the same as we had experienced with the UDMS report writer. When comparing to the eNVy Systems Data Warehouse, the ODBC gateway takes over 4-5 hours to complete versus the warehouse completion of 5-7 minutes. Our experience has taught us to use ODBC gateways only as a last resort. They performed acceptably when accessing only certain data elements in MANMAN (selection by MANMAN key - PRTNO, MA_SOHNO). But in our environment, due to performance and lengthy delays, few use the ODBC gateway to access MANMAN. Most data retrieval is either from the eNVy warehouse or by using the appropriate MANMAN command. Our experience indicated that ODBC gateways should not be the primary end-user reporting tool.
The SMG environment is extremely complex with international user communities. Users throughout the world now depend on the IIS ASP data warehouse system--even those users who do not speak MANMAN. The data warehouse and ASP have worked together to report statistics on critical manufacturing processes. The latest project we are currently working on is the development of the Manufacturing Extranet that will be available to our suppliers over the public Internet network. The eNVy Systems Warehouse Suite has made it easier for us to bring MANMAN to the desktop for our users worldwide. Each day we find new opportunities with the eNVy warehouse.