We have a situation wherein we have to propose a architecture to a customer. Customer maintains three different databases around three different locations. Now he wants to consolidate all the transaction happening in 3 different databases into 1 database and take the reports.
If you are looking to report on a snapshot basis, say end of previous day, you could use SQL Analysis Services to combine the data into a reporting database. Then use SQL Reporting Services or Crystal (or other tool) to create and run reports. The database load could also be used to preprocess data for reports.
If you want realtime info, then David’s suggestion is the way to go.
I am not an expert on Crystal, nor SQL, but I would recommend the way babrown suggest of creating a consolidation database and getting the data in to that, and from there use Crystal or SQL reporting services to get the data out. One reason for this, is as Marq mentions, you can link to an external database through Navision, which just makes it all a bit more flexible as you could even then create reports directly from a new Navision database, this would mean then that the updates would only be the transactional data, you would just use your current NAvision’s Static tables (Cusotmer Item etc,) and link to the extra data.
Do you know why? Some how that just seems like a bit much.
If this is the case, then you are going to need full replication. Is it possible that they are just saying “everything”, but in fact have a specific requirement, that is difficult to define.
The job to daily merge all this data is big, and orders of magnitude more complex than a citrix solution. There are some solutions out there that may be good starting points, such as kloster or expandit.
Well if its just inventory, then no need for a ful replication. Also if its inventory, then probably a nightly sync is not going to be enough.
I would suggest that you create a small program using C/FRONT that can get the quantity on hand of a particular item numer at a particular location ona aparticuar database. Then create a SQL fucntion that can reotely clal this routine from one centralized location. Then in Navision create a form that can access this new centralized database form any of the remote locations. When a user looks at inventry on hand, this can be a matric from that then makes a call out to each database and gets the current qty on hand. Sionce you are only going to be looking at a hand full of items at a time, the internet traffic wil be minimal, and the numebrs will allways be instantly current.
BUT ! I think the first issue that you need to address, is to sit with the client and do a full analysis. It does not seem that you have a full picture of the clients needs, and that can only lead to one of two possible scenarios.
A/ You design the wrong solution for the wrong issue, and then end out losing money by having to deliver the correct solution.
B/ You design the wrong solution for the wrong issue, and then the customer loses money by having to pay to deliver the correct solution.
Please find out exactly what the client wants. Other wise someone (maybe in sales) will say to the client “Ah Navision has consolidation built in as standard, so that won’t cost you anything.”
Sounds to me like you are a good candidate for a data warehouse and a BI application. If I were you I would get some DW/BI people to demonstrate some options for you, preferably who have experience with Navision. There may be some people here that know something about this subject, but you really want to talk to some experts.
As far as what tool to use, instead of going with a 2rd party tool, I would seriously consider upgrading (at least technically) to 4.0 SP1 or SP2 on SQL Server 2005, and use all the tools that are included for free. You may have to invest in the SQL Server license, but then you get a full set of DW/BI tools included in the product (integration services, reporting services, full seamless integration with office, query tools, etc.). Put it all on Windows Server 2003 and you can use Sharepoint to publish the information. This stuff is very potent and has many possibilities.
I don’t see any need for you to go with any type of replication, that would only create duplicate copies of your databases for which you would then have to develop something to expose the information.
Daniel, in hindsight, what you suggest looks like the proper solution.
Although I’ve seen the liturature, and read the blurbs, I haven’t yet setup a sharepoint system with Navision. Could you expalain a bit more about how it all ties in together. I am sure that a lot of peoplr will be interested.