How Microsoft Great Plains e Enterprise Utilizes SQL

How Microsoft Great Plains e. Enterprise 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 - SQL Server Maintenance Activities

History Of Microsoft Great Plains e. Enterprise • • • 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. gl. Delete. Batch @I_i. SQLSession. ID int = NULL, @I_c. Batch. Source char(15) = NULL, @I_c. Batch. Number char(15) = NULL, @I_c. Product. Name char(30) = NULL, @O_t. Notes. Deleted tinyint = NULL output, @O_i. Error. State int = NULL output as declare @t. Transaction tinyint, @i. Status int, @GL_Normal char(15), @GL_Clearing char(15), @BATCH_WINDOW smallint, @c. User. ID char(15), @c. Company. Name char(64), @m. Note. Index numeric(19, 5), @c. DBName char(5), @TRUE tinyint, @FALSE tinyint, @t. Notes. Deleted tinyi nt, @i. Error int, @t. Vat. Mode tinyint if @I_i. SQLSession. ID is NULL or @I_c. Batch. Source is NULL or @I_c. Batch. Number is NULL or @I_c. Product. Name is NULL begin select @O_i. Error. State = 20667 return end select @O_i. Error. State = 0, @O_t. Notes. Deleted = 0 exec @i. S tatus = DYNAMICS. dbo. sm. Get. Constant. String 'GL_NORMAL_STR', @GL_Normal output, @O_i. Error. State output select @i. Error = @@error if @i. Status = 0 and @i. Error <> 0 select @i. Status = @i. Error if @O_i. Error. State <> 0 or @i. Status <> 0 return @i. Status exec @i. Stat us = DYNAMICS. dbo. sm. Get. Constant. String 'GL_CLEARING_STR', @GL_Clearing output, @O_i. Error. State output select @i. Error = @@error if @i. Status = 0 and @i. Error <> 0 select @i. Status = @i. Error if @O_i. Error. State <> 0 or @i. Status <> 0 return @i. Status exec @i. Sta tus = DYNAMICS. dbo. sm. Get. Constant. Int 'BATCH_WINDOW', @BATCH_WINDOW output, @O_i. Error. State output select @i. Error = @@error if @i. Status = 0 and @i. Error <> 0 select @i. Status = @i. Error if @O_i. Error. State <> 0 or @i. Status <> 0 return @i. Status exec @i. Status = DYNAMICS. dbo. sm. Get. Constant. Int 'FALSE', @FALSE output, @O_i. Error. State output select @i. Error = @@error if @i. Status = 0 and @i. Error <> 0 select @i. Status = @i. Error if @O_i. Error. State <> 0 or @i. Status <> 0 return @i. Status exec @i. Status = DYNAMICS. dbo. sm. G et. Constant. Int 'TRUE', @TRUE output, @O_i. Error. State output select @i. Error = @@error if @i. Status = 0 and @i. Error <> 0 select @i. Status = @i. Error if @O_i. Error. State <> 0 or @i. Status <> 0 return @i. Status if @@trancount = 0 begin select @t. Transaction = 1 begin transaction end if @I_c. Batch. Source = @GL_Normal begin exec @i. Status = dta. Remove. Records. For. Batch @I_c. Batch. Number, @I_c. Batch. Source, @O_i. Error. State output select @i. Error = @@error if @i. Status = 0 and @i. Error <> 0 select @i. Status = @i. Error if @O_ i. Error. State <> 0 or @i. Status <> 0 return(@i. Status) exec @i. Status = vat. Line. Analisys. Mode @t. Vat. Mode output, @O_i. Error. State output select @i. Error = @@error if @i. Status = 0 and @i. Error <> 0 select @i. Status = @i. Error if @O_i. Error. State <> 0 or @i. Status < > 0 return(@i. Status) if @t. Vat. Mode = @TRUE begin delete VAT 10301 from VAT 10301 VAT, GL 10000 HDR where convert(int, VAT. DOCNUMBR) = HDR. JRNENTRY and HDR. BACHNUMB = @I_c. Batch. Number and HDR. BCHSOURC = @I_c. Batch. Source and VAT. RCTRXSEQ = 0 selec t @i. Error = @@error if @i. Error <> 0 begin if @t. Transaction = 1 rollback transaction select @O_i. Error. State = 21067 return(@i. Error) end delete GL 10001 from GL 10001 LINE, GL 10000 HDR where HDR. JRNENTRY = LINE. JRNENTRY and HDR. BACHNUMB = @I_c. Batch. Number and HDR. BCHSOURC = @I_c. Batch. Source select @i. Error = @@error if @i. Error <> 0 begin if @t. Transaction = 1 rollback transaction select @O_i. Error. State = 20681 return(@i. Error) end else begin delete GL 10002 from GL 10002 CLEARIN G, GL 10000 HDR where HDR. JRNENTRY = CLEARING. JRNENTRY and HDR. BACHNUMB = @I_c. Batch. Number and HDR. BCHSOURC = @I_c. Batch. Source select @i. Error = @@error if @i. Error <> 0 begin if @t. Transaction = 1 rollback transaction select @O_i. Error. State = 20682 return(@i. Error) end delete SY 03900 from GL 10000 HDR, SY 03900 NOTE where HDR. BACHNUMB = @I_c. Batch. Number and HDR. BCHSOURC = @I_c. Batch. Source and HDR. NOTEINDX = NOTEINDX if @@rowcount > 0 select @t. Notes. Deleted = @TRUE select @i. Error = @@erro r if @i. Error <> 0 begin if @t. Transaction = 1 rollback transaction select @O_i. Error. State = 20683 return(@i. Error) end delete GL 10000 where BACHNUMB = @I_c. Batch. Number and BCHSOURC = @I_c. Batch. Source select @i. Error = @@error if @i. Error <> 0 begin if @t. T ransaction = 1 rollback transaction select @O_i. Error. State = 20684 return(@i. Error) end select @m. Note. Index = BATCH. NOTEINDX from SY 00500 BATCH, SY 03900 NOTE where BATCH. BACHNUMB = @I_c. Batch. Number and BATCH. BCHSOURC = @I_c. Batch. Source and BATCH. NOTEIN DX = NOTEINDX if @m. Note. Index > 0 begin delete SY 03900 where NOTEINDX = @m. Note. Index if @@rowcount <> 1 begin if @t. Transaction = 1 rollback transaction select @O_i. Error. State = 20683 return end select @t. Notes. Deleted = @TRUE end delete SY 0 0500 where BACHNUMB = @I_c. Batch. Number and BCHSOURC = @I_c. Batch. Source select @i. Error = @@error if @i. Error <> 0 begin if @t. Transaction = 1 rollback transaction select @O_i. Error. State = 20685 return(@i. Error) end select @c. DBName = db_name() exec @i. Status = DYNAMICS. . sm. Add. Record. Deleted. Record @I_i. SQLSession. ID, 'SY 00500', @I_c. Batch. Number, 2, @I_c. Product. Name, @c. DBName, 0, @O_i. Error. State output select @i. Error = @@error if @i. Status = 0 and @i. Error <> 0 begin select @i. Status = @i. Error end if @i. Status <

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