Master Data Services SQL Server 2008 R2

Maximus

Hi,

I'm posting this message to see if anyone has had experience with the Master Data Services that come with SQL Server 2008 R2. I know of a solution that manages master data from within Navision:

http://www.to-increase.com/en/Solutions ... ement.aspx

This solution is integrated into NAV and calls the Business logic, but what are the posibilities if you manage your Master Data at SQL Level? Any contributions are welcome. Thnaks in advance.

Regards Max

stryk

Hi Max,

what exactly do you want to accomplish?

Maximus

Hi Jörg,

we now have one company in one of our NAV databases that is called Master Data. In this company one user manages the Item data for our whole concern. He can indicate on the Item card to which other databases and companies this data must be distributed. For distribution we use the Job Queue, some codeunits and XML Ports. Off course this is all customization. Now it has been indicated that we want to manage more Master Data than just Item Data. For me this is a great opportunity to implement an addon to prevent more customization and even get rid of some customization. Off course it would be great if we don't have to use an addon at all but can use a standard tool that is included in SQL Server 2008 R2. What I want is to setup Master Data like Items, Customers, Vendors, Jobs etc. in one place and distribute it to other companies and databases. I have dlded some documentation about this and understand this is possible, but what about the Business Logic in NAV?

Regards, Max

Btw: nice presentation in Antwerp last week

stryk

Well, generally it should be possible to transfer the data with SQL features; but I would not really do that ...

- Using real SQL Server Replication features could be tricky to set up and often you'll encounter problems, e.g. when "replicating" changes of the table schema ...
- If transferring data with SQL then NO business logic in NAV is excuted - this could jeopardize the data integrity!

I guess you need to implement at least a mixture of both - NAV &SQL - features. For example:
You could create a SQL Trigger on "Item", e.g. if an Item is changed etc. the trigger could transfer (INSERT INTO ... SELECT FROM ...) ino a buffer table; then the "target" system could process this buffer - with NAV business logic! - to apply the real NAV changes to that "Item" ... or something like this ...
So technically there are several options, but there are risks. So you really need to create a detailed data-modelling-scheme, defining exactly which data/changes you want to transfer, then you could decide how to accomplish this.

But: many retail solutions for NAV are facing this problem every day; e.g. transferring master data from back-office to shop or order data from shop to office ... many of these add-ons have features to deal with this! One solution I have in mind is from : Dynavics http://www.dynavics.com/Products.aspx?catId=c04
Maybe something like this suits your needs?

ndbcs

I also prefer solutions where at least the import will be done by C/AL code.
You'll get into big trouble sooner or later with just SQL Server replication (or other "external" tools).

In the past the biggest problem for replication scenarios implemented with just C/AL code was the fact that there were no triggers that fired on absolut every write action on a table, but with NAV 2009 R2 we have those new global triggers in Codeunit 1, that absolut always fire.
You'll find some useful information on this triggers in Freddy Kristiansens presentation from NAVtechdays about CRM integration.

If you want to do the export with SQL tools, I suggest to use SQL Server Change Tracking (CT).
With CT you don't need to modify NAV tables (adding triggers), wich I think is kind of "dangerous".
I used CT before 2009 R2 and I like it a lot.

Tobias

rlcomte

Hi Max, and others,

Master Data Management is an area that is being discussed extensively and a number of products are available for it. However most of these products are located at the database level, and therefore lack the required enterprise application integration. SQL Server replication tools are in that sense quite low-level.
Furthermore there is more to MDM than only the data exchange. Aspects like a consistent conceptual data definition, data ownership and change management are also quite relevant. Furthermore you need appropriate monitoring and management tools to see what is actually happening, what is going wrong and how it can be corrected. In our view Master Data Management is a part of a complete Enterprise Integration architecture, serving the Connected Enterprise. Other parts include transactional data exchange using EDI, application integration and interaction with users.

// begin of short product description
The To-Increase Replication Management product, part of the Business Integration Solution, can support the master data scenario that you described. It provides model-driven replication management, covering full table replication and horizontal and vertical partitioning based on filters and documents. It supports a clean publish-subscribe mechanism, with both web based and file-based data transfers. It is fullly implemented in NAV, and invokes the appropriate business logic. It uses the change logs, and utilizes the NAV 2009 R2 change log mechanism and global database triggers for high performance and easy maintenance. Its extensibility features make it possible to provide specific processing if required.
// end of short product description

Rine le Comte
Product Manager Business Integration Solutions
To-Increase

Luiza_M

As far as I am concerned there is a new module for NAV dedicated to data synchronization and management in multi-branch oragnizations - called MDMS ( Master Data Management System).

Maximus

That sounds interesting Luiza. Please elaborate

Luiza_M

This system finds its major use when individual locations work on their own databases. It also operates on the assumption that the majority of subsidiaries (including the head office) is already using or will use Microsoft Dynamics NAV at some point in the future.
I’m attaching the image that overviews MDMS.
In what exact information are you interested in Max?

Luiza_M

Maximus do you have any questions or maybe you need more info?