The MATRIX - Database of Databases

AutoSoft Systems

2 Round Hill Court

East Greenwich, RI 02818

401.885.3631

401.884.5653 Fax

401.996.3631 Cell

AMDG

Decision Ready Information!

autosoft@aol.com

Commercial & custom multi-user computer software for a variety of applications including performance metrics, statistical analysis, data extraction and merger from multiple large databases, computer simulation and management information systems. Founded in 1982.

We know Excel! Whenever possible, Excel is the user interface, VBA is the programming language, ODBC connects to the database of your choice.

Pharmaceutical & Biotech

Construction Management

AMDG

Database of Databases

Maximo® is a trademark of IBM

Trackwise® is a trademark of Sparta Systems

AutoSoft is not affiliated with neither Sparta Systems nor IBM.

Large corporations store terabytes of data in a myriad of disconnected databases (ERP, Maintenance Management, Quality, Lab Information, Documents, Drawings, Change Control, ….). In the search for information, employees are forced to search each database, one at a time, using a different search tool on each database. Often, these different search tools have vastly different user interfaces, requiring the user to learn many different paradigms in order to complete a comprehensive search across all of these different databases.

These "web-based" search tools prove to be quite clumsy. Because of the very nature of web-based applications, users are forced to wade through several web pages, alternatng between the mouse and the keyboard, in order to enter in key words and date ranges in an attempt to find the right information.

When they finally get the search properly defined, they then have to wait minutes for the web-based search engine to retrieve the information. It is then displayed in the web browser. In order to get the information in the form that can be used, an additional step has to be taken to export it typically into a CSV or tab-delimited text file, which when opened in Excel, requires formatting (font, column width, word wrap, autofilter, ....) before it is useful.

The MATRIX makes this entire painful process disappear. The MATRIX has an Excel front end, running on the user's own computer, The MATRIX searches all of the Company's databases with one click! Enter a key word (or two or three), wait a few seconds, and the MATRIX prepares an Excel workbook. Each worksheet in the workbook contains formatted and autofiltered results of the search for one of the selected databases. Additionally, hyperlinks on each record can bring the user right to the record in the source database.

Early implementors of the MATRIX when surveyed reported average savings of 1.5 hours per week per person in the quest for information. One department at a multi-billion dollar biotech firm calculates savings of $800,000/year, with the savings corporate wide well in excess of $10,000,000.

I know this sounds too good to be true, but it is worth a few minutes of your time to see a demo of the MATRIX in action. You can judge for yourself. Contact us to arrange a WebEx demo.

The MATRIX Survey results

After serveral months of implementation, Dr. Serdakowski took advantage of the extensive transaction logs of the MATRIX and sent a quick survey to all MATRIX users. The results are quite impressive:

Survey results indicate that the average user is saving about 1.5 hours per week looking searching for information in their multiple corporate databases. A huge savings when implemented corporate-wide!

Given the other search and reporting options available at this corporation, the MATRIX has proven to be far and away the best option. (Hmmm, I wonder what I can do to reach the other 3%?????)

The Cost Savings are really HUGE!!!

Let us take some really conservative estimates based on the results of this survey:

•

The typical MATRIX user will save 1 hour a week, or 48 hours a year. (Our survey indicated a 1.5 hour per week average savings)

•

The typical MATRIX user salary and benefits are worth $75,000 per year.

•

The typical MATRIX user works 2,000 hours per year.

So the time savings for a single MATRIX user is:

100 users = $180,000 annual savings

1,000 users = $1,800,000 annual savings

The MATRIX Main Screen

This screen shot is configured for a large Biotech firm. Note how the standard Excel ribbon at the top of the page has been replaced with a custom ribbon containing MATRIX commands and navigation buttons. The yellow cell is where the user places the ke words to be searched. Multiple key words are separated by a semi-colon [;]. Wild cards are represented by a percent [%] sign. The user can choose to search "All Sites" of this multi-national corporation or can limit the search by site. Listed in the first column are all of the databases that the MATRIX can scan. The databases that are gray are those that are not accessable to this user. The second column tells the user how "fresh" the data is. The databases that require updating are colorized. If green, the data is current. As the database ages, the color changes from green to yellow to red. The third column is controlled by the user. A simple click toggles between "Yes" and "No" to select the databases for the current search. If the user is uncertain as to the number of records to be found, she\he can first execute a "COUNT" before executing "EXTRACT". In this example, the user is looking for information on diaphragm values, so the key word "diaphragm" is searched.

The third column is controlled by the user. A simple click toggles between "Yes" and "No" to select the databases for the current search. For this search, the user will look through all documents in the corporate documents database (a total of If the user is uncertain as to the number of records to be found, she\he can first execute a "COUNT" before executing "EXTRACT". In this example, the user is looking for information on diaphragm values, so the key word "diaphragm" is searched.

The user has chosen to search the "Documents", "Drawings", "Assets", "Job Plans", "Work Orders for 2013", "NCs (non conformances) from 2009 forward", "CAPAs (corrective - preventive actions)", "CCMS (change control)" and the "Knowledge Marketplace" databases for the key word "diaphragm". The COUNT took 15 seconds, returning the number of records found in each database. For example, of the 590,356 documents in the documents database, there are 861 with the word "diaphragm" in the title. Similar results are returned for the other databases as well.

Now that the user knows that the query will return a reasonable number of records, the user presses the "EXTRACT" command. In only 30 seconds, the MATRIX has built a stand alone Excel workbook with 8 different worksheets, one worksheet for each search. AMAZING!

Here is the worksheet generated from the "Knowledge Marketplace database:

Note how the 5 records match the preliminary COUNT above, and how the other searches are different worksheets in this workbook. The blue Hyperlinks will connect back to the source database with just a click.

The first few records of the Job Plans Report has the same look and feel, with Hyperlinks into the source database, in this case Maximo. Once logged into Maximo, the user is brought right to the individual Job Plan (or Asset, or Work Order, or Location, ....)

And so does the Drawings Report, with hyperlinks right back to the drawings themselves!

For a "Deeper Dive", one can click on the icons at the top of the Main page. Let us go to the CCMS screen and conduct a detailed search. We are looking for "Clinical" change controls created in 2012 that have "Pending Tier 1 Assessments", that are "Computer Related" and are "Level 1". We set the advanced filters accordingly. We want to return a number of optional fields, which we have checked. We then click "COUNT", and in less than 3 seconds, we know we have a total of 15 reocrds of information, all pertaining to the same Change Control.

We click the "EXTRACT" button, and in 5 seconds we have a one worksheet stand alone workbook with the 15 records, AND a hyperlink back to the source. I am not joking, this really works this fast!

The Maximo® Work Order search screen offers a number of fields upon which to search:

As does the Maximo Assets® screen:

And the Maximo Job Plans screen:

The Trackwise Non Conformance screen (so many fields I had to split the display below):

And the Trackwise CAPA screen:

The MATRIX can serve up data from any corporate database with speed and precision.

AutoSoft Systems | 2 Round Hill Court, East Greenwich, Rhode Island, USA 02818 | 401.885.3631 | Fax: 401.884.5653 | Mobile: 401.996.3631

This web page was last updated at 03/10/2014 11:22 AM and is written in EXCEL!

AXIOM is a Trademark of Consona - USYS is a Trademark of Zumbach - AutoSoft Systems is not affiliated with Consona nor Zumbach