Featured Post

Dynamics GP 2018 - Upcoming Features

We are counting the days for Dynamics GP 2018 release which is promising to introduce a new set of features that have been highly recogniz...

Sunday, May 31, 2015

SQL Server 2016 – Public Preview is now Available


It is with excitement that I am posting about the public preview for SQL server 2016 which is now available for download with powerful capabilities and features. As it has been described by Microsoft, it is the biggest leap forward in Microsoft’s data platform history with real-time analytics, rich visualization on mobile devices, built-in advanced analytics, new advanced security technology and new hybrid cloud scenarios.

The link to download and evaluate SQL Server 2016 can be reached on TechNet Evaluation Center on the following link: SQL Server 2016 Community Technology Preview 2

 SQL Server 2016 Data Sheet

This release is mostly perceived by experts as a “Massive” BI version of SQL server, as derived from the variety of features to be introduced encompassing several essentials areas. Here are some of the essentials features as included in the SQL Server 2016 data sheet (some but not limited to):

Performance Enhancements:
  • Operational Analytics
  • In-memory OLTP enhancement
  • Query Data Store
  • Native JSON
  • Temporal Database
Security Upgrades:
  •  Always encrypted
  • Row Level Security
  • Dynamic Data Masking
Higher Availability with enhanced Always On    

Improved Scalability with enhanced database caching.

Accessing Data (any data), PolyBase, Power Query for analytics and reporting and enhanced SSIS

Powerful insights on any device including Mobile BI and Enhanced reporting services

Build-in advanced analytics, which is bringing predictive analytic algorithms directly into SQL Server. 

Hybrid solution, including stretch database, Power BI with on-premises data and enhanced backup to Azure.
Details above have been retrieved from the SQL Server 2016 data sheet, which can be found on the following Download link

Best Regards,
Mahmoud M. AlSaadi 

Monday, May 18, 2015

Dynamics GP 2015 R2 – Upcoming Features Approaching


As derived from the well known six month release cycle for Dynamics GP, we are just few steps away from the release of Dynamics GP 2015 R2, for that I am listing some of the new features which are being published on Inside Microsoft Dynamics GP Blog by Pam Misialek.

As the norm goes, Dynamics GP Essential blog will provide illustrative articles for each of these features after the release of GP 2015 R2, just as it was for the prior releases; GP 2015 and GP 2013 R2 under Releases and Features tag.

The list below will be updated accordingly, as new articles are published:

  • Purchasing – All in one Document viewer
  • Purchasing – All in one Document view Filter
  • Purchasing – All in one Document view checkbox
  • Purchasing – All in one Document view – Options
  • Historical Received not Invoices Report
  • Payables Transaction workflow
  • Scriptable Provisioning and Management
  • Analytical Accounting Transaction Lists
  • Smartlist Designer Create View
  • Smartlist Designer view Workflow Type
  • Self Service User Tasks and Roles
  • Employee Self Service – W2

All in one Document Viewer Features:

In prior releases of Dynamics GP, attachments were reached through the original documents or master files to which they were attached. I have provided multiple SQL scripts to provide a detailed report for all the attachments existing in Dynamics GP, which can be found on GP Essentials – SQL Script Page. With the release of GP 2015 R2, there will be a complete elegant functionality to check all existing attachment for the purchasing module under what’s called “All in one document viewer”.

All in One Doc View

Checkbox: The all in one document viewer is designed to provide intelligent functionality and considerable user experience. Selecting one of the documents within the viewer such as a specific purchase order will apply to other related document such as receipts, invoices, payments and returns related to this PO.

All in One Doc View checkbox

Filter: To help the user better handle attachments and filter bulk data, there is a filter icon to allow the user to select specific documents such as open or historical.

All in One Doc View filter

 

Options: This controls the layout and elements to be included within the document viewer, it is pretty much needed when you need to exclude specific elements such as purchase orders (if you don’t use PO for instance)

All in One Doc View options

Payables Transaction Workflow

This is a new workflow type which allows the user to submit a single payable transaction for approval without the need to consider payable batch approval workflow. By now, clients can consider either the payable batch workflow or the payable transaction workflow, which are both available in GP 2015 R2.7

ap workflow 2015 r2

Scriptable Provisioning and Management

This feature will add APIs and PowerShell cmdlets that will enable automated/scriptable GP provisioning and management.

powershell 2015 r2

Analytical Accounting Transaction Lists

This is one thing that is quite essentials when having Analytical Accounting module enabled, the current analytical accounting reporting is enhanced by adding further more columns and information to enrich the value of the AA report, along with a distinguished enhancement made for the display of transaction dimension and associated codes.

AA Nav List 2015 R2

Smartlist Designer Create View

Within SmartList Designer a user can send a SmartList through workflow to create a SQL view based on the SmartList query.  This SQL view can then be used outside of GP for such purposes as creating reports using SQL reporting services or Power BI reports in Excel.  The view will also allow the user to publish the SmartList as a refreshable excel report from inside of Dynamics GP.”

SLD Create View 2015R2

 

Smartlist Designer View Workflow Type

Now you may control the creation of SQL-based views. These views will have to go through a predefined workflow to gain the required approvals.

sld workflow gp2015r2

 

Best Regards,
Mahmoud M. AlSaadi

Wednesday, May 13, 2015

When Writing an SQL Query, Performance Really Matters. “Dynamics GP Custom Report - Practical Case”


I have been working recently on a report to retrieve data through SQL script, the database I was dealing with is almost 50 GB and the number of daily transactions is huge. In such cases, performance really matters, it should be the main parameter to check through the QA phase. So I will initially provide the final presentation layer of the report then digs back in the SQL to see how calculations have been analyzed and considered.

The following case is a good example of how to minimize the execution duration from hours to only one second

Presentation Layer

Now, apparently, the design implies considering two primary characteristics which are:

  • Historical Report: There should be a date range determining the starting date and ending date of the reported data.
  • Cumulative: the report should provide running total column for both, the quantity balance and cost balance on a transaction-bases .

Technical Consideration

When writing the SQL query for this, there are plenty of ideas which could flash into your mind, such as; but not to limit:

  • Inner join
  • Sub query
  • Cursor

In this article, the comparison will be limited to both methods; sub query and cursor.

Sub query Method

DECLARE

    @ItemNumber NVARCHAR(MAX) ,

    @LocationCode NVARCHAR(MAX) ,

    @From_DateRange DATE ,

    @To_DateRange DATE

       

--SET @ItemNumber = ' '

--SET @LocationCode = '  '

--SET @From_DateRange = '  '

--SET @To_DateRange = '  '

 

SELECT  [Record index] ,

        [Document Type] ,

        [Item Number] ,

        [Document Date] ,

        [Document Number] ,

        [Location Code] ,

        [Unit of Measurement] ,

        [Quantity in Base] ,

        [Quantity Balance] ,

        [Unit Cost] ,

        [Extended Cost] ,

        [Cost Balance]

FROM    ( SELECT TOP 1

                    0 AS 'Record Index' ,

                    @ItemNumber AS 'Item Number' ,

                    @From_DateRange AS 'Document Date' ,

                    'Beginng Balance' AS 'Document Type' ,

                    '' AS 'Document Number' ,

                    @LocationCode AS 'Location Code' ,

                    '' AS 'Unit of Measurement' ,

                    0 AS 'Transaction Quantity' ,

                    ( SELECT    SUM(TRXQTYInBase)

                      FROM      dbo.SEE30303

                      WHERE     ITEMNMBR = @ItemNumber

                                AND LOCNCODE = @LocationCode

                                AND DOCDATE < @From_DateRange

                    ) AS 'Quantity in Base' ,

                    ( SELECT    SUM(TRXQTYInBase)

                      FROM      dbo.SEE30303

                      WHERE     ITEMNMBR = @ItemNumber

                                AND LOCNCODE = @LocationCode

                                AND DOCDATE < @From_DateRange

                    ) AS 'Quantity Balance' ,

                    0 AS 'Unit Cost' ,

                    ( SELECT    SUM(EXTDCOST)

                      FROM      dbo.SEE30303

                      WHERE     ITEMNMBR = @ItemNumber

                                AND LOCNCODE = @LocationCode

                                AND DOCDATE < @From_DateRange

                    ) AS 'Extended Cost' ,

                    ( SELECT    SUM(EXTDCOST)

                      FROM      dbo.SEE30303

                      WHERE     ITEMNMBR = @ItemNumber

                                AND LOCNCODE = @LocationCode

                                AND DOCDATE < @From_DateRange

                    ) AS 'Cost Balance'

          FROM      dbo.SEE30303

          UNION ALL

          SELECT    *

          FROM     

                ( SELECT  Record_Index ,

                    ITEMNMBR ,

                    DOCDATE ,

                    DOCUMENTType ,

                    DOCNUMBR ,

                    LOCNCODE ,

                    UOFM ,

                    TRXQTY ,

                    TRXQTYInBase ,

                     ( SELECT    SUM(TRXQTYInBase)

                           FROM     

                           (   SELECT   

                                  RANK() OVER

                                  ( ORDER BY DOCDATE,

                                         DOCNUMBR,

                                         DEX_ROW_ID

                                  ) AS Record_Index ,

                                  DOCDATE ,

                                  ITEMNMBR ,

                                  LOCNCODE ,

                                  TRXQTYInBase ,

                                  DOCNUMBR

                     FROM      dbo.SEE30303

                     WHERE    

                     ITEMNMBR = @ItemNumber

              AND LOCNCODE = @LocationCode

                           ) AS I

                           WHERE    

                           I.Record_Index <= X.RECORD_Index

                     ) AS 'Quantity Balance' ,

                     UNITCOST ,

                     EXTDCOST ,

                     ( SELECT    SUM(EXTDCOST)

                           FROM      ( SELECT   

                                                RANK() OVER

                                                ( ORDER BY DOCDATE,

                                                DOCNUMBR, DEX_ROW_ID )

                                                AS Record_Index ,

                                                DOCDATE ,

                                                ITEMNMBR ,

                                                LOCNCODE ,

                                                TRXQTYInBase ,

                                                DOCNUMBR ,

                                                EXTDCOST

                           FROM      dbo.SEE30303

                           WHERE    

                           ITEMNMBR = @ItemNumber

                     AND LOCNCODE = @LocationCode

                                         ) AS I

                           WHERE    

                           I.Record_Index <= X.RECORD_Index

                     ) AS 'Cost Balance'

       FROM      ( SELECT   

                           RANK() OVER

                           ( ORDER BY DOCDATE,

                           DOCNUMBR, DEX_ROW_ID )

                           AS Record_Index ,

                           ITEMNMBR ,

                           DOCDATE ,

                           CASE DOCTYPE

                                  WHEN 1 THEN 'Adjustment'

                                  WHEN 2 THEN 'Variance'

                                  WHEN 3 THEN 'Transfer'

                                  WHEN 4 THEN 'Purchase Receipt'

                                  WHEN 5 THEN 'Sales Return'

                                  WHEN 6 THEN 'Sales Invoice'

                                  WHEN 7 THEN 'Assembly'

                                  WHEN 8

                                  THEN 'POP Cost Adjustment'

                                  WHEN 11

                                  THEN 'IV Cost Adjustment'

                                  ELSE ''

                           END AS DOCUMENTType ,

                           DOCNUMBR ,

                           LOCNCODE ,

                           UOFM ,

                           TRXQTY ,

                           TRXQTYInBase ,

                           UNITCOST ,

                           EXTDCOST

              FROM      dbo.SEE30303 AS a

              WHERE     ITEMNMBR = @ItemNumber

                           AND LOCNCODE = @LocationCode

                     ) AS X

       ) AS Y

       WHERE     Y.DOCDATE BETWEEN @From_DateRange

                     AND     @To_DateRange

       ) AS RPT

 

 

When executing the query above, it was so clear that the performance is just too miserable for it to be deployed on a live environment with thousands of daily transactions. Here is the “Plan Diagram” as generated by SQL Sentry Plan Explorer (You may use the traditional SQL execution plan for this as well). Apparently, the same table is scanned too many times, it is quite consuming.

With each sub-query, SQL server rescans the table to get the running total for the specific transaction generated.

Plan Diagram

If we look at the “Top Operations” tab, we can see the table scan processing consuming up to 98 % of the overall process (15 % for each * 7 times)

Top Operations

Of course, there is no “balanced” DBA on earth who would even think of running this script on a live environment. Although, my curiosity pushed my sanity to give it a shot.

Database: Testdb ( back up from the live one  Winking smile
Database Size: above 50 GB
Table Size (SEE30303): 823,008

Number of Rows (SEE30303): 835160 rows

The selected parameters (Item, Location, Starting data and Ending Date) shall retrieve a data set of almost 7000 + rows.

After One hour and a half, I stopped the query from running since I got bored of waiting, and I still have around 4000 records. See below:

Un Satisfying Results

 

Cursor Method

For me, the case was quite tempting to roll up my sleeves and start the SQL journey to understand how I could end up with enhanced and better performance. For that, I picked the cursor method. Here is the code:

DECLARE @ResutlTable TABLE

    (

      [Date] DATETIME ,

      ITEMNumber NVARCHAR(MAX) ,

      LocationCode NVARCHAR(MAX) ,

      Quantity NUMERIC(38, 10) ,

      Quantitybalance NUMERIC(38, 10) ,

      Cost NUMERIC(38, 10) ,

      CostBalance NUMERIC(38, 10)

    )

 

DECLARE @From DATE ,

    @To DATE ,

    @Date DATETIME ,

    @ItemNumber NVARCHAR(MAX) ,

    @LocationCode NVARCHAR(MAX) ,

    @Quantity NUMERIC(38, 10) ,

    @Cost NUMERIC(38, 10) ,

    @QuantityBalance NUMERIC(38, 10) ,

    @CostBalance NUMERIC(38, 10)

      

      

      

SET @QuantityBalance = 0

  SET @CostBalance = 0

--SET @ItemNumber = ''

--SET @LocationCode = ''

--SET @From = ''

--SET @To =

 

 

DECLARE IV_CurSOR CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY

FOR

    SELECT  RANK() OVER

       ( ORDER BY DOCDATE,

         DOCNUMBR, DEX_ROW_ID )

         AS Record_Index ,

            ITEMNMBR ,

            LOCNCODE ,

            TRXQTYInBase ,

            EXTDCOST

    FROM    dbo.SEE30303

    WHERE   DOCDATE BETWEEN @FROM AND @To

            AND ITEMNMBR = @ItemNumber

            AND LOCNCODE = @LocationCode

    ORDER BY DOCDATE ,

            DOCNUMBR ,

            DEX_ROW_ID

 

OPEN IV_CurSOR;

 

FETCH NEXT FROM IV_CurSOR

       INTO @Date,

               @ItemNumber,

               @LocationCode,

               @Quantity,

    @Cost

 

WHILE @@FETCH_STATUS = 0

    BEGIN

 

        SET @QuantityBalance = @QuantityBalance + @Quantity

        SET @CostBalance = @CostBalance + @Cost

 

        INSERT  @ResutlTable

                ( [Date] ,

                  ITEMNumber ,

                  LocationCode ,

                  Quantity ,

                  Quantitybalance ,

                  Cost ,

                  CostBalance

                )

                SELECT  @Date ,

                        @ItemNumber ,

                        @LocationCode ,

                        @Quantity ,

                        @QuantityBalance ,

                        @Cost ,

                        @CostBalance;

 

        FETCH NEXT FROM IV_CurSOR

                     INTO @Date,

                            @ItemNumber,

                            @LocationCode,

                            @Quantity,

                            @Cost;

    END

 

CLOSE IV_CurSOR;

DEALLOCATE IV_CurSOR;

 

SELECT  *

FROM    @ResutlTable

 

 

Here is the plan diagram generated by the same tool above:

Cursor - Plan Diagram

Here it the top operations for the cursor:

Cursor Top Operations

The cursor took only one second to be executed, one the same database mentioned above with the same parameters. The SQL Server scans the table only one time, and as the cursor is fetching new parameters, local variables for the quantity balances and cost balances are updated accordingly.

See below, Now we are talking !

Cursor Result

The bottom line is, be quite aware when writing your SQL query, pay attention to the SQL Query analyzer, execution plan and other associated tools, because simply, performance really matters.

Related reports can be found on Dynamics GP Essentials - SQL Scripts Page
Codes for the reports above can be downloaded from here >> Download Link

Best Regards,
Mahmoud M. AlSaadi