data mining douglas c. atkins, ocp faster staff: 1998 – 2010
TRANSCRIPT
![Page 1: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/1.jpg)
Data Mining
Douglas C. Atkins, OCP
FASTER Staff: 1998 – 2010
![Page 2: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/2.jpg)
Customer Takeaway DescriptionCustomer Takeaway Description
Best Suited for system administrators, fleet managers, fleet analysts and supervisors
Morning Session. How to ask your database the right questions / Using FASTER, Excel and other query related tools to get info out of FASTER
![Page 3: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/3.jpg)
AgendaAgenda Introductions and Credentials
Definition of Data Mining
Types of Results
Tools for Data Mining
Real-Life Examples How, What, When, Who, Why
Build data queries to take home
Questions & Answers
![Page 4: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/4.jpg)
Introductions and CredentialsIntroductions and Credentials Fleet Services Team Leader
FASTER Staff Member for 12 Years
All Script Requests / Conversions Go through FS
Oracle OCP, MSSQL DBA Training
Worked with you and your staff learning fleet during my tenure, never worked in the industry, here’s my fleet …
Goal: “Get some data out”!
![Page 5: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/5.jpg)
Data Mining: What Is It?Data Mining: What Is It?
The process of extracting patterns from data.
Knowing what to ask.
Knowing how to ask.
Knowing how to present the data.
![Page 6: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/6.jpg)
Educating Our ConsumersEducating Our Consumers There is a tendency for insufficiently
knowledgeable "consumers" of the results to attribute "magical abilities" to data mining, treating the technique as a sort of all-seeing crystal ball.
![Page 7: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/7.jpg)
Sample Set vs. Entire PopulationSample Set vs. Entire Population The discovery of a particular pattern in a
particular set of data does not necessarily mean that pattern is representative of the whole population from which that data was drawn. Hence, an important part of the process is the verification and validation of patterns on other samples of data
![Page 8: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/8.jpg)
Types of ResultsTypes of Results
Result Sets Lists
All Assets in Company 001
Comparisons Parts Obsolescence
Trends Asset Historical Cost Per Meter by Month
![Page 9: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/9.jpg)
Tools for Data MiningTools for Data Mining
FASTER
Excel
Access
Business Objects (Crystal Reports)
SQL (Structured Query Language) FSQL: FASTER TOAD, SQL Developer: Oracle or Microsoft SQL Plus: Oracle SQL Server Management Studio Express:
Microsoft
![Page 10: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/10.jpg)
Database ConnectivityDatabase Connectivity
Name of your Database FasterCS, FasterDB TNSName
Windows Authentication
User Name and Password DBA to Create Your Account Select Permission Only Report Account
![Page 11: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/11.jpg)
Tools Require ConnectivityTools Require Connectivity
FASTER OLEDB
Excel, Access, Query Developer Tools OLEDB, ODBC
TOAD, SQL Plus, SQL Server Management Studio Native Drivers Licensed Data Base Software
Let’s Create an ODBC for use in class …
![Page 12: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/12.jpg)
ODBC Data Source ODBC Data Source AdministratorAdministrator
![Page 13: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/13.jpg)
ODBC Data Source ODBC Data Source AdministratorAdministrator
![Page 14: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/14.jpg)
ODBC Data Source ODBC Data Source AdministratorAdministrator
![Page 15: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/15.jpg)
ODBC Data Source ODBC Data Source AdministratorAdministrator
![Page 16: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/16.jpg)
ODBC Data Source ODBC Data Source AdministratorAdministrator
![Page 17: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/17.jpg)
ODBC Data Source ODBC Data Source AdministratorAdministrator
![Page 18: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/18.jpg)
ODBC Data Source ODBC Data Source AdministratorAdministrator
![Page 19: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/19.jpg)
Locating the DataLocating the Data FASTER Data Dictionaries
http://customer.ccgsystems.com/download-center/manuals-documentation/ (requires login/password)
Core FASTER Application HTML View for Easy Navigation Tables
FASTER Reports Data Dictionary HTML View for Easy Navigation Views and Stored Procedures
Let’s look at the dictionaries
![Page 20: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/20.jpg)
Real-Life ExamplesReal-Life Examples Lists
FASTER Search and Query FASTER Standard Reports Custom Reports & Queries
How many assets are in the fleet?, in a Department?, in a Class?, in a Shop?, are Take Home Vehicles?, are Off-Road Vehicles?
Let’s look at some queries …
![Page 21: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/21.jpg)
Real-Life ExamplesReal-Life Examples Comparisons
FASTER Search and Query, Standard Reports, Custom Reports & Queries
Do I have enough technicians?, enough Bays?, am I stocking the right parts?, do I have adequate staffing in the parts room?
Are my vehicles being under/over utilized?, are there vehicles that should be sending up red flags (CPM/MPG)?, which ones need replacing?
Let’s look at some comparisons …
![Page 22: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/22.jpg)
Real-Life ExamplesReal-Life Examples Trends
Cost of PM, by Asset Class, by Year Let’s Build It Let’s Analyze It Let’s Talk about How It Can Help Us Make a
Decision Let’s Take It Home
![Page 23: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/23.jpg)
How to Build a QueryHow to Build a Query Select
The columns to be selected for the result set. The select list is a series of expressions separated by commas.
All, Distinct, * Specific Column Names Formulas
Select EHKey, EHCompany, …
Select Count(EHKey), …
![Page 24: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/24.jpg)
How to Build a QueryHow to Build a Query From
Specifies the tables, views, and joined tables used in DELETE, SELECT, and UPDATE statements.
Select … From faster.Eheader Select … From faster.Eheader JOIN faster.EPM on
(Eheader.EHUID = EPM.EPEHuid) Inner (only matches) Full Outer (All rows) Left Outer (All matches, includes rows from left) Right Outer (All matches, includes rows from
right)
![Page 25: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/25.jpg)
How to Build a QueryHow to Build a Query Where
Specifies the condition for the rows returned by a query. Defines the condition to be met for the rows to be returned. There is no limit to the number of conditions.
Select … From … Where EHeader.EHUID = WHeader.WHEHUID and Wheader.WHUID = WLabor.WLWHUID and WLabor.WLRTY like ‘PM%’
![Page 26: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/26.jpg)
How to Build a QueryHow to Build a Query Group By
Divides a table into groups. Groups can consist of column names or results or computed columns.
Select … From … Where … Group by EHeader.EHClass, EHeader.EHYear
![Page 27: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/27.jpg)
How to Build a QueryHow to Build a Query Order By
Specifies the sort for the result set. Select … From … Where … Group By … Order By EHeader.EHClass, EHeader. EHYear
![Page 28: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/28.jpg)
Our QueryOur Query Select EH…
From EH…
Where faster.EHeader, …
Group BY EHeader.EHClass …
Order BY EHeader.EHClass …
![Page 29: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/29.jpg)
Mining in ActionMining in Action
Let’s build a query together …
At the beginning of class …
In the middle we …
Now what can you do …
![Page 30: Data Mining Douglas C. Atkins, OCP FASTER Staff: 1998 – 2010](https://reader030.vdocuments.us/reader030/viewer/2022032707/56649e235503460f94b10cab/html5/thumbnails/30.jpg)
Question & AnswerQuestion & Answer
What does your Database need to tell you?
Are you asking the right questions?