Back to Basics - PowerPoint Presentation

Slide1

Back to Basics (keeping it as simple as possible)

2015 WinGAP Conference

Kenny Colson – jkcolson@wingap.net

Gregg Reese– rgreese@wingap.netSlide2

TopicsData mining – What is it?Tools that are needed

Caution

Databases, Data Tables and Fields

Common Data Requests/Needs

What to do with Results

NADA/ABOS/DNRSlide3

Data MiningData Mining is an analytic process designed to explore

data

(usually large amounts of

data

- typically business or market related - also known as "big

data

") in search of consistent patterns and/or systematic relationships between

variables

In other words, finding the stuff you want to seeSlide4

Data Mining ToolsSSMS

SQL Server Management Studio

Software external to WinGAP

Installed only on computers specified by Chief Appraiser and/or IT

SQL Master

WinGAP menu driven application

Tools >> SQL Database Utility

Limited access via Password Admin in WinGAP

Must be setup on computers that will be using it

ExcelSlide5

Data Mining ToolsWhich should I use?SSMS

Little more robust than SQL Master

Very easy to copy/paste data into Excel spreadsheet

SQL Master

Handy with it being on WinGAP menu

Contains a few functions not found in SSMS

Directly works with DBF data

Facility to copy an existing DB to a “test” DB

Writes data to CSV files that can be opened with ExcelSlide6

Data Mining ToolsWhich should I use?Excel

Process of connecting to live data is complicated and hazardous

Best used with data that has been extracted with SSMS or SQL MasterSlide7

Data Mining – Caution?Remember the old days when you heard “Let’s run Fox!”

Same cautionary procedures apply!!!Slide8

Data Mining – Caution?Plan your actionsRemove as many distractions as possibleSlide9

Data Mining – Caution?Be careful with mouse clicks or menu selections

Some can remove all your dataSlide10

Working with DataBefore using data altering statements such as

Update

Replace

Delete

BACKUP!!!

We will not be talking about those command…Slide11

Databases – Tables - Columns

AY2016

(

Database)

Owner (Table)

Realprop

(Table)

Personal (Table)

LastName

(Column)

Parcel_No

(Column)

Invn_Val

(Column)Slide12

Databases – Tables - ColumnsSlide13

Databases – Tables - ColumnsDatabases containTables

Stored Procedures

Other SQL stuff

Examples of Databases

AY2015

AY2016Slide14

Databases – Tables - ColumnsTables are found within a database and contain columnsExamples of Tables

Owner

Realprop

Personal

MobileSlide15

Databases – Tables - ColumnsColumns are within tables and contain data in rowsExamples of columns in the table Owner

LastName

Address1

Examples of columns in the table

Realprop

Parcel_No

TotalAcres

Examples of columns in the table Personal

Perskey

Invn_ValSlide16

DataData is what we are after!

Where does it live?

Within rows under Columns found in Tables

How do we know which Table or Column to use?

WinGAP Technical Manual

Use SSMS/SQL Master tree view

Ask

Email

Text

ExperienceSlide17

Data Example from OwnerSlide18

Data TypesChar (N)Character data

Letters

Numbers

Symbols

N is width of the field

Must be delimited with single quote

LastName

= ‘smith john’

Case does not matterSlide19

Data TypesInt,

T

inyint

,

Bigint

, Numeric(N,D)

All are numbers

Size of largest value determines which one is used

All are treated the same when evaluating data

(N,D) – width of field, number of decimal places

Decimal point counts as 1 position

No delimiters are needed

Totalacres

= 100Slide20

Data TypesBitUsed for logical fields

Notice flags

Audits

Pricing method,

etc

Values are

0

= false

1 = true

No delimiters are needed

Guide = 1Slide21

Data TypesDateEvaluated in the same manner as we write dates

Must be delimited with single quotes

Example

Datenow

= ‘06/24/2014’Slide22

Data Types – How do I know?Use the tree view in SSMSExpand the Database

click the + to the left of the DB name

Expand the Table

Click the + to the left of the Table

Expand Columns

Click the + to the left of Columns

Data Type information for each column is available

To close, click the minus sign to the left of each itemSlide23

Review What best describes Data Mining?

Extracting ore from an open pit quarry

Panning for gold in Dahlonega

Getting the information out of WinGAP tablesSlide24

Review What are some Data Mining tools?

Caterpillar skid steer

SSMS & SQL Master

John Deere backhoeSlide25

Review What is the proper hierarchical listing for SQL data?

Tables – Columns - Database

Database – Tables - Columns

Columns – Database - TablesSlide26

Review In WinGAP terms, what is the definition of a Database?

Most of the data & procedures for an Appraisal Year

No clue

The data for commercial improvementsSlide27

Review How do I know which Table to use?

Depends on where my fav food is placed

WinGAP Tech manual

SWAGSlide28

Review What is the technical term for the item that holds the heated area of a house?

Column

Table

DatabaseSlide29

Review In SQL, where you do find all the info for a particular mfg

home?

PRC in filing cabinet

On your co-worker’s desk

In a row within the Mobile tableSlide30

Review Which of these is NOT

a SQL data type?

Column

Int

CharSlide31

Review Which of these data types must be delimited with single quotes in a SQL statement?

Int

Bit

CharSlide32

Review Which of the data types below are numeric?

Int

Char

TinyIntSlide33

Review What Data type would be used to identify when a parcel was added to WinGAP?

Int

Bit

DateSlide34

Review What should you do before using SQL commands like Update, Replace or Delete?

Say a little prayer

Never use them

Backup DatabaseSlide35

Getting your hands dirty!!Slide36

Creating SQL StatementsRun SSMSSlide37

Creating SQL StatementsExpand Databases (click on + to left of Databases)Slide38

Creating SQL StatementsRight click on Database you want to work withSelect New QuerySlide39

Creating SQL StatementsNow you have a query window (on the right) where commands can be typedSlide40

Creating SQL StatementsMany types of SQL commandsEverything we are going to do begins with

SelectSlide41

Select StatementFollows the following formatSelect

always comes first

Second section of statement contains what you wish to see (column list)

Items are separated by commas

Can be calculated or data combined columns

*

can be substituted for column list

Displays all columns

Third section is where to pull data from (the table)

Table name is always preceded by “from”Slide42

Select StatementVery basic exampleSelect

lastname

from owner

Displays all the

lastname

data in owner after

Execute

is clicked or F5 is pressedSlide43

Select StatementFollows the following formatSelect

always comes first

Second section of statement contains what you wish to see (column list)

Items are separated by commas

Can be calculated or data combined columns

Third section is where to pull data from (the table)

Table name is always preceded by “from”

Can contain multiple tables using a “join” clauseSlide44

Select Statement (optional sections)Filter section

Always follows the “table” section

Begins with “where”

Typically contains

Field name or calculated fields

Relational operator

=, >, <, <>

Comparative value to the right of relational operator

Multiple filters can be presentSlide45

Select Statement (optional sections)Filter section examples

Where

Totalacres

>= 500

Where guide = 0

Where

lastname

= ‘smith tom’ and state = ‘

ga

’Slide46

Select Statement (optional sections)Order By section (sorting the list)

Always follows the “filter” section

Begins with “order by”

Typically contains

Field name or calculated fields

Multiple orders can be imposedSlide47

Select Statement (optional sections)Order By section examples

Order by

lastname

Order by

parcel_no

Order by

taxdistric

,

perskeySlide48

Select Statement ConstructSelect

<column list>

from

<table name>

[where <filters>]

[order by <column list>]

[ ] indicates optional sectionSlide49

Select StatementSelect statement comparison (column list VS *)Slide50

Select StatementTwo tabs are presentResults – displays columns

Message – displays # of rowsSlide51

Select Statement – What if I do something wrong?

Is itSlide52

Select Statement – What if I do something wrong?

SQL will tell you

What is wrong with this statement

select from ownerSlide53

Select Statement – What if I do something wrong?

select

from

owner

Here is what SQL says

Red squiggly line indicates general area of errorSlide54

Select StatementsDisplay owner name and city for Smith’s that live in Portal

Select * from owner

Select

lastname

from owner where

lastname

= ‘smith’ and city = ‘

statesboro

’

Select

lastname

from owner where

lastname

like

‘

smith%’

and city = ‘

norwood

’Slide55

Select StatementsDisplay owner name and city for Smith’s that live in PortalSlide56

Select StatementsDisplay cost items that need revaluing ($1.00 value) and order by acct #

Select * from personal where

meff_val

= 1

Select

perskey

,

costkey

from cost where

valmethod

= ‘m’ and

marketval

= 1 order by

perskey

Select * from cost order by

perskeySlide57

Select StatementsDisplay cost items that need revaluing ($1.00 value) and order by acct #Slide58

Select StatementsDisplay a list of Sales Reasons

Select * from

saleinfo

Select * from reason

Select * from reason where

reasontype

= ‘s’Slide59

Select StatementsDisplay a list of Sales ReasonsSlide60

Select StatementsDisplay a list of Sales ReasonsSlide61

Select StatementsDisplay parcel #, acres for parcels over 20.00 acres

Select

parcel_no

,

totalacres

from

realprop

where

totalacres

> 20

Select

parcel_no

, acres from

landsubs

where acres > 20

Select * from

realprop

where

totalacres

> 20Slide62

Select StatementsDisplay parcel #, acres for parcels over 20.00 acresSlide63

Select StatementsHow many personal property accounts do I have with a value over 1 million dollars?

Select * from personal where

curr_val

> 1,000,000

Select

curr_val

from personal where

curr_val

>= 1000000

Select count(*) from personal where

curr_val

> 1000000Slide64

Select StatementsHow many personal property accounts do I have with a value over 1 million dollars? Slide65

Let’s kick it up a notch!!!Slide66

Select StatementsList parcel #, improvement #,

pct

complete for all res imps less than 100% complete and order by parcel #

Select

parcel_no,impkey,pctcomp

from

reprop

where

pctcomp

<100

Select

r.parcel_no

,

ri.repropkey

, ri.pcom from

reprop

ri

inner join

realprop

r on

ri.realkey

=

r.realkey

where

ri.pcom

< 1 and

ri.occupancy

<>4 order by r.parcel_noSelect

r.parcel_no, ri.repropkey, ri.pcom from reprop ri inner join realprop r on

ri.realkey = r.realkey where ri.pcom < 100 and ri.occupancy<>4 order by r.parcel_noSlide67

Select StatementsList parcel #, improvement #, pct

complete for all res imps less than 100% complete and order by parcel #Slide68

Select StatementsCreate a list of parcels on maps 6 through 14 to be reviewed for

pct

complete.

Select

parcel_no,impkey,pctcomp

from

reprop

where

pctcomp

<1.00 and

parcel_no

>= ‘006’ and

parcel_no

<=‘014’

Select r.parcel_no, ri.repropkey

,

ri.pcom

from

reprop

ri

inner join

realprop

r on

ri.realkey

=

r.realkey

where

ri.pcom < 1 and ri.occupancy<>4 and parcel_no>=‘006’ and parcel_no

<=‘014’ order by r.parcel_no Select r.parcel_no, ri.repropkey,

ri.pcom from reprop ri inner join realprop r on ri.realkey = r.realkey where ri.pcom

< 1 and ri.occupancy<>4 and left(parcel_no,3)>=‘006’ and left(parcel_no,3)<=‘014’ order by r.parcel_noSlide69

Select StatementsCreate a list of parcels on maps 6 through 14 to be reviewed for

pct

complete. Slide70

Select StatementsCreate a list containing names and parcel id’s of property owners with vacant land

Select

o.lastname,r.parcel_no

from

realprop

r inner join owner o on

r.ownkey

=

r.ownkey

where

a_value+p_value

>0

Select

o.lastname,r.parcel_no

from realprop r inner join owner 0 on

r.realkey

=

o.ownkey

where

fmvres+fmvcom+fmvacc

=0

Select

o.lastname,r.parcel_no

from

realprop

r inner join owner 0 on

r.ownkey

= o.ownkey where

fmvres+fmvcom+fmvacc=0Slide71

Select StatementsCreate a list containing names and parcel id’s of property owners with vacant landSlide72

Select StatementsCreate a list of deleted prebill

mfg

homes. Include owner acct #,

prebill

acct #,

mfg

, model

Select

ownacct

,

prebillacct

,

mfg

, model from mobile where deletedSelect ownkey,mobilekey,mfg,model from mobile where deleted and

mobtype

= 3

Select

ownkey,mobilekey,mfg,model

from

del_mobile

where

mobtype

= 3Slide73

Select StatementsCreate a list of deleted prebill

mfg

homes. Include owner acct #,

prebill

acct #,

mfg

, modelSlide74

Select StatementsCreate a list comparing Freeport accounts with previous year Freeport accounts. Display Acct # and

freeport

values

Select acct,

freeportvalue

from personal where

freeeportvalue

>0

Select

perskey,frport_val

from personal where

frport_val

>0

Select

p.perskey,pr.frport_val as ay2015freeport,

p.frport_val

as ay2016freeport from personal p inner join ay2015.dbo.personal

pr

on

p.perskey

=

pr.perskey

where

p.frport_val

> 0 or

pr.frport_val

> 0Slide75

Select StatementsCreate a list comparing Freeport accounts with previous year Freeport accounts. Display Acct # and

freeport

valuesSlide76

Copying Data to ExcelRight click on data cell in Results tabChoose the Select All option

Again, right click on data cell and select Copy with Headers option

Open a new Excel worksheet

Right click on first cell (A1)

Select PasteSlide77
Slide78

Open Reporting Services and click on County ProjectsSlide79

1

2Slide80
Slide81
Slide82
Slide83
Slide84
Slide85
Slide86
Slide87
Slide88
Slide89
Slide90
Slide91
Slide92
Slide93
Slide94
Slide95
Slide96
Slide97
Slide98
Slide99
Slide100
Slide101
Slide102

Using report queries in SSMSSlide103

Using report queries in SSMSSlide104

Using report queries in SSMSSlide105

Using report queries in SSMSSlide106

Using report queries in SSMSSlide107

Using report queries in SSMSSlide108

Copy query from SSMS to ReportsSlide109

Copy query from SSMS to ReportsSlide110

Copy query from SSMS to Reports