This is our situation: we want to store "realtime" information. It will be a website statistics application. We were thinking to setup the database-part as follows:
Because there can be many requests in less time, all data should be stored as quickly as possible. We thought of one simple table, that stores raw data. If we want to store the data directly in the relational database, the storing application needs time to check certain things, which includes selecting data from the database first. When using one single raw table, the application can store the data directly.
2. Transferring to relational
Because a single table isn't very clean and the stored raw data can't be used easy, we want to transfer the raw data to a relational database. A stored procedure could do this every hour for example. The checking, etc. is done by this stored procedure. The collected data is stored in a nice relational database, without the storing application (from 1) being delayed, which could have its consequences.
3. Presentation data... statistics... OLAP?
We want to present the stored statistics to the user. This includes overviews, summaries, etc. The relational database can grow very large, which means that generating realtime statistics with each new user request, can take a lot of time. OLAP can solve this problem. To use OLAP, a star scheme is needed. So we could denormalize the relational database (from 2) to a star scheme, but we really want a relational database that holds the actual data. So we want a external star scheme database that is managed by the OLAP service.
This is a simple image of our idea. Is this a good approach: raw table (fast storage), relational database (actual storage), star scheme database (OLAP, presentation data)? Can OLAP be used in this situation?