💎🤖💫SQL Reloaded: Copilot Stories (Part VII: Using the sp_MSForEachTable Undocumented Stored Procedures in SQL Server)

Probably few know that SQL Server makes available a set of undocumented stored procedures that can be useful in certain scenarios that don't involve code that will deployed into production. One of them is sp_MSForEachTable on which I wrote several posts until now. The stored procedure executes one or more scripts based on a set of constraints against the subset of available tables. The first surprising fact is that Copilot does know the respective stored procedure and seems to correctly generate code for it!

Disclaimer: The below code was generated by Copilot via prompts used for demonstration purposes!

Please note that the following code is provided as it is and you can use it on your own risk! Don't run the code in a productive environment and make sure that you have a usable backup! Please consider all caution when you use a feature marked as undocumented!

Let's start with a simple script and increase the complexity of the queries as it goes!

Prompt:
Write a query that uses the sp_MSForEachTable stored procedure in SQL Server

Output:

-- query that uses this stored procedure to update a specific column in all tables:

EXEC sp_MSForEachTable 'UPDATE ? SET YourColumn = ''NewValue'' WHERE YourCondition'

/*
? is a placeholder for the table name.
YourColumn is the column you want to update.
NewValue is the new value you want to set.
YourCondition is the condition that must be met for the update to occur.*/

As the description says, "?" acts as placeholder for table's name while the other values need to be provided explicitly. The code can be used to overwrite the value of a column that exists in some of the tables. For example, when the data come over an interface from various system, it may happen that there is a mix of upper and lower case values that designate the same value. This might not be an issue for Dynamics AX but can create issues further on. 

Here's the actual code for performing the update:

-- update DataAreaId for DMEMF company
EXEC sp_MSForEachTable 'UPDATE ? SET DataAreaId = Upper(DataAreaId) WHERE DataAreaId = ''demf'''

Executing the code blindly can create serious damage, so always test first the code in a test environment and have a backup available! One needs to be careful if there are fields which have same name(s) but different meaning! It's important to perform a thorough analysis in this direction.

Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to select records from a set of tables.

Output:

-- return all columns
EXEC sp_MSForEachTable 'SELECT * FROM ?'

-- return specific columns based on condition
EXEC sp_MSForEachTable 'SELECT Column1, Column2 FROM ? WHERE YourCondition'

The second query will result in errors if the columns don't exist, though the execution runs further! The errors can be filtered out in Excel by sorting them accordingly.
The query works in general for combinations of columns available in many tables. It's the case of DataAreaId and RecId available in Dynamics 365 for Finance and Operations for a high percentage of the tables. RecId should be available for all the tables.

Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to count all the records from a table

Output:

-- retrieving the number of records for all tables
EXEC sp_MSForEachTable 'SELECT ''?'' AS TableName, COUNT(*) AS RecordCount FROM ?'

Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to select all the records from tables containing the word invent.

Output:

DECLARE @sql NVARCHAR(MAX)

SET @sql = ''
EXEC sp_MSForEachTable 
    @command1 = 'IF ''?'' LIKE ''%invent%'' BEGIN SET @sql = @sql + ''SELECT * FROM '' + ''?'' + ''; '' END',
    @replacechar = '?',
    @command2 = 'EXEC sp_executesql @sql'

PRINT @sql

Here's the working version of the query:

-- retrieve the records for tables having 'invent' intheir name
EXEC sp_MSForEachTable 
    @command1 = 'DECLARE  @sql nvarchar(max) = ''''
		IF ''?'' LIKE ''%invent%'' BEGIN SET @sql = @sql + ''SELECT * FROM '' + ''?'' + ''; '' END;
                    EXEC sp_executesql @sql;'

A few days later, using the same prompt in Edge I got the following answer, simpler and correct:

-- second version run on Edge Copilot
EXEC sp_MSForEachTable 
    @command1 = 'IF ''?'' LIKE ''%invent%'' 
                BEGIN 
                    PRINT ''?'' 
                    EXEC(''SELECT * FROM ?'') 
                END'

Prompt:
Write a query that uses the sp_MSForEachTable stored procedure when the DataAreaId field exists in the table.

Output:

DECLARE @sql NVARCHAR(MAX)

SET @sql = ''
EXEC sp_MSForEachTable 
    @command1 = 'IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = PARSENAME(''?'', 1) AND COLUMN_NAME = ''DataAreaId'') 
                 BEGIN 
                     SET @sql = @sql + ''SELECT * FROM '' + ''?'' + ''; '' 
                 END',
    @replacechar = '?',
    @command2 = 'EXEC sp_executesql @sql'

PRINT @sql

The query uses the metadata available to retrieve the tables in scope.

EXEC sp_MSForEachTable 
    @command1 = ' DECLARE  @sql nvarchar(max) = ''''
	IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = PARSENAME(''?'', 1) AND COLUMN_NAME = ''DataAreaId'') 
        BEGIN 
            SET @sql = @sql + ''SELECT * FROM '' + ''?'' + ''; '' 
			EXEC sp_executesql @sql
        END'

Here's the answer in Edge:

EXEC sp_MSForEachTable 
    @command1 = 'IF EXISTS (SELECT * FROM sys.columns WHERE object_id = OBJECT_ID(''?'' ) AND name = ''DataAreaId'') 
                BEGIN 
                    PRINT ''?'' 
                    EXEC(''SELECT * FROM ?'') 
                END'

Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to select records from a set of tables while it checks whether the fields exist in the table.

Output:

DECLARE @sql NVARCHAR(MAX)

SET @sql = ''
EXEC sp_MSForEachTable 
    @command1 = 'DECLARE @table NVARCHAR(128)
                 SET @table = PARSENAME(''?'', 1)
                 IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND COLUMN_NAME = ''Field1'')
                 AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND COLUMN_NAME = ''Field2'')
                 BEGIN
                     SET @sql = @sql + ''SELECT Field1, Field2 FROM '' + ''?'' + ''; ''
                 END',
    @replacechar = '?',
    @command2 = 'EXEC sp_executesql @sql'

PRINT @sql

The query used the metadata available in the INFORMATION_SCHHEMA.COLUMNS. Unfortunately, the code doesn't work as expected. The second command is not necessary for performing the task, so the code can be written as follows, and now it works:

EXEC sp_MSForEachTable 
    @command1 = 'DECLARE @table NVARCHAR(128), @sql nvarchar(max) = ''''
                 SET @table = PARSENAME(''?'', 1)
                 IF EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND COLUMN_NAME = ''DataAreaId'')
                 AND EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @table AND COLUMN_NAME = ''ItemId'')
                 BEGIN
                     SET @sql = @sql + ''SELECT DataAreaId, ItemId FROM '' + ''?'' + ''; ''
                 END
		 EXEC sp_executesql @sql'

The query is easy to modify to include more columns after the same pattern, if such tables exist after all (e.g. in Dynamics 365 one can search for the combination ItemId, DataAreaId and one or more Inventory dimension like InventSiteId, InventLocationId, etc.). 

Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to count all the records for a subset of tables.

Output:

DECLARE @sql NVARCHAR(MAX)

SET @sql = ''
EXEC sp_MSForEachTable 
    @command1 = 'IF ''?'' LIKE ''%subset%'' 
                 BEGIN 
                     SET @sql = @sql + ''SELECT ''''?'''' AS TableName, COUNT(*) AS RecordCount FROM '' + ''?'' + ''; '' 
                 END',
    @replacechar = '?',
    @command2 = 'EXEC sp_executesql @sql'

PRINT @sql

Unfortunately, that's not what I was expecting. So, let's give it a second try:

Prompt:
Write a query that uses the sp_MSForEachTable stored procedure to count all the records for a subset of the tables available in the SYS.TABLES.

Output:

EXEC sp_MSForEachTable 
    @command1 = 'IF OBJECT_ID(''?'' , ''U'') IN (SELECT OBJECT_ID FROM SYS.TABLES WHERE NAME IN (''YourTable1'', ''YourTable2'', ''YourTable3'')) 
                BEGIN 
                    PRINT ''?'' 
                    EXEC(''SELECT COUNT(*) FROM ?'') 
                END'

Replacing the tables names accordingly, the query becomes

EXEC sp_MSForEachTable 
    @command1 = 'IF OBJECT_ID(''?'' , ''U'') IN (SELECT OBJECT_ID FROM SYS.TABLES WHERE NAME IN (''InventTable'', ''InventDim'', ''InventDimCombination'')) 
                BEGIN 
                    PRINT ''?'' 
                    EXEC(''SELECT COUNT(*) FROM ?'') 
                END'

Comments:
1) Edge Copilot returned the expected solution directly and I wonder what the reason could be? It can be the fact that I run the tests on different machines at different points in time, though more likely the reason is that I used two different Copilots - from Edge, respectively Microsoft 365. The expectation was to get better answers in M365 Copilot, which wasn't the case in the above tests!

The two Copilots have different architectures and were trained with different sets of data, respectively have different sources. One important difference is that Edge Copilot primarily relies on web content and users' interactions within the browser, while M365 Copilot relies on data from the D365 services. Both use LLM and NLP, while M365 Copilot uses Microsoft Graph and Syntex.

One thing I remarked during the tests is that sometimes I get different answers to the same question also within the same Copilot. However, that's expected given that there are multiple ways of addressing the same problem! It would still be interesting to understand how a solution is chosen.

2) I seldom used sp_MSForEachTable with more than one parameter, preferring to prepare the needed statement beforehand, as in the working solutions above.

Happy coding!

Refer4ences:
[1] SQLShack (2017) An introduction to sp_MSforeachtable; run commands iteratively through all tables in a database [link]

Acronyms:
LLM - large language model
M365 - Microsoft 365
NLP - natural language processing

Previous Post <<||>> Next Post

💎🤖SQL Reloaded: Copilot Stories (Part III: Formatting Code via Prompts)

When debugging poorly formatted code, usually it helps to format first the code before doing any other work on it. I observed in the previous post that Copilot can be used to restructure the code based on a set of commands that summarize the best practices. 

Example 1: Simple Query

Let's start with a simple example from the web: 

Prompt:
Format the below code by using the available SQL best practices
1) each join condition is written on its own line
2) the commas are placed at the beginning of each line
3) use 3 letter based aliases for all tables
4) compact the code by reducing the indentation

SELECT po.ExpectedDeliveryDate, po.OrderDate, pol.LastReceiptDate, pol.LastEditedWhen, p.ValidFrom, p.ValidTo FROM Purchasing.PurchaseOrders AS po JOIN Purchasing.PurchaseOrderLines AS pol ON pol.PurchaseOrderID = po.PurchaseOrderID JOIN Application.People AS p ON p.PersonID = po.ContactPersonID WHERE LastReceiptDate > '1/1/2016';

Output:

SELECT po.ExpectedDeliveryDate
    , po.OrderDate
    , pol.LastReceiptDate
    , pol.LastEditedWhen
    , p.ValidFrom
    , p.ValidTo
FROM Purchasing.PurchaseOrders AS po
JOIN Purchasing.PurchaseOrderLines AS pol
    ON pol.PurchaseOrderID = po.PurchaseOrderID
JOIN Application.People AS p
    ON p.PersonID = po.ContactPersonID
WHERE LastReceiptDate > '1/1/2016';

Of course, the colors were added by the hilite.me formatting to better identify the reserved words. The output of the above prompt is not exactly what I intended in 3), so repeating the slightly changed command seems to obtain the expected result. Even if a 3-letter alias was not used for all tables, the query looks better:

Prompt:
Use 3 letter based aliases for all tables.

Output:

SELECT po.ExpectedDeliveryDate
    , po.OrderDate
    , pol.LastReceiptDate
    , pol.LastEditedWhen
    , ppl.ValidFrom
    , ppl.ValidTo
FROM Purchasing.PurchaseOrders AS po
JOIN Purchasing.PurchaseOrderLines AS pol
    ON pol.PurchaseOrderID = po.PurchaseOrderID
JOIN Application.People AS ppl
    ON ppl.PersonID = po.ContactPersonID
WHERE LastReceiptDate > '1/1/2016';

The script looks good, though it would be great if we could align the constraints with the order of the tables.

Prompt:
Rewrite the query by using the first table from the join in the join constraint.

Output:

SELECT po.ExpectedDeliveryDate
    , po.OrderDate
    , pol.LastReceiptDate
    , pol.LastEditedWhen
    , ppl.ValidFrom
    , ppl.ValidTo
FROM Purchasing.PurchaseOrders AS po
JOIN Purchasing.PurchaseOrderLines AS pol
    ON po.PurchaseOrderID = pol.PurchaseOrderID
JOIN Application.People AS ppl
    ON po.ContactPersonID = ppl.PersonID
WHERE LastReceiptDate > '1/1/2016';

As observed in the last post, multiple scripts can be transformed withing the same prompt, especially when the statements were generated by Copilot as well. 

Example 2: Complex Dynamics AX Query

How about a more complex query? 
Let's consider the below query written for Dynamics AX:

Prompt:
Format the below code by using the available SQL best practices
1) each join condition is written on its own line
2) the commas are placed at the beginning of each line
3) use 3 letter based aliases for all tables
4) compact the code by reducing the indentation

SELECT ADR.DATAAREAID, DPT.FIRSTNAME, DPT.LASTNAME, DPT.LANGUAGEID, DPR.PartyId, DPR.IsPrimary, CASE DPR.Status WHEN 0 THEN 'Active' WHEN 1 THEN 'Inactive' END Status, NullIf(Cast(DPR.ValidFromDateTime as Date), '31-12-1900') ValidFromDateTime, NullIf(Cast(DPR.ValidToDateTime as Date), '30-12-2154') ValidToDateTime, DPR.Shared  , ADR.ADDRTABLEID , ADR.ADDRRECID , ADR.TYPE , CASE ADR.TYPE WHEN 0 THEN 'None' WHEN 1 THEN 'Invoice' WHEN 2 THEN 'Delivery'WHEN 3 THEN 'Alt. delivery'WHEN 4 THEN 'SWIFT'WHEN 5 THEN 'Payment' WHEN 6 THEN 'Service' WHEN 7 THEN 'Home' WHEN 8 THEN 'Other' WHEN 9 THEN 'Business' WHEN 10 THEN 'RemitTo' WHEN 11 THEN 'Third-party shipping address' END AddressDescription, ADR.NAME , ADR.ADDRESS , ADR.STREET, ADR.ZIPCODE , ADR.CITY , ADR.STATE , ADR.COUNTY , ADR.COUNTRYREGIONID , ADR.PHONE , ADR.TELEFAX , ADR.URL FROM ADDRESS ADR JOIN DIRPARTYADDRESSRELATIONSHI1066 DAC ON DAC.REFCOMPANYID = ADR.DATAAREAID and DAC.ADDRESSRECID = ADR.RECID and DAC.DATAAREAID = ADR.DATAAREAID JOIN DIRPARTYADDRESSRELATIONSHIP DPR on DPR.RECID = DAC.PARTYADDRESSRELATIONSHIPRECID and DPR.DATAAREAID = DAC.DATAAREAID JOIN DIRPARTYTABLE DPT on DPT.PARTYID = DPR.PARTYID and DPT.DATAAREAID = DPR.DATAAREAID  JOIN EmplTable EMP on DPT.PARTYID = EMP.PARTYID and DPT.DATAAREAID = EMP.DATAAREAID  

Output:

SELECT 
    ADR.DATAAREAID
    , DPT.FIRSTNAME
    , DPT.LASTNAME
    , DPT.LANGUAGEID
    , DPR.PartyId
    , DPR.IsPrimary
    , CASE DPR.Status 
        WHEN 0 THEN 'Active' 
        WHEN 1 THEN 'Inactive' 
      END AS Status
    , NullIf(Cast(DPR.ValidFromDateTime AS DATE), '31-12-1900') AS ValidFromDateTime
    , NullIf(Cast(DPR.ValidToDateTime AS DATE), '30-12-2154') AS ValidToDateTime
    , DPR.Shared
    , ADR.ADDRTABLEID
    , ADR.ADDRRECID
    , ADR.TYPE
    , CASE ADR.TYPE 
        WHEN 0 THEN 'None' 
        WHEN 1 THEN 'Invoice' 
        WHEN 2 THEN 'Delivery'
        WHEN 3 THEN 'Alt. delivery'
        WHEN 4 THEN 'SWIFT'
        WHEN 5 THEN 'Payment' 
        WHEN 6 THEN 'Service' 
        WHEN 7 THEN 'Home' 
        WHEN 8 THEN 'Other' 
        WHEN 9 THEN 'Business' 
        WHEN 10 THEN 'RemitTo' 
        WHEN 11 THEN 'Third-party shipping address' 
      END AS AddressDescription
    , ADR.NAME
    , ADR.ADDRESS
    , ADR.STREET
    , ADR.ZIPCODE
    , ADR.CITY
    , ADR.STATE
    , ADR.COUNTY
    , ADR.COUNTRYREGIONID
    , ADR.PHONE
    , ADR.TELEFAX
    , ADR.URL
FROM 
    ADDRESS ADR
JOIN 
    DIRPARTYADDRESSRELATIONSHI1066 DAC 
    ON DAC.REFCOMPANYID = ADR.DATAAREAID 
    AND DAC.ADDRESSRECID = ADR.RECID 
    AND DAC.DATAAREAID = ADR.DATAAREAID
JOIN 
    DIRPARTYADDRESSRELATIONSHIP DPR 
    ON DPR.RECID = DAC.PARTYADDRESSRELATIONSHIPRECID 
    AND DPR.DATAAREAID = DAC.DATAAREAID
JOIN 
    DIRPARTYTABLE DPT 
    ON DPT.PARTYID = DPR.PARTYID 
    AND DPT.DATAAREAID = DPR.DATAAREAID
JOIN 
    EmplTable EMP 
    ON DPT.PARTYID = EMP.PARTYID 
    AND DPT.DATAAREAID = EMP.DATAAREAID;

The query looks good, though it needs more transformations:

Input (to be run line by line):

Rewrite the query by writing names in camel with the beginning letter in upper case.
Rewrite the query by writing table aliases in upper case.

Output:

SELECT 
      ADR.DataAreaId
    , DPT.FirstName
    , DPT.LastName
    , DPT.LanguageId
    , DPR.PartyId
    , DPR.IsPrimary
    , CASE DPR.Status 
        WHEN 0 THEN 'Active' 
        WHEN 1 THEN 'Inactive' 
      END AS Status
    , NullIf(Cast(DPR.ValidFromDateTime AS DATE), '31-12-1900') AS ValidFromDateTime
    , NullIf(Cast(DPR.ValidToDateTime AS DATE), '30-12-2154') AS ValidToDateTime
    , DPR.Shared
    , ADR.AddrTableId
    , ADR.AddrRecId
    , ADR.Type
    , CASE ADR.Type 
        WHEN 0 THEN 'None' 
        WHEN 1 THEN 'Invoice' 
        WHEN 2 THEN 'Delivery' 
        WHEN 3 THEN 'Alt. Delivery' 
        WHEN 4 THEN 'Swift' 
        WHEN 5 THEN 'Payment' 
        WHEN 6 THEN 'Service' 
        WHEN 7 THEN 'Home' 
        WHEN 8 THEN 'Other' 
        WHEN 9 THEN 'Business' 
        WHEN 10 THEN 'RemitTo' 
        WHEN 11 THEN 'Third-Party Shipping Address' 
      END AS AddressDescription
    , ADR.Name
    , ADR.Address
    , ADR.Street
    , ADR.ZipCode
    , ADR.City
    , ADR.State
    , ADR.County
    , ADR.CountryRegionId
    , ADR.Phone
    , ADR.Telefax
    , ADR.Url
FROM 
    dbo.Address ADR
JOIN 
    dbo.DirPartyAddressRelationship1066 DAC 
    ON DAC.RefCompanyId = ADR.DataAreaId 
    AND DAC.AddressRecId = ADR.RecId 
    AND DAC.DataAreaId = ADR.DataAreaId
JOIN 
    dbo.DirPartyAddressRelationship DPR 
    ON DPR.RecId = DAC.PartyAddressRelationshipRecId 
    AND DPR.DataAreaId = DAC.DataAreaId
JOIN 
    dbo.DirPartyTable DPT 
    ON DPT.PartyId = DPR.PartyId 
    AND DPT.DataAreaId = DPR.DataAreaId
JOIN 
    dbo.EmplTable EMP 
    ON DPT.PartyId = EMP.PartyId 
    AND DPT.DataAreaId = EMP.DataAreaId;

Example 3: Simple X++ Query

How about code written in other languages (e.g. X++)?

I tried to transform the definition behind X++ form from Dynamics 365 for Finance without effect

    InventItemPurchSetup.CalendarDays = NoYes::No;
    InventItemPurchSetup.HighestQty = 0.00;
    InventItemPurchSetup.InventDimId = "AllBlank";
    InventItemPurchSetup.InventDimIdDefault = "AllBlank";
    InventItemPurchSetup.ItemId = "1000";
    InventItemPurchSetup.LeadTime = 0;
    InventItemPurchSetup.LowestQty = 0.00;
    InventItemPurchSetup.MandatoryInventLocation = NoYes::No;
    InventItemPurchSetup.MandatoryInventSite = NoYes::No;
    InventItemPurchSetup.MultipleQty = 0.00;
    InventItemPurchSetup.Override = NoYes::No;
    InventItemPurchSetup.StandardQty = 0.00;
    InventItemPurchSetup.Stopped = NoYes::No;
    InventItemPurchSetup.Sequence = 0;
    InventItemPurchSetup.OverrideDefaultStorageDimensions = NoYes::No;
    InventItemPurchSetup.SysRowVersionNumber = 0;
    InventItemPurchSetup.insert();

Multiple attempts were performed without any progress, though after a break I was able to apply the following transformations:

Prompts:

Replace the word "InventItemPurchSetup") with ", IPS." and remove the text after the equality sign.
Add a comma in front of each line and remove the semicolon at the end.

With a few manual change the code becomes:

SELECT IPS.CalendarDays 
, IPS.HighestQty 
, IPS.InventDimId 
, IPS.InventDimIdDefault 
, IPS.ItemId 
, IPS.LeadTime 
, IPS.LowestQty 
, IPS.MandatoryInventLocation 
, IPS.MandatoryInventSite 
, IPS.MultipleQty 
, IPS.Override 
, IPS.StandardQty 
, IPS.Stopped 
, IPS.Sequence 
, IPS.OverrideDefaultStorageDimensions 
, IPS.SysRowVersionNumber 
FROM dbo.InventItemPurchSetup IPs

The prompts reflect what I was doings in Excel manually via a replace, respectively the use of "Text to columns" functionality".

 

Probably, there's the opportunity to test further various ideas. Frankly, the transformation from upper case to camel could have helped me in the past a lot!

Disclaimer:
The queries were not tested after the changes were performed (because of unavailability of the systems).

Happy coding!

Previous Post <<||>> Next Post

#️⃣Software Engineering: Mea Culpa (Part V: All-Knowing Developers are Back in Demand?)

Software Engineering Series

I’ve been reading many job descriptions lately related to my experience and curiously or not I observed that many organizations look for developers with Microsoft Dynamics experience in the CRM, respectively Finance and Operations (F&O) and Business Central (BC) areas. It’s a good sign that the adoption of Microsoft solutions for CRM and ERP increases, especially when one considers the progress made in the BI and AI areas with the introduction of Microsoft Fabric, which gives Microsoft a considerable boost. Conversely, it seems that the "developers are good for everything" syntagma is back, at least from what one reads in job descriptions. 

Of course, it’s useful to have an inhouse developer who can address all the aspects of an implementation, though that’s a lot to ask considering the different non-programming areas that need to be addressed. It’s true that a developer with experience can handle Requirements, Data and Process Management, respectively Data Migrations and Business Intelligence topics, though if one considers that each of the topics can easily become a full-time job before, during and post-project implementations. I’ve been there and I (hopefully) know that the jobs imply. Even if an experienced programmer can easily handle the different aspects, there will be also times when all the topics combined will be too much for a person!

It's not a novelty that job descriptions are treated like Christmas lists, but it’s difficult to differentiate between essential and nonessential skillset. I read many jobs descriptions lately in which among a huge list of demands, one of the requirements is to program in the F&O framework, sign that D365 programmers are in high demand. I worked for many years as programmer and Software Engineer, respectively in the BI area, where SQL and non-SQL code is needed. Even if I can understand the code in F&O, does it make sense to learn now to program in X++ and the whole framework? 

It's never too late to learn new tricks, respectively another programming language and/or framework. It even helps to provide better solutions in usual areas, though frankly I would invest my time in other areas, and AI-related topics like AI prompting or Data Science seem to be more interesting on the long run, especially when they are already in demand!

There seems to be a tendency for Data Science professionals to do everything, building their own solutions, ignoring the experience accumulated respectively the data models built in BI and Data Analytics areas, as if the topics and data models are unrelated! It’s also true that AI-modeling comes with its own requirements in what concerns data modeling (e.g. translating non-numeric to numeric values), though I believe that common ground can be found!

Similarly, the notebook-based programming seems to replicate logic in each solution, which occasionally makes sense, though personally I wouldn’t recommend it as practice! The other day, I was looking at code developed in Python to mimic the joining of tables, when a view with the same could be easier (re)used, maintained, read and probably more efficient, even if different engines will be used. It will be interesting to see how the mix of spaghetti solutions will evolve over time. There are developers already complaining of the number of objects used in the process by building logic for each layer from the medallion architecture! Even if it makes sense from architectural considerations, it will become a nightmare in time.

One can wonder also about nomenclature used – Data Engineer or Prompt Engineering for the simple manipulation of data between structures in data transformations, respectively for structuring the prompts for AI. I believe that engineering involves more than this, no matter the context! 

Previous Post <<||>> Next Post

🧭🪄☯Business Intelligence: SQL Server Reporting Services (The Good, the Bad and the Ugly)

SQL Server Reporting Services (SSRS) is the oldest solution from the modern Microsoft BI stack. Released as add-on to SQL Server 2000, it allows covering most of an organization's reporting requirements, either if we talk about tables, matrices or crosstab displays, raw data, aggregations, KPIs or visualizations like charts, gauges, sparklines, tree maps or sunbursts.

The Good: Once you have a SQL query based on any standard data sources (SQL Server, Oracle, SharePoint, OData, XML, etc.), it can be used in just a few minutes to create a report with the help of a wizard. Sure, adding the needed formatting, parameters, custom code, drilldown and drill-through functionality might take some effort, though in less than an hour you have a running report. The use of templates and a custom branding allows providing a common experience across the enterprise. 

The whole service is available once you have a SQL Server license, fact that makes from the SSRS a cost-effective tool. The shallow learning curve and the integration with SharePoint facilitates the development and consumption of reports.

With its pixel-accurate display of data, SSRS is ideal for printing business documents. This was probably one of the reasons why SSRS become with Microsoft Dynamics AX 2009 also the main reporting platform for the further versions. One can use an AX 2009 class as source for the report, or directly use the base tables, which can increase reports’ performance in the detriment of reengineering the logic from AX 2009. With a few exceptions in finance area the reporting logic is easy to build.  

With SQL Server 2016 it got a HTML5 rendering engine, while with SSRS 2017 it supports a responsive web design. The integration of the SSRS and Power BI environments has the chance to further extend the value provided by this powerful combination, however it depends also in which direction Microsoft will develop this idea.   

The Bad: One of the important downsides of SSRS is that it doesn’t allow custom authentication. Even if some examples exist on the Web, it’s hard to understand Microsoft’s stubbornness of not providing this by design. 

Because SSRS still uses an older MS Office driver, it allows exporting only 65536 records to Excel, fact that makes data consumption more complicated. In addition, the pixel-perfect isn’t that perfect, the introduction of empty columns when exporting to Excel, adds some unnecessary burden.

In total, the progress made by SSRS between the various releases is small when compared with the changes suffered by SQL Server. Even if the visualization capabilities cover most of the requests, it loses field when compared with Power BI and similar visualization tools. 

The Ugly: SSRS, as the typical BI developer knows it, is different than the architecture frameworks provided when working with Business Central, respectively Dynamics 365 and CRM. Even if there are maybe entitled reasons, Microsoft failed to unite the three architectures into a flexible solution. Almost all the examples available on the Web target CRM, and frankly it’s hard to understand that. It feels like Microsoft wants to sabotage their own product?! What’s hard to understand is that besides SSRS and Power BI Microsoft has several other reporting tools for Dynamics 365. Building reports for Business Central or Dynamics 365 requires certain skills, while the development time increased considerably, thus SSRS losing from the appeal it previously had, allowing other tools to join the landscape (e.g. electronic documents).

SSRS can’t be smoothly integrated with Office 365 Online, remaining mainly a solution for on-premise architectures.  This can become a bottleneck when the customers move to the cloud, the BI strategy needing to be eventually rethought as well. 

Previous Post <<||>> Next Post

💫ERP Systems: AX 2009 vs. D365 FO - Product Prices with two Different Queries

In Dynamics AX 2009 as well in Dynamics 365 for Finance and Operations (D365 FO) the Inventory, Purchase and Sales Prices are stored in InventTableModule table on separate rows, independently of the Products, stored in InventTable. Thus for each Product there are three rows that need to be joined. To get all the Prices one needs to write a query like this one:

SELECT ITM.DataAreaId 
, ITM.ItemId 
, ITM.ItemName 
, ILP.UnitId InventUnitId

, IPP.UnitId PurchUnitId
, ISP.UnitId USalesUnitId
, ILP.Price InventPrice
, IPP.Price PurchPrice
, ISP.Price SalesPrice
FROM dbo.InventTable ITM
     JOIN dbo.InventTableModule ILP
       ON ITM.ItemId = ILP.ItemId
      AND ITM.DATAAREAID = ILP.DATAAREAID 
      AND ILP.ModuleType = 0 -- Inventory
     JOIN dbo.InventTableModule IPP
       ON ITM.ItemId = IPP.ItemId
      AND ITM.DATAAREAID = IPP.DATAAREAID 
      AND IPP.ModuleType = 1 -- Purchasing
     JOIN dbo.InventTableModule ISP
       ON ITM.ItemId = ISP.ItemId
      AND ITM.DATAAREAID = ISP.DATAAREAID 
      AND ISP.ModuleType = 2 -- Sales 
WHERE ITM.DataAreaId = 'abc'

Looking at the query plan one can see that SQL Server uses three Merge Joins together with a Clustered Index Seek, and recommends using a Nonclustered Index to increase the performance:

To avoid needing to perform three joins, one can rewrite the query with the help of a GROUP BY, thus reducing the number of Merge Joins from three to one:

SELECT ITD.DataAreaID 
, ITD.ItemId 
, ITM.ItemName  
, ITD.InventPrice
, ITD.InventPriceUnit
, ITD.InventUnitId
, ITD.PurchPrice
, ITD.PurchPriceUnit
, ITD.PurchUnitId
, ITD.SalesPrice
, ITD.SalesPriceUnit
, ITD.SalesUnitId
FROM dbo.InventTable ITM
     LEFT JOIN (—price details
     SELECT ITD.ITEMID
     , ITD.DATAAREAID 
     , Max(CASE ITD.ModuleType WHEN 0 THEN ITD.Price END) InventPrice
     , Max(CASE ITD.ModuleType WHEN 0 THEN ITD.PriceUnit END) InventPriceUnit
     , Max(CASE ITD.ModuleType WHEN 0 THEN ITD.UnitId END) InventUnitId
     , Max(CASE ITD.ModuleType WHEN 1 THEN ITD.Price END) PurchPrice
     , Max(CASE ITD.ModuleType WHEN 1 THEN ITD.PriceUnit END) PurchPriceUnit
     , Max(CASE ITD.ModuleType WHEN 1 THEN ITD.UnitId END) PurchUnitId
     , Max(CASE ITD.ModuleType WHEN 2 THEN ITD.Price END) SalesPrice
     , Max(CASE ITD.ModuleType WHEN 2 THEN ITD.PriceUnit END) SalesPriceUnit
     , Max(CASE ITD.ModuleType WHEN 2 THEN ITD.UnitId END) SalesUnitId
     FROM dbo.InventTableModule ITD
     GROUP BY ITD.ITEMID
     , ITD.DATAAREAID 
    ) ITD
       ON ITD.ITEMID = ITM.ITEMID
      AND ITD.DATAAREAID = ITM.DATAAREAID
WHERE ITD.DataAreaID = 'abc'
ORDER BY ITD.ItemId

And here’s the plan for it:

It seems that despite the overhead introduced by the GROUP BY, the second query performs better, at least when all or almost all records are performed.

In Dynamics AX there were seldom the occasions when I needed to write similar queries. Probably, most of the cases are related to the use of window functions (e.g. the first n Vendors or Vendor Prices). On the other side, a bug in previous versions of Oracle, which was limiting the number of JOINs that could be used, made me consider such queries also when was maybe not the case.  

What about you? Did you had the chance to write similar queries? What made you consider the second type of query?

Happy coding!

Previous Post <<||>> Next Post

💫ERP Systems: Dynamics AX 2009 (Part I: Deleting Obsolete Companies)

Introduction   

During implementations, migrations and other projects are created in Dynamics AX temporary companies (aka legal entities, data areas) that aren’t needed anymore once they fulfilled their purpose. Excepting the fact that obsolete companies occupy space in the data center, under certain circumstances they can lead to performance problems. The logical thing to do would be to delete the obsolete companies as long there’s no further demand from the business.    

In what follows we will look at several methods for deleting obsolete companies. The scripts were tested in Dynamics AX 2009, and more likely they’ll work in coming versions as long the data model behind was kept.

Warning:
Please note that the scripts are provided “AS IS” only to exemplify a technique and they come without any warranty! Before attempting any of the methods described here, review the comments from “Further Considerations” section!

Method 1: Using DynamcsAX Built-In Functionality   

Dynamics AX 2009 provides built-in functionality for deleting a company, however when the volume of data in the system goes above a certain limit the functionality starts to perform poorly, even when run directly on an AOS. (It is recommended to run long-running administration jobs directly on the AOS rather than clients.)    For example, it was attempted to use this method to delete several companies in Dynamics AX Test environment. By the first company the deletion job needed a few hours, while by the second company the job hasn’t finished after two days, being thus forced to stop it. After two further failed attempts it came the time to look for another solution.

Warning:
It seems that this solution can lead to orphaned data (see [1]). So, even if you are using this method, you might need to consider one of the following methods as well.

Method 2: Using sp_MSforEachTable   

In almost all tables in AX the company is stored in a DataAreaId attribute. Over this attribute the records belonging to a company are logically partitioned. This allows writing a script via the undocumented sp_MSforEachTable stored procedure:

--delete the data for one data area
sp_MSforEachTable @command1 = 'DELETE FROM ? WHERE DataAreaId = ''m01'''

An error with be thrown for the tables that don’t contain the DataAreaId attribute:

"Msg 207, Level 16, State 1, Line 1"

Invalid column name 'DataAreaId'.The script can be extended to delete in the same step two or more companies:

--delete the data for multiple data areas
 sp_MSforEachTable @command1 = 'DELETE FROM ? WHERE DataAreaId IN (''m01'', ''m02'')'

During the first test the script needed half of hour to run, however a few tables  in which the company is stored in other attributes remained untouched. One can either search for such tables manually, via a script, or run the built-in AX functionality. We opted for running the built-in functionality, which managed to delete the remaining data relatively fast.

Warning:
Microsoft doesn’t support this method and can be used when the volume of obsolete data is relatively small!    What does it mean relatively small? The most important limitation of this method is the transaction log, considering that the deleted data are logged. One can either change log’s size to accommodate the volume of data to be deleted or run the deletion only for a subset of the tables. (Changing the recovery model to “simple” or “bulk-logged” won’t make a difference.)

The second important limitation is the available memory, once the available memory is reached SQL Server having to paginate the data, fact that could lead to further disk space consumed.    Other limitations have more with the performance to do, e.g. each deletion is reflected also in the indexes. One might consider for example dropping the indexes before deletion and recreating them afterwards.

Method 3: Using a Cursor    

Instead of using the undocumented sp_MSforEachTable stored procedure, the loop can be performed via a cursor (see [1]). This method is advantageous when the deletion needs to be performed only for a subset of tables one could use a cursor. The deletion can be grouped together with other activities and run together.

Method 4: Using „Shadow“ Tables    

When the volume of data available is huge, and the volume of data that remain in the table is small compared with the overall data, it might be useful to consider using “shadow” tables. One can take advantage of the fact that a truncate command performs incomparable better than a delete command.  To use a truncate on a table, the records that need to be kept could be saved temporarily to a copy (aka “shadow”) of the table, the truncate then applied, and the copied records could be moved back. The following scripts exemplify the logic needed to delete the records from InventDim (inventory dimensions) table:

-- (optional) prove the number of records
SELECT count(*) 
FROM dbo.InventDim 
WHERE DataAreaId = 'm01'

-- create the “shadow” table
CREATE TABLE [dbo].[INVENTDIM_Dump](
[INVENTDIMID] [nvarchar](30) NOT NULL,
[INVENTBATCHID] [nvarchar](21) NOT NULL,
[WMSLOCATIONID] [nvarchar](12) NOT NULL,
[INVENTSERIALID] [nvarchar](21) NOT NULL,
[INVENTLOCATIONID] [nvarchar](10) NOT NULL,
[CONFIGID] [nvarchar](10) NOT NULL,
[INVENTSIZEID] [nvarchar](10) NOT NULL,
[INVENTCOLORID] [nvarchar](10) NOT NULL,
[INVENTSITEID] [nvarchar](10) NOT NULL,
[DATAAREAID] [nvarchar](4) NOT NULL,
[RECVERSION] [int] NOT NULL,
[RECID] [bigint] NOT NULL,
[WMSPALLETID] [nvarchar](18) NOT NULL,
[INVENTSTYLEID] [nvarchar](10) NOT NULL
) ON [PRIMARY]

-- copy the data into the “shadow” table
INSERT INTO [dbo].[InventDim_Dump] WITH (TABLOCK)
SELECT *
FROM [dbo].[InventDim] 
WHERE DataAreaId = 'm01'

-- truncate the data frome the main table 
--TRUNCATE TABLE [dbo].[InventDim]

-- copy the data back
INSERT INTO [dbo].[InventDim] WITH (TABLOCK)
SELECT *
FROM [dbo].[InventDim_Dump]

-- (optional) prove whether the IDs were correctly copied 
SELECT count(*)
FROM [dbo].[InventDim] A
JOIN [dbo].[InventDim_Dump] B
ON A.recid = B.RECID 
AND A. DATAAREAID = B.DATAAREAID 
WHERE A.DataAreaId = 'm01'

-- drop the „shadow“ table 
--DROP TABLE[dbo].[InventDim_Dump]

 As can be seen the “shadow” tables are simplified versions of the original tables, without constraints or indexes. They can be eventually created in another schema or even other database.   

Except the script for table’s creation in the other scripts table’s name can be easily replaced in the editor via the search and replace functionality, trick that reduces considerably the time needed for development. I needed on average 5 minutes for each table, plus 3-4 hours for further tests.    

The optional steps are more for exemplification and can be eventually removed.  

The Tablock hint used in inserts provides better performance and minimizes the volume of data logged.    

I used this method only for the tables having more than 3 million records, around 50 tables in total. Between them there were a few tables having 20-200 GB worth of records. I started with these big tables and figured out that also smaller tables could benefit from this method. A few minutes gained for each small table resulted in the end in a gain of a couple of hours.

The remained records were 0-25% of the initial tables.   

In theory, these steps could be performed within a cursor in which the creation of the “shadow” tables could be automated via table metadata as well. This approach will pay-off especially when the schema is not fixed, or the procedure needs to be repeated on different schemas.

Method 5: Delete Records in Batches    

There will be a point beyond which the performance provided by the fourth method will deprecate considerably. This point is based on the volume of records available in the table, and the records needed to be inserted back and forth. Without further tests, I suppose that this point lies in the 50-75% interval. Beyond this point for big tables in range of 10x or 100x GB it might be useful to delete the data in batches. A push in this direction might be constrained by the need to shrink the transaction log in between the deletes. The query could be written as follow:

-- deleting top x records 
DELETE top 10000
FROM dbo.InventDim WITH (TABLOCK)
WHERE DataAreaId = ‘m01’

The query can be included in a loop or run manually until no records are returned. It can be tested with different batch sizes to determine the best solution. In between is recommended to check also the growth of the log file and truncate it accordingly when needed.

Method 6: Using X++ code

For those having some basic knowledge of X++ and Dynamics AX classes, a solution based on deleting data via AX code could prove to be a better solution as standard functionality can be leveraged, functionality that eventually considers also the business logic implemented. The downside is the code that need to be written for this purpose, however there are already some examples available on the web (see [4]).

Hint:
In AX 2012 built-in support for batch deletes was added via the delete_from statement (see [3]).

Further Aspects     

Before attempting a deletion, it might be useful to analyze how many records will be deleted from each table, and eventually devise different scenarios for specific table categories. To get the number of records one can use either the built-in functionality from AX or use the sp_MSforEachTable stored procedure and export the results to text, following to overwork the data further in Excel:

-- listing the number of records per company 
sp_MSforEachTable @command1 = 'SELECT dataareaid, ''?'' table_name, count(*) no_records FROM ? WHERE DataAreaId IN (''m01'', ''m02'') GROUP BY dataareaid'

The results can be used also to approximate the space occupied by the data.   

Independently of the method used it is recommended to restrict users‘ access to the system and to deactivate the scheduled AX or SQL Server jobs. This will ensure that no blockings will occur in the system during the respective time.    

As data are synchronized between the AOS’s and the database, it is recommended to shut down the not needed AOS services before the deletions are performed, and restart them once all activities were performed.   

To minimize the risks associated with the loss of data it’s recommended to perform a backup of the database(s) before performing any changes.    

By deleting the data directly on the database, the business logic from AX (including customizations) is skipped. In theory this can lead to logical inconsistencies, however considering that all the data for a company are deleted, the risks are very small, unless intercompanies are involved.   

After the data are deleted it is recommended to recreate the indexes and update the statistics on the tables.  

Check whether the transaction log can accommodate the volume of records to be deleted! In extreme cases your SQL Server might crash! From this consideration it might be advantageous to delete only a company at a time.    

Based on the volume of data available in the transaction log it might be needed to truncate the log(s) between the steps, as well at the end.  

After the principle “better safe than sorry”, it might be a good idea to check the physical and logical consistency of the data before letting the users in.   

To minimize the impact on the business, it is recommended to perform the deletion outside the working hours, otherwise the action can lead to blocking and even deadlocks in the system.     Always attempt to use standard functionality and resort to other methods only when there’s no way around it.

It is recommended to always test the scripts thoroughly in the test environment before attempting their productive usage!

Previous Post <<||>> Next Post

References:
[1] Microsoft Dynamics AX Technical Support Blog (2010) How to delete orphaned data remained from deleted company?, by Martin Falta [link] 
[2] Art of Creation (2010) Delete an AX company on SQL [link]
[3] MSDN (2012) delete_from Statement [link]
4] Kevin’s blog (2017) Dynamics Ax 2012 History cleanup, by Kevin Roos [link]