Transcript PowerPoint Presentation - Hitachi Solutions America

FSA 2012 R3
DATA MODEL
CU1
Rob George
FSA Senior Product Manager
Hitachi Solutions America
HIGH-LEVEL DATA MODEL
Foundational Entities
Calls
Or
Project
Quotes,
Service Orders
And
Appointments
Locations
And
Contacts
Resources
Surveys
Organizational Units
Service
Agreements,
Pricing and
Taxes
Maintenance
Plans
Users
CALLS
Inbound calls
Outbound calls
•
•
Related Service Order
Related Labor Resource
SERVICE ORDERS
• Task Oriented
• Name
Project 1
• Service Location
Service Order A
• Bill To
• Start, End, Duration
Task 1
• Deadline
• Dependencies
Task 2
• Service Agreements
• Resources / Appointments
Task 2.1
• Company Structure
• Qualifications
Service Order Template
Task 1a
Task 1b
Task 2
• Notes
Task 1c
• Documents
• Templates
• …
SERVICE ORDERS
• Service Object Oriented
• Name
Project 1
• Service Location
Service Order A
Service Object 1
Service Object 2
Subassembly 1
• Bill To
• Start, End, Duration
• Deadline
• Dependencies
• Service Agreements
• Resources / Appointments
• Company Structure
Service Order Template
• Qualifications
• Notes
• Documents
• Templates
• …
QUOTES
Project 1
Quote A
Quote B1
Quote C1
Quote Template
Quote B2
• Task or Service Object
Oriented
• Quote Versions
• Name
• Service Location
• Bill To
• Start, End, Duration
• Deadline
• Dependencies
• Service Agreements
• Company Structure
• Notes
• Documents
• Templates
• …
LOCATIONS
Bill To Locations
Service Locations
Site X
Building A
Area 1
Owner
Vehicle
Agent
Warehouse
Tenant
Branch
Developer
Service
Location
Area 2
Other Locations
Building B
Area 3
Inventory Locations
Employee
Vendor
Prospect
• Contacts
• Many to many
relationship
• Resources
• Qualifications
• Documents
• Surveys
• …
RESOURCES – LABOR
Tech 1
Tech 2
Tech 3
Subcontractor 1
• Name
• Home Location
• Current Location
• Costs
• Rates
• Skills
• Work Schedule
Subcontractor 2
• Appointments
• Qualifications
• Notes
• Templates
• …
RESOURCES – SERVICE OBJECTS
Containment
Equipment 1
Subassembly A
Inheritance
Base Template
Model A Template
Component 1
Model A – Serial #1
Component 2
Model A – Serial #2
Component 3
Subassembly B
Component 4
Model A – Serial #3
Model B Template
Model B – Serial #4
• Name
• Serial Number
• Tag Number
• RFID
• Type
• Make
• Model
• Location
• Bill of Materials
• Warranty
• Templates
• Qualifications
• …
RESOURCES – OTHER
Vehicles
Items
Tools
Fees
Meters
Rental
…
SERVICE ORDER RESOURCES – SCHEDULING
REQUEST LINES
Service Order A
•
•
Utilize Labor
•
Mechanic
•
•
Prep Tool
Utilize Labor
Utilize Tool
Utilize Labor
Teardown Tool
Utilize Labor
•
•
•
Action
Duration
Dependencies
Discipline
Performed At
Resource
Notes
…
SERVICE ORDER RESOURCES – PLANNED LINES
Service Order A
•
Prep Tool
Utilize Tool
Teardown Tool
Utilize Joe
Utilize Steve
Utilize Joe
Service Object
•
•
•
•
•
•
•
Action
Duration
Start On
End On
Current Event for Labor
Resource
Notes
…
SERVICE ORDER RESOURCES – REFERENCED
LINES
Service Order A
•
•
Items
•
•
Codes
Fees
Etc.
Resource
Invoice Segments
Notes
…
SERVICE ORDER RESOURCES – PERFORMED
LINES
Service Order A
•
Items
•
Labor
•
•
•
Fees
•
Service Object
Items
Labor
Fees
•
•
•
Pricing
Action
Duration
Start On
End On
Current Event for Labor
Resource
Notes
…
SERVICE ORDER RESOURCES – QUOTED LINES
•
Service Order A
•
•
Items
•
•
Labor
•
•
Fees
etc
•
•
Pricing
Action
Duration
Start On
End On
Current Event for Labor
Resource
Notes
…
SERVICE ORDER RESOURCES – INVENTORY
REQUEST LINE
Service Order A
•
•
Item 1
Item 2
•
Resource
Notes
…
ORGANIZATIONAL UNITS – OVERVIEW
Company Structure
Disciplines/Specialties
Mechanic
Company
ATM Repair
Certifications
MCSD
MCAD
Region A
Branch 1
Zone A
Zone B
Region B
Zone C
Electrical
New
Construction
MCSE
Business Units
Operations
Unions
IBEW
UA Plumbers
Finance
ORGANIZATIONAL UNITS – COMPANY
STRUCTURE
All Companies
Company A
West Region
Branch 1
Company B
East Region
North Region
Branch 2
Zone A
Zone C
Zone B
Zone D
Branch 3
Zone E
South Region
Branch 4
Zone F
CALENDARS – WORK CALENDARS
•
Company Structure
Enterprise-wide
Calendar
•
Holidays
Company A
Calendar
Holiday Overrides, Work Times
Branch 1
Calendar
Resource
Calendar
Company B
Calendar
•
Type
Appointments
–
Recurring
–
One-off
–
Any combination
Time zone
Holiday Overrides, Work Time Overrides
Work Time Overrides, On-Call Times, Time Off,
Service Order & Meetings Appointments
SURVEYS
Survey 1
•
•
Section A
•
•
Question 1
Question 2
Survey 2
Question 3
Question 4
•
Sections
Question Groups
Questions
Dependencies
Responses
– Answers
• Yes/No
• Text
• Custom
• Min/max choices
• Service Order Creation
SERVICE AGREEMENTS & PRICING
Master Agreement 1
•
•
Pricing Rules
Service Agreement
Coverage
Pricing Rules
SLAs
Warranty
•
•
•
•
•
•
•
•
Locations
Type
• Service agreement, perpetual
agreement, warranty, trade agreement
Coverage Type
• Per Location or Per Service Object
Start Date
Expiration Date
Pricing Rules
• Effective Dates
SLA Rules
• Response time
• Deadline
Bill To
Templates
Renewal
MAINTENANCE PLANS
Maintenance Plan
•
Service
Trigger
•
•
•
•
•
•
Maintenance Location Type
Services
• Triggers
Service Order Template
Service Object Template
Usage-based or Aging
Manual meter reading or
automatic
Recurring or one-time
EXTENDING THE DATA MODEL
DATABASE CONVENTIONS –
TABLES
• Two types of tables
• Writeable tables & Lookup tables
• Domain Roots
• E.g., ServiceOrders, Locations, Resources, etc.
• Children
• ServiceOrderResources, LocationResources, ResourceDocuments, etc.
• Special Tables
• Members tables
• Requires a MemberOf FK field and
• A corresponding Members table, e.g, ServiceOrderMembers
• Eventable object
• Requires CurrentEvent, CurrentEventDetails and PreviousEvent columns and
• An Event table, EventDetails and an EventTypes table. E.g., ServiceOrderEventTypes
• And an EventTransitions table
• OrganizationalUnitConstants
• Holds runtime parameters and other config information
DATABASE CONVENTIONS –
COLUMNS
• Non-Foreign Keys
• e.g., Name, Address, PostalCode, etc
• No underscores allowed
• Foreign Keys
• e.g., ServiceLocation_LocationID, TimeZone_LocationTimeZoneID
• Underscore required to separate name from type
• To a lookup table
• 16-bit, (type Lookup)
• Dropdown list is generated in UI
• To a writeable table
• 32-bit, (type int)
• LookupSearch control generated in UI
• Foreign key constraints are set for you by TierDrops
DATABASE CONVENTIONS –
COLUMNS (CONT.)
• Special column names
• All tables must have
• <EntityName>ID, e.g., ServiceOrderID for primary keys
• must be first column in table
• Writeable tables must have
• CreatedOn, ModifiedOn, DeletedOn, CreatedByUser, ModifiedByUser, DeletedByUser
• Version stamp
• UniqueID
• Lookup tables must have
•
•
•
•
Name, Custom Name, Alias
DeletedOn
SortOrder
Computed column with readable name (this column is the one displayed in the UI)
• Manually change this property in the Entity Model to initial lowercase.
EXTENDING EXISTING TABLES
• Add your DDL for extending the schema to:
•
“Create Implementation Schema.sql” file
• e.g, ALTER TABLE Locations …
• Script will be executed by TierDrops
CREATING NEW TABLES
• Add your DDL for creating new tables to “Create Implementation
Schema.sql” file
• e.g., CREATE TABLE MyTable …
• Add metadata for new table using TierDrops
• This script will be executed by TierDrops
• Template scripts are available for creating writeable tables and lookup
tables
• “Create Writeable Table Template.sql”
• “Create Lookup Table Template.sql”
UPDATING ENTITY MODEL IN
VISUAL STUDIO
• When making any implementation changes to
the data model always refresh the model in
Visual Studio
1. Right-click in the Entity Designer
2. Click on the menu item “Update Model from
Database…”
3. Rebuild
WARNING: Do not remove any FSA product
entities from the implementation data model
METADATA
CUSTOMIZING BEHAVIOR VIA
METADATA
• Certain lookup tables and other tables contain columns of metadata
• Used to drive business rule behavior
• Used to control UI options
• Allows an implementer to tailor FSA Office and FSA Mobile down to only UI and
behavior desired by a customer
• Allows an implementer to extend FSA’s behavior
• Some of the important tables to learn
• ResourceTypes, ResourceActionTypes, ResourceTypesActionTypes
• ServiceOrderEventTypes, ServiceOrderResourceEventTypes,
ServiceOrderEventDetails, ServiceOrderResourceEventDetails,
ServiceOrderEventTransitions, ServiceOrderResourceEventTransitions
• LocationTypes
Q&A