Transcript Data Mining and Business Intelligence ( file)

Data Mining
Douglas C. Atkins, OCP
FASTER Staff: 1998 – 2010
Customer 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
Agenda
 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
Introductions 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”!
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.
Educating 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.
Sample 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
Types of Results
 Result Sets
 Lists
 All Assets in Company 001
 Comparisons
 Parts Obsolescence
 Trends
 Asset Historical Cost Per Meter by Month
Tools 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
Database Connectivity
 Name of your Database
 FasterCS, FasterDB
 TNSName
 Windows Authentication
 User Name and Password
 DBA to Create Your Account
 Select Permission Only
 Report Account
Tools 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 …
ODBC Data Source Administrator
ODBC Data Source Administrator
ODBC Data Source Administrator
ODBC Data Source Administrator
ODBC Data Source Administrator
ODBC Data Source Administrator
ODBC Data Source Administrator
Locating the Data
 FASTER Data Dictionaries
 http://customer.ccgsystems.com/downloadcenter/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
Real-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 …
Real-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 …
Real-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
How 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), …
How 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)
How 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%’
How 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
How to Build a Query
 Order By
 Specifies the sort for the result set.
 Select … From … Where … Group By …
 Order By EHeader.EHClass, EHeader. EHYear
Our Query
 Select EH…
 From EH…
 Where faster.EHeader, …
 Group BY EHeader.EHClass …
 Order BY EHeader.EHClass …
Mining in Action
 Let’s build a query together …
 At the beginning of class …
 In the middle we …
 Now what can you do …
Question & Answer
What does your Database
need to tell you?
Are you asking the right
questions?