Extensible Data Security examples – Secure by retail channel
When I initially learned AX 2012, before it got released, I was quite enthusiastic about the security changes and the organization hierarchies. Then also I learned about the eXtensible Data Security (XDS) concept. While working on one of my first AX2012 implementations, I also had to do a presentation for a Dutch community. The topic was about the Organizations and Hierarchies. While preparing the session, I suddenly got the idea to combine security role assignments, organizational hierarchies and XDS. After the presentation, I lost the demo and objects used at that time. For another presentation during the Summit EMEA in Dublin this year, also about organizational hierarchies, I decided to create a new demo. In a former blog, you can find the link for downloading the objects for this example. (Extensible Data Security examples for Microsoft Dynamics)
In this blog, I will explain you how this “Secure by retail channel” works functionally and technically.
(meer…)
Extensible Data Security examples – Secure by warehouse
In this post, I will continue explaining the examples created with eXtensible Data Security. In this part, I will explain how I did think of a solution for restricting warehouse access for users. There were a lot of questions on forums in the past about how to secure this. So, for a presentation on the Summit EMEA in Dublin this year, I decided to step into the challenges that comes with securing access to warehouses and related tables like inventory journals or purchase orders. I created a demo which will be covered in this part of the XDS blog series. In addition, this is a good example how to deal with different assignment of groupings per user without creating a policy per group or user.
(meer…)
Extensible Data Security examples – Secure by legal entity
In my last blog, I shared some code examples for eXtensible Data Security (XDS). In this post, I will explain how it works and also introduce a V2 version which will be more advanced in determine which legal entities will be visible for the user.
(meer…)
Extensible Data Security examples for Microsoft Dynamics
The last few months, I did spend a lot of time on speaker sessions for the Summit EMEA, MVP Monday webcast and a coming Dutch Dynamics Community event. In the meantime, it was also extremely busy completing work. One of the topics I talked about recently, is eXtensible Data Security (XDS) in Microsoft Dynamics 365 for Finance and Operations and Microsoft Dynamics AX 2012.
Code examples
There is not that much written related to this XDS topic. You may find two white papers and some blogs posts on this topic. One whitepaper also describes an example how to restrict financial dimension values.
Developing Extensible Data Security Policies (White paper) [AX 2012]
Securing Data by Dimension Value by using Extensible Data Security (White paper) [AX 2012]
As there is in general not that much knowledge and experience, I decided to share my knowledge in this area on some events and now also using my blogs.
I did create and demo some examples what can be achieved using XDS. During the sessions related to this topic, I promised to share these examples. The examples created do have the next features:
- Secure legal entities
When you do assign security roles with organizations assigned, by default a user can still see them all in the Legal entities form. This policy has the ability to limit it to only those assigned to the user. - Warehouse security
This example show how you can achieve some record based security on warehouses and sites. It uses a custom setup form to specify the warehouses linked to a user. - Retail channel security
This example is combining the organization hierarchies, security organization assignment and XDS. Quite a powerful example which could be an inspiration for you.
Detailed information how to use these examples and an explanation how they are created will be posted in separate blogs. Watch them coming or come back on this page where the links will be updated. Also, the list with examples might grow in future.
At this moment, I can share the examples based on AX2012 and they reside on my OneDrive. The same features for Dynamics 365 for Finance and Operations will be added in the future. The location of the code examples might change in future, so ensure to bookmark this page to be able to find the examples anytime. Currently, you can find them on this location: My OneDrive DynamicsShare
If you want to explore these examples, feel free to download and use it. The software is provided as-is and you cannot obtain any rights if something is not working correctly. You have to ensure you will install the examples in a separate environment first and test it carefully. If you have questions or feedback, feel free to add comments or send a message.
That’s all for now. Till next time!
Segregation of Duties: Dynamics AX meets the financial auditor (part 3)
In the previous parts the present way of handling Segregation of Duties in AX was covered. In this final part some improvements or if you prefer some open topics will be commented on.
Date dependancy or versioning
When looking at the form where Segregation of duties rules are setup and maintained and one thinks of the importance of such rules it is a pity that these rules don’t have versioning or a date of when it became active or something similar. In other AX functionality in many cases a rule or version must be activated but for Segregation of duties rules none of these things are present. I hope that in future versions of AX (Dynamics 365 for Operations) this will change and some thought would be put in versioning (and store the old versions of a rule) .
Maybe Microsoft wanted to add this functionality already because when looking in the AOT one can notice that there are two date fields that could be used for versioning. These fields are not standard shown on the form of the Segregation of duties rules, but they are part of the table anyway. So maybe in the future there will be functionality added These fields are shown in the frame below.
Clean Up table containing resolved conflicts
As mentioned earlier when conflicts are resolved the resolution of the conflict is stored for future use. This can be both wanted or unwanted depending on how you look at it.
It is wanted because it shows that the Segregation of Duties in in place and working. It can be unwanted when a conflict is allowed, to resolve an issue for which temporarily more access rights where necessary. However this resolution is kept and can’t be deleted from AX using one of the forms. When the role is assigned again no conflict will be shown! To avoid this it can be useful to clean the table from these resolutions using the AOT (or an X++ or SQL script).
Changes to functionality of AX
As an AX environment is a dynamic environment changes to this environment will occurr on a regular basis, will it be an upgrade by Microsoft, installing an add-on or changes created by the organization itself or its implementation partner. When additional functionality is added, or existing functionality is changed, being the introduction of new buttons, new duties or new privileges or changing existing ones, the Segregation of Duties must be evaluated to determine if changes are necessary in the setup of the rules (are new rules necessary, can we dispose of rules, must we change existing rules).
To keep it short, once you start using Segregation of Duties in AX it must be maintained and be a part of a change procedure of an organization.
Standard roles and standard AX
When looking at the standard AX roles in regard to Segregation of Duties it becomes clear that Microsoft created AX and the AX standard roles that support business processes, but not always had a keen eye for Segregation of Duties. As an example I would point out that creating negative purchase (RMA-orders) and creating normal purchase orders are processes that you would normally split, because inbound and outbound inventory tranactions shouldn’t be handled by one role. In the standard roles these processes are not separated. Also in the security elements this is not or not reflected. These anomalies will be encountered when a Segregation of Duties policy must be setup in AX.
So when a Segregation of Duties must be implemented it might well be, depending on the goals to achieve, that security elements must be added (privileges, duties, roles) in order to be able to ensure that it will work.
Kaya Consulting and Segregation of Duties
As one can understand is the above a result of coping with Segregation of Duties issues at various customers, working in various branches that have to deal with their own risks. It is also observed that this important issue is not always covered during the initial setup of AX at a customer. However it is an issue that should be addressed during an implementation or upgrade of AX.
When struggling with issues regarding Segregation of Duties and when help is needed give us a call.
Kaya Consulting has several consultants available who can assist with the setup of a Segregation of Duties policy (the SoD Matrix), implementing it in Dynamics AX (consisting of reviewing the existing security roles, creating new security roles and new security elements, setup of the Segregation of duties rules in AX) and documenting it, so the financial auditor gets a clear insight of what is going on in an organization for a Segregation of Duties point of view and IT-support staff knows how the Segregation of Duties can be maintained.
Segregation of Duties: Dynamics AX meets the financial auditor (part 2)
Setup and validation of Segregation of Duties rules in AX
Prior to the setup of the Segregation of Duties rules in AX itself they should be determined by the organization (create an SoD matrix). Next step is to determine which Duties contain access to the functionality that must be checked when given access to. In this step the standard Security Development Tool of AX can be used. When all this has been done the actual setup of the Segregation of Duties rules can be undertaken.
Setting up all Segregation of Duties rules can be a time consuming, depending on the amount of duties that need to generate a conflict. This can especially be the case when Duties have been copied to be used in various roles, but the prime entries are still present in this Duty (for instance sales order related access is still present in the copied duty whereas simultaneous access to purchase and sales information must be prevented). When a rule is setup it might look like below (this is a complete fictional example). The fields First duty and Second duty are of course the most important fields and for that reason these fields are mandatory. All other fields are more or less of an informational level.
We advice to add some fields to this form in order to make setup easier and to be able to check if proper duties have been used for the setup. For that reason it is very useful to add the AOT name tot he form to both duties; this can be done by personalizing this form.
Once all setup has been done it is time to use rules for what they are intended for, namely to validate if users have received too much access to functionality of AX. However this validation can also be used to verify if a role grants in itself too much access to AX. Validation can and is performed at various moments.
With the button on above form the validation can be performed. In this way a check is performed on the existing roles if they are in conflict with the rules setup. Be aware that all rules must be executed one by one (!) and that roles present in AX are validated, so also roles that are not used or will not be used (because new roles are created that must be used to assign to users). In case validation errors are shown appropiate action can be undertaken. i.e. change roles in order to resolve to conflicts.
When a user gets a new (additional) role assigned the validation of segregation rules is performed automatically. In case conflicts are present a message is shown indicating what rule is violated.
Apart from this automatic validation there is also an option present in order to establish a periodic validation. This can help to ensure that no violations of the Segregation of Duties creep in without being noticed and evaluated.
Go to System administration > Setup > Security > Segregation of duties > Verify compliance of user-role assignments
- The validation can be executed as a batch-process (1)
- Use button “OK” to execute the validation (2)
When the validation is completed AX will display an Infolog with all validation errors showing below information per conflict:
- User id;
- SoD validation rule name;
- Role 1 in conflict;
- Duty in conflict within role 1;
- Role 2 in conflict;
- Duty in conflict within role 2.
An example of such a message would like shown below.
When conflicts arise these have to be resolved. The person responsible fort he Segregation of Duties should be informed and he/she should take appropiate action like assigning user(s) to other roles, reassigning roles to users, change configuration of user roles or SoD rules or accept the violation of the conflict. We will now have a look at how conflicts can be resolved.
Resolve Segregation of Duties conflicts in AX
When a user gets an additional role assigned and there is a Segregation of Duties conflict a message will be displayed.
After pressing Yes AX will display an Infolog with details as well as the form “Resolve segregation of duties conflicts”:
Find all SoD conflicts in AX
All conflicts identified by AX are listed in the form “Segregation of duties conflicts” (path: System administration > Setup > Security > Segregation of duties > Segregation of duties conflicts):
Note that all conflicts that occurred remain in AX and SoD rules related to conflicts cannot be deleted from AX. This gives an auditor an overview of all conflicts that have appeared in the past. However actions undertaken due to a conflict have to be retrieved from other parts of AX.
The field resolution (not listed in the screen print above) contains information regarding the status of the conflict.
Status can be:
- None: conflict to be resolved (buttons Deny assignment and Allow assignment are available)
- Override: conflict resolved (buttons Deny assignment and Allow assignment are not available).
Find unresolved SoD conflicts in AX
To find only unresolved SoD conflicts follow the path System administration > Setup > Security > Segregation of duties > Segregation of duties unresolved conflicts:
This resembles the form where all conflicts are shown. Only records with field resolution populated with “None” are shown. In this form a filter can be applied to find the conflict on hand, usually a filter is applied on UserId, like shown in the example above.
Allow SoD conflict
Based on the actual Segration of Duties policy it can be decided that a conflict can be allowed. It might seem strange to allow a conflict, but bear in mind that it can occurr that in some cases some staff members must be allowed access rights that they normally would not be granted. In these cases an exception can be made by allowing the conflict.
In order to do so, select the record containing the conflict and click the button “Allow assignment” (1).
When the button “Allow assignment” is activated AX will display a dialog where a reason for override can be entered.
It would be helpful to populate this field with a note that make sense. Some suggestions might be:
- Direct based on the SoD policy of the organization (known conflict)
Date of approval
“Known conflict” + communicated to person responsible for SoD policy
- Based on approval by person responsible (unknown conflict)
Date of approval
Name of person who approved the conflict
Additional text as determined by the approver
Important! After assignment is allowed one must return to the user form and assign oncemore the security roles that were not allowed initially. The system will now allow the role assignment.
Note: Once a SoD conflict has been allowed for a user, there will be no future conflicts displayed for this user and SoD rule combination. So in case a new conflict must be shown tables in the AOT have to be edited!
Deny SoD conflict
On the same form where SoD conflicts are allowed they can also be denied, so follow the path System administration > Setup > Security > Segregation of duties > Segregation of duties unresolved conflicts.
In case a conflict is not allowed, select the record with conflict and click the button “Deny assignment” (1).
The role with validation errors is not assigned to the user. However, if you were assigning two roles with a conflict in between these roles, one of the two was properly assigned while the other not. In case the SoD validation conflict is not approved, you must ensure that the correct role from the conflicting pair is assigned to the user. This may require unlinking the assigned role and linking the other role to the user instead. In case you were assigning one role in which a SoD conflict exists this role is not assigned and can’t be assigned by trying again (same SoD conflict will be reported.
Make sure that roles that are known to contain a SoD conflict are listed in a SoD Matrix and can be assigned to users.
Segregation of Duties: Dynamics AX meets the financial auditor (part 1)
Organizations at risk
Every organization has to deal with the risk that staff might be able to undertake fraudulent actions whilst at work. This can be actions like direct theft, paying out fake invoices, paying out actual invoices to “wrong” bank accounts. But as mankind is creative there are lots of other cases known where employees took advantage of there employers in an illegal way. In order to assess these risks and to decrease these risks financial auditors check work processes and to what extent they contain risks at fraudulent actions and advice companies how to improve their work processes in this area. As work processes are nowadays often supported by (ERP) software the audit performed usually extends to the way work processes are configured in this software. As Dynamics AX is also used to support work processes often configuration of Dynamics AX is checked by and commented on by financial auditors.
Financial auditor at work
As financial auditors not only check the financial well being of an organization but also check the administrative organization and the way an organization has guarded itself to prevent fraudulent actions of its staff and to what extent risks are present in the way work processes are configured in Dynamics AX. Given this, financial auditors will check the configuration of security roles of Dynamics AX in an organization and will pose questions in what way this configuration helps to prevent fraudulent actions of staff. Usually employees of Financial or Accounting department(s) will receive these questions. Segregation of Duties is in common part of the answer to such questions.
Often also an authorization matrix is present in an organization. This will of course also periodically be audited by the financial auditor. The financial auditor will have a look at the way security roles are set up and to what extent they are covered by the authorization matrix and to what extent they are in line. This also helps them to get an idea if a Segregation of Duties is in place at an organization.
Apart from security roles usually at organizations there are also mandates in place. Usually they are used in workflows in order to establish who has to approve a purchase order or a received invoice. The financial auditor will for this reason also have a look at the way workflows are setup in order to determine if mandates are properly used.
Financial auditors, especially working at larger accounting firms, often have a keen eye for users with Administrative access rights. These access rights are not only handed out to actual users, like system administrators and application managers but also to service accounts. This will lead to questions like why has helpdesk employee X an administrative like account in AX, why has interface Y an administrative like account in AX. As it turns out in these cases is it not always necessary to grant these administrative rights. So in order to avoid to answer these ackward questions, it is better to create roles that exactly grant what is necessary to perform tasks at hand.
Security roles and Segregation of Duties
The above questions posed by Financial Auditors can usually be answered by showing an overview of roles assigned to users. With some effort these data can be retrieved from Dynamics AX.
When must be demonstrated in Dynamics AX how a Segregation of Duties is achieved it gets more troublesome. Not only are employees of Financial or Accounting departement(s) in common not the people that have setup the security roles in Dynamics AX but also it is difficult to exactly demonstrate in what way the setup of security roles helps to minimize the risk on fraudulent actions. Just showing the content of a role on a form is insufficient and doesn’t prove that the setup was exactly the same during the fiscal year that is audited. Even additional logging on various tables like vendor bank accounts is just additional evidence, but not conclusive evidence.
When a Segregation of Duties has to be demonstrated to a Financial Auditor one has to bear in mind that in general there are two approaches to setup security in Dynamics AX:
- Grant more than sufficient access rights to roles to perform their activities (overgranting)
- Usually there are a limited amount of roles containing lots of duties or sub-roles
- Users have often just one role
- Specific privileges are not often used
- Users don’t ask or just in few cases for more access rights after start of configuration; when this happens this is often resolved by granting a role containing even more access rights
- Grant just enough access rights to perform their activities (undergranting)
- Usually there are various roles containing some duties and privileges
- Users have often multiple roles
- Specific privileges are regularly used
- Users ask frequently for more access rights after start of configuration, this is often resolved by adding one or a few privilege(s) to a role or create a new role with limited access rights and add this new role as a sub-role.
Both approaches have their pro’s and con’s. In case of overgranting business processes won’t stop, but there is a risk that the scale of the access rights of the available roles is too large in order to speak of roles that support Segregation of Duties. There are cases known that warehouse workers were allowed to post packing slips, change addresses and could create sales invoices as well. This is very usefull to speed up a business process, but is of course quite tricky from a Segregation of Duties point of view.
In case of undergranting there is a risk that if a business process is not completely tested, especially when only the happy flow is tested, a business process can’t be completed. As more roles are necessary to complete a business process this means that more cooperation between Dynamics AX users is necessary, unless users get several roles assigned (that can lead to the same effect as overgranting). Undergranting leading to problems with completing business processes can easily arise when workflows are used. At a customer where security roles with limited access rights where used workflows stopped due to insufficient access rights, so some vendors couldn’t get paid in a normal way because invoices couldn’t be processed in case of a mismatch between invoice and purchase order (for the handling of mismatches a separate sub workflow was used) or due to missing date necessary to assing a workflow step properly (in the past when a lot of access rights were granted such data could easily be corrected, but in the new situation that was not possible anymore).
As one might understand already from a Segregation of Duty point of view undergranting is the approach that helps best to be in control, but it also implies that business processes must be tested more thoroughly, especially the way exceptions or data errors are handled. This is even more important when workflows are used.
In real live it can also occurr that an organization starts with one approach (overgranting) and switches to another approach (undergranting). This can lead to all kinds of complaints of users that they can’t do their work anymore. When the switch is made from undergranting to overgranting usually there are no complaints of users, but controllers and financial auditors are usually not very fond of such a switch as they increase the risk that fraudulent actions can be undertaken. So in general such a switch is quite uncommon.
Another consideration is that standard AX roles are not (all) created to support an organization where Segregation of Duties is vital. A lot of standard roles contain access rights to both Inbound and Outbound activities. Usually this is something that financial auditors disapprove of, so a proper setup of security roles involved leads in a lot of cases to the creation of new roles where inbound and outbound duties are split or even to splitting duties that contain too many access rights. In various cases it might even be necessary to create additional duties and privileges in order to achieve a proper Segregation of Duties.
Segregation of Duties in AX
In order to ensure that the setup of security in AX is compliant with Segregation of Duties as the organization it intents, adviced or not by the financial auditor, it is possible to actually configure a Segregation of Duties in AX. In the module System administration (system administration – setup – security – segregation of duties) is a form present where the Segregation of Duties can be setup. In this form the Segregation of Duties rules can be defined.
Access to this to functionality is controlled by the privilege SysSecSegregationOfDutiesMaintain which is part of the standard Security Administrator role. So according to Microsoft the setup of the Segregation of Duties should be performed by an AX Security Administrator. But given his or her IT background the setup of these Segregation of Duty rules can’t be done by by a AX Security Administrator alone. Usually input from the Financial Department is necessary as this department is usually responsible for internal control and auditing, so Segregation of Duties is their cup of tea. So what often can be noticed is that Segregation of Duties is setup and maintained as a result of close cooperation of Financial and IT department. This is even more true as the setup of security roles is an activity where the AOT and security development tool of Dynamics AX are used, these are usually the play ground of the IT department, but they lack usually the business insight necessary to establish a proper Segregation of Duties.
As mentioned the term used is Segregation of Duties. This means exactly what it says only a Segregation of Duties can be configured in Dynamics AX. Where-as in Dynamics AX in the end access to entry points (menu-items) is controlled by privileges it is not possible to configure a Segregation of Duty rule based on privileges, this can only de done on duty level. The standard roles present in Dynamics AX are setup in order to ensure that business processes of organizations are supported, however these roles are not always (or one could say quite often) not compliant with requirements that a proper Segregation of Duties demands. In these cases an additional Duty has to be created (it can be useful to name these SoD_XXXXX, so it is clear that these duties are specifically created for Segregation of Duties purposes).
How we saved an international retailer from bankruptcy! Part 2
So let’s recap… Previously on “the walking dead”
- You are clueless about where your items are, orders are not received on time or at all, vendors are not fulfilling their whole purchase order, nor do they deliver on time. The multi-million-dollar system you just bought gives inaccurate forecast and your in house made point of sale system can’t process items that are not on stock in your store and so on… Click here if you missed the first part of my blog.
How the Mobile Device capacity in AX2012 R3 fixed these problems
The next step was to ease up on all the regulations implemented and we disabled some automated functionalities and we gave some control back to the people on the floor. In practice we created a few simple apps using Microsoft Dynamics AX mobile device capacity:
- First priority: Count the warehouse. This should be simple: scan the location, scan the item, enter quantity. The catch here is that in AX we created an algorithm that dealt with ‘item and stock status’ or ‘items not or already present on location”. Once ready it took us one weekend to count all warehouses and all stores.
- Second priority: Give some control back to the people on the floor. If AX doesn’t know measurements of locations or items, or if vendors don’t deliver according to what they promise, then you have to be flexible. There is no way AX can tell you were to put the item based on wrong or none existing data. So let the warehouse employee decide were to put the item. This app should be as simple as scan the item, scan the “from location”, scan the location where the items should be placed and enter the quantity. Once again AX will take care of possible issues. Once ready, we cleared the inbound dock and were able to move items around within the warehouse.
- Third priority: Create crossdock functionality. Since most or part of the items were to be send right away to the stores it would be very beneficial to create crossdock functionality. With a small modification within AX it was easy to identify the items delivered and were meant for crossdock to supply the shops and meant for the webshop.
- Fourth priority: Create outbound functionality from the general warehouse and receive functionality in the stores. This one was tricky. Not so much what the functionality should do but simply because there was no AX and Wifi available in the stores. And this is why I love working at Kaya Consulting, we have a team available for just that issue! So we created a mobile app using windows phone and the 4G network and that matched the outbound license plates functionality with what is scanned in the stores.
Today, the AX issue backlog is clear. The system is upgraded to the latest CU and we’re still busy, but now we are implementing all those cool features that the company have on their bucket list.
And yes, I’m bored again. So if you have a mission impossible for me. Do give me a call!
Please share your thoughts and experiences in the comments below and don`t forget to subscribe to our newsletter to be the first to receive our latest blog content!
SQL Trace Flag for Dynamics Ax: Do we need it?
What is a SQL Trace Flag?
A SQL Trace Flag is an indicator that changes the way the SQL engine reacts. Why would we want to that? Well because the DBA always knows best, uh well actually because the standard behavior of the SQL engine will not always give the best possible performance for a Dynamics Ax ERP. Which SQL Trace Flag could be of interest for a Dynamics Ax Database? Well I found 5 that work great for some situations. (-T1117 -T1224 -T2371 -T4136 -T4199). Yes I said “some situations” because which SQL Trace Flag can be enabled depends on lots of things, and should be decided carefully. Let me inform you about these 5 SQL Trace Flags.
SQL Trace Flag T1117: TempDB in multiple files
When you divided the TempDB in multiple data files to match the number of CPU cores as recommended by Microsoft for Dynamics AX, SQL Trace Flag –T1117 comes in handy. It will change the standard behavior of auto growing database files in a file group. The standard behavior is to auto grow one of the files at a time. With this SQL Trace Flag enabled all files in a file group will grow simultaneously.
For example:
1 db divided in 4 data files with an initial size of 100MB and an auto grow of 20MB each. When your reaches the 400MB and all 4 files are full, one of them will auto grow to 120MB. But the next data will be written in the file with the freest space. Guess which one that will be…. The one that grew off course. Now the same example with SQL Trace Flag –T1117 globally enabled: When your reaches the 400MB and all 4 files are full, all of them will auto grow to 120MB. The next data will be written in the file with the freest space. Guess which one that will be…. All of them.
Should we enable this SQL Trace Flag this minute?
No, there is a drawback. This SQL Trace Flag is great for the TempDB but it affects all data files of all databases in the instance. They will simultaneously grow if they’re in the same file group. So think before you act, if you don’t want the files to grow simultaneously put them in different file groups, or don’t enable this SQL Trace Flag.
More information on this SQL Trace Flag: Microsoft Dynamics Ax Performance team
SQL Trace Flag T1224: Running big operation during the day.
When running big operations (5000+ rows) during the day, like master planning, they could be blocking your users from doing their daily business. This is due to lock escalation. SQL locks a page to make sure only one process effects that page at a time. When a certain number of pages of a table are locked by the same process SQL can decide to lock the table if this is less costly on memory. This will make all requests for pages in that table wait till the first process is done. SQL Trace Flag -T1224 Disables lock escalation based on the number of locks. However, memory pressure can still activate lock escalation.
Should we enable this SQL Trace Flag this minute?
No, if you don’t need to run big operations simultaneously or with users in the system, you’re probably free of blocking by lock escalation. And lock escalation will have a positive effect on the performance of your SQL. So please plan large operations carefully outside office hours where possible.
SQL Trace Flag T2371: Keeping statistics up-to-date on large tables
The SQL query optimizer uses statistics to create query plans that improve query performance. Performance of Dynamics AX depends largely on up-to-date statistics. That’s why it is recommended to set “Auto update statistics” on. But when will the statistics be updated? Well when 20% of the rows are updated/inserted. This SQL Trace Flag is only available on SQL Server 2008 R2 SP1 and all later versions. There was a small update to this SQL Trace Flag in SQL Server 2008 R2 SP2 and SQL Server 2012 SP1 that makes it more precise.
For example:
On a table containing 1,000 rows the statistics are updated when 200 rows are updated. And the same table after a while containing 1,000,000 rows the statistics are updated when 200,000 rows are updated. Did you read that right? Yes you need to update 200,000 rows till the statistics are updated. Imagine even larger tables. We all know that in an ERP like Dynamics AX these large tables are pretty common. Microsoft also recognized that and released SQL Trace Flag –T2371. This SQL Trace Flag will make the 20% rule a dynamic percentage rule that kicks in at 25,000 rows.
For example with SQL Trace Flag –T2317 globally enabled:
SQL Server will update statistics on a table containing 1,000 rows when 200 rows are updated. And on a table containing 1,000,000 rows the statistics are updated when 32,000 rows are updated. In a chart the dynamic percentage rule looks like this:
Should we enable this SQL Trace Flag this minute?
No, first examine if your database has a lot of large tables (above 100.000 rows is a good rule of thumb). Without large tables this SQL Trace Flag is useless.
SQL Trace Flag T4136: Parameter sniffing optimisation
Dynamics AX uses parameterized SQL statements if we don’t use the ‘Forceliterals’ parameter in AX. I personally have never seen a developer who fully understood the working of ‘Forceliterals’ let alone use it in code. It even is best practice not to use it.
Why? Well when using parameterized SQL statements the query plans are cached and reused. SQL Server will use one plan for all similar queries instead of compiling each query. This can significantly reduce CPU overhead and improve throughput. That sounds like a good thing and it is.
But there is a drawback, if the queries are complex and/or the data distribution on the columns against which the parameter is compared vary, the cost of different plan choices can change. A plan that is optimal for one parameter value may perform poorly for another value. The query optimizer still needs to estimate the selectivity and cardinality of predicates using a parameter value. In Dynamics AX especially from version 2012 on the queries are almost always complex.
One way to avoid parameter sniffing is to put the OPTIMIZE FOR UNKNOWN option to all SQL statements leaving AX. Another way is to enable SQL Trace Flag -T4136. This SQL Trace Flag is only available on SQL Server 2008 R2 Cumulative Update 2, SQL Server 2008 SP1 Cumulative Update 7 and SQL Server 2005 SP3 Cumulative Update 9 and later.
For example:
If we have 2 companies in our Dynamics Ax Application and one only 2 customers and the other a couple thousand. The following could happen: if the first query we send to loop through customer is done in the company with a couple thousand customers , it could look like:
SELECT * FROM CUSTTABLE WHERE DATAAREAID = ‘@P2’
And SQL Server Engine will solve it by doing a full table scan because almost all records are needed.
Next time we do the same loop in the other company due to the parameter on dataareaid (and partition in AX2012) the query is identical. That is why SQL Server will solve it in the same manner, with a table scan, where a page scan could be more appropriate. If only SQL could store a queryplan per dataareaid….
Should we enable this SQL Trace Flag this minute?
No, because Microsoft provided a way to use parameters but not for dataareaid (and partition in AX2012). This makes SQL server store a queryplan per dataareaid, making SQL Trace Flag T4136 completely unnecessary.
AX2009 SP1: KB 3000700
AX2012 RTM: KB 2920058
AX2012 R2: KB 2969229
You can find more information on the blog of Dynamics in the field.
More information on this SQL Trace Flag: Microsoft Support on SQL Trace Flag T4136
SQL Trace Flag T4199: Query execution plan updates
Microsoft turned off All query execution plan updates/hotfixes by default to make sure that an update will not affect the way your query executes. Several of these hotfixes will positively boost performance for Dynamics AX. Microsoft controls all future query processor fixes that are scheduled to be ‘On-By-Default’ in a later release with trace flag t4199. This SQL Trace Flag is only available on Cumulative update package 6 for SQL Server 2005 Service Pack 3, Cumulative update package 7 for SQL Server 2008, and Cumulative update package 7 for SQL Server 2008 Service Pack 1 and all later versions.
Should we enable this SQL Trace Flag this minute?
No, there is are 2 drawbacks. 1: Your DBA will send you off to an insane asylum because of the next drawback. 2: If you enable this SQL Trace Flag you enable all future hotfixes/updates to the query execution plans, because we all know you’re going to forget about enabling this SQL Trace Flag.
More information on this SQL Trace Flag: Microsoft Support on SQL Trace Flag T4199
SQL Window Functions in Ax? Yes we can
Are SQL Window Functions new?
Since 2003 in the SQL ANSI/ISO standard and extended in 2008. Microsoft implemented the first window functions in their SQL Server 2005 version (known as OVER clause) and extended them in the following versions.
Window Functions in all SQL Versions
So to be short no they are not new for the ANSI/ISO standard and for Microsoft SQL Server window functions exist for a few version now. Within in Dynamics Ax we use X++ which is translated by the Dynamics Ax Kernel to T-SQL to make the database return a result set. This makes us depended of the implementation of X++, which in Dynamics Ax 2012 doesn’t support window functions for select statements etc. But I can show you a way on how to use them in AX 2012 and up.
What are SQL Window Functions?
Let me show you with an example. As this is a blogpost for Dynamics Ax minded people let us use the SalesLine table.
If you want to select a few fields of this table, you would write something like the following (in SQL and X++):
SELECT SALESID, ITEMID, SALESCATEGORY, CUSTACCOUNT, LINEAMOUNT FROM SALESLINE;
If you would like to see the total amount of the salesorder in the same result set you would need to do a “GROUP BY”:
SELECT SALESLINE.SALESID, ITEMID, SALESCATEGORY, CUSTACCOUNT, LINEAMOUNT FROM SALESLINE JOIN (SELECT SUM(LINEAMOUNT) AS Total, SALESID FROM SALESLINE GROUP BY SALESID) AS A ON A.SALESID = SALESLINE.SALESID;
in X++ you would do something like this:
While Select SalesId, ItemId, SalesCategory, CustAccount, LineAmount from SalesLine
{
select sum(LineAmount) from salesLine2 where salesLine2.SalesId == salesLine.SalesId;
}
But it will send a statement for each salesline to the database.
TIP
A better way to do this in Ax would actual be:
- Create a Query object for the ‘select sum(LineAmount), SalesId from salesLine’ bit.
- Put that Query in a View.
- Now create a new Query with in the datasource the SalesLine and join it with the View.
Now let’s make it even more interesting. I would like a statement to get a resultset with the following columns:
- the SalesId, ItemId, SalesCategory, CustAccount, LineAmount.
- the total amount of the salesorder.
- the avg LineAmount of the customer in the same category.
- the LineAmount of the last time this customer ordered this item.
- running total of this customer.
SELECT SALESID, ITEMID, SALESCATEGORY, CUSTACCOUNT, LINEAMOUNT, SUM(LINEAMOUNT) OVER (PARTITION BY SALESID) AS [Total amount salesorder], AVG(LINEAMOUNT) OVER (PARTITION BY CUSTACCOUNT, SALESCATEGORY) AS [Avg LineAmount customer category], LAG(LINEAMOUNT,1) OVER (PARTITION BY CUSTACCOUNT,ITEMID ORDER BY SALESID) AS [Last time ordered for], SUM(LINEAMOUNT) OVER (PARTITION BY CUSTACCOUNT ORDER BY SALESID ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS [Running Total];
And here we are, our first window functions. They are called window functions because they put a window (subset) over the rows you get out of your FROM clause.
The <window function> part can be: Ranking functions, Aggregate functions or Analytic functions.
After the OVER keyword you can limit the size of the window:
- OVER() isn’t limiting at all so all rows are considered.
- OVER (PARTITION BY X) means limit the window to al rows that have matching values in Column X in current Row.
- OVER (ORDER BY) changes the sort order in the window. (Can be nice if you use RANK or ROW_NUMBER window functions)
- OVER( ROWS BETWEEN … AND …) limits the window to the rows between the given criteria seen from current row.
How to use these powerful window functions in Dynamics AX?
As I mentioned before X++ isn’t a real options. But in the View objects in the AOT there is a little gem that we can polish for our purpose.
- Create a Query for the SELECT SALESID, ITEMID, SALESCATEGORY, CUSTACCOUNT, LINEAMOUNT FROM SALESLINE part.
- Use this Query in a new View.
- Create a method on the View with the following code:
public static server str windowFunction() { //make sure you use this Dictview because the names of the tables are changed in the SQL statement send from Ax to SQL. //This makes sure the right names are used DictView dv = new DictView(tableNum(<<name of view>>)); //This is the real magic return 'SUM('+dv.computedColumnString('SalesLine', 'LineAmount') +') OVER ( PARTITION BY ' + dv.computedColumnString('SalesLine', 'SalesId'); } - Now right click on the ‘fields’ branch of the view choose NewReal computed column.
- Give the new field a nicer name and set the name of the method in the ‘viewmethod’ property.
- Save and synchronise the view.
Now what did we just do?
We changed the create statement of the view to include ‘SUM(LINEAMOUNT) OVER (PARTITION BY SALESID)’ as a column. By synchronizing we created a view in SQL Server that contains the window functions we want to.
Could we use this for other things then window functions?
Yes you can use all possibilities of the T-SQL language here. But remember you are changing the Select clause of the view here. Putting a select statement in here (or a stored procedure) will for sure make the view run a long time as every row will trigger a separate statement. But a scalar value function can be very valuable sometimes (like ISNULL()).
Interesting links:
A 20 minute training about window functions in SQL 2012.