Thursday 31 December 2015

Bulk disable of indexes

Recently we did some performance assessment of our system using DynamicsPerf and identified unused indexes. Some of these indexes were heavily updated but were not used. So a decision was made to disable these indexes. The indexes were in a csv file (in the format tableName, indexName).

Following script was used to disable the indexes instead of manually doing the task. As we use source control, the script checks if the table is in source control and if so, it checks it out before disabling it. If not it adds the table to source control after disabling the index. It also creates a private project with all the affected tables added to it. DB synchronization will have to be done manually.

 static void readAndDisableIndexes(Args _args)  
 {  
   #AOT  
   #File  
   #SysVersionControl  
   
   TreeNodePath        path;  
   TextIo              file;  
   container           data;  
   FileIOPermission    permission;  
   int                 totalTableAdded, totalIndexesDisabled;  
   str                 fileName = 'C:\\Temp\\UnUsedIndex.csv';  
   Set                 set = new Set(Types::String);  
   Set                 setAddedToVS = new Set(Types::String);  
   boolean             inVS;  
   boolean             vcsEnabled = SysVersionControlParameters::isVCSEnabled();  
   
   UtilElements        currUtilElement;  
   IdentifierName      tableName, indexToDisable;  
   IdentifierName      projectName = 'MSPerfPorject';  
   
   ProjectSharedPrivate    projectType = ProjectSharedPrivate::ProjPrivate;  
   ProjectNode             projectNode = SysTreeNode::getPrivateProject().AOTfindChild(projectName);  
   SysVersionControlSystem vcsSys = VersionControl.parmSysVersionControlSystem();  
   
   ProjectNode createProject(IdentifierName _projectName, ProjectSharedPrivate _projectType)  
   {  
     ProjectNode projectNodeLoc = SysTreeNode::getPrivateProject().AOTfindChild(_projectName);  
   
     info(strFmt('Project name : %1', _projectName));  
     if (projectNodeLoc)  
     {  
       projectNodeLoc.AOTdelete();  
       info('Project deleted as it already existed.');  
     }  
   
     projectNodeLoc = SysTreeNode::createProject(_projectName, _projectType);  
     info('Project created.');  
   
     return projectNodeLoc;  
   }  
   
   void addTableToProject(ProjectNode _projectNode, IdentifierName _tableToAddName)  
   {  
     _projectNode.addUtilNode(UtilElementType::Table, _tableToAddName);  
     info(strFmt('Table added: %1', _tableToAddName));  
   }  
   
   boolean checkoutIfInVS(SysVersionControlSystem _vcsSys, UtilElements _utilElement)  
   {  
     boolean ret = false;  
     TreeNode treeNode;  
     SysVersionControllable controlable;  
   
     treeNode = SysTreeNode::findNodeInLayer(_utilElement.recordType, _utilElement.name, _utilElement.parentId, currUtilElement.utilLevel);  
     controlable = SysTreeNode::newTreeNode(treeNode);  
   
     if(!_vcsSys.allowCreate(controlable))  
     {  
       ret = true;  
       _vcsSys.commandCheckOut(controlable);  
       info('Table checked out as it is part of VCS.');  
     }  
   
     return ret;  
   }  
   
   void addToVS(SysVersionControlSystem _vcsSys, UtilElements _utilElement)  
   {  
     TreeNode treeNode;  
     SysVersionControllable controlable;  
   
     treeNode = SysTreeNode::findNodeInLayer(_utilElement.recordType, _utilElement.name, _utilElement.parentId, currUtilElement.utilLevel);  
     controlable = SysTreeNode::newTreeNode(treeNode);  
   
     if(_vcsSys.allowCreate(controlable))  
     {  
       _vcsSys.commandAdd(controlable);  
       info('Table added to VCS.');  
     }  
   }  
     
   void disableIndex(IdentifierName _tableName, IdentifierName _indexNameSQL)  
   {  
     DictIndex  dictIndex;  
     DictTable  dictTable = new DictTable(tableName2id(_tableName));  
     int counter;  
       
     if(dictTable)  
     {  
       counter = dictTable.indexNext(counter);  
       while (counter)  
       {  
         dictIndex = dictTable.indexObject(counter);  
   
         if(dictIndex.enabled() && dictIndex.name(DbBackend::Sql) == _indexNameSQL)  
         {  
           dictIndex.modify(false, dictIndex.allowDuplicates(), false);  
           info(strFmt('Disabled index : %1 - %2', dictIndex.name(), _indexNameSQL));  
         }  
   
         counter = dictTable.indexNext(counter);  
       }  
     }  
   }  
   
   // project initialisation  
   projectNode = createProject(projectName, projectType);  
   projectNode.lockUpdate();  
   
   // file operations  
   permission = new FileIOPermission(fileName, #io_read);  
   permission.assert();  
   file = new TextIO(fileName, #io_read);  
   file.inFieldDelimiter(",");  
   
   //Read file  
   While (file.status() == IO_Status::Ok)  
   {  
     data = file.read();  
   
     if (conlen(data))  
     {  
       inVS = false;  
   
       tableName   = conPeek(data, 1);  
       indexToDisable = conPeek(data, 2);  
   
       // check and add table to project  
       currUtilElement = xUtilElements::find(UtilElementType::Table, tableName);  
       path = xUtilElements::getNodePath(currUtilElement);  
   
       if(!set.in(path))  
       {  
         addTableToProject(projectNode, tableName);  
         set.add(path);  
   
         if(vcsEnabled)  
         {  
           inVS = checkoutIfInVS(vcsSys, currUtilElement);  
         }  
       }  
         
       // Make the change  
       disableIndex(tableName, indexToDisable);  
   
       // if not in VS Add to Version Control  
       if (vcsEnabled && !inVS && !setAddedToVS.in(tableName))  
       {  
         addToVS(vcsSys, currUtilElement);  
       }  
       setAddedToVS.add(tableName);  
     }  
   }  
   
   // Save the project  
   projectNode.unlockUpdate();  
   projectNode.AOTsave();  
   projectNode.AOTrestore(true);  
     
   // Close file  
   file = Null;  
   CodeAccessPermission::revertAssert();  
   
   info('Finished process.');  
 }  

This posting is provided "AS IS" with no warranties. Use code at your own risk.

Wednesday 30 December 2015

AX7 Technical Changes Summary

With the AX 7, Microsoft Dynamics AX have been changed in such a manner that it can truly be classed as a brand new platform. On the function side, the changes are minimal (only the required changes to run the features on this  new platform).

In today's post, we'll look briefly at what is changed from technical perspective.

Client
The win 32 rich client is replaced with a browser (IE, Edge, Chrome, Safari).

AOS
Replaced with the ASP.Net web application running on an IIS.

Batch
If the AOS is gone, how my batches will run? Well a new windows service will perform this task.

MorphX
This is replaced with Visual Studio. Extensions have been added to VS to deal with model management, code compile and build. The compilation can either be done from VS or using a standalone tool xppc.

XPP
XPP stays as it is  (on the surface). Under the hood there is no p-code, no interpreter. The compile now results in CIL. The unit of compile is the object itself. What does this means? Before AX7 if a method of a class fails to compile, other methods were still usable. IN AX7 the whole class's compilation will fail. There are a lot of other enhancements to XPP. MFP's blog is the best place to find out what these are.

AOT
AOT is visible from VS in a read-only mode and is called "Application Explorer". It is available in "Classic view" and "Model View". The object location have changed for some of the objects  (EDT, classes etc.) and these are now grouped with similar objects.

Model Store
Model store is no more in the database. The AX application code is stored in the files on disk in XML format.

Model
The concept of model is same as previous versions i.e. a group of objects that typically can be distributed.

Package
New concept in AX7. A package can consist of one or more models and is a used for deployment/compilation of these models. Each package have its own folder. Inside the package folder model folders exist.

VS Solution
A VS solution is a collection of projects. The solution information is not saved in the packages/models.

VS Project
A VS project is a group of objects that are related. A VS solution can have more than one VS project. Similar to VS solution, the project information is not saved in the packages/models. A VS project can only belong to one Model. Projects can be exported as .axpp file and imported on other AX7 installations.

Debugging
There is no standalone debugging application. VS is used for debugging. To start debugging a start-up project and start-up object must be specified.

Build
A single project, model or a set of models can be build. The option to synchronise database is available as part of the build process.


This posting is provided "AS IS" with no warranties. Use code at your own risk.

Tuesday 22 December 2015

Read/Write Excel files from AOS using OpenXML

Read/write of Excel files on the client tier is well documented and it often requires Office to be installed on the machine. There are wrappers available in AX as well. However if you want to do the same operations on the server tier, options are limited. And as most administrators don't want to install office on the AOS, this further limits the options available.

One solution it to use Office OpenXML SDK. Microsoft has already added the required DLLto AX and can be found in References node in the AOT as shown below.


There are wrapper classes around this DLL as well.


As you can see from the name suffix these classes were written for Russian localisation. They work very well on the client tier. However on the server tier these classes have an issue as these uses methods from another class "ComExcelDocument_RU" which is marked to run on Client. Hence any use of these classes on server tier will result in error.

An easy work around is to copy the methods to the class OXMLSpreadsheetDocument_RU and replace ComExcelDocument_RU with OXMLSpreadsheetDocument_RU in the rest of classes. Below is screenshot of modified class.


Once changed, these classes can be used on the server tier.

Note: In AX 2012 R2 (and possibly in other versions) the class OXMLSpreadsheetDocument_RU only opens an excel file, It does not create one. There are two possible solutions to this.

1. Modify the class to create a new excel document.
2. Save an empty excel file as a "Resource" in AOT. And every time you need to write data to excel file, save the resource as file and use OXMLSpreadsheetDocument_RU for writing data in it.

This posting is provided "AS IS" with no warranties. Use code at your own risk.

Monday 21 December 2015

Running AX7 VM on Oracle VirtualBox

In my previous post I described how to obtain the VM for AX7 CTP8. To run this VM you do not have to use Hyper V, VirtualBox works perfectly as well. In today's post I'll outline the steps required to run this VM in the VirtualBox.

Once you have downloaded the VM (20 files of almost 20 GB size), double click on the first file.


This will ask you the folder where to extract the files. Choose a folder and click Extract. Make sure that the drive where you are extracting the files have at least 120 GB space. More space will be required if you want to save the state of the machine instead of turning it off every time.


This step will take a while depending on your hardware capabilities. Once done you'll have a single VHD file.


Open VirtualBox and click on New button.


Name the virtual machine and choose windows OS as shown below.


Click on Next and choose RAM. MS recommends having a VM of at least 16 GB RAM. In my test machine I could only give it 6 GB, it was slow but not very slow.


Click Next and choose the extracted VHD,


Clicking on Create will create the VM. Before starting the VM, click on the Settings and change the number of CPUs as shown below. More CPU the better it will be.


The network on a VirtualBox VM is by default configured using NAT as shown below so no special setup is required here.


Now the VM can be started. Once started, on the the login page you'll see the familiar Windows 2012 credentials screen. The famous MS password for the VM Administrator account can be used to login.


Once logged in, open IE and click in the address bar. Here the AX7 link should automatically appear (highlighted below) as it was used previously when MS created the VM.


Once clicked, it will navigate to the Sign in page.


The password for this is mentioned on the AX 7 Sign Up page on Customer/Partner source. After entering the password, AX 7 opens up.


As my test machine was not very powerful, I initially got an error (I assume not everything was started), however after some time when I came back to this page and refreshed it, it worked fine.

This posting is provided "AS IS" with no warranties. Use code at your own risk.

Sunday 20 December 2015

How to get AX 7 Technical Preview VM

Couple of days ago "Microsoft Dynamics AX" public preview went live. @satishthomas from Microsoft blogged more info on this (link). In summary following needs to be done to get it going

1. For customer source use this link  and for partner source use this link.
2. Login using either customer or partner source credentials.
3. Once logged in scroll down and you'll see a small paragraph as shown below


Clicking on the highlighted part will get you going on signing up for the preview. More details on these steps can be found on Satish's blog.

However if you want to get a VM to run it locally, you'll have to take some additional steps. Once you have signed up for the preview, come back to the sign up page on customer/partner source and scroll further down in the FAQ section and find the feedback question as shown below.


Clicking on the link will take you to Connect site where you'll be automatically added to preview program. There you'll see a download section as shown below

The download section will contain the link to download the preview VM



This posting is provided "AS IS" with no warranties. Use code at your own risk.

Friday 18 December 2015

AX 7 Help Wiki available

Help wiki is now available for technical preview of new "Microsoft Dynamics AX" aka AX 7. It can be accessed from the following link

https://ax.help.dynamics.com/en/

The wiki details how to sign up for AX 7, get an instance running either in cloud or get a vhd to run locally, technical changes and a lot more.

This posting is provided "AS IS" with no warranties. Use code at your own risk.