AX2012 R2

Index fragmentation effects on performance

Before jumping into index fragmentation I’ll first explain what indexes are and how they are used by the SQL Server Engine. I tried to compare it to a real world way of searching data, to paint a clear picture in your head. I’m not going into the differences between clustered and non-clustered indexes as that would be a nice new blog.

How do indexes work?

Indexes are used by SQL Server to search for records in tables as we use indexes in books to get to certain chapters/words/authors etc. The way an index in SQL Server works is similar to the way we lookup a phone number in a (old fashioned and hard copy) phonebook.

1. We decide we need a phonebook for this search not a cookbook.
2. We pick up the phonebook of the state we need.
3. We look at the index to find the pages that have the correct city.
4. We go to the page that contains the first letter of the last name of whose phone number we need.
5. We find the last name and see that there are in fact several.
6. We look at the address and find the right number.

1. It decides it needs a certain index.
2. It will look at the 1st level of the index to find the right part (phonebook of state).
3. It will look at the 2nd level of the index to find the right part again (correct city).
4. It will look at the 3th level of the index to find the right part again (last name).
5. It finds the page containing the correct last name.
6. It traverses through the records for the one that contains the needed address.

What is index fragmentation?

What happens with index fragmentation?

How to temporarily or partly prevent index fragmentation?

TIP: Workflow and AIF are using GUID’s in indexes so set fill factor accordingly and maintain them.

How to permanently prevent index fragmentation to be an issue?

1. Make sure the physical drives your data is on are not shared with other applications. Mainly for HDD storage as the other applications will use the HDD rotation as well.
2. Use the scripts of Ola Hallengren to reduce index fragmentation without rebuilding all indexes. Set the parameter of minimum pages to 100 instead of 1000 for Dynamics Ax databases.
3. Use SDD’s for storage of data instead of HDD’s. Because SDD’s don’t use moving parts.
4. Cache your database by assigning enough memory to SQL Server to cache the entire database, or at least the records that are frequently used. Because cached data is way faster then any stored data on hard discs.

Is index fragmentation the root cause of performance issues?

1. Poorly chosen indexes. (indexes on fields that are updated all the time)
2. Changing of usage of the system. (Users are allowed to change id fields, which wasn’t in the original design)
3. Poorly developed queries. (queries that cannot make full use of the indexes that are already there)
4. and a thousand other reasons.

All code samples, walkthroughs and other instructions are provided as-is. Use any and all information provided in this blog at your own risk. It is never advised to import or write code in a production environment without rigorous testing in a test or development environment.
All opinions expressed in this blog are solely my own and do not necessarily reflect the opinions of my employer.
The names of actual companies and products mentioned in this blog may be the trademarks of their respective owners.
Microsoft and Dynamics AX are registered trademarks of Microsoft Corporation in the United States and other countries.

Security Development Tool – Part 7

After reading the six posts about the Security Development Tool from André, I got excited about the tool and started using it on a regular basis. It proved to be a powerful tool which cut down development time quite a bit. Almost everything you need for security development is available. But there was one scenario where I missed a functionality in the tool. I was redesigning a few security roles to fit the users need and because the basis was done very poorly I decided to create new roles. Everything worked fine except for 1 tiny little thing that wasn’t picked up by the registration. So what I wanted to do is compare the old role to the new role to figure out what was missing. There is no functionality in the tool for this, and as I’m firmly against using excel, I made it my mission to get this information directly from the tool. In my search I found a ‘dirty and limited’ way to do it and a ‘nicer and more extended’ way to get the job done. I’ll explain them both for a complete picture.

Option 1: ‘Dirty but effective’ way to compare roles in Security Development Tool

If the only security objects you want to compare are Roles then there is a simple but ‘dirty way’ to do it. When you open the ‘Security entry point permissions’ form it will show you the entry point permissions of the System User Role. We could fill this column of the form with a different role to compare it to the role we’re going to edit.

The way to accomplish this is fairly easy. In the AOT open up the form ‘Security entry point permissions’ and under ‘methods’ find the method ‘LoadSystemUserPermissions’. In line 12 you see :

Select firstonly Recid from systemUserRole Where systemUserRole == 'SystemUser';

Simply change ‘SystemUser’ to the AOTname of the desired Role. After a quick compile the rights of the choosen role will be presented in the column ‘System User Role Acces Level’ next to the access level of the role you are developing.

Option 2: ‘Nicer and more extended’ way to compare object in Security Development Tool

The first way is good for developers and gives fast results for when you only need this functionality ones. Now let’s build something lasting, that consultants and end users can use as well. And to make things even more interesting let’s build some filtering options as well.

So the general idea is to build something like this:

Security Development Tool including Comparison and filtering

Where you can compare roles/duties and privileges and use filters to easily find the differences between to objects of the same type.

More elaborated what we want to achieve is:

1. A possibility to add a column with access rights to the grid based on the selection in the ‘Compare’ combobox. The list of the combobox should depend on the selection made in the ‘Type’ combobox, like the list of the ‘Name’ combobox.
2. A filtering mechanism to quickly identify the differences between the ‘acess level’ column and the newly created ‘Compare Results’ column in the grid.

Part 1 comparison in Security Development Tool

To be able to extend functionality you first need to understand how the existing functionality (technically) works. As mentioned before the behavior of our ‘Compare’ combobox has to look quite similar to the behavior of the ‘Name’ combobox. So let’s see what the flow of the functionality is:

1. On initiating the form ‘SysSecEntryPointManager’ a ‘in memory’ temp table is being filled with all security objects and their access rights for the ‘SystemUser’.
2. After choosing a ‘Type’ in the combobox the list of the combox is changed.
3. After choosing a security object in the ‘Name’ combobox, the code will check if it is a role otherwise (duty or priviledge) create a custom role. And insert the data in the temp table.

Data dictionary

First thing to do is create a column in the forms datasource for storing the access rights associated with the security object we are comparing with.

In the Table ‘SysSecEntryPointTmp’ we have to create a column called ‘CompareUserRight’ based on the Enum ‘AccessRight’. The label property should contain something like ‘Comparison Results’.

(The ‘Name’ object populates the field ‘AccessRight’.)

Creating a new field on the datasource of the Security Development Tool form

Put the field in the ‘DataManager’ group to make it automatically available on the form.

DesignFunctionality change

Let us first set a few variables in the ClassDeclaration that we are going to need throughout the next steps:

In the classDeclaration insert the following at line 65

//BWAL Security Development Tool Addon: compare User rights
    Map                             compareUserAccessRightsMap;
    SecurityRoleAotName             compareRoleAOTName;
    recid                           compareRoleID;
    IdentifierName                  compareDevelopmentObject;
    SecurityRoleName                compareRoleName;
//END BWAL Security Development Tool Addon: compare User rights

Create a method called ‘CompareDevelopmentObject’ like this in the root of the form to be able to use it later on:

//BWAL Security Development Tool Addon: compare User rights
public IdentifierName CompareDevelopmentObject()
{
    return compareDevelopmentObject;
}
//END BWAL Security Development Tool Addon: compare User rights

In the form ‘SysSecEntryPointManager’ in the design node create a new group beneath ‘Group:Settings’ and call this one ‘Compare’.

Create a new group for Security Development Tool Comparison

Set its properties as:

Width = Column Width
RightMargin = 2
ColumnSpace = 2

Let’s create a combobox similar to the ‘Name’ combobox including it’s methods.

In the group ‘Compare’ we create a stringEdit called ‘Compareobject’ with the following properties:

AutoDeclaration = yes 

On this stringEdit we create 2 methods:

First:

//BWAL Security Development Tool Addon: modified method compareObject control.
public boolean modified()
{
    boolean ret;

    ret = super();

    element.SelectObject();
    return ret;
}
//END BWAL Security Development Tool Addon: modified method compareObject control.

What does this do? If you choose a security object it will then insert or update the access rights in the temp table.
Second:

//BWAL Security Development Tool Addon: Lookup form for compareObject control.
public void lookup()
{
    str lookupForm;
    SysSecDevelopmentType selectedType = ObjectType.selection();
    FormRun formRun;
    Args args;

    switch(selectedType)
    {
        case SysSecDevelopmentType::Role:
            lookupForm = formStr(SysSecRoleLookup);
            break;
        case SysSecDevelopmentType::Duty:
            lookupForm = formStr(SysSecDutyLookup);
            break;
        case SysSecDevelopmentType::Privilege:
            lookupForm = formStr(SysSecPrivilegeLookup);
            break;
    }

    if(lookupForm != '')
    {
        args = new Args(lookupForm);
        args.caller(this);
        formRun = classfactory.formRunClass(args);
        formRun.init();
        this.performFormLookup(formRun);
    }
}
//END BWAL Security Development Tool Addon: Lookup form for compareObject control.

What does it do? This method changes the list of the combobox making it dependable on the selection in the ‘Type’ combobox.

To make sure that when you choose a different type the ‘Compare’ combobox selection is deleted insert the following code:
In the ‘modified’ method of the ‘Combox:ObjectType’ at line 8 insert:

//BWAL Security Development Tool Addon: Clear CompareObject
    CompareObject.text('');
//END BWAL Security Development Tool Addon: Clear CompareObject

Next thing to do is change properties on the previously created column available in the grid. To do this go to ‘MaingroupRightGroupEntryPointsGridEntryPointsGrid_CompareUserRight’. The properties should look like this:

AutoDeclaration = Yes
Visible = No

To make the contextmenu visible on this column as it is on other columns create a method on this column like this:

//BWAL Security Development Tool Addon: compare User rights
public int showContextMenu(int _menuHandle)
{
    return EntryPointsGrid.showContextMenu(_menuHandle);
}
//END BWAL Security Development Tool Addon: compare User rights

The earlier created ‘modified’ method on the ‘Compare’ combobox calls the ‘SelectObject’ method of the form.
When looking at this method we see that it needs some modifications like:
insert at line 15(after: element.MapDutyOrPrivilegeToRole();)

//BWAL Security Development Tool Addon: compare User rights
        if(CompareObject.text() != '')
        {
            element.MapCompareDutyOrPrivilegeToRole();
        }
//END BWAL Security Development Tool Addon: compare User rights

What does it do? This is a call to a method that we are going to create. That method will hold the code that creates a role from a duty or privilege.

And insert at line 38 (after: else
{
error(strFmt(“@SDT107”, CurrentObject.text()));
}
}):

//BWAL Security Development Tool Addon: compare User rights
        if(CompareObject.text() != '')
        {
            //Verify role exists
            select firstOnly recid, Name, AOTName from role where role.Name == CompareObject.text();
            if(role.RecId != 0)
            {
                compareRoleID = role.RecId;
                compareRoleAOTName = role.AotName;
            }
            else
            {
                error(strFmt("@SDT107", CompareObject.text()));
            }
        }
//BWAL Security Development Tool Addon: compare User rights

What does it do? The actual insert/update of data in the temp table.
Now let’s create the method we’re calling for in the ‘SelectObject’ method. Create it under ‘methods’ in the root of the form.
Create a method called ‘MapCompareDutyOrPrivilegeToRole’:

//BWAL Security Development Tool Addon: compare User rights
public void MapCompareDutyOrPrivilegeToRole()
{
    TreeNode devRole = SysSecEntryPointManager::FindOrCreateUniqueRoleForCompareUser();
    SecurityRole securityRole;
    TreeNode childNode;
    TreeNodeIterator tIterator;
    str typeToAdd;
    SecurityTask task;

    if(!SysSecEntryPointManager::ObjectEditableVCS(devRole))
    {
        error(strFmt("@SDT89", devRole.AOTname()));
        return;
    }

    //Remove existing duty/privileges
    tIterator = devRole.AOTfindChild('Duties').AOTiterator();
    childNode = tIterator.next();
    while(childNode)
    {
        childNode.AOTdelete();
        childNode = tIterator.next();
    }

    tIterator = devRole.AOTfindChild('Privileges').AOTiterator();
    childNode = tIterator.next();
    while(childNode)
    {
        childNode.AOTdelete();
        childNode = tIterator.next();
    }

    if(CompareObject.text() != '')
    {
        //Add duty/privilege
        typeToAdd = 'Privileges';
        if(element.CurrentDevelopmentType() == SysSecDevelopmentType::Duty)
        {
            typeToAdd = 'Duties';
            select firstOnly AOTName from task where task.Name == CompareObject.text() && task.Type == SecurityTaskType::Duty;
        }
        else
        {
            select firstOnly AOTName from task where task.Name == CompareObject.text() && task.Type == SecurityTaskType::Privilege;
        }

        if(task.AotName == '')
        {
            devRole.AOTrestore();
            error(strFmt("@SDT90", element.CurrentDevelopmentType(), CompareObject.text()));
            return;
        }

        //Validate object exists
        if(TreeNode::findNode(strFmt('%1\%2', #SecurityPath, typeToAdd)).AOTfindChild(task.AotName) == null)
        {
            devRole.AOTrestore();
            error(strFmt("@SDT91", CompareObject.text()));
            return;
        }

        compareDevelopmentObject = task.AotName;
        compareRoleAOTName = devRole.AOTname();
        compareRoleName = devRole.AOTgetProperty(#PropertyLabel);

        select RecId from securityRole where securityRole.AotName == compareRoleAOTName;
        compareRoleId = securityRole.RecId;

        childNode = devRole.AOTfindChild(typeToAdd).AOTadd(task.AotName);
        childNode.AOTsetProperty('Name', element.CompareDevelopmentObject());
    }

    //Avoid current form loosing focus due to compiler output
    WinAPI::enableWindow(infolog.hWnd(), false);
    try
    {
        devRole.AOTsave();
    }
    catch
    {
        WinAPI::enableWindow(infolog.hWnd(), true);
        throw Exception::Error;
    }
    WinAPI::enableWindow(infolog.hWnd(), true);
}
//END BWAL Security Development Tool Addon: compare User rights

Great the first line of this code is calling a method that doesn’t exist on the class ‘SysSecEntryPointManager’… Yes I know, Let’s create it then.
Have a look at the ‘FindOrCreateUniqueRoleForCurrentUser’ it creates a Role in the AOT for the security object chosen on the form. We have to do almost the same for the ‘CompareObject’ we just created on the form. So let’s copy it and rename it to ‘FindOrCreateUniqueRoleForCompareUser’. (I know this isn’t best practice not even good practice but I didn’t want to change the parameters of the method already used because then I had to replace more code of the Security Development Tool and had to explain it which would be beside the point of this blogpost).
Only thing we need to change is in line 6 we replace ‘%1%2’ with ‘%1%2Compare’ to make it look like this:

str roleAOTName = strfmt('%1%2Compare',#SecGeneratedRoleAOTNamePrefix,CurUserId());

Now a bit more difficult to find but we need to change the ‘SetPermissions’ method as well. Because :

1. In the ‘SelectObject’ method we call the ‘LoadPermissions’ method which calls the ‘SetPermissions’ method.
2. The ‘Refresh’ button calls it.

//BWAL Security Development Tool Addon: compare User rights
            if(CompareObject.text() != '')
            {
                compareDevelopmentObject = '';
                compareRoleName = '';
                compareRoleAOTName = '';
                element.MapCompareDutyOrPrivilegeToRole();
            }
//END BWAL Security Development Tool Addon: compare User rights

And at line 94 (after: EntryPointsGrid_AccessRight.visible((currentRoleAOTName != ”));):

//BWAL Security Development Tool Addon: compare User rights
        EntryPointsGrid_CompareUserRight.visible((compareRoleAOTName != ''));
//END BWAL Security Development Tool Addon: compare User rights

This line toggles the columns visibility.
The ‘SetPermissions’ method in it’s turn calls the method ‘LoadEntryPointPermissions’ which we also have to alter:
Insert in method ‘LoadEntryPointPermissions’ at line 116 (before: //Update permissions that no longer exist and so are now NoAccess
):

//BWAL Security Development Tool Addon: compare User rights
    entryPointPermissions = SysSecEntryPointManager::GetEntryPointPermissionsForRole(compareRoleID);
    for(i = 1; i <= conLen(entryPointPermissions); i = i+2)
    {
        objectKey = conPeek(entryPointPermissions, i);
        accessRightVar = conPeek(entryPointPermissions, i+1);

        update_recordSet SysSecEntryPointTmp setting compareUserRight = accessRightVar
            where SysSecEntryPointTmp.ObjectKey == objectKey;
    }
//END BWAL Security Development Tool Addon: compare User rights

Part 2 filtering in Security Development Tool

Data Dictionary Change

We first create an enum called ‘SysSecFilterCompare’:

Enum created for Security Development Tool Filtering

Change the ‘Style’ property to ‘Radio button’ so we can drop it on the form later. Don’t forget to fill out the label properties as well.

Designfunctionality change

Next we drop the previously create enum ‘SysSecFilterCompare’ into the previously created group ‘Compare’ and rename the enum to ‘FilterCompare’.

Set the following properties accordingly:

Auto Declaration = yes
Columns = 5

On this radiobutton we only need 1 method:

//BWAL Security Development Tool Addon: modified method Fiter control.
public boolean modified()
{
    boolean ret;

    ret = super();

    element.AddCompareFilter();
    SysSecEntryPointTmp_ds.executeQuery();
    return ret;
}
//END BWAL Security Development Tool Addon: modified method Filter control.

What does it do? It calls the functionality to add a filter on the datasource and re-executes the query.

Create a method ‘AddCompareFilter’ to make the filter work, like this:

//BWAL Security Development Tool Addon: Actual Filter code
public void AddCompareFilter()
{
    QueryBuildDataSource    qbds;
    QueryBuildRange         qbr;
    str                     compareFieldStr = fieldStr(SysSecEntryPointTmp,SystemUserRight);
    str                     currentFieldStr = fieldStr(SysSecEntryPointTmp,AccessRight);

    if(CurrentObject.text() != '')
    {
        if(Compareobject.text() != '')
        {
            compareFieldStr = fieldStr(SysSecEntryPointTmp,CompareUserRight);
        }
        qbds = SysSecEntryPointTmp_ds.query().dataSourceTable(tableNum(SysSecEntryPointTmp));
        qbds.clearRange(fieldNum(SysSecEntryPointTmp, DataAreaId);
        qbr = qbds.addRange(fieldNum(SysSecEntryPointTmp, DataAreaId));
        switch(FilterCompare.selection())
        {
            case SysSecFilterCompare::Equal :
                qbr.value(strFmt('(%1 == %2)',currentFieldStr,compareFieldStr));
                break;
            case SysSecFilterCompare::NotEqual :
                qbr.value(strFmt('(%1 != %2)',currentFieldStr,compareFieldStr));
                break;
            case SysSecFilterCompare::More :
                qbr.value(strFmt('(%1 > %2)',currentFieldStr,compareFieldStr));
                break;
            case SysSecFilterCompare::Less :
                qbr.value(strFmt('(%1 < %2)',currentFieldStr,compareFieldStr));
                break;
            default :
                break;
        }
        qbr.status(RangeStatus::Locked);
    }
}
//END BWAL Security Development Tool Addon: Actual Filter Code

What does it do? If there is a value in the ‘Compare’ combobox it will compare the rights in the ‘Compare Results’ column with the ‘Acces Right’ column. The type of comparison is depending on the option selected. If there is no selection in the ‘compare’ combobox then the ‘SystemUser’ rights column is compared to the ‘Access right’ column.

TIP: Use Comparison with filter and recording

You can even use this new feature in combination with the standard filter which is applied after recording security object. So if you want know the difference in access rights between 2 security objects in a certain area of AX. Just record it and use comparison and filtering.

Interesting links:

Installing the Security Development Tool

Security Development Tool Explained by Andre – Part 1

Security Development Tool Explained by Andre – Part 2

Security Development Tool Explained by Andre – Part 3

Security Development Tool Explained by Andre – Part 4

Security Development Tool Explained by Andre – Part 5

Security Development Tool Explained by Andre – Part 6

All code samples, walkthroughs and other instructions are provided as-is. Use any and all information provided in this blog at your own risk. It is never advised to import or write code in a production environment without rigorous testing in a test or development environment.
All opinions expressed in this blog are solely my own and do not necessarily reflect the opinions of my employer.
The names of actual companies and products mentioned in this blog may be the trademarks of their respective owners.
Microsoft and Dynamics AX are registered trademarks of Microsoft Corporation in the United States and other countries.

Missing index will cause slow performance: SQL can help you

Introduction

As a new member of the Kaya family I feel the urge to introduce myself. Hi I’m Boye Walters, a 34 year old developer with over 12 year experience in Microsoft Dynamics Ax and Microsoft SQL Server. Having worked with all versions of Ax ever released in the Netherlands I can truthfully say that I’ve seen a lot already in Ax. As for SQL Server I’ve actively worked with SQL2000 and above, using all features available including integration, analysis and reporting.
Now the introduction is done let’s start off with something I experienced last week at a customer.

A badly performing View

I was asked to build a view with a dozen or so datasources having inner, outer  and exist joins. To maximize performance I minimized the number of fields and put some relevant ranges in place minimizing the number of records. I also used the relations already on the tables. It should produce an ultrafast view you would think. Too bad, it took me 40 minutes to open the view in the Ax client. But after 40 minutes it showed a lot of records: 2. Because I didn’t use any business logic, user interface or what so ever, I figured it should be SQL causing the delay. As a ran the query in SQL I believed to be proven wrong again, because it ran in 4 seconds. But SQL did suggest an index that could improve performance 85%. After applying it running the query in SQL took under a second. And in Ax it also took under a second. Sounds strange? Yes. Explainable? Absolutely , because of the missing index the query requested a table lock on dbo.salesline which was heavily queried so it took a while to get that.

How did I identify the missing index?

Well after synchronizing the view in Ax I started SQL Server Management Studio (SSMS) and went to the right database and View. There I rightclicked on it and selected ‘Script view asSELECT TONew Query editor window’.

‘Script view as – SELECT TO – New Query editor window’

This will generate a select script for the view.
Now instead of running it went for the execution plan. Using ‘Display Estimated Executing Plan (Ctrl+L)’ button on the top of my Management Studio. Look at the second query (first is just to select the right database), if SQL determines it is needs an index to give you the answer to your query it will say so. The way it does is by showing a create statement for the missing index in green. Like it did with me:

Now I’m going to test it before I build it in Ax. First I rightclicked on the message and choose ‘Missing Index Details’. This opened up a new Query window with a create statement that is commented like this:

I uncommented the query and gave the index a name as requested.
Now I ran this script, and the index was created in SQL. I went back to the previous script and used the ‘Display Estimated Executing Plan (Ctrl+L)’ button again, to make sure SQL didn’t suggest another missing index.
No more missing index, so I went to Ax and ran the view again. After recovering from the Wow factor (and going through several emotions: impressed, not believing, not understanding, believing, grasping the understanding and still wowed ) I created the missing index in Ax after removing it in SQL.

Why ask a program not a developer?

As many of you know, SQL Server works in a (for non SQL developers) odd way. You tell SQL Server what you want and how it should be formatted but SQL Server determines what would be the best way to deliver you what you want. That makes SQL Server the one that can tell you what it is missing to do it’s job properly. This also means you could use this method to determine what missing index would speed up a SQL query that you caught with the trace parcer. There are even queries possible in SQL Server that show you all missing indexes at ones. And that is exactly what we use in our Performance Dashboard.

Interesting links:

Technet on finding missing indexes

Top 3 reasons why administrators love our AX Performance Dashboard

Reason 1:  Easily identify current performance issues

The first step to solve performance issues is to identify the cause, and this is the administrator’s worst headache. This is because performance issues can occur because of a multitude of different reasons and it can also be due to a combination of events.

(meer…)

Conversion of values in Data import/export framework (DIXF)

During a conversion where I used the Data import export framework (DIXF), I encountered an issue with the substitution of values. The conversions are used if the source file has other codes compared to the setup in your AX environment. This post will tell you how the conversion of values is performed and how to solve the issue I had.

(meer…)

Rebuild balances in AX 2012 – Do you have a minute? Or more….

Compared to previous versions of Microsoft Dynamics AX, the implementation of balances has been redesigned in AX 2012 to improve performance. Updating the balances is working correct in many scenarios. However some scenarios will still cause wait times on the rebuild or update of the balances or even… database blocks. This post will inform you about the possible problems and how you could prevent them. (meer…)

Streamline installations

Streamline installations are supposed to make our lives easy, that is if they are properly configured.

First question is how to make a streamline installation. These can be downloaded from https://lcs.dynamics.com

One has to create a project. It is important that it is for the correct build (RTM, retail, R2 or R3).

Next, scroll to the right on the LCS site. On the More tools plane, one can find 2 important buttons: Issue search and Update.

Issue search makes it possible to download isolated fixes (KB numbers).

The updates button allows you to download whole CU updates. When you press the Updates button, the webpage below appears.

The first option (Complete) has it all, namely an installation wizard, binaries and model store. This can also be used in case one only wants to install the binaries.

If only the binaries and the installation wizard are needed, choose the next option (Binary-only)

And finally we come to the third option (Slipstream). The Slipstream has no installation wizard. When this is extracted, one gets something as per the screenshot below:

These extracted files are to be copied to the correct place on the AX 2012 R2 RTM image. Remember to extract this image first.

When the image is extracted, one can find an Updates folder. Open this folder and create folders CU8 and myModels inside as show below:

Next copy the extracted CU8 files into the CU8 folder and copy your own models in myModels.

One would think that the slipstream installation is now up and running….. NOT!

This is strange, this is CU8, so what went wrong?

Time to check LCS….. check for CU8 or slipstream but we find no answer.

Time to log an incident on connect.microsoft.com. An update will follow….

But there is more to this, even when you have correct CU patches, one can still run in issues. It looks like the order of importing the models is wrong. Last week my model had a dependency on the upgrade model.

The dependency was correct, but seems like the size of some field on upgrade staging tables have been changed. When I slipstream this particular installation, my model is rejected because the upgrade (SYS) model is not there.

Time to log another incident…

Conclusion, don’t slipstream in both AX 2012 R2 CU8 and AX2012 R3 CU8

DMF

Yes just a new post about DMF, not really about how it works, just an update on the latest know issues.

What you have to know:

• DMF AX2012 R2 and higher the staging table MUST have an one on one relation to the target Table.
• The menu item you select in the DMF wizard. Is for jumping from the staging table form to the target Ax Form.
• DMF wizard sometimes duplicate the table cache setting of the target table, in case of entire table cache, DMF will fail.
• In case your records have a refrecid to a record in the same table, DMF will not be able to do the job, (Chicken and his egg, what was first.)
• Add the natural keys of the related tables (where a refrecid is pointing to) to the staging table and write the replacement code yourself. Good examples are
  • DMFBomEntityClass for InventDim fields
  • DMFBomVersionEntityClass for HRMworkers (generate Approver)
• Running DMF not in Batch can be debugged with the AX debugger.
• DMF staging Tables use labels and not the value, so If you are running in EN-US and your customer in NL-NL, trust me it will not work out.

• DMF data is company independent but your target ax tables not, be Shure that you run them in the correct company.

• Import and export your DMF settings. DMF framework uses no refrecid add all so create a definition group
• 
  • And on the end remove all tables that do no start with ‘DMF’
• Create a clean start, in below example the table ending on Entity where my staging tables

AX2012 R2 CU7

• In AX 2012 R2CU7 the labels are missing, you can correct this by importing the labels from AX 2012R2 older ones.
• Don’t forget to remove these label files when you upgrade to CU8.

delete an AX 2012 R2 or R3 partition

We are able to delete a company in AX, but deleting a partition is not possible.

So why not? It should be easy to create a partition and put data in it with tools like DMF, to enable us to create ad hoc demo / training partitions. Having played for a while with some SQL code, the following code was obtained.

First browse the partitions table to find the RecId of the partition one wants to delete.

The script below contains SQL code to delete PS, EXT and Cube partitions.

Be careful don’t delete the initial partition or a partition with production data. Also note that it will not ask for a confirmation when you run this script. I would strongly recommend you to create a backup of the database before running this.

This script is ‘as-is’. There are no guarantee on this code. I you use it, test it carefully. The results are your responsibility.

It is also possible to delete a company in AX 2012, however you have to delete first all transactions with class sysDatabaseTransDelete (see also http://daxture.blogspot.nl/2014/09/delete-legal-entities-in-ax-2012.html)

As a side not, cleaning up the partition on the Contoso demo data will not reduce the size of your database.

Violation of PRIMARY KEY constraint ‘PK_ModelElementData_ElementHandle’ with AX 2012R2 CU8

Last week, I was upgrading AX 2012 R2 CU7 to CU8 and ran into a model store error. The error was similar to http://blogs.msdn.com/b/axsupport/archive/2014/01/08/violation-of-primary-key-constraint-pk-modelelementdata-elementhandle-error-installing-some-hotfixes-on-microsoft-dynamics-ax-2012-r2.aspx

It scared the hell out of me! First thing that came to mind was: is my model store corrupt in production?

But the source of the problem was indeed different; found out that the issue was label files.

In the AX 2012 R2 CU7, the DMF labels were missing, so this was fixed by adding them manually (see picture below)

This means that my AX2012 R2CU7 model store was consistent.

Label files cannot be deleted directly from the AOT. In such cases, the following workaround is necessary:

1. Create a new model
2. Move the labels to this new model
3. Finally, delete this model