RSS

Category Archives: DIXF

Import Opening Balance Through AX 2012 Data import/Export Framework

Yesterday I came across the following issue  and thought I’d share my findings and solution.

Customer asked me to import opening balances from their existing legacy system.Data Migration is the key point for any implementation and Data Import/Export tool is most powerful tool with in Dynamics AX 2012 for data migration.

There are so many blogs written corresponding to this topic”How to import Opening balance through DIXF”.But everyone explained the feature of Dynamics AX Data Import/Export Framework and gave example importing of financial dimensions into a ledger journal for ledger accounts only But my customer requirement is different he wants to import financial dimensions into a ledger journal for customer,Vendor,Project account as well.

Unfortunately, I could not find any article which helps me to import financial dimensions into a ledger journal for Project,Bank & customer/vendor accounts.

Here I would not explain from the scratch how to import opening balance by using Dynamics AX 2012 data import/export framework.

You can refer Hai Nguyen’s Blog, How to start with Opening balances through DIXF

Importing a General Journal using Data Import/Export Framework AX 2012

I have been trying to import data from an CSV file using Data Import Export Framework and generated a sample file.

DIXF1.png

To demonstrate this,I am using AX 2012 R3 CU8 INMF Company.I am taking Bank Account “IND OPER”and Default dimension 024-003 where 024 is Deparment and 003 is Bussines unit.On other side Offset Account is ledger account 110125-024-005.Here 110125 is Main Account,024 Department and 005 is Business unit because my account structure is configured like below

Dixf2.png

Important note:If you want to import financial default dimension corresponding to then you have to choose extra field Default dimension for Account and for offset account choose field OffsetDefaultDimension while generating sample file(screenshot above).

So here question can arise why i am taking DefaultDimension field  for bank only and why not for ledger account.Because framework is capable to import financial dimensions without any issue for ledgers.

So lets take forward with the issue and solution,Even though the mapping  and staging data is validated successfully but when I try to import the data into target table – I get the below error:

Results. Record (‘INMF-00392-VN00000002-1.00’) . Unable to return DimensionAttributeValue record for 024
Data written to target ‘Opening balance’ (‘0’ records created, ‘0’ records updated)

When I tried to debug GenerateDefaultDimension methods in DMFLedgerBalanceEntityClass then I found cause.

As I mentioned above chart of account structure is with sequence Main Account-Department-Business Unit So Ledger accounts(offset account) should be according to account structure only.

Dixf3.png

But why its not working for Bank,even we have used same sequence for Default Dimension as well?? Big question ??

After debugging multiple time i got to know the reason that system not able to generate default dimension rec id then   I found DIXF framework is taking in alphabatical order of Source Data format Dimension code which is different from account structure. 🙂

Dixf4.png

 

So I changed the DefaultDimension value with Business Unit-Department(003-024) instead of Department-Business Unit(024-003).

Dixf5.pngDixf5.png

Now system won’t give you any error this time and opening balance is imported successfully. 🙂

Dixf6.png

Happy Daxing. 🙂

Advertisements
 

Import Data With Data Migration Framework Dynamics AX 2012 R3-Step by Step

This is the Third post on my new blog about Data Migration Framework and as promised I will deliver something interesting.

Our team are using the Data Migration Framework to import data into Dynamics AX 2012.  We have created a lot of custom entities, which has been challenging given the lack of documentation on the subject.  I thought I’d share a tutorial how to import data in to AX by using existing Custom entity.

After installing the framework you will be able to start using it with some of the entities that are already delivered like customers, vendors, customer addresses, vendor addresses, open sales orders, … you can find the entire list in the documentation or in the Target entities form in the Data Migration Framework Area Page.

DIXF Existing Entities

let walks through  set up of Data Import Export Framework very briefly

Setup shared directory

An important first configuration is to provide Location of Shared Directory. Click Data Migration Framework->Setup->Data migration framework parameters. Provide the path to a shared directory and click Validate to let system verify the path. This directory will be used by DMF to put some files for internal use. User configuring the shared directory path must have Read/Write permission on the directory.

Setup source Data Format

There are demo files you should look at. http://technet.microsoft.com/EN-US/library/jj225593

C:\Program Files\Microsoft Dynamics AX 2012 Data Migration Framework (Beta)\DemoFiles\Delimited

Click on Data Migration Framework > Setup > Source data formats to set up your source data format. Currently there is on flat file imports available with possible future enhancements from Microsoft to support other types.

Give Source Name e.g. CSV in Description column write CSV File, inType column select File, then assign the additional details about the data source in right pane of Source Data Format Screen.

Fill following sections on right pane:

File Section

1. File format-provide file format here Delimited or Fixed Width, we will choose Delimited for our example
2. First Row Header check box– check this check box if first row is a header for remaining records
3. Row Delimiter-Select this if your file contains multiple rows. Here user can select “CR,LF,Semicolon {;},Colon {:},etc.” we will select {CR}{LF} for our example.
4. Column Delimiter-Select this if your file contains multiple columns to import, this is used to distinguish values between column, there are multiple options available to distinguish we will use Comma for our example.
5. Text Qualifier- Select this if your column type is string and you have to use Delimiter in data e.g. your Delimiter is Comma and you have to use it as data e.g. “2-A Ahmed Block, New Garden Town” here you have to write the comma in double quotes “,” so that system don’t consider it as Column Delimiter. For our example we will write ” as Text Qualifier.

Regional Settings

 
1. Code Page – Character encoding for the file, there are different options available select your desired one, for our example we will select Western European (Windows)
2. Unicode – Select this if your file is using Unicode, we will not use this option for our example
3. Language Local – Select Language, we will use en-us for our example

Multiple value separator

1. Role Separator – This is used to display multiple values in a column e.g. if a customer have multiple email addresses. We will use semi column for our example.

Now General Settings are finished.
Under the Applications content, you can select your dimensions. The Dimension code lookup is a multiselection lookup (tick the selection box on the left of the lookup and ok).

Create Processing Group

Next step is to create Processing Group. Processing Group is similar to Definition Groups in AX 2012. Click on Data Migration Framework > Common > Processing group.

Click New give name & description, write “ProcG” in Name and “Processing Group 1” in description column.Press Save and Entities button will be enabled.

From the Processing group form, click on Entities.

Create a new record. In this example I created one for a Vendor import. The Sample file path field will be blank.

Note: You can use the demo file located under the installed folder. C:\Program Files\Microsoft Dynamics AX 2012 Data Migration Framework (Beta)\DemoFiles

If you use the Demo files, then skip steps 5 to 7. If you want to modify the columns, then do steps 5 to 7, as it will ask you if you want to modify the existing file format.

Generate a sample file by clicking on Generate source file button. Follow the wizard and select the fields to import.

Then click the Generate sample file button. This will open up the sample file in notepad.

Click Finish when done. Now fill in your data on the txt file.

Back on the main form click the Generate source mapping button. This will analyse the file and do the mapping between the file and the stagging table.

Now you can open and view the mapping by clicking on the Modify source mapping button.

It should looks something like this. The Source is your txt file and the Staging is your staging table in AX. If your source file is messed up, you should be able to see it here.

Click Save button when done. Now validate the setup by clicking the Validate button on the main form. click on View target mapping. This is a mapping between the Staging to the Target table. This should already be predefined. You can however modify the mapping.

Notice a the Function options on the left. These are predefined methods in the class to allow you to execute business logic. The example below, when you pass in a string of dimensions A-B-C, the GenerateDefaultDimension function splits the string up and generates a DefaultDimension RecId. A similar thing happens with the GenerateParty, which creates all the required global address book records to return a Party RecId.

Back on the main form.Click the Preview source file button. This loads up the file, to give you a preview of your file..

Go back to the Processing group form and click the Get staging data. This opens up a dialog with the processing group, click OK. You will then get this form.

Click on the Run to run client side or Click on Run on AOS to run server side. Usually, the user account doing the import wont have access to SQL integration servers. Use the second option and it will run under the service account. This should run and complete with out errors.

You can now see the staging table by going to the Execution history from the Processing group form. From there you click the View staging data button. You can modify the staging data before you process it to the target.

To process to the target, click on the Copy data to target button. Follow the options to execute the process.

You are now done. The result should be the vendor record created.

 

Tags: , , , ,

Data Import/Export Framework–Source Data format

Today I want to write a brief on the different data formats that are available in the Data Import/Export framework.

File The File format allows for text (ie. csv) types to be imported.
ODBC ODBC format allows for SQL type queries to be used. Imagine writing SQL statement to pull data from another database.
AX AX format allows for exporting data in dat/def format. This is a nice addition because it allows you to move data to another AX instance.
In other words, this pulls the AX entity data into the staging table and then you export it.
Then you go to your other instance of AX you want to import into. Import the dat file and then process it.

Check out TechNet on the source data formats.

http://technet.microsoft.com/EN-US/library/jj225596.aspx#DefineSource

I will try to write a little bit more about the Create custom entity mapping by using the wizard

File format at another time because that is confusing at first. Simply, think of it as your way of loading CSV file to AX database.

 

Tags: , , , ,

Data import/export framework in AX 2012 R3

The Microsoft Dynamics AX 2012 Data Import/Export Framework is an extension that helps you export data and import it into Microsoft Dynamics AX. Examples of the data that you can import include master data(customer,vendor), open stock, and balances.

Architecture of DMF:

DIXF Architecture

Import/Export Process

1) Prepare Source Data
The file must have a header, which includes the field names identifying each data column.

2) Define type of AX entity to be used
Define entities that will be loaded to AX. For example, Customers, Vendors…

3) Mapping and validation of source file fields to staging AX fields
The DMF tool will allow you to map each field from the source file to an AX field in the staging table. The DMF uses the field names to map the fields in the staging table.

4) Transferring data from source file to the staging table
Once fields from the source file have been mapped to AX fields in the staging table data should be ready to be transferred to the staging table.

5) Transfer of data from staging to target AX destination
Once data has been reviewed and validated in the stating table, the data is then moved to the target AXentity and AX records are created.

Configuration

1) Determine entities – DMF > Setup > Target entities
There is a set of predefined entities. These entities also include procedures to move data from the staging table to the destination.

2) Define the source data format – DMF > Setup > Source data formats

  • Create a new data format
  • File format: delimited by a character or fixed
  • First row header: indicate if first row of the file, is a header row
  • Row / column delimiter: which character will be used to determine the end of the line / column
  • Text qualifier: indicates characters to encase text and commas
  • Regional settings: regional settings for the file
  • Role separator: used to indicate multiple entries, for example: 317-509 ; 508-999

3) Define the data format for each entity in the source environment
The data format can be a file that is delimited or fixed width, an ODBC data source, or a Microsoft Dynamics AX table

for more details read about Source data format in DIXF Data Import/Export Framework–Source Data format

4/5) Define the processing group / Source to staging table

  • Create a new processing group
  • Entities:
    1. Create a new entity and select the source data format
    2. Run business logic: two flags to enable the check for AX business logic
    3. Sample file path: the path of the file
    4. Generate source mapping: maps the source file against the staging table. If you use AX column names as your header, the DMF tool will automatically try to do the mapping
    5. Modify source mapping: the user can review and modify the mappings
    6. Validate: the validation ensure that all fields in the source file are correctly mapped to the staging table
    7. View target mapping: this option can be used to validate the mappings
    8. Preview source file: the user can view a preview of the source file before the file is copied to the staging table
  • Get staging data: data is ready to be copied to the staging table. This button is used to start the process. A message indicate that the data has been copied to the staging table

6) Moving data from staging table to final ax destination
Select the “Copy data to target”

 

Tags: , , ,