What is Data Mining? Can I do that with EPDM??

Happy New Year EPDM Developers!

Monologue
It is now 2013 and everyone seems to have a fresh perspective and well rested. I certainly spent ample time in the recliner dozing and eating and dozing and …I’m sure you get the pattern.

With the developments of late I am having to manage my expectations. My plans for teaching, mentoring, and consulting have slowed slightly. However I have been asked to consult a 3-site EPDM implementation on the east coast this month and I am doing engineering work with a local company which is stimulating the creative side of my noodle. 🙂

Today’s Blogging Goal
To complement my philosophy of “EPDM is more about what you can get out of it than what you can put in it.” I will share one of my core operating credos of using a stored procedure (SP) as a reporting tool to allow Managers to status their Engineering drawings, Supply Chain part status, Production work cards (job cards), etc. at a glance using data from EPDM.

NOTE: This philosophy has one assumption: The data you are wanting to mine has to be stored within EPDM already. If the data is in EPDM, you CAN report on it so PREPARE YOUR DATA!

What is a Stored Procedure?
A stored procedure (SP) is a canned query located inside the SQL server that manages EPDM. This SP runs a pre-defined query when you call it and really shows its value when you program it to receive parameters. Parameters make the SP modular for plug and play fun!!

Parameters are the input to the SP and can include (but not limited to):

• File name to search for
• File name filter (only search for *.pdf files)
• File path to search within
• Data card variable name(s) to return
• etc.

My favorite parameters are “File name filter”, “File path”, and “Data card variable name”.

For example: ALL of the data in the projects I have worked on can be narrowed by searching for only “PDF” files in a certain project folder with a data card variable “Document Type” = “Engineering”.

The parameter inputs tell the SP what you want and it serves the data back to you in the form of a data set which can be mined for those nuggets of gold you are looking for. Very powerful.

How do I use a Stored Procedure?
There are numerous ways to use an SP but the main one I use them for is data mining from within Excel. There are other more sophisticated and far more efficient tools to use for data mining but fortunately Excel is present in most offices and requires minimal training to use.

I usually deploy 3 tools when using a SP in Excel:

1. Pivot Table – Used for drilling down to the exact data you want to see
2. Tabular Data – Used for viewing a data set
3. Chart Data – Used for viewing chart data of the Tabular Data

Pivot Table Data example: Show all drawings assigned to the structural engineering department and not released to production. This data is pulled from the data set based on data card variable values.

Tabular Data example: Show all drawings in columnar format with columns for File name, Description, Revision, State, Comments, and a Hyperlink to dive right into EPDM folder where the drawing is stored. Enter the File names of the documents in the project. The Description, Revision, State, & Comments are each data card values pulled from the data set using a VLOOKUP. The Hyperlink is also pulled from the data set using a VLOOKUP to get the DocumentID and ProjectID which are concatenated together to create the Hyperlink.

Chart data example: Show me how many drawings are “Past Due”, how many drawings due this week, and the balance of drawings due after this week. This can be made from the Tabular Data using a data card field “Due Date”. My standard chart uses the old stoplight philosophy:

• Red = Due last week (past due)
• Yellow = Due this week
• Green = Due after this week

This will allow project managers to get the status of the project at a glance.

Development Assistance
If you are interested in developing this simple yet powerful functionality within your organization, post a comment below or “mine” my contact information for my number and I will contact you about consulting.

Happy New Year 2013 developers!

Tim