Export methods from Navision Dynamics 5.0 to datawarehouse/OLAP purposes?

Question

I'm using Navision Dynamics 5.0 and need to export all the financial data into my datawarehouse on a regular basis (1 time daily). And therefore I don't want to use csv-files as exporting method.

Which other methods are normally used? This must be a regular task for all companies who uses Navision Dynamics, and needs to get the data out in an automatic manner.

I'm of course also worried about locking the tables when exporting the data.

I can think of these methods so far:

1) direct ODBC access to all the underlying tables

2) Creation of a read only indexed view (mateterialized view) on top of the Navision tables, which holds a copy of Navision data and then can be accessed by the datawarehouse. (NB: An indexed view is a view that has been materialized. This means it has been computed and stored.)

3) ?

4) ?

Let me hear you typical ways of doing the export.

PS: I have heard that there is no webservice export option for Navision Dynamics 5.0, only in the newest version NAV2009. So I cannot use a webservice method.

Answer 1

I found this document describing some of the various export methods: http://www.navisionguider.dk/downloads/Nav_IntegrationGuide1.2.pdf

So to continue my list, here are some more options:

3) Seems like a solution could be using Navisions own ODBC driver called NAV ODBC Driver (NODBC)

4) Another solution could be using the Navision in-build Dataports for exporting data. However Dataports can only produce csv-files.

Answer 2

You could also use XmlPorts, if an XML file is preferable to csv. Both DataPorts and XmlPorts allow you to aggregate data: for example you can export sales headers along with the lines for each header, if this is useful in your scenario.

You can also use filters, so you can export incremental updates to the warehouse daily. If you are concerned about holding locks for a long time, you could also try using filters to export the data in chunks.

I believe most solutions use the NAS (Navision Application Server) to schedule running DataPorts or XmlPorts, so the export is driven by NAV.

As a further alternative to using NODBC, you could also explore using CFront, which is a C/.NET API giving relatively low level access to the data including the facility to evaluate flow fields etc. NODBC and CFront are really the only options if you want to call into NAV (rather than using the NAS to pushing data out as csv/xml).

I haven't compared the relative performance of each method, but suspect that NODBC and CFront would be fastest for large volumes of data.

NODBC, CFront and the NAS all require specific granules in your license - so you might want to check which, if any, you are currently licensed to use.