Why do we need Staging Table in Data Import/Export framework in AX 2012

(0) Share

Report

Posted on by Community Member

I am aware that in both the cases of import and export, the data first go to the Staging Table from the source and then to the destination. But what why cant we directly import or export the data without using Stating Table?

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Sohaib Cheema 48,811 User Group Leader on at

Like (0)

Report

RE: Why do we need Staging Table in Data Import/Export framework in AX 2012

The answer is "its the way Microsoft developers thought to develop it"

of course, because there is business logic involved between staging and final destination/tables

Was this reply helpful? Yes No
Suggested answer

5400 7,162 on at

Like (3)

Report

RE: Why do we need Staging Table in Data Import/Export framework in AX 2012

Hi Prateek,

Let me explain in details.

why staging is required.

1. Data type mapping for external data

2. It is de-normalized table. So end user don't be worried regarding external file mapping which field will mapped where.

3. There is scope to update the data before taking into target

4. There is scope to validate the data before taking to target

5. if you have bulk import, 50% data went through but 50% data having issue, you can check in your staging and rectify the data and import it again.

6. Staging is getting populated through SSIS. It is more faster.

7. How will take care data conversion logic.

8. How will take care defaulting logic entity wise.

9. lot more

Yes, for export staging is not required, we can export into file directly for target. This functionality has developed and release in R3 CU9. Now it is supporting TSQL.

Hope it is clear to you.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Why do we need Staging Table in Data Import/Export framework in AX 2012

Thanks a lot. It really helped.

Was this reply helpful? Yes No
5400 7,162 on at

Like (0)

Report

RE: Why do we need Staging Table in Data Import/Export framework in AX 2012

If you are satisfy with answered please mark as verify for others.

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 236,320 Most Valuable Professional on at

Like (1)

Report

RE: Why do we need Staging Table in Data Import/Export framework in AX 2012

First of all, staging tables are not required in all cases. If you create a "table" entity, therefore there are no complicated queries nor any extra business logic to run, DIXF won't use staging tables.

But things are often not so simple, therefore the process is split into two distinct parts. Let me show it on import.

The first step is reading the file and putting it into AX. This step cares about details of data formats (CSV, XML etc.) and not what the content represents in AX.

The other step is all about AX business logic. It takes the data and split it into right tables, call methods and so on. It doesn't care about how the data was obtained - whether read from some file, loaded from another AX company, modified manually or anything else.

The role of staging table is mainly to allow these two processes to communicate with each other.

From my perspective, any architecture that would mix dealing with files and AX business logic would be wrong.

Was this reply helpful? Yes No
André Arnaud de Cal... 299,349 Super User 2025 Season 2 on at

Like (1)

Report

RE: Why do we need Staging Table in Data Import/Export framework in AX 2012

Hi Prateek,

In addition. It is possible to enable the option to directly execute the step to copy the data to the target when you start the import. This can be used if you are 100% sure the data is correct and would not require attention. Of course there can be some exceptions. In that case you can find the not processed staging records in the history.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

RE: Why do we need Staging Table in Data Import/Export framework in AX 2012

Hi Prateek,

It would be reasonable to have a staging database mainly due to the challenges experienced during data importations that result in exceptions occurring during import. The staging database can be a good opportunity to confirm that all data has come in correctly before moving to the required destination tables.

Another point is the fact that there might be different field types between the source system and AX, therefore, the staging table provides an opportunity to carry out mapping.

Was this reply helpful? Yes No
lally 8 on at

Like (0)

Report

RE: Why do we need Staging Table in Data Import/Export framework in AX 2012

[quote user="André Arnaud de Calavon"]. It is possible to enable the option to directly execute the step to copy the data to the target when you start the import.[/quote]

Hi Andre ,

Can you share the option to directly execute the data to target ?

Was this reply helpful? Yes No