Solution
Development Plan
Solution: Good To Go! Pass
Owner: Catherine W.
Date: 2016-10-14
Change
Log
Summary
Washington State’s
“Good to Go!” Pass program wants to keep track of vehicles traveling across the
520 toll bridge. We need to keep track of individual users, the
vehicles associated with each person's account, the trips taken by each vehicle, the
fees assessed for each trip, and the address and payment information for each
account.
Solution Naming
Conventions
ETL
Staging Database (SQL Server)
Object Type
|
Convention
|
Example
|
Description
|
Table
|
EtlTableName
|
EtlTimeTable
|
A table used to temporarily hold data for the ETL
process
|
View
|
vEtlViewName
|
vEtlCastAccountData
|
A
saved select statement that provides ETL data
|
Stored Proc.
|
pEtlProcedureName
|
pEtlTransformAccountData
|
A set of saved statements that provides ETL data
|
Function
|
fEtlFunctionName
|
fEtlLookupValues
|
A
saved select statement that provides ETL data
|
…
|
|
|
|
Data
Warehouse Database (SQL Server)
Object Type
|
Convention
|
Example
|
Description
|
Fact Table
|
FactTableName
|
FactTrips
|
A table that holds measures and dimensional keys
|
Dimension Table
|
DimTableName
|
DimAccounts
|
A
table that holds dimensional keys and descriptive dimensional attributes
|
View
|
vRptViewName
|
vRptVehiclesByAccount
|
A saved select statement used for reporting
|
Stored Proc.
|
pRptProcedureName
|
pRptTripsByVehicle
|
A
saved set of statements used for reporting
|
Function
|
fRptFunctionName
|
fRptFeesByTrip
|
A saved select statement used for reporting
|
…
|
|
|
|
ETL
Processing Objects (Integration Services)
Object Type
|
Convention
|
Example
|
Description
|
File Connection
|
NameOfFile
|
Accounts.txt
|
A SSIS File connection object
|
Database Connection
|
ServerName.DatabaseName
|
LocalHost.Gtg
|
A
SSIS DB connection object
|
Task
|
ObjectTypeObjectName
|
DataFlowLoadDimAccounts
|
A SSIS task object
|
Container
|
ObjectTypeObjectName
|
SequenceContainerLoadFactTables
|
A
SSIS container object
|
…
|
|
|
|
OLAP
Cube Database (Analysis Server)
Object Type
|
Convention
|
|
Description
|
Data Source
|
NameOfDataWarehouse
|
DW-GTG
|
A SSAS Data Source
|
D.S. View
|
NameOfConnectionNameOfSubject
|
DW-GTGTripFacts
|
A
SSAS Data Source View
|
Dimension
|
DimVehicle
|
DimVehicles
|
A SSAS Dimension
|
Cube
|
Trips
|
CubeTripFacts
|
A
SSAS Cube
|
…
|
|
|
|
Report
Objects (Reporting Services)
Object Type
|
Convention
|
|
Description
|
Shared Data Source
|
SharedObjectTypeNameOfSource
|
SharedDataSourceDW-GTG
|
A DataSource that is shared between reports
|
Local Data Source
|
ObjectTypeNameOfSource
|
DataSourceDW-GTG
|
A
DataSource that is part on a individual report
|
Shared Data Set
|
SharedObjectTypeNameOfSubject
|
ShareDataSetVehiclesByAccount
|
A DataSet that is shared between reports
|
Local DataSet
|
ObjectTypeNameOfSource
|
DataSetTripsByVehicle
|
A
DataSet that is part of an individual report
|
Report Component
|
ObjectTypeNameOfSubject
|
TableFeesByTrip
|
A SSRS Report Component
|
…
|
|
|
|
Data
Warehouse Objects
ETL Staging
Objects
ETL Staging Database Tables
Table Name: Accounts
Description: Unique users of
the Good to Go! pass system
Columns
Column
Name
|
Data
Type
|
Example
|
Max
Value
|
Min
Value
|
Invalid
Examples
|
AccountID
|
int
|
12345
|
n/a
|
1
|
NON-NULLABLE
|
PaymentID
|
int
|
23456
|
n/a
|
1
|
NON-NULLABLE
|
AddressID
|
int
|
34567
|
n/a
|
1
|
NON-NULLABLE
|
Active
|
T/F
|
TRUE
|
n/a
|
n/a
|
NON-NULLABLE
|
Table Name: Vehicles
Description: Unique vehicles
crossing the 520 bridge
Columns
Column
Name
|
Data
Type
|
Example
|
Max
Value
|
Min
Value
|
Invalid
Examples
|
VehicleID
|
int
|
45678
|
n/a
|
1
|
NON-NULLABLE
|
AccountID
|
int
|
56789
|
n/a
|
1
|
|
LicensePlateState
|
Char(2)
|
WA
|
n/a
|
n/a
|
|
LicensePlateNo
|
Char(9)
|
ANA9468
|
n/a
|
n/a
|
|
AxleNo
|
Int(2)
|
2
|
n/a
|
2
|
NON-NULLABLE
|
Table Name: Trips
Description: Unique trips by
vehicles crossing the 520 bridge
Columns
Column
Name
|
Data
Type
|
Example
|
Max
Value
|
Min
Value
|
Invalid
Examples
|
VehicleID
|
int
|
45678
|
n/a
|
1
|
NON-NULLABLE
|
TripID
|
Int
|
56789
|
n/a
|
1
|
NON-NULLABLE
|
TripDatetime
|
Datetime
|
2016-10-12
23:50:45
|
n/a
|
n/a
|
NON-NULLABLE
|
Direction
|
Char(4)
|
EAST
|
n/a
|
n/a
|
NON-NULLABLE
|
Table Name: TripFees
Description: The amount of
money charged for the trip
Columns
Column
Name
|
Data
Type
|
Example
|
Max
Value
|
Min
Value
|
Invalid
Examples
|
TripID
|
Int
|
56789
|
n/a
|
1
|
NON-NULLABLE
|
Fee
|
money
|
$1.90
|
n/a
|
$0
|
NON-NULLABLE
|
ETL Staging Database Views
View Name: Vehicles By
Account
Description: Show all the
vehicles listed under an account
Columns
Column
Name
|
Source
|
Example
|
Max
Value
|
Min
Value
|
Invalid
Examples
|
AccountID
|
Accounts
|
12345
|
n/a
|
1
|
NON-NULLABLE
|
VehicleID
|
Vehicles
|
45678
|
n/a
|
1
|
NON-NULLABLE
|
View Name: Trips By Vehicle
Description: Show all the
trips across 520 taken by a vehicle
Columns
Column
Name
|
Source
|
Example
|
Max
Value
|
Min
Value
|
Invalid
Examples
|
VehicleID
|
Vehicles
|
67890
|
n/a
|
1
|
NON-NULLABLE
|
TripID
|
Trips
|
78901
|
n/a
|
1
|
NON-NULLABLE
|
Project Management Section
