Hi All,
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.
Can we use BizTalk Server or SQL Mirroring…
comments will be greatly appreciated
What sort of reporting is required across all databases?
Have you analysed the group reporting requirements and found what nature, number and frequency of reports are needed?
If this is only for Financials have you considered Consolidation?
Have you considered SQL eporting Tools?
There are other tools for group reporting, COGNOS, TM1, Hyperion.
Do you have the option of merging into one database, and then using terminal server/citrix to run Navision? If so this will be the most cost effective.
If not, explain in more detail what you need this for, i.e. is it just for GL type reporting, or must it cover Inventory AR AP etc.
Hi David,
Thanks a lot for the info. Even though it’s a cost effective solution the complexity involved is too high.
Can we use any reporting tool which can fetch the data from 3 different databases & can integrate in one report.
Please Suggest
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.
Another option can be when all datbases are sql databases you can define views in sql and link them to a table in the navision database.
This way you can create a report in the navisiondatabase.
I would go for the external reporting tools though
In that case then as BaBrown says, SQL reporting services.
The next issue will be keeping core data in sync (i.e. customer numbers, Item numbers GL accounts etc.)
Hi,
Thanks for the info.
And the David’s comment “The next issue will be keeping core data in sync (i.e. customer numbers, Item numbers GL accounts etc.)” is valid.
So what we thought is, We will use the Crystal Reporting Tool to fetch the data from 3 different database, and a consolidated report will be generated.
And for this, I want to know will it be the right way!!!
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.
What sort of reports does the company require?
They want to record all the transactions…or every transactions done on the database.
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.
Thanks David,
I think they want to control or monitor inventory as a whole(ie from all the 3 database), i mean, they want to know at a given point where one can get the required inventory for a component.
That It!!
once again Thanks to everyone
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.
thanks.
Actually I am planning to write a little something about NEP for internal use (planning, not scheduling yet [;)]). Once I have that I wouldn’t mind summarizing it into a downloadable document.
There are demo scripts with flashmovies on partnersource that show you how it works…