Transcript Document

How Microsoft Great Plains
eEnterprise Utilizes SQL Server
William Boynes Jr
Rules of The Presentation
• First Rule of Bill’s Presentation – THIS IS
NOT A PRODUCT DEMO
• Second Rule of Bill’s Presentation – THIS
IS NOT A PRODUCT DEMO
• Third Rule of Bill’s Presentation – There
will be one instance of a product demo
• Fourth Rule of Bill’s Presentation – More
Business than Technical So SCREAM OUT
Today’s Discussion
•
•
•
•
•
What is Microsoft Great Plains
Overview of Functionality
System Architecture
System Architecture - SQL Server
Maintenance Activities
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
Overview of Functionality
•
•
•
•
•
•
•
Financial
Distribution
Human Resources
Payroll
Customer Relationship Management
Project Accounting
Manufacturing and Supply Chain Mgmt
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
System Architecture - Diagram
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
Database Structure
• Two or More Databases
–
–
–
–
Master
System-wide user information
Companies
Posted and Unposted Tables
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
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
Stored Procedure Example
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
•
create procedure dbo.glDeleteBatch @I_iSQLSessionID int = NULL, @I_cBatchSource char(15) = NULL, @I_cBatchNumber char(15) = NULL, @I_cProductName char(30)
= NULL, @O_tNotesDeleted tinyint = NULL output, @O_iErrorState int = NULL output as declare
@tTransaction tinyint, @iStatus int, @GL_Normal char(15), @GL_Clearing char(15), @BATCH_WINDOW smallint, @cUserID char(15), @cCompanyName char(64),
@mNoteIndex numeric(19,5), @cDBName char(5), @TRUE tinyint, @FALSE tinyint, @tNotesDeleted tinyi
nt, @iError int, @tVatMode tinyint if @I_iSQLSessionID is NULL or @I_cBatchSource is NULL or @I_cBatchNumber is NULL or @I_cProductName is NULL begin
select @O_iErrorState = 20667 return end select @O_iErrorState = 0, @O_tNotesDeleted = 0 exec @iS
tatus = DYNAMICS.dbo.smGetConstantString 'GL_NORMAL_STR', @GL_Normal output, @O_iErrorState output select @iError = @@error if @iStatus = 0 and @iError
<> 0 select @iStatus = @iError if @O_iErrorState <> 0 or @iStatus <> 0 return @iStatus exec @iStat
us = DYNAMICS.dbo.smGetConstantString 'GL_CLEARING_STR', @GL_Clearing output, @O_iErrorState output select @iError = @@error if @iStatus = 0 and @iError
<> 0 select @iStatus = @iError if @O_iErrorState <> 0 or @iStatus <> 0 return @iStatus exec @iSta
tus = DYNAMICS.dbo.smGetConstantInt 'BATCH_WINDOW', @BATCH_WINDOW output, @O_iErrorState output select @iError = @@error if @iStatus = 0 and @iError
<> 0 select @iStatus = @iError if @O_iErrorState <> 0 or @iStatus <> 0 return @iStatus exec @iStatus
= DYNAMICS.dbo.smGetConstantInt 'FALSE', @FALSE output, @O_iErrorState output select @iError = @@error if @iStatus = 0 and @iError <> 0 select @iStatus =
@iError if @O_iErrorState <> 0 or @iStatus <> 0 return @iStatus exec @iStatus = DYNAMICS.dbo.smG
etConstantInt 'TRUE', @TRUE output, @O_iErrorState output select @iError = @@error if @iStatus = 0 and @iError <> 0 select @iStatus = @iError if @O_iErrorState <>
0 or @iStatus <> 0 return @iStatus if @@trancount = 0 begin select @tTransaction = 1
begin transaction end if @I_cBatchSource = @GL_Normal begin exec @iStatus = dtaRemoveRecordsForBatch @I_cBatchNumber, @I_cBatchSource, @O_iErrorState
output select @iError = @@error if @iStatus = 0 and @iError <> 0 select @iStatus = @iError if @O_
iErrorState <> 0 or @iStatus <> 0 return(@iStatus) exec @iStatus = vatLineAnalisysMode @tVatMode output, @O_iErrorState output select @iError = @@error if
@iStatus = 0 and @iError <> 0 select @iStatus = @iError if @O_iErrorState <> 0 or @iStatus <
> 0 return(@iStatus) if @tVatMode = @TRUE begin delete VAT10301 from VAT10301 VAT, GL10000 HDR where convert(int,VAT.DOCNUMBR) =
HDR.JRNENTRY and HDR.BACHNUMB = @I_cBatchNumber and HDR.BCHSOURC = @I_cBatchSource and VAT.RCTRXSEQ = 0 selec
t @iError = @@error if @iError <> 0 begin if @tTransaction = 1 rollback transaction select @O_iErrorState = 21067 return(@iError) end end delete GL10001 from
GL10001 LINE, GL10000 HDR where HDR.JRNENTRY = LINE.JRNENTRY and HDR.BACHNUMB =
@I_cBatchNumber and HDR.BCHSOURC = @I_cBatchSource select @iError = @@error if @iError <> 0 begin if @tTransaction = 1 rollback transaction select
@O_iErrorState = 20681 return(@iError) end end else begin delete GL10002 from GL10002 CLEARIN
G, GL10000 HDR where HDR.JRNENTRY = CLEARING.JRNENTRY and HDR.BACHNUMB = @I_cBatchNumber and HDR.BCHSOURC = @I_cBatchSource select
@iError = @@error if @iError <> 0 begin if @tTransaction = 1 rollback transaction select @O_iErrorState = 20682
return(@iError) end end delete SY03900 from GL10000 HDR, SY03900 NOTE where HDR.BACHNUMB = @I_cBatchNumber and HDR.BCHSOURC =
@I_cBatchSource and HDR.NOTEINDX = NOTE.NOTEINDX if @@rowcount > 0 select @tNotesDeleted = @TRUE select @iError = @@erro
r if @iError <> 0 begin if @tTransaction = 1 rollback transaction select @O_iErrorState = 20683 return(@iError) end delete GL10000 where BACHNUMB =
@I_cBatchNumber and BCHSOURC = @I_cBatchSource select @iError = @@error if @iError <> 0 begin if @tT
ransaction = 1 rollback transaction select @O_iErrorState = 20684 return(@iError) end select @mNoteIndex = BATCH.NOTEINDX from SY00500 BATCH, SY03900
NOTE where BATCH.BACHNUMB = @I_cBatchNumber and BATCH.BCHSOURC = @I_cBatchSource and BATCH.NOTEIN
DX = NOTE.NOTEINDX if @mNoteIndex > 0 begin delete SY03900 where NOTEINDX = @mNoteIndex if @@rowcount <> 1 begin if @tTransaction = 1 rollback
transaction select @O_iErrorState = 20683 return end select @tNotesDeleted = @TRUE end delete SY0
0500 where BACHNUMB = @I_cBatchNumber and BCHSOURC = @I_cBatchSource select @iError = @@error if @iError <> 0 begin if @tTransaction = 1 rollback
transaction select @O_iErrorState = 20685 return(@iError) end select @cDBName = db_name() exec @iStatus
= DYNAMICS..smAddRecordDeletedRecord @I_iSQLSessionID, 'SY00500', @I_cBatchNumber, 2, @I_cProductName, @cDBName, 0, @O_iErrorState output select
@iError = @@error if @iStatus = 0 and @iError <> 0 begin select @iStatus = @iError end if @iStatus <
Check and Default Constraints
• VERY MISLEADING
• Bulk of Check Constraints Related to
Datetime
• Four Default Constraints
–
–
–
–
GPS_CHAR
GPS_DATE
GPS_INT
GPS_MONEY
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
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
Primary And Foreign Keys
• Explicit Definition of Keys
• Used on Major Setup and Transaction
Tables
–
–
–
–
Chart of Accounts
General Ledger Transaction Tables
Vendor Master
…
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
Managing The Installation
•
•
•
•
Care and Feeding of The Server
Business Alerts
Indexing Scheme
Referential Integrity
– Outside Systems
• Backup and Recovery
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???