Skip to main content

Connecting to Microsoft Dynamics 365 F&O (extractor)

The Microsoft Dynamics 365 F&O (Finance & Operations) extractor is based on the Celonis Platform Extractor Builder and uses the OData APIs of the source system to query the data (see: OData reference by Microsoft).You can either use the default extractor that is provided by Celonis or use the Extractor Builder to customize to your requirements.

Known limitations

This extractor has the following known limitations:

  • No automated way to access Changelog data (only via manual file uploads).

  • Objects/tables of P2P and O2C are preconfigured. Additional objects and tables need to be added manually.

Authentication and Permissions

The Microsoft Dynamics API uses OAuth2 for authentication. The following information is required for the connection set-up:

  • Host URL of the system

  • Tenant ID

  • Client ID

  • Client secret

In order to use the Extractor, you need to set-up the Azure Active Directory Portal for 3rd party app authentication with Dynamics 365 F&O. For that you can follow these steps:

  1. Navigate to portal.azure.com

  2. Open the Application Active directory and click App Registration.

  3. Add a new app registration with the following information:

    • Name: Assign a name to the application.

    • Supported account types: Accounts in this organizational directory only.

    • Redirect URI: <Your-Dynamics-Host-URL>/oauth

  4. After registering the application an application id (client id) and a directory id (tenant id) will be created. This will be required as input parameters for the connection set-up later.

  5. Navigate to ‘Certificates & secret’ and ‘client secrets’ and create a new client secret. Make sure to copy the client secret as it will be only visible now and masked afterwards.

  6. Navigate to ‘API permissions’ and add a new permission:

    • Select API permissions for ‘Dynamics ERP’.

    • Select ‘Delegated permissions’.

    • Make sure to check ‘Odata.Full.Access’.

  7. Go to the Microsoft Dynamics 365 system and navigate to ‘System administration’ → ‘Setup’ → ‘Azure active directory applications’ and add the application id (client id) that was created in the previous steps.

  8. The set-up on the source system side is completed and you can create a data connection in Celonis using the obtained inputs:

    • Host URL of the system

    • Tenant ID

    • Client ID

    • Client secret

Used Endpoints

The current version of the Extractor is supporting the endpoints that are required for the processes Purchase-to-Pay and Order-to-Cash. In total the following 84 data entities are supported. They can be extended by customizing the Extractor using the Extractor Builder.

InventoryPolicies

  • BillOfMaterialsHeaders

  • BillOfMaterialsLines

  • BillOfMaterialsVersionsV4

  • BusinessDocumentNonStockedPackingSlipLines

  • BusinessDocumentStockedPackingSlipLines

  • CashDiscounts

  • ContactPersons

  • Currencies

  • CustomerGroups

  • CustomerPaymentJournalLines

  • CustomerPaymentMethods

  • CustomerPostalAddresses

  • CustomersV3

  • DeliveryModesV2

  • DeliveryTerms

  • FinancialDimensionValues

  • FormulaLinesV2

  • FormulaVersionsV2

  • InventItemPendingPricesV2

  • InventoryPolicies

  • InventoryReservationHierarchies

  • LanguageCodes

  • LineDiscountProductGroups

  • LineDiscountVendorGroups

  • MultilineDiscountCustomerGroups

  • MultilineDiscountProductGroups

  • MultilineDiscountVendorGroups

  • OpenPurchaseLineDiscountJournalLines

  • OpenPurchasePriceJournalLinesV2

  • PaymentJournalLineSettledInvoices

  • PaymentSchedules

  • PaymentTerms

  • PostingProfileHeaders

  • ProductDefaultOrderSettings

  • ProductReceiptHeaders

  • ProductReceiptLines

  • ProductSpecificOrderSettingsV3

  • Prospects

  • PurchaseAgreementConfirmationLines

  • PurchaseAgreementConfirmations

  • PurchaseAgreementLinesV2

  • PurchaseAgreements

  • PurchaseLineDiscountAgreements

  • PurchaseMultiLineDiscountAgreements

  • PurchaseOrderConfirmationHeaders

  • PurchaseOrderConfirmationLines

  • PurchaseOrderHeadersV2

  • PurchaseOrderLinesV2

  • PurchasePriceAgreement

  • PurchaseRequisitionHeaders

  • PurchaseRequisitionLines

  • PurchaseTotalDiscountAgreements

  • QualityOrderHeaders

  • QualityOrderLineResults

  • ReleasedProductCreationsV2

  • ReleasedProductsV2

  • RequestForQuotationReplyHeaders

  • RequestForQuotationReplyLines

  • RetailTenderTypes

  • ReturnOrderHeaders

  • ReturnOrderLines

  • SalesInvoiceHeadersV2

  • SalesInvoiceV3Lines

  • SalesLineDiscountAgreements

  • SalesMultiLineDiscountAgreements

  • SalesOrderConfirmationHeaders

  • SalesOrderConfirmationLines

  • SalesOrderHeadersV2

  • SalesOrderLines

  • SalesPriceAgreements

  • SalesQuotationHeadersV2

  • SalesQuotationLines

  • SalesTotalDiscountAgreements

  • TaxGroups

  • TotalDiscountVendorGroups

  • UnitsOfMeasure

  • VATNumTables

  • VendorBankAccounts

  • VendorGroups

  • VendorInvoiceHeaders

  • VendorInvoiceLines

  • VendorPaymentJournalLineSettledInvoices

  • VendorPaymentJournalLines

  • VendorsV2

Limitation: Access to Changelog data

The OData APIs of Microsoft Dynamics 365 don’t allow us to access and extract any changelog data from the system. To fetch that data, we need to rely on the built in database log functionality, export the logs in files and upload these into the Celonis Platform. Missing Changelog access is the main limitation of this extraction method because it can only be done in a manual way.

The database logs are required to track any kind of change activities.

To enable the database logs you can following these steps:

  1. Go to Microsoft Dynamics 365 and navigate to System Administration → Setup → Database log → Database log setup.

  2. Click New.

  3. Click next in the Step ‘Logging and database changes’

  4. Activate the checkboxes ‘Show all tables’ and ‘Show table names’.

  5. Select all Tables and Fields (columns) for which you want to enable Database logs → As part of the Process Connectors for P2P and O2C we provide a recommendation of fields. You can find these in the first transformation script of the respective process connector.

  6. Click on Next and select the ‘Update’ check box for the fields you have selected in the previous step.

  7. Click on next and finish the process.

Extract the Database logs by following these steps:

  1. Go to Microsoft Dynamics 365 and navigate to System Administration → Inquiries → Database logs.

  2. Click on Database Log on the action pane at the top of the screen.

  3. Click Ok to run the report as a Batch job.

  4. Export the database log as a Excel file and upload it in the Celonis Platform.

Configuring the Microsoft Dynamics 365 F&O extractor

This section describes the basic setup of configuring the Bamboo extractor. To configure the extractor:

Note

For configuration, the Microsoft Dynamics 365 F&amp;O extractor has specific instructions for OAuth2 fields. For more information, see Authentication and Permissions.