D365FO. Working with Azure File storage.

Azure Storage - Files

Current version of AX uses Azure Blob storage for various things like document handling, retail CDX files, DIXF and Excel add-in. You can find several blogs explaining how to upload and download files to Blob, SharePoint or temporary storage. However, what about file shares?

Azure File storage implements SMB 3.0 protocol and could be easily mapped to your local computer. You need just a few minutes to create new storage account and mount it, watch this how-to video for details.

To read file from newly created share we can use next code:

using Microsoft.Azure;
using Microsoft.WindowsAzure.Storage;
using Microsoft.WindowsAzure.Storage.Blob;
using Microsoft.WindowsAzure.Storage.File;

class RunnableClass1
{
    public static void main(Args _args)
    {
        System.IO.MemoryStream memoryStream;

        var storageCredentials = new Microsoft.WindowsAzure.Storage.Auth.StorageCredentials('AzureStorageAccountName', 'AzureStorageAccountKey');

        CloudStorageAccount storageAccount = new Microsoft.WindowsAzure.Storage.CloudStorageAccount(storageCredentials, true);

        CloudFileClient fileClient = storageAccount.CreateCloudFileClient();

        CloudFileShare share = fileClient.GetShareReference('AzureStorageShareName');

        if (share.Exists(null, null))
        {
            CloudFileDirectory rootDir = share.GetRootDirectoryReference();

            CloudFileDirectory fileDir = rootDir.GetDirectoryReference('folder');

            if (fileDir.Exists(null, null))
            {
                CloudFile file = fileDir.GetFileReference('file.txt');

                if (file.Exists(null, null))
                {
                    memoryStream = new System.IO.MemoryStream();
                    file.DownloadToStream(memoryStream, null, null, null);
                }
            }
        }
    }
}

References:

Edited:

Azure File Storage client source code could be found on GitHub

Advertisements

D365O. Trick to pass a value between Pre and Post event handler using XppPrePostArgs.

Recently we came across a scenario where we needed to check if a field has changed after super() in update method of a table. Back in the days of AX 2012 you could easily compare original field’s value with current using orig() method before super() and call necessary logic after.

public void update()
{
    boolean myFieldHasChanged = this.MyField != this.orig().MyField;

    super();

    if (myFieldHasChanged)
    {
        this.doStuff();
    }
}

Now we want to do the same using extensions. We can create Pre and Post event handlers, but they are static, so we need a way to pass a value between them.
First option is to use static field, like it’s done in RunBase extension example

public static class MyTableEventHandler
{
    private static UnknownNoYes myFieldHasChanged;

    [PreHandlerFor(tableStr(MyTable), tableMethodStr(MyTable, update))]
    public static void MyTable_Pre_update(XppPrePostArgs _args)
    {
        MyTable myTable = _args.getThis() as MyTable;

        if (myTable.MyField != myTable.orig().MyField)
        {
            MyTableEventHandler::myFieldHasChanged = UnknownNoYes::Yes;
        }
        else
        {
            MyTableEventHandler::myFieldHasChanged = UnknownNoYes::No;
        }
    }

    [PostHandlerFor(tableStr(MyTable), tableMethodStr(MyTable, update))]
    public static void MyTable_Post_update(XppPrePostArgs _args)
    {
        MyTable myTable = _args.getThis() as MyTable;

        if (MyTableEventHandler::myFieldHasChanged == UnknownNoYes::Yes)
        {
            myTable.doStuff();
        }

        MyTableEventHandler::myFieldHasChanged = UnknownNoYes::Unknown;
    }
}

Another option is to use XppPrePostArgs as a vehicle for new parameter. XppPrePostArgs has collection of parameters under the hood, so nothing stops us to add one more and framework will take care of passing it between Pre and Post event handler!

XppPrePostArgs_collection.jpg

public static class MyTableEventHandler_XppPrePostArgs
{
    const static str myFieldHasChangedArgName = 'myFieldHasChanged';

    [PreHandlerFor(tableStr(MyTable), tableMethodStr(MyTable, update))]
    public static void MyTable_Pre_update(XppPrePostArgs _args)
    {
        MyTable myTable = _args.getThis() as MyTable;

        boolean myFieldHasChanged = myTable.MyField != myTable.orig().MyField;

        <strong>_args.addArg(MyTableEventHandler_XppPrePostArgs::myFieldHasChangedArgName, myFieldHasChanged);</strong>
    }

    [PostHandlerFor(tableStr(MyTable), tableMethodStr(MyTable, update))]
    public static void MyTable_Post_update(XppPrePostArgs _args)
    {
        MyTable myTable = _args.getThis() as MyTable;

        <strong>boolean myFieldHasChanged = _args.getArg(MyTableEventHandler_XppPrePostArgs::myFieldHasChangedArgName);
</strong>
        if (myFieldHasChanged)
        {
            myTable.doStuff();
        }
    }
}

Using one of these approaches you should remember that static fields apply to the class, not to instances of the class, so do not mix well with concurrency. Trick with XppPrePostArgs tightly depends on current implementation, that could be changed anytime and comes with no warranty.

To overcome this and other limitations of extensions capabilities Microsoft is introducing Method wrapping and chain of command and I’m pretty sure that we’ll see blogs on this from my MVP fellows soon.

D365O. How to deploy Demo VM using Visual Studio subscription.

Many of us have Visual Studio subscription (formerly called MSDN subscription) and there is monthly Azure credit coming with it. It could be used to spin up D365O demo VM, but the whole process is not straightforward. I would describe it step by step today.

  1. Activate monthly Azure credit. You will get an email with subscription id after completion.ActivateSubscription.png
  2.  Go to Azure portal where you can check details of new subscription.AzurePortal.jpgAzurePortalSubscription.jpg
  3. Different types of subscriptions give you different amount, enterprise is the best (but costs a lot) and gives you 150$ monthly credit, that is enough to run D12V2 VM for 235 hours per month.
  4. With new subscription you get new tenant. Now we need to activate D365 trial on this tenant. That’s a mandatory step for ARM onboarding. Existing customers can follow steps on the customer source  to request access via email. Partners can find details on the partner source , you have 2 options there: request new tenant with trial or activate trial on existing tenant. We will activate trial because we already have a tenant with subscription, but it’s possible to transfer subscription to new tenant as well.
  5. To activate trial on existing tenant
    1. Create new user .AzureActiveDirectoryAddUser.jpg
    2. Make new user Owner of the subscriptionAzureSubscriptionOwner.jpgAzureSubscriptionOwnerNew.jpg
    3. Make new user Global admin of Active Directory.AzureActiveDirectoryUserRole.jpg
    4. Follow a link to trial offer from partner source article. You need to login using credentials of newly created user.ActivateTrial.jpgconfirmtrial
  6. No we are ready to create new LCS project. Go to LCS and login using user created on step 4. LCSNewProject.jpg
  7. Fill all the details required: Version of AX, project name and methodology.LCSNewProjectDetails.jpg
  8. Setup connection to Azure.LCSMicrosoftAzureSettings.jpg
  9. In the organizations list you will see tenants of project users. You can invite users from different tenants to a project and you will see their organization in the list as well.  lcsnewprojectauthorizeCurrent user should be administrator of the tenant to complete authorization.LCSNewProjectAuthorization.jpgLCSNewProjectAuthorizationComplete.jpg
  10. Add Dynamics Deployment Services [wsfed-enabled] to the subscriptionAddDynamicsDeploymentServices.jpg
  11. Setup Azure connector. Check “Configure to use Azure Resource Manager” checkbox or you won’t be able to deploy any VM starting from Update 2. Azure Subscription Id and tenant name could be found on Azure portal.LCSSetupAzureConnector.jpgLCSSetupAzureConnector2.jpgLCSSetupAzureConnector3.jpg
  12. For ARM deployments we don’t need to download any certificate, so just click “Next” on this step.lcssetupazureconnector4
  13. Select Azure region. Please note that not all regions are available for Visual Studio Subscriptions. If you select wrong region you will have to create new connector with another region because all deployments would fail.LCSSetupAzureConnectorRegion.jpg
  14. Deploy new Demo VMLCSDeployNewVM.jpgLCSDeployNewVM2.jpgLCSDeployNewVM3.jpg
  15. Enter VM name and go to Advance settingsLCSDeployNewVM5.jpg
  16. Select demo data package or “None” for blank environment. Here you could select demo data from ISV partners as well if they shared it with you.LCSDeployNewVM6.jpg
  17. Select number of disks. Select 7 if you want to scale it down to D12V2 later, otherwise you would have to manually delete them, that is not an easy task, because it’s not possible to change size if target VM has different disks configuration. LCSDeployNewVM7.jpg
  18. Select GER configuration to be deployed. You can deploy any later from assets library if you missed this step.LCSDeployNewVM8.jpg
  19. Select VM size. Different sizes have different hardware configuration and costs. To find costs you can use Pricing calculator. I prefer to use D13V2 and D12V2 because I think they have best  price–performance ratio.LCSDeployNewVM9.jpg

That’s all. Usually deployment takes up to 5 hours. Don’t forget to setup auto shutdown to save your costs!

Links

AX 7. Trick to override method in derived class without overlaying.

Scenario:

The VendDocumentLineType class has several subclasses that handle specific types of vendor document lines. We want to change a behaviour for invoice lines and implement new logic for Purchase Unit field defaulting.

Solution using overlaying:

Overlay VendDocumentLineType_Invoice class and override determineDefaultPurchUnit method.

Solution without overlaying:

venddocumentlinetypehierarchy

1) Create class derived from VendDocumentLineType_Invoice:

[Form]
/// <summary>
/// The <c>VendDocumentLineType_Invoice_Sample</c> class is used for validation and applying default values to invoice lines.
/// </summary>
class VendDocumentLineType_Sample extends VendDocumentLineType_Invoice
{
}

2) Override determineDefaultPurchUnit method:

protected PurchUnit determineDefaultPurchUnit()
{
    // implement new behaviour
    return VendParameters::find().DefaultPurchUnit_Sample;
}

3) Create post event handler for VendDocumentLineType constructor to replace VendDocumentLineType_Invoice with new implementation:

/// <summary>
/// Handles events raised by <c>VendDocumentLineType</c> class.
/// </summary>

public static class VendDocumentLineTypeEventHandler_Sample
{
    /// <summary>
    /// Post event handler for VendDocumentLineType.createFromTable(...)
    /// </summary>
    /// <param name="_args"></param>
    [PostHandlerFor(classStr(VendDocumentLineType), staticMethodStr(VendDocumentLineType, createFromTable))]
    public static void VendDocumentLineType_Post_createFromTable(XppPrePostArgs _args)
    {
        // get params from args
        VendDocumentLineMap vendDocumentLineMap = _args.getArg(identifierStr(_vendDocumentLineMap));
        PurchLine           purchLine           = _args.getArg(identifierStr(_purchLine));
        PurchParmUpdate     purchParmUpdate     = _args.getArg(identifierStr(_purchParmUpdate));

        // construct and init new object
        VendDocumentLineType strategy = VendDocumentLineType_Invoice_Sample::createFromTable(vendDocumentLineMap, purchLine, purchParmUpdate);

        // replace return value only if new strategy is created; otherwise use standard
        if (strategy)
        {
           // replace return value with new implementation
            _args.setReturnValue(strategy);
        }
    }

}

4) Implement methods that will instantiate and initialize VendDocumentLineType_Invoice_Sample class:

/// <summary>
/// Constructs a new instance of a <c>VendDocumentLineType_Invoice_Sample</c> class derivative.
/// </summary>
/// <param name="_vendDocumentLineMap">
/// A <c>VendDocumentLineMap</c> record.
/// </param>
/// <param name="_purchLine">
/// A <c>PurchLine</c> table record that is used when you apply the default values; optional.
/// </param>
/// <param name="_purchParmUpdate">
/// A <c>PurchParmUpdate</c> table record that is used when you apply the default values; optional.
/// </param>
/// <returns>
/// A <c>VendDocumentLineType_Invoice_Sample</c> class.
/// </returns>
public static VendDocumentLineType_Invoice_Sample createFromTable(VendDocumentLineMap _vendDocumentLineMap, PurchLine _purchLine = null, PurchParmUpdate _purchParmUpdate = null)
{
    VendDocumentLineType_Invoice_Sample strategy;

    // VendDocumentLineType_Invoice is created only for this 2 types
    if (_vendDocumentLineMap.Ordering == DocumentStatus::Invoice ||
        _vendDocumentLineMap.Ordering == DocumentStatus::ApproveJournal)
    {
        strategy = new VendDocumentLineType_Invoice_Sample();
        strategy.init(_vendDocumentLineMap, _purchLine, _purchParmUpdate);
    }

    return strategy;
}

public void init(VendDocumentLineMap _vendDocumentLineMap, PurchLine _purchLine, PurchParmUpdate _purchParmUpdate)
{
    // code copied from VendDocumentLineType::createFromTable to init new instance
    this.vendDocumentLineMap(_vendDocumentLineMap);
    this.purchLine(_purchLine);
    this.physicalStrategy(VendDocumentLineTypePhysical::createFromTable(this, _vendDocumentLineMap));
    this.purchParmUpdate(_purchParmUpdate);
}

You can apply this approach to any class hierarchy in AX that has construct method (like SalesLineType, PurchLineType, etc.) when you need to override methods in derived class to implement new behaviour.

AX 2012 R3. How to disable inventory dimension hashing to improve performance.

As all of you may know there is a SQL limitation on maximum number of fields in index. We cannot have more than 16 (actually maximum is 14 because index includes DataAreaId and Partition fields). It’s quite important for inventory dimensions story and this limitation was hit in AX 2012 R3 with new fields for advance warehouse.

To overcome this limitation new hash field is introduced.  This field contains hashed value of other dimension fields, so-called “secondary dimensions”.  Using new approach, we can add more than 14 dimensions but have to pay a performance overhead for this flexibility.

However, usually we don’t use all inventory dimensions like InventGtdId_RU, InventProfileId_RU and InventOwnerId_RU. So we can disable inventory extensibility to reduce the performance overhead.

Let’s do it step by step.

1. Remove unused fields from DimIdx index of InventDim table.

We don’t use Russian localization, so we will remove InventGtdId_RU, InventProfileId_RU, and InventOwnerId_RU fields from the index.

DimIdx

Also we need to remove SHA1Hash filed because we don’t need it any more.

We will add InventStatusId and LicensePlateId fields to DimIdx index because we are using advance warehouse.

DimIdxNewFields

2. Disable InventDimExtensibility configuration key that controls dimensions extensibility.

3. Modify InventDIm.hashKey() method to skip hash calculation on update and insert.

/// <summary>
/// Calculates the string that is used when the hash value is calculated for the dimension values that
/// are not included in the <c>DimIdx</c> index.
/// </summary>
/// <returns>
/// A string that contains the calculated hash value.
/// </returns>
public str hashKey()
{
    str     hashKey   = '';
    str     hashKeyCaseInsensitive = '';

    #InventDimDevelop

    //Disable invent dimension extensibility -->
    if (!isConfigurationkeyEnabled(configurationKeyNum(InventDimExtensibility)))
    {
        return hashKey;
    }
    //Disable invent dimension extensibility <--

    /*
    SQL Server has a limitation of 16 fields in one index. For InventDim this is a problem if extra dimensions are added in an installation.
    Instead we have added a new field which can store hashed values of the least used dimensions.
    The class InventDimUniquenessEnabling can be used to validate if the proper indexes are defined and this method includes the right fields.

    Create a string this is unique for every combination of the dimensions. This can for example be achieved by using the code pattern below for each field included in the hash.
    The field values must be trimmed for trailing spaces, as this method is invoked before insert() - where such trimming also occurs.

    if (this.<FieldName>)
    {
        hashKey += (hashKey ? '~' : '') + '<FieldName>:' + strRTrim(this.<FieldName>);
    }
    */

    // Due to index limitations, hash the values.
    if (this.LicensePlateId)
    {
        hashKeyCaseInsensitive += (hashKey ? '~' : '') + 'LicensePlateId:' + strRTrim(this.LicensePlateId);
    }

    if (this.InventStatusId)
    {
        hashKeyCaseInsensitive += (hashKeyCaseInsensitive ? '~' : '') + 'InventStatusId:' + strRTrim(this.InventStatusId);
    }

    return hashKey + strLwr(hashKeyCaseInsensitive);
}

4. Modify InventDIm.findDim() method to search by new index fields.

client server static public InventDim findDim(
    InventDim   _inventDim,
    boolean     _forupdate = false)
{
    // <GEERU>
    #ISOCountryRegionCodes
    // </GEERU>
    InventDim       inventDim;

    if (_forupdate)
    {
        inventDim.selectForUpdate(_forupdate);
    }

    // Fields might not have been selected on the specified buffers, or might have been updated since selection
    _inventDim.checkInvalidFieldAccess(false);

    if (isConfigurationkeyEnabled(configurationKeyNum(InventDimExtensibility)))
    {
        select firstonly inventDim
        where inventDim.ConfigId            == _inventDim.ConfigId
           && inventDim.InventSizeId        == _inventDim.InventSizeId
           && inventDim.InventColorId       == _inventDim.InventColorId
           && inventDim.InventStyleId       == _inventDim.InventStyleId
           && inventDim.InventSiteId        == _inventDim.InventSiteId
           && inventDim.InventLocationId    == _inventDim.InventLocationId
           && inventDim.InventBatchId       == _inventDim.InventBatchId
           && inventDim.wmsLocationId       == _inventDim.wmsLocationId
           && inventDim.wmsPalletId         == _inventDim.wmsPalletId
           && inventDim.sha1Hash            == _inventDim.hashValue() // Needed to hit unique index cache. All dimensions should be included in the where clause - also those included in the hash key,
           && inventDim.InventSerialId      == _inventDim.InventSerialId
           && inventDim.InventGtdId_RU      == _inventDim.InventGtdId_RU
           && inventDim.InventProfileId_RU  == _inventDim.InventProfileId_RU
           && inventDim.InventOwnerId_RU    == _inventDim.InventOwnerId_RU;
    }
    else
    {
        select firstonly inventDim
            where inventDim.ConfigId         == _inventDim.ConfigId
               && inventDim.InventSizeId     == _inventDim.InventSizeId
               && inventDim.InventColorId    == _inventDim.InventColorId
               && inventDim.InventStyleId    == _inventDim.InventStyleId
               && inventDim.InventSiteId     == _inventDim.InventSiteId
               && inventDim.InventLocationId == _inventDim.InventLocationId
               && inventDim.InventBatchId    == _inventDim.InventBatchId
               && inventDim.wmsLocationId    == _inventDim.wmsLocationId
               && inventDim.wmsPalletId      == _inventDim.wmsPalletId
               //Disable invent dimension extensibility -->
               /* Orig -->
               // <GEERU>
               && inventDim.InventGtdId_RU      == _inventDim.InventGtdId_RU
               && inventDim.InventProfileId_RU  == _inventDim.InventProfileId_RU
               && inventDim.InventOwnerId_RU    == _inventDim.InventOwnerId_RU
               // </GEERU>
               && inventDim.InventSerialId   == _inventDim.InventSerialId;
               Orig <-- */
               && inventDim.InventSerialId   == _inventDim.InventSerialId
               && inventDim.InventStatusId   == _inventDim.InventStatusId
               && inventDim.LicensePlateId   == _inventDim.LicensePlateId;
               //Disable invent dimension extensibility <--
    }
    #inventDimDevelop

    return inventDim;
}

5. Review all customization and standard code that use removed\added dimensions fields in where statement. This could be done using cross references.

That’s all!

To read more about promoting and demoting inventory dimensions please refer to this msdn article.

Also additional improvements were made in AX 7, for more details read this blog post.

AX 7. How to create new excel template for general journal lines.

Open lines in Excel is a new cool feature in AX 7 that allows you to edit general journal lines using excel.

OpenInExcel

But unfortunately it supports only two account types out of the box: ledger account and customer account. Today we will create a new one for bank.

First of all, we need to create a new Data Entity. There is no difference between Data Entities for account types so I simply copied LedgerJournalLine entity, renamed it and changed AccountType range value to “Bank”.

DataEntity

Public collection name and public entity name properties should be changed, relation entity roles should be updated as well.

Next we need to create a new Excel template file.  I used Excel workbook designer in AX to generate new template based on the new Data Entity. I made my design pretty similar to existing templates for general journal.

Excel

Please note that template has two data entities: one for header and another for lines. As you can see I took the same header entity as other general journal templates has.

This template file should be added to AOT into resources node.

Finally, we need to create a new class. This class should be derived from DocuTemplateRegistrationBase and implement LedgerIJournalExcelTemplate.

 

using Microsoft.Dynamics.Platform.Integration.Office;

/// <summary>
/// The <c>BankJournalExcelTemplate</c> is the supporting class for the Bank Journal Template.
/// </summary>
class BankJournalExcelTemplate extends DocuTemplateRegistrationBase implements LedgerIJournalExcelTemplate
{
    // resource that contains xlsx file.
    private const DocuTemplateName ExcelTemplateName = resourceStr(BankJournalTemplate);
    // lines Data Entity
    private const EntityName LineEntityName = tableStr(BankJournalLineEntity);
    private const FieldName LineEntityJournalNum = fieldStr(BankJournalLineEntity, JournalBatchNumber);
    private const FieldName LineEntityDataAreaId = fieldStr(BankJournalLineEntity, dataAreaId);
    private const FieldName HeaderEntityName = tableStr(LedgerJournalHeaderEntity);
    private const FieldName HeaderEntityJournalNum = fieldStr(LedgerJournalHeaderEntity, JournalBatchNumber);
    // header Data Entity.
    private const FieldName HeaderEntityDataAreaId = fieldStr(LedgerJournalHeaderEntity, dataAreaId);

    public void registerTemplates()
    {
        this.addTemplate(OfficeAppApplicationType::Excel,
                         ExcelTemplateName,
                         ExcelTemplateName,
                         "@GeneralLedger:LedgerJournalLineEntryTemplateDescription",
                         "@MyLabels:BankLedgerDailyJournalTemplateName",
                         NoYes::No,
                         NoYes::No);
    }

    public boolean isJournalTypeSupported(LedgerJournalType _ledgerJournalType)
    {
	// only daily journals are supported in this template.
        return _ledgerJournalType == LedgerJournalType::Daily;
    }

    public DocuTemplateName documentTemplateName()
    {
        return ExcelTemplateName;
    }

    public Set supportedAccountTypes()
    {
	// Set with supported account types.
        Set accountSetTypes = new Set(Types::Integer);

        accountSetTypes.add(LedgerJournalACType::Bank);
        accountSetTypes.add(LedgerJournalACType::Ledger);

        return accountSetTypes;
    }

    public Set supportedOffsetAccountTypes()
    {
        // Set with supported offset account types.
        Set offsetAccountTypeSet = new Set(Types::Integer);

        offsetAccountTypeSet.add(LedgerJournalACType::Ledger);

        return offsetAccountTypeSet;
    }

    public boolean validateJournalForTemplate(LedgerJournalTable _ledgerJournalTable)
    {
        return LedgerJournalExcelTemplate::validateJournalForTemplate(_ledgerJournalTable, this);
    }

    public EntityName headerEntityName()
    {
        return HeaderEntityName;
    }

    public EntityName lineEntityName()
    {
        return LineEntityName;
    }

    public FieldName headerJournalBatchNumberFieldName()
    {
        return HeaderEntityJournalNum;
    }

    public FieldName headerDataAreaFieldName()
    {
        return HeaderEntityDataAreaId;
    }

    public FieldName lineJournalBatchNumberFieldName()
    {
        return LineEntityJournalNum;
    }

    public FieldName lineDataAreaFieldName()
    {
        return LineEntityDataAreaId;
    }

    public FilterCollectionNode appendHeaderEntityFilters(FilterCollectionNode _headerFilter, ExportToExcelFilterTreeBuilder _headerFilterBuilder)
    {
        return _headerFilter;
    }

    public FilterCollectionNode appendLineEntityFilters(FilterCollectionNode _lineFilter, ExportToExcelFilterTreeBuilder _lineFilterBuilder)
    {
	// creates excel filter to show only lines that have Bank account type
        // and Ledger offset account type
        FilterCollectionNode lineFilter = _lineFilterBuilder.and(
            _lineFilterBuilder.areEqual(fieldStr(CustInvoiceJournalLineEntity, AccountType), LedgerJournalACType::Bank),
            _lineFilterBuilder.areEqual(fieldStr(CustInvoiceJournalLineEntity, OffsetAccountType), LedgerJournalACType::Ledger));

        return _lineFilterBuilder.and(_lineFilter, lineFilter);
    }
}

As you can see, code is pretty straightforward and there is only one interesting method appendLineEntityFilters(). It uses FilterCollectionNode to create filters for template, so this template will show only journal lines that has account type bank and offset account type ledger.

When development is done, we need to reload system templates. Go to Common -> Office integration -> Document templates and click “Reload system templates”.

ReloadSystemTemplates.jpg

After that, our new template should appear under Open lines in Excel button.

OpenLinesInExsel.jpg

AX 7. How to add financial dimensions as separate columns to Data Entity.

Everyone who is working with financial dimensions in AX would like to see them in separate columns in Excel and today we will look how to achieve this for all Data Entities.

1

It’s also awesome because we have wizard that will do all the job for us!

In the Visual Studio under Dynamics AX > Addins select “Add financial dimensions for OData…”

2

Now we need to enter dimensions we want to expose. As you can see from a help text this dimensions should be setup in AX on Data Entities tab under General ledger > Chart of accounts > Dimensions > Financial dimension configuration for integrating applications.

3

Also we need to specify a model for new objects. Please note that the model should have reference to the Dimensions model, however you can add reference afterwards.

After clicking “Apply”, VS will show new project creation dialog. New project will be created. It will contain two Data Entity Extensions:

4

Please note that you cannot rename them because internal code works only with entity extensions that have name = Data Entity Name + “DimensionIntegration”.

Both entities has identical changes, the only difference is that one show default dimensions and another one ledger dimensions.

Let’s looks at DimensionSetEntity extension:

5

It has 3 new fields, one for each financial dimensions we specified in the wizard.

This fields are computed columns, each of them use same data method “getValueColumnForDimensionAttributeSql”.

6

It is quite interesting and has some smarties inside but hardcoded values as well.

That all we need to do. After compilation and synchronization we can open Data Entity in Excel and will see new fields in data connector designer.

7

How does it work? That the most exciting part. All the magic is inside DimensionAttributeValueSet table. If we look at it in AOT – nothing is really changed since AX 2012, but if we go to SQL management studio we will see bunch of new columns:

8

For each financial dimensions we have 2 columns: one contains value and another RecId.

Each computed column in Dimensions entity select value from this table.

So, if you are doing BI with 3rd party tools, now you can use  DimensionAttributeValueSet and DimensionAttributeValueCombination tables to query all financial dimensions directly.