Load trade agreements using Excel add-on

Today, a colleague asked me to help loading trade agreements. I tried Excel add-on and there is a little trick. When I opened in Excel the PriceDiscAdmTrans table where the trade agreements reside, got an error

Here is what I did:

1. 1     Manually create a trade agreement in AX and take a note of the journal number. Leave lines blank.
   2.  Go to AOT and modify the followings on the usr layer 
   a.       Go to table PriceDiscTable, Indexes PriceDiscIdx, change AlternateKey to Yes
   b.      Go to table PriceDiscTable, Properties, change ReplacementKey to PriceDiscIdx
   c.       Save changes
   d.      Click Generate incremental CIL button
   3.  Now use Excel add-on to populate the fields for table PriceDiscAdmTrans and you should be good to go. See my previous post on how to work with the Excel add-on.
   4. After all this of course remember to delete all changes on the usr layer and re-compile.

Override permissions in a role

I recently came across a trick on setting up security in AX 2012. We wanted to create a new role by copying an existing role and removing a few duties from the role. I managed copying the role through some coding and that went well. However, when I tested the new role, it doesn't behave exactly the same as the original role. After poking around a bit, I found this nice new function in AX 2012 "Override Permissions" on the role form. As Microsoft noted, "Overrides for securable objects are not associated with specific duties or privileges. If you apply an override, the access level for the object is set for the role, regardless of access levels specified by the duties and privileges assigned to that role." I found this is better and easier than changing the duties or privilege itself.

You can also go to the AOT and look up in the table SecurityRolePermissionOverride and see what permissions are overridden.

One last note, you don't want to override too many permissions as it'll slow down your system performance.


Updated on 2/29/2012
Today had two discoveries on AX 2012 security settings.

1. Unlike AX 2009, any change from the Security Role/Privileges form (e.g. adding a new role, adding/removing a duty from a role) auto triggers a change on the AOT under the Security section! If you're working from a usr layer (as a regular functional consultant would do), the changes will be on the USR layer!! I learned this in a hard way as I found all my security settings gone after a recent code deployment. So, if you don't want to keep redo your security settings in AX 2012, log onto the CUS layer and do the configuration, or merge the code after you're done on the USR layer. 
2. Another way to duplicate an existing role is to go to AOT>Security>Roles and duplicate the role. Then all duties and permissions will get copied to the new role. Much better.

AX 2012 Data Export/Import

Meant to do a blog on data import in AX 2012 for a long time but didn't have time. In previous AX versions, data can be import/export using Excel template in AX. Data migration in AX 2012 is very different because of the data structure changes in tables. For some reason it's been a mysterious puzzle in AX 2012 implementation for a lack of documentation, and it's become a non-intuitive process. Based on sources available from AX online community and Microsoft support, the followings are the options for data import in AX 2012:

1. Write code to use AIF web services. This is actually recommended by Microsoft support. Problem is you'll need a developer.
2. Use AX export/import definition group. This option exists in previous versions too and it creates a .dat file and .def file. Problem is the exported data is hard to edit and the .def file is difficult to understand. It's a good tool make a duplicate company but not for loading master records.
3. Microsoft just released an online service RapidStart last October, which is meant to facilitate initial company config and data import. Though I had a bad dream with this service for the first time played it, I still have my faith in this tool. Microsoft engineer has come back to us and asked for more testing, but I just didn't get time to take on this thread for now. Will report back if I get time to test it more. Feel free to drop me a line if you have experience with the service.
4. Use Excel AX add-on. This is by far the most efficient tool we've found for data import. Similar to the old Excel template and easy to use.

Here is how to load items into AX 2012 using Excel add-on. To make our example more interesting, I'll load products and items (i.e. released product) together. Assume product and item setups have been properly configured in AX. 

Open Excel and click Dynamics AX tab. Click Connections, select Legal Entity and click Ok

Select Add Data - Add Table, and add the following tables and click Ok

• EcoResProduct
• EcoResTrackingDimensionGroupProduct
• EcoResStorageDimensionGroupProduct
• EcoResProductTranslation 
• EcoResProductDimensionGroupProduct
• InventTable 
• InventModelGroupItem
• InventItemGroupItem
• InventTableModule (optional)
• InventItemSetupSupplyType  (optional)

InventItemSetupSupplyType is optional. But, if it's not included, item won't show on the item dropdown on purchase order lines.

Fill in data on each worksheet. If need to look up field value, can click Field Lookup button on the ribbon. A few tricks on working with the tables:

• Close the Field Chooser side column before your edit data
• Only include table fields you'll put data in.
• Make sure your cursor is outside the work area, or otherwise Excel would complain your data is incomplete. 
• DO NOT tab at the end of the table, or otherwise it'll generate a new line at the end and cause data incomplete error.

After filled in all data needed, click on Publish Data - Publish Options. You must tell AX the sequence how to load the tables, for example, the above listed tables are in a correct sequence. Or otherwise, when you try to load data, you'll get an error saying data doesn't exist in other tables. 

Finally, click Publish Data - Publish All to load data. If everything goes well, you should get a separate worksheet and it shows the number of records that have been loaded successfully or how many were failed. 

Here is the template file I generated. Very simple and you can expand it to include more fields to fit your needs.

Excel AX add-on seems like a great tool for data migration. I'm still learning all the tricks myself too. Just find another blog on the same topic today Dynamics AX 2012 Products Import using Excel Add-in. It has great details. If you have any experience or comment on data migration in AX 2012, feel free to let me know.

RapidStart experiment

As started planning out a new AX 2012 implementation, we wanted to give it a try on the RapidStart service , a free online tool offered by Microsoft to help configure and deploy Dynamics AX 2012. It's a new service, probably out there for several months. The whitepaper lays out all the great features and it looks like a great product that can be leveraged by any AX 2012 project. You can read more about the service from AX team blog.

Our experience with the RapidStart service wasn't pleasant. First it was a service registration issue. After we could log in and see the interface, we couldn't get the configuration completed in any of the ways described in the whitepaper. See screenshots below for the errors. We logged an support ticket with Microsoft and they confirmed the issues. Their support group is actively working on solutions. I'll give updates when I hear things back. 

No. of pending questions "-16"

Error at the end

Two small bugs in AX 2012 RTM image

Working on the AX 2012 RTM image today and found two bugs.
1. When to post and print PO proforma/confirmation, get the following error

To fix it, go to Procurement > Setup > Forms > Sorting parameters, remove UNKNOWN 

2. When to invoice PO, debugger pops up. Commenting out the assertion line in VendDocumentLineType - VendInvoiceInfoTable should do it.

AX 2012 Model Management

AX 2012 introduces a "model" concept to help manage different solutions in the same layer. It also provides a commond-line tool to manage models, AxUtil.exe. (Some of its functions can be achieve through Model Management Wizards under Tools in the development environment.)

• Default path to the command: <drive>:\Program Files\Microsoft Dynamics AX\60\Server\<InstanceName>\Bin
• To list all models: AxUtil.exe list
• To create a model: AxUtil.exe create /model:CONTOSO /layer:usr
• To edit a model version number: AxUtil.exe edit /model:CONTOSO /manifest:Version="2.0.0.0"
• To export a model: AxUtil.exe export /file:c:\ContosoModel.axmodel/model:CONTOSO
• To delete a model: AxUtil.exe delete /model:CONTOSO

See Models, Layers, and the Model Store on TechNet for more discussion on how to deploy customizations through different AX environments.

AX 2012 system architecture diagram

A nice and concise illustration of AX 2012 system architecture from MS

Or, a more comprehensive and detailed diagram from TechNet

AX 2012 system architecture from TechNet

AX 2012 Types of Reports

A nice summary and comparison from MS training manual.

AX 2012 Reporting tools

Or as described in the AX developer center (from a functionality perspective)

AX 2012 Types of Reports

Duplicate company in AX 2012

The Duplicate Company function in AX 2009 is deprecated in AX 2012, and the reason is "The organization model represents a paradigm shift in Microsoft Dynamics AX 2012", said by Microsoft. To copy a company in AX 2012 for demo or test, now you'll have to

1. create a definition group
2. export the def group from old company
3. import the def group to the new company

When you create def group, go to Include table group tab and make sure you've selected the tables you want to bring into your new company. When you import, go to the Advanced tab and select/deselect the Include shared tables checkbox as it's needed. Microsoft has a very detailed tutorial on this process:

Use Microsoft Dynamics AX to transfer (export and import) data [AX 2012]

Error when launch AX 2012 in VM using non-admin user

If you've tried to launch AX 2012 RTM in the VM using one of the enabled regular users (instead of admin, you probably have seen the error message: Logon Failure: the user has not granted the requested logon type at this computer. Remember the OS in the provided image is Windows Server 2008. Of course it doesn't allow local access by a non-admin user! The workaround is to go to Active Directory Users and Computers, select the user, double click to bring up Properties, click Member Of tab, and assign Domain Admin to the user. You don't want to do this in a real world but good enough to get AX in the VM going.

Microsoft Dynamics AX Tech Conf 2011 Part II

New Way of Configuring Products – Constraint-based Approach

• Dev license is no longer needed for configuring products
• Can be used to configure both item and service
• All levels of the model can be viewed on the same form
• It’s based on a Constraint Solver – delivered by the MS Solver Foundation
• Constraint-based approach is a declarative language of natural rule formalization. Compared to rule-based, more efficient and no more nested if-else statements needed.
• Configurator components include: Attributes, Constrainnts (table and expression), Sub components, User requirements, BOM lines, Route operations
• Rule-based configuration is still supported but not recommended.
• Shipped with a configurator API
• Limitations: no support on intercompany sales due company-specific BOM’s; re-generate configuration id each time even when the same attributes are selected

Developing Adv Reports: Deep Dive

• Underlying message: MorphX report is still supported by AX 2012 but not recommended an will be eventually replaced by SSRS

Upgrade Tools for Developers & Admin

• Tool: Preprocessing Upgrade Checklist
• Support upgrade from 4.0 and 2009 to AX 2012
• Upgrade procedure is as follows:
• Upgrade readiness: checks if there is any upgrade conflict or issue
• Preprocessing: converts data and puts into shadow tables. There are some out-of-box classes that can handle data conversion to adapt differences between the old versions and AX 2012. e.g. financial dimensions, products, etc. In fact, I think this is a good reference/overview of what’s new in AX 2012.
• Delta preprocessing: only preprocesses data that has been updated since last preprocessing. Makes upgrade more efficient.
• Actual upgrade
• Preprocessing consists 80% of the upgrade and it doesn’t affect the system availability. Only the last step requires downtime.
• Preprocessing tasks can run in parallel and workload can be controlled based on the traffic in the system

App Integration Framework Enhancements

• Simplified config: the number of AIF config menu items are reduced to very few; “End points” are replaced by “Ports”
• Support non-XML input files: non-XML is auto converted to XML format in AX
  Purchasing Process Drill-down
• Procurement category: managed in a structured hierarchy and can be imported from files. Allows purchasing without an item number!
• Procurement catalog: internal procurement catalog and external vendor catalog. External vendor catalog allows user purchasing from a vendor’s website.
• Procurement policies: act as validation rules on purchasing
• Purchase req’s with the same vendor id can be combined and converted to one PO.
• 17 out-0f-box procurement workflows, e.g. purch order, purch line, etc.

Microsoft Dynamics AX Tech Conf 2011 Part I

I was thrilled to attend the DAX tech conf 2011 last week. It was a great 4-day event, because not only I had the chance to have a very refresh look of the new AX release – AX 2012, but also I was surprised and encouraged by the number of attendees – over 1200, doubling MS expectations. It’s great to see such tremendous interests in the market. Without any doubt, I think AX 2012 is a big improvement over the previous versions. Here is a summary of the sessions I went.

Financial Data Architecture

• Shared COA, currencies, calendars and dimensions
• Unlimited # of dimensions
• Dimension can be created based on user’s custom list, e.g. site, campaign, etc.
  Global Address Book
• To reduce data redundancy, addresses are ONLY stored in the address table and not populated in other related tables, e.g. salestable, purchtable.
• Unlimited # of addresses: 1 type of address can have multiple addresses.
• Addresses can be selected on the go.
• Security on GAB controlled by “policies”: creat a team and assign the team to GAB to restrict access on related records.

Patterns in Building Forms for Usability (2 sessions)

• Form layout has changed. It has nav pane, action pane (replacing buttons on the RHS), filter view, grid view, description pane, and fact box.
• When design a form, can select from Styple, e.g. list page, detail form, etc.
• Grid view is grouped by fast tabs instead of tabs
• Form has Edit and View modes, to prevent user accidentally deleting/editing records
• Same form can be published for EP use. Simple like that! This is controlled by a new form property “Display Target”, i.e. auto, form, EP
• Parts (like widget). Can be reused on any form. E.g. fact box, description pane.
• Fact boxes display a collection of related records to the current record, e.g. customer, sales orders for this customer.
• AX wizard tool for validating form style. Inconsistencies can be fixed by either auto fix or user manual change.
• Menu item has a region/country property. In each company, it only shows menu items matching the country code.

Role-based Security (2 sessions)

• Roles (80): a collection of business duties, more like business title e.g. AP clerk, sales manager, etc.
• Duties (767): job functions and can be categories by process cycles, e.g. cutting cheques, sales order entry, etc.
• Priviledges (5777): a group of entry points for specific tasks i.e. menu accesses, e.g. create AP payment journal, create sales order, etc.
• Permissions (20k+): security on objects, e.g. CREATE ledgerjournaltable, CREATE ledgerjournaltrans, etc.
• Out-of-box roles can be categorized by functional (AP clerk), org title (CFO), app role (system user)
• Can dynamically assign roles to users based on user defined query, e.g. based on job title
• Role hierarchy is supported, e.g. acct mgr could include acct clerk role.
• Duties can be categorized as Enable, Maintain, Inquire, Record, Verify, etc.
• Duties should may need to customize to represent business domains, and should stay relatively stable over release.
• New security access to standard AX objects should rarely go below the Duties level, meaning adding/editing roles and duties should be good enough to meet business security requirements.
• Dont need to maintain security for different domains. Can select which company user should get the role when do role assignment. Can assign to companies across domains!!
• Can assign user either roles or duties or priviledges
• Duty segregation: to avoid security conflict, can set up segregation rules so whenever a conflict occurs AX will alert for proper resolution.
• XDS (extended Data Security) framework: allows to define “policies” to restrict views on linked tables. e.g. Primary table (custtable) – constrained tables (custtrans, saels table, projtable), and a policy defines a query that the user shouldn’t see any records from certain customers.
• Out of box AX 2012 has 11 predefined policies
• Ax users don’t have to in the AD. Easier to manager external users.
• Security key is no longer needed

Product Master Data

• Add in a new concept “Product” on the top. It is shared across all companies.
• “Items” are replaced by “Released Products”. Items can’t be used till products are released
• Item can be directly created without first going through product master, and a product master will be created correspondingly.
• “Inventory dimension” is replaced by “proudct dimension”, “storage dimension” and “tracking dimension”.
• Items can be shared across different companies when they’re released
• Product dimensions, storage dimensions and prod attributes are shared
• Product type: Item, Service
• BOM is not shared
• Product can have different “product variants” based on combination of dims. Each prod variant can be released as an item.
• Item model group has added a checkbox for stock or non stock items. Item item with non-stock (e.g. expense items)- inventtrans but no inventsum.

To be continued.