Sometimes you just need a fast way to get specific data from Dynamics 365 Finance (F&O) for real time processing of F&O data in another application. The data analyst and I were debating the architecture and exporting the F&O data to a data warehouse was not meeting one of the most important requirements: performance.

The clients needs the data from F&O to be up to date and in real time without delays. Since the data output requirement did not involve large datasets, my solution was to use set based operations to fill a temporary table and to expose the data in a custom service. I am very happy with the performance of this service and definitely recommend using this pattern to everyone out there.

Disclaimer: don't be a silly Willy and use this pattern to output large amounts of data. Use other methods for that.

On the highest level, there are basically two methods to insert data in to the database: row based and set based. Let's briefly discuss both methods so that it's clear why I chose the set based method.

Set based operations

Set based operations essentially sends a single SQL statement to the SQL Server. This statement selects data from tables and inserts it directly into a temporary table. This is extremely fast and effective.
Microsoft documentation: insert_recordset statement – Copy multiple records directly from one or more tables into another table in one database trip.

Row based operations

Definitely the most used method in the standard application. It is straight forward and flexible to use but the drawback is that it's slow. Row based operations inserts each row in separate database round trip. The system first has to featch the data, create X++ objects of each row, and then create a separate SQL statement, from each X++ object / data row that should be inserted. This whole process adds a lot of overhead and therefore is inheritantly slow because of its design.

Okay enough of that, let's start.

Create a temporary table

Create a temporary table with the fields that you will need when collecting the data and that will be exposed by the custom service. Set the Table Type property to TempDB.

Create a service class

The code of the custom service class orchestrates data retrieval and prepares the data to be exposed by the custom service framework. When the data is retrieved, it uses a set based operation, i.e. insert_recordset, which is very fast. The different steps in the service are:

• Retrieves data from tables
• Populates the temp table
• Enumrerates the temp table -> for each row in the temp table
• Transfer the data from the table to a object
• Add the populate object a list.

public class AVGenJourAccEntrySvc
{
    [AifCollectionTypeAttribute('return', Types::Class, classStr(AVGenJourAccEntryBalances))]
    public List getBalances(DataAreaId _dataAreaId, AccountingDate _accountingDateFrom, AccountingDate _accountingDateTo)
    {
        AVGenJourAccEntrySvcContract    contract;               //data class for retrieving data.
        AVGenJourAccEntryAggTmp         genJourAccEntryAggTmp;  //temp table where our data is stored.
        AVGenJourAccEntrySvcDP          genJourAccEntrySvcDP;   //logic to retrieve data and populate temp table.
        List                            list = new List(Types::Class); //list of data contracts.

        if (_dataAreaId && _accountingDateFrom && _accountingDateTo)
        {
            contract = new AVGenJourAccEntrySvcContract();

            contract.parmAccountingDateFrom(_accountingDateFrom);
            contract.parmAccountingDateTo(_accountingDateTo);
            contract.parmDataAreaId(_dataAreaId);

            changecompany(contract.parmDataAreaId())
            {
                genJourAccEntrySvcDP = AVGenJourAccEntrySvcDP::construct();
                genJourAccEntryAggTmp = genJourAccEntrySvcDP.populateDataBalances(contract); //retrieve data and populate temp table.

                //get data from temp table
                while select genJourAccEntryAggTmp
                    order by genJourAccEntryAggTmp.MainAccountId,
                             genJourAccEntryAggTmp.MainAccountName,
                             genJourAccEntryAggTmp.Dim1CostCenter,
                             genJourAccEntryAggTmp.Dim2Dept,
                             genJourAccEntryAggTmp.Dim3,
                             genJourAccEntryAggTmp.TransactionCurrencyCode
                {
                    
                    AVGenJourAccEntryBalances balances = AVGenJourAccEntryBalancesenJourAccEntryBalances::construct();
                    balances.initFromAVGenJourAccEntryAggTmp(genJourAccEntryAggTmp); //transfer data from temp table to data contract.

                    list.addEnd(balances); //add data object to list.
                }
            }
        }

        return list;
    }
}

The populate balances method uses two other methods which also use set based operations. All data collected with this class is done purely with set based operations making it extremely fast.

public AVGenJourAccEntryAggTmp populateDataBalances(AVGenJourAccEntrySvcContract _contract)
{
    //1. insert general journal account entry data joining with flattened dimension data.
    this.populateBalanceSheetIncomeStatementMovementsTmp(_contract);

    //2. insert general journal account entry data joining with flattened dimension data filtered by main account and dim3.
    this.populateBalanceSheetClosingsTmp(_contract);

    //3. combine and aggregate result sets.
    insert_recordset genJourAccEntryAggTmp
        (
        Company,
        AccountingCurrencyAmount,
        TransactionCurrencyAmount,
        TransactionCurrencyCode,
        MainAccountId,
        MainAccountName,
        Dim1CostCenter,
        Dim2Dept,
        Dim3
        )
        select
            Company,
            sum(AccountingCurrencyAmount),
            sum(TransactionCurrencyAmount),
            TransactionCurrencyCode,
            MainAccountId,
            MainAccountName,
            Dim1CostCenter,
            Dim2Dept,
            Dim3
        from genJourAccEntryAggTmpOpenBal
        group by
            Company,
            TransactionCurrencyCode,
            MainAccountId,
            MainAccountName,
            Dim1CostCenter,
            Dim2Dept,
            Dim3;

    insert_recordset genJourAccEntryAggTmp
        (
        Company,
        AccountingCurrencyAmount,
        TransactionCurrencyAmount,
        TransactionCurrencyCode,
        MainAccountId,
        MainAccountName,
        Dim1CostCenter,
        Dim2Dept,
        Dim3
        )
        select
            Company,
            sum(AccountingCurrencyAmount),
            sum(TransactionCurrencyAmount),
            TransactionCurrencyCode,
            MainAccountId,
            MainAccountName,
            Dim1CostCenter,
            Dim2Dept,
            Dim3
        from genJourAccEntryAggTmpAccDim
        group by
            Company,
            TransactionCurrencyCode,
            MainAccountId,
            MainAccountName,
            Dim1CostCenter,
            Dim2Dept,
            Dim3;

    //4. delete zero amount transactions.
    delete_from genJourAccEntryAggTmp
        where genJourAccEntryAggTmp.AccountingCurrencyAmount == 0;

    return genJourAccEntryAggTmp;
}

//1. insert general journal account entry data joining with flattened dimension data.
private void populateBalanceSheetIncomeStatementMovementsTmp(AVGenJourAccEntrySvcContract _contract)
{
    FiscalCalendarPeriod                        fiscalCalendarPeriod;
    MainAccount                                 mainAccount;
    GeneralJournalEntry                         generalJournalEntry;
    GeneralJournalAccountEntry                  generalJournalAccountEntry;
    DimensionAttributeValueGroup                dimensionAttributeValueGroup;
    DimensionAttributeLevelValue                dimensionAttributeLevelValue;
    DimensionCombinationEntity                  dimensionCombinationEntity;

    insert_recordset genJourAccEntryAggTmpOpenBal
        (
        Company,
        AccountingCurrencyAmount,
        TransactionCurrencyAmount,
        TransactionCurrencyCode,
        MainAccountId,
        MainAccountName,
        Dim1CostCenter,
        Dim2Dept,
        Dim3
        )

        select SubledgerVoucherDataAreaId from generalJournalEntry

        join AccountingCurrencyAmount, TransactionCurrencyAmount, TransactionCurrencyCode from generalJournalAccountEntry
            where generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId

        join MainAccountId, Name from mainAccount
            where generalJournalAccountEntry.MainAccount == mainAccount.RecId
                    
        join Dim1CostCenter, Dim2Dept, Dim3
            from dimensionCombinationEntity
                where generalJournalAccountEntry.LedgerDimension == dimensionCombinationEntity.RecordId
            
        exists join fiscalCalendarPeriod
            where generalJournalEntry.FiscalCalendarPeriod == fiscalCalendarPeriod.RecId &&
            
            generalJournalEntry.Ledger         == Ledger::current()                    &&
            generalJournalEntry.AccountingDate >= _contract.parmAccountingDateFrom()   &&
            generalJournalEntry.AccountingDate <= _contract.parmAccountingDateTo()     &&
            fiscalCalendarPeriod.Type          == FiscalPeriodType::Operating          &&
          ((mainAccount.MainAccountId < #FirstIncomeStatmentMainAccount && dimensionCombinationEntity.Dim3 != '') || mainAccount.MainAccountId >= #FirstIncomeStatmentMainAccount); //balance sheet transactions with movements or income statement transactions.
}

//2. insert general journal account entry data joining with flattened dimension data filtered by main account and dim3.
private void populateBalanceSheetClosingsTmp(AVGenJourAccEntrySvcContract _contract)
{
    DimensionValue                              closingMovementCode = '777';
    MainAccount                                 mainAccount;
    GeneralJournalEntry                         generalJournalEntry;
    GeneralJournalAccountEntry                  generalJournalAccountEntry;

    DimensionAttributeValueGroup                dimensionAttributeValueGroup;
    DimensionAttributeLevelValue                dimensionAttributeLevelValue;
    DimensionCombinationEntity                  dimensionCombinationEntity;

    insert_recordset genJourAccEntryAggTmpAccDim
        (
        Company,
        AccountingCurrencyAmount,
        TransactionCurrencyAmount,
        TransactionCurrencyCode,
        MainAccountId,
        MainAccountName,
        Dim1CostCenter,
        Dim2Dept,
        Dim3
        )

        select SubledgerVoucherDataAreaId from generalJournalEntry

        join AccountingCurrencyAmount, TransactionCurrencyAmount, TransactionCurrencyCode from generalJournalAccountEntry
            where generalJournalAccountEntry.GeneralJournalEntry == generalJournalEntry.RecId

        join MainAccountId, Name from mainAccount
            where generalJournalAccountEntry.MainAccount == mainAccount.RecId

        join Dim1CostCenter, Dim2Dept, closingMovementCode
            from dimensionCombinationEntity
                where generalJournalAccountEntry.LedgerDimension == dimensionCombinationEntity.RecordId &&

            generalJournalEntry.Ledger          == Ledger::current()                    &&
            generalJournalEntry.AccountingDate	>= _contract.parmAccountingDateFrom()   &&
            generalJournalEntry.AccountingDate  <= _contract.parmAccountingDateTo()     &&
            mainAccount.MainAccountId           < #FirstIncomeStatmentMainAccount; //only balance sheet transactions.
}

Create a service

In order to expose your custom service, you need to create a service and specify your service class and methods. Give the service an appropriate name.

Create a service group

Create a custom service group and add your service class to the group.