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 two of the most important requirements: performance and timeliness (i.e., data that must be up to date and not stale).
The client 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.
On the highest level, there are basically two methods to insert data into 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
With set-based operations, the system creates a single SQL statement and sends it to the SQL Server, even if millions of records are processed in the select part of the statement. This statement selects data from tables and inserts it directly into the target table. It does the selection and insertion at the same time. This is extremely effective, and that makes it so fast.
Microsoft documentation: insert_recordset statement – Copy multiple records directly from one or more tables into another table in one database trip.
Row-based operations
Row-based operations are definitely the most used method in the standard application. They are straightforward and flexible to use, but the drawback is that they're slow. Row-based operations insert each row in a separate database round trip. The system first has to fetch the data, create X++ objects for each row, and then create a separate SQL statement for each X++ object / data row that should be inserted. This whole process adds a lot of overhead and therefore is inherently slow because of its design.
Disclaimer
- The code below is for demonstration purposes only. Use at your own risk.
- Don't be a silly Willy and use custom services for large datasets. Use other methods for that.
Okay, enough of all that, let's dive into the code part.
Temporary table
Create a temporary table with the fields that you will need for collecting the data and that will be exposed by the custom service. Set the TableType property to TempDB.
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 class are:
- Retrieve data from tables.
- Populate the temp table.
- Enumerate the temp table (for each row in the temp table).
- Transfer the data from the table to an object.
- Add the populated object to a list.
The code below is the actual code that I created for the client. It is a real-world working example and has not been simplified, because it would have taken too much time and might contain bugs. I did change the prefixes everywhere in the code below for obvious reasons, and I anonymized some of the dimension field names.
The service has two methods, one to retrieve the balances of ledger accounts for a specified period. The second to retrieve all the transactions for a specified main account. The code below is just the first part because I don't want to make the post longer than needed. The two other helper methods demontrate how to use the set-based operations in X++.
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 populateDataBalances method uses two other methods that 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.
}
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.
Service group
Create a custom service group and add your service class to the group.
