Loading Data from Microsoft Dynamics AX via FDM

Rodney P

Hi Everyone,

I was wondering if anyone was utilizing FDM to load data from Microsoft Dynamics AX into HFM via FDM, and how this was being done. We are currently on version 11.1.2.2.0, thanks for any insight.

SH_INT

The most straightforward way would be to get the MS Dynamics team to supply a flat file extract with the necessary data and then use FDM to import to HFM. There are no out of the box adapters to directly interface with MS Dynamics but one other option would be to build an integration script to interface directly with the data in the relevant MS Dynamics tables via FDM but this would be more involved and require a detailed knowledge of the underlying MS Dynamics database, FDM scripting, have possible sec and would be much harder to maintain for the average user.

SH_INT

The most straightforward way would be to get the MS Dynamics team to supply a flat file extract with the necessary data and then use FDM to import to HFM. There are no out of the box adapters to directly interface with MS Dynamics but one other option would be to build an integration script to interface directly with the data in the relevant MS Dynamics tables via FDM but this would be more involved and require a detailed knowledge of the underlying MS Dynamics database, FDM scripting, have possible sec and would be much harder to maintain for the average user.

Rodney P

SH, thanks for the quick reply. When we export the flat file from Microsoft Dynamics, we will be required to rearrange the data so that it is in a format that FDM/HFM will be able to receive it as, correct?

Dimensions....,Data?

Thanks

SH_INT

That can all be handled in the import format definition in FDM, or you can get the MS Dynamics team to produce an extract to a specific format, if that is possible and they have the skills. The most important part of the extract definition is ensuring it has all the required fields and correct grain of data for your requirements.

Francisco Amores

Hi,

you could also have the MS team to prepare a SQL view which you can easily import data from using the integration script as Simon suggested.

Ideally you would put the complexity in the SQL code the view so you get the data in the format you want:

ACCOUNT|ENTITY|....

Hope that helps

Rodney P

Yea we currently have a script written to pull data from a progress database for another ERP system, but now we will need to do the same thing for MS Dynamics. Im thinking easiest will just be to take an export and manually import the data into FDM.

Rodney P

Does the file that I load need to be in a specific "FDM" format? I created an import format but I am still unable to load. I have opened up a ticket with oracle support, but I figured I would ask you as well.

Thanks

SH_INT

The file format shouldn't matter, that is what the import format is for, to parse out the required information from the file. What exactly is the import issue you are having? 

Francisco Amores

please be more precise.

which error? which issue you have?

Rodney P

I was actually able to resolve this last night, I started with a 400 line file of data to load and it included headings. I removed the headings, changed the format of the file from an xls to csv and I was successfully able to load the data. Now I just have to create a macro which will automatically do this within excel for me when we receive our output file from Microsoft Dynamics. I appreciate the help from you guys.

Thanks

SH_INT

The fact that it was an xls file was the issue, the headings wouldn't have made a difference. When we talk about the import of flat files we are usually assuming the audience knows that this implies they are ASCII compatible files ... perhaps I should have been more explicit ;-)