Microsoft Dynamics NAV: Let Your Data Flow With Linked Servers

Microsoft Dynamics NAV utilizes several technologies that allow raw data to flow in amazing ways, allowing real time access to data and information.

A FlowField flows information from a single source field from a single table into the FlowField as a value. Behind the scenes, the information is possible via SQL Views.

I would like to introduce another technology that I like to call a FlowTable. A FlowTable flows information from 1 or more source tables into the FlowTable as a record set. Behind the scenes, the information is possible via SQL Views. In fact, this technology already exists in NAV by another name: LinkedTables.

Here's another less prevalent technology I like to call FlowDatabase. A FlowDatabase flows information from an entire external database into the FlowDatabase as a database. In fact, this technology already exists as well, by the name: Linked Server.

NAV is our world when it comes to Data and Information. For developers, the development experience is constantly straddling a fine line between coding and enforcing business processes. NAV was developed with the business processes as the core. 99% of the functionality a typical client needs is in NAV out of the box. The other 1% is where it gets fun and the reason NAV is such an appealing product. Virtually anything can be customized in NAV, and that is even more true as we progress into the future with NAV 2013.

One of the most common customizations is integration. Integration is where developers typically get their hands dirty in non-business processing related logic. FTP, File Management, Parsing etc.. Integration is all about manipulating and moving data around. Fun stuff, for sure.

Every business system involves external data for various reasons. Partners need product info, Web Sites need synchronizing, Multi-Company General Ledgers need consolidating, CRM data needs to be integrated with Contacts etc... All this data needs to get into NAV, either once, periodically or live real-time. NAV provides an array of possibilities, including Dataports, XMLPorts, Web Services, File Streaming, FTP Add-Ins, Automation via .Net dll's and OCX's. All NAV developers will encounter most, if not, all of these options in the normal course of their monthly development projects.

One of the most under-used integration technologies, in my opinion, is Linked Tables. I like to think of them as FlowTables. Linked Tables are created just like a normal table except the "Linked Table" property is set to "YES". It assumes the same named View in SQL Server. So, the underlying data is actually a View. One use would be to create a UNION view that included the GL from multiple companies. A UNION view is basically a stacked view of similar data. You can also create views that update data either directly or with enhanced T-SQL. A view in SQL Server is limited only by the developer’s imagination. Virtually any number of permutations of data can be presented in a view, including, formatting, consolidation, calculations, summarizing etc…. It truly is infinite.

As useful as Linked Tables might be, when you combine them with Linked Servers, or, what I like to call FlowDatabases, you have an incredibly powerful technology at your fingertips. Linked Servers have been around since at least 2002.

A Linked Server allows a SQL Server Database Engine to execute commands against external data stores. The type of data store is irrelevant. For instance, you could create a Linked Server to Other SQL Servers, Oracle, Access, Excel, MySQL, PostGreSQL and even Text Files. A Linked Server is setup in SQL Server and then allows a developer to create SQL Statements prefixing the Datasource with the Linked Server Name, effectively querying data in external data stores as if the data were in the SQL Server Db! Now, you could create a Linked Table that's underlying source is a view into the external db. Wow. That is awesome. It's a deceptively simple technology, yet hard to get your head around if you've never seen it.

Let’s look at a couple client scenarios to get a better feel for how you could leverage a Linked Server:

Client Scenario A: Client has a 3rd party CRM db with a SQL Server backend running on Server A and a NAV db running on Server B. Client has asked to come up with a solution to "synch" the CRM contacts with NAV contacts.

Solution A: In Server B - Linked Server to Server A, Linked Table to CRM Contacts, NAV process to copy Linked Table to Contact

A Linked Table is a view into live data. A Linked Table can be based on a View into data that is queried from a Linked Server. So, the CRM data from another SQL Server(or any data store) can now exist in NAV and can be accessed in NAV just like any other table.

Client Scenario B: Client has 18 companies across the world(all in English and US $) on 18 separate servers with separate instances of NAV and SQL Server. They want to keep the db’s separate for the foreseeable future. They want a solution that consolidates all 18 companies General Ledgers into a Master NAV db for reporting purposes. They currently have 5 of their employees who spend most of their time manually entering this data. They are willing to approve virtually any suggested design.

Solution B: 18 Linked Servers in Master DB SQL Server to each of the 18 NAV Db’s. A single Linked Table based on a UNION view. NAV process that creates Gen Journal Entries from Linked Table.

Scenario B was actually given to me as a project once. It was delivered to me with a design already hashed out in meetings before I was involved. The original design was to run NAS’s executing dataports on each of the 18 servers and passing the files around with some crazy file manipulation/deletion and naming logic. They had over 80 hours into the design discussions and specifications alone and over 150 hours approved to create 18 dataports, create the file handling code, set up all the NAS’s, write the Gen Journal logic and test.

After spending 2 hours analyzing the nightmare design doc that was handed to me, I suggested using a single Linked Table in the Master db and querying the data from the 18 companies using a UNION view via Linked Servers set up on the Master Db SQL Server. They agreed with this design. It ended up taking me 18 hours total and testing took less than a day.

Note to self: Blog Topic – Discuss how important it is to involve the developer early and often in all design sessions.

Of all the solutions that may come up in conversations with other developers, including mapping tools like Scribe or BizTalk and/or Application Servers, Web Services etc... They all have one thing in common: they all require the same validation and logic to get data into NAV, i.e, insert to Journals, Sales Headers etc... But, they also all have very different technologies to get the data to NAV. Scribe uses NAS's and DTS's, Dataports require files, WebServices require the external app call with compatible parameters passed to the exposed WebService.

All the above options require custom "interfaces" that pass data around. Data exists in multiple places and synchronization needs to be built into the interfaces. This is all good and sometimes necessary. But now you can always consider Linked Tables via Linked Servers as a viable option. Other integration options require caching, temp data and transporting data via various sized buckets.

Linked Servers allow a direct data flow from external data stores to a Linked Table, real time data flowing directly into NAV from virtually any source.

Think about this for a minute and imagine what this could mean for your system.