Looking for something like a data warehouse for NAV

Miklos_Hollender

I heaped one custom development on another to fulfill our reporting needs and now it looks like I basically reinvented a data warehouse. Now I want to replace it with a real data warehouse as it would be cleaner and more maintainable. This is what it would need to do (as this is what the homegrown solution does). Important to understand that the scenario is a company group consisting of one headoffice and subidiaries in multiple countries.

- Copy data from various geographically distant NAV servers/databases/companies into one place several times a day

- Consolidate tables, in the sense that from every company's Item Ledger Entry one common Consolidated Item Ledger Entry is generated with a primary key like Company, Entry No.

- Define reports on top of these

- The reports get emailed out automatically in an Excel format at specified times to specified receivers

- Central Dimension-like thing. I don't really mean it in the NAV Dimensions sense but NAV Dimensions are one possible solution for this. But I actually mean it in the OLAP Dimension sense. Perhaps best explained through an example of how I am doing it in my custom development. Headoffice enters Global Item Numbers into a central database here. From this every day an XML file is automatically generated and automatically sent to every subsidiary's NAV server where it is automatically imported into a custom table. Users when entering an Item can give it any number they want but also link it via a new field to this new table containing the global item numbers. When the data such as Item Ledger Entries is uploaded into the central system and consolidated into one table (see above) this also comes with it. Thus, from the consolidated item ledger entry table, summing it up by the global item number, you can create a report that shows you the inventory all over the company group. Or sales. On a broader general note the requirement is that users of a local NAV in a subsidiary should be able to assign "Dimension-like data" to items, customers, accounts etc. selecting from a list that is not maintained by them but maintained by the central organization on a central server geographically distant.

I THINK I just reinvented a data warehouse - something like SAP BW? I don't know that product but I think what I developed must be similar to what a lot of other global companies need for reporting? Is this even called data warehouse? I think so. But I would like to replace it with a clean standardized solution, not home-developed.

My main worry is that it would be hard for someone else to maintain my solution. Also, it is not that reliable. All this exporting files via Job Queue and uploading them with scripts and FTP is not 100% reliable. It requires constant monitoring. For whatever reason the Job Queue and XMLPorts sometimes say "I/O error" then the whole thing stops, does not try it again, we have to keep an eye on them.