1 / 19

How Microsoft Great Plains eEnterprise Utilizes SQL Server

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

norwood
Download Presentation

How Microsoft Great Plains eEnterprise Utilizes SQL Server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. How Microsoft Great Plains eEnterprise Utilizes SQL Server William Boynes Jr

  2. 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

  3. Today’s 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 @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 < • > 0 or @O_iErrorState <> 0 begin if @tTransaction = 1 rollback transaction return(@iStatus) end select @cCompanyName = ACT.CMPNYNAM, @cUserID = ACT.USERID from DYNAMICS.dbo.ACTIVITY ACT, DYNAMICS.dbo.SY01500 COMP where ACT.SQLSESID = @I_iSQLSessi • onID and COMP.CMPNYNAM = ACT.CMPNYNAM delete DYNAMICS.dbo.SY00800 where WINTYPE = @BATCH_WINDOW and USERID = @cUserID and CMPNYNAM = @cCompanyName and BCHSOURC = @I_cBatchSource and BACHNUMB = @I_cBatchNumber select @iError = @@error if @iError <> 0 • begin if @tTransaction = 1 rollback transaction select @O_iErrorState = 20687 return(@iError) end select @O_tNotesDeleted = @tNotesDeleted if @tTransaction = 1 commit transaction return

  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???

More Related