Hi,I am currently investigating alternatives on how to design a new system architecture, the scenario is as follows:We have gathered financial data from our ERP-system into a database. The same database also has budgeting information. The solution for how to present and write budgeting information back to the database is deprecated and now we need a totally new solution.

The GUI should present aggregated data to the user and when budgeting figures change these needs to be saved back to the database. The data presented should be grouped by many different dimensions depending on what is presented. It is also important to be able to drill down from in each view and edit individual transaction values if necessary in the budgeting process.

As our budgeting database transactional table contains up to 1M rows I need a swift architecture for aggregating data to the client and also handle updates from the client in a swift way. The first thought that comes to mind is real-time OLAP cubes, but I want to know if there are other possible solutions other than cubes, or are the in your opinion no better solutions? The aggregation logic don't need to be too complex, we just need to have a very good query performance, possibly in-memory databases. Also could entity framework in .net be uses for the aggregation logic on top of an in-memory database or similar..

My thoughts are as follows, hope they help:If you want to aggregate data, slice-and-dice by different dimensions, etc. then you need to build a reporting system (data warehouse/OLAP) based on a dimensional model - I guess this is obvious and appears to be what you are planning, based on your post.So the real question is how you edit this data and there appear to be 2 options:1. Build/use a transactional (OLTP) system that the users can edit and use this as a data source for your data warehouse. I guess you'd need to make the load process (ETL) real-time but this shouldn't need to be a major challenge if you design it with this requirement in mind from the start.It is also quite possible to configure 'drill across' functionality from your OLAP system (where your users issue queries) to your OLTP system (where your users edit the data)2. Build edit capabilities into your OLAP system. Here, I guess you are constrained by the functionality available in your COTS BI tool - assuming you are using one - or unconstrained if you are building your reporting tool from scratch.My most recent experience is with Oracle OBIEE and this enables editing in two ways:a) you can configure 'writeback' on specific fields that makes them editable - useful for small-scale editingb) ADF integration - you can embed OBIEE catalogue objects into ADF applications as well as embed ADF 'pages' in an OBIEE dashboard or similar. This is effectively ends up being a version of option 1 but with your OLAP and OLTP systems combined into a single UI for your usersGiven that most BI tools have similar functionality (and as soon as one implements new functionality the rest follow) I'm guessing SSRS/SSAS, Business Objects, etc. all do similar things.

As an aside, you mention having 1M rows and needing a swift architecture. For a properly designed dimensional model (with pre-built aggregates, etc.) this is a trivial volume of data and performance should be a non-issue. Most current versions of the mainstream DBs, on properly spec'd hardware, will handle queries against 10's or even 100's of millions of records (held in a a dimensional model) without any problems.