How Microsoft Great Plains eEnterprise Utilizes SQL Server

1
How Microsoft Great Plains eEnterprise Utilizes
SQL Server

• William Boynes Jr

2
Rules of The Presentation

• First Rule of Bills Presentation THIS IS NOT A
  PRODUCT DEMO
• Second Rule of Bills Presentation THIS IS NOT
  A PRODUCT DEMO
• Third Rule of Bills Presentation There will be
  one instance of a product demo
• Fourth Rule of Bills Presentation More
  Business than Technical So SCREAM OUT

3
Todays Discussion

• What is Microsoft Great Plains
• Overview of Functionality
• System Architecture
• System Architecture - SQL Server
• Maintenance Activities

4
History Of Microsoft Great Plains eEnterprise

• Released in July 1994
• Initially 100 Programmers Now Over 250!
• Provides full Enterprise Functionality
• Targeted At The Medium Sized Enterprise
• Runs on SQL Server

5
Overview of Functionality

• Financial
• Distribution
• Human Resources
• Payroll
• Customer Relationship Management
• Project Accounting
• Manufacturing and Supply Chain Mgmt

6
System Architecture

• Base Development tool MS C
• Customization Toolset Dexterity
• MS VBA ships as part of the toolset
• Used to Extend the Business Logic Level
• SQL Server
• Primarily SQL Stored Procedures

7
System Architecture - Diagram
8
System Architecture SQL Server

• Multiple Database Structure
• Over 800 Tables and 15 Views
• Over 11000 Stored Procedures!
• Over 1200 Check and Default Constraints
• Over 18 Triggers
• Over 75 Primary and Foreign Key Constraints
• Scheduling Engine

9
Database Structure

• Two or More Databases
• Master
• System-wide user information
• Companies
• Posted and Unposted Tables

10
Tables and Views

• Normalized
• Heavy emphasis on indexing
• Tables Use Identity Columns as Keys
• Used as Undeclared Primary and Foreign Keys
• Scarcity of Views
• Reports seen as extension of Business Logic Layer

11
Stored Procedures

• Do the Heavy Lifting of the Application
• Inserting New Records
• Posting Enterprise Transactions
• Assisting with the Business Logic Level
• Extensive use of variables from front end and
  temporary tables on back end

12
Stored Procedure Example

• create procedure dbo.glDeleteBatch
  _at_I_iSQLSessionID int NULL, _at_I_cBatchSource
  char(15) NULL, _at_I_cBatchNumber char(15)
  NULL, _at_I_cProductName char(30) NULL,
  _at_O_tNotesDeleted tinyint NULL output,
  _at_O_iErrorState int NULL output as declare
• _at_tTransaction tinyint, _at_iStatus int, _at_GL_Normal
  char(15), _at_GL_Clearing char(15), _at_BATCH_WINDOW
  smallint, _at_cUserID char(15), _at_cCompanyName
  char(64), _at_mNoteIndex numeric(19,5), _at_cDBName
  char(5), _at_TRUE tinyint, _at_FALSE tinyint,
  _at_tNotesDeleted tinyi
• nt, _at_iError int, _at_tVatMode tinyint if
  _at_I_iSQLSessionID is NULL or _at_I_cBatchSource is
  NULL or _at_I_cBatchNumber is NULL or
  _at_I_cProductName is NULL begin select
  _at_O_iErrorState 20667 return end select
  _at_O_iErrorState 0, _at_O_tNotesDeleted 0 exec
  _at_iS
• tatus DYNAMICS.dbo.smGetConstantString
  'GL_NORMAL_STR', _at_GL_Normal output,
  _at_O_iErrorState output select _at_iError _at__at_error if
  _at_iStatus 0 and _at_iError ltgt 0 select _at_iStatus
  _at_iError if _at_O_iErrorState ltgt 0 or _at_iStatus ltgt 0
  return _at_iStatus exec _at_iStat
• us DYNAMICS.dbo.smGetConstantString
  'GL_CLEARING_STR', _at_GL_Clearing output,
  _at_O_iErrorState output select _at_iError _at__at_error if
  _at_iStatus 0 and _at_iError ltgt 0 select _at_iStatus
  _at_iError if _at_O_iErrorState ltgt 0 or _at_iStatus ltgt 0
  return _at_iStatus exec _at_iSta
• tus DYNAMICS.dbo.smGetConstantInt
  'BATCH_WINDOW', _at_BATCH_WINDOW output,
  _at_O_iErrorState output select _at_iError _at__at_error if
  _at_iStatus 0 and _at_iError ltgt 0 select _at_iStatus
  _at_iError if _at_O_iErrorState ltgt 0 or _at_iStatus ltgt 0
  return _at_iStatus exec _at_iStatus
• DYNAMICS.dbo.smGetConstantInt 'FALSE', _at_FALSE
  output, _at_O_iErrorState output select _at_iError
  _at__at_error if _at_iStatus 0 and _at_iError ltgt 0 select
  _at_iStatus _at_iError if _at_O_iErrorState ltgt 0 or
  _at_iStatus ltgt 0 return _at_iStatus exec _at_iStatus
  DYNAMICS.dbo.smG
• etConstantInt 'TRUE', _at_TRUE output,
  _at_O_iErrorState output select _at_iError _at__at_error if
  _at_iStatus 0 and _at_iError ltgt 0 select _at_iStatus
  _at_iError if _at_O_iErrorState ltgt 0 or _at_iStatus ltgt 0
  return _at_iStatus if _at__at_trancount 0 begin select
  _at_tTransaction 1
• begin transaction end if _at_I_cBatchSource
  _at_GL_Normal begin exec _at_iStatus
  dtaRemoveRecordsForBatch _at_I_cBatchNumber,
  _at_I_cBatchSource, _at_O_iErrorState output select
  _at_iError _at__at_error if _at_iStatus 0 and _at_iError ltgt
  0 select _at_iStatus _at_iError if _at_O_
• iErrorState ltgt 0 or _at_iStatus ltgt 0
  return(_at_iStatus) exec _at_iStatus
  vatLineAnalisysMode _at_tVatMode output,
  _at_O_iErrorState output select _at_iError _at__at_error
  if _at_iStatus 0 and _at_iError ltgt 0 select _at_iStatus
  _at_iError if _at_O_iErrorState ltgt 0 or _at_iStatus lt
• gt 0 return(_at_iStatus) if _at_tVatMode _at_TRUE
  begin delete VAT10301 from VAT10301 VAT,
  GL10000 HDR where convert(int,VAT.DOCNUMBR)
  HDR.JRNENTRY and HDR.BACHNUMB _at_I_cBatchNumber
  and HDR.BCHSOURC _at_I_cBatchSource and
  VAT.RCTRXSEQ 0 selec
• t _at_iError _at__at_error if _at_iError ltgt 0 begin if
  _at_tTransaction 1 rollback transaction select
  _at_O_iErrorState 21067 return(_at_iError) end end
  delete GL10001 from GL10001 LINE, GL10000
  HDR where HDR.JRNENTRY LINE.JRNENTRY and
  HDR.BACHNUMB
• _at_I_cBatchNumber and HDR.BCHSOURC
  _at_I_cBatchSource select _at_iError _at__at_error if
  _at_iError ltgt 0 begin if _at_tTransaction 1
  rollback transaction select _at_O_iErrorState
  20681 return(_at_iError) end end else begin
  delete GL10002 from GL10002 CLEARIN
• G, GL10000 HDR where HDR.JRNENTRY
  CLEARING.JRNENTRY and HDR.BACHNUMB
  _at_I_cBatchNumber and HDR.BCHSOURC
  _at_I_cBatchSource select _at_iError _at__at_error if
  _at_iError ltgt 0 begin if _at_tTransaction 1
  rollback transaction select _at_O_iErrorState
  20682
• return(_at_iError) end end delete SY03900 from
  GL10000 HDR, SY03900 NOTE where HDR.BACHNUMB
  _at_I_cBatchNumber and HDR.BCHSOURC
  _at_I_cBatchSource and HDR.NOTEINDX NOTE.NOTEINDX
  if _at__at_rowcount gt 0 select _at_tNotesDeleted _at_TRUE
  select _at_iError _at__at_erro
• r if _at_iError ltgt 0 begin if _at_tTransaction 1
  rollback transaction select _at_O_iErrorState
  20683 return(_at_iError) end delete GL10000 where
  BACHNUMB _at_I_cBatchNumber and BCHSOURC
  _at_I_cBatchSource select _at_iError _at__at_error if
  _at_iError ltgt 0 begin if _at_tT
• ransaction 1 rollback transaction select
  _at_O_iErrorState 20684 return(_at_iError) end
  select _at_mNoteIndex BATCH.NOTEINDX from
  SY00500 BATCH, SY03900 NOTE where
  BATCH.BACHNUMB _at_I_cBatchNumber and
  BATCH.BCHSOURC _at_I_cBatchSource and BATCH.NOTEIN
• DX NOTE.NOTEINDX if _at_mNoteIndex gt 0 begin
  delete SY03900 where NOTEINDX _at_mNoteIndex
  if _at__at_rowcount ltgt 1 begin if _at_tTransaction 1
  rollback transaction select _at_O_iErrorState
  20683 return end select _at_tNotesDeleted
  _at_TRUE end delete SY0
• 0500 where BACHNUMB _at_I_cBatchNumber and
  BCHSOURC _at_I_cBatchSource select _at_iError
  _at__at_error if _at_iError ltgt 0 begin if _at_tTransaction
  1 rollback transaction select _at_O_iErrorState
  20685 return(_at_iError) end select _at_cDBName
  db_name() exec _at_iStatus

13
Check and Default Constraints

• VERY MISLEADING
• Bulk of Check Constraints Related to Datetime
• Four Default Constraints
• GPS_CHAR
• GPS_DATE
• GPS_INT
• GPS_MONEY

14
Check and Default Constraints Examples

• (datepart(hour,DATERECD) 0 and
  datepart(minute,DATERECD) 0 and
  datepart(second,DATERECD) 0 and
  datepart(millisecond,DATERECD) 0)
• create default dbo.GPS_MONEY AS 0.00

15
Triggers

• Minimal use of Triggers
• Used to Enforce Some Business Logic
• Enforces Referential Integrity
• Used in Place of Stored Procedures
• Not As Efficient Use Has Been Reduced

16
Primary And Foreign Keys

• Explicit Definition of Keys
• Used on Major Setup and Transaction Tables
• Chart of Accounts
• General Ledger Transaction Tables
• Vendor Master

17
Scheduling Engine

• Business Alerts
• Series of Stored Procedures
• Checks for Conditions Within The System
• Uses the Scheduling Engine to E-Mail Users
• Done Through Front End Wizard
• Demonstration

18
Managing The Installation

• Care and Feeding of The Server
• Business Alerts
• Indexing Scheme
• Referential Integrity
• Outside Systems
• Backup and Recovery

19
Conclusion

• Fully functioning, incredibly sophisticated
  system
• Takes Advantage of Most Base SQL Functions
• Very Open
• Allows Data to Be Put Easily into System
• Easy Access to Data Model
• Questions???