Architectural Question about Data analytics

Question

I understand that in many OLTP architectures there is some ELT stuff which creates a OLAP Data warehouse model and then you can run data analytics on this. However, this can take some time to generate. Suppose you need to run your data analytics immediately, is there another well known pattern or approach for this?

Many thanks.

Answer 1

1. Have your OLAP structures already in place (star schemas, fact and dimension tables).

2. Populate your OLAP structures with a feed from your OLTP systems. This can take quite a while the first time through but this feed is where the transformations happen. Once all of the data has been transformed and loaded into the fact and dimension tables, you use the same logic to run but only bring in new or updated data. This is a trickle update.

3. Once your data is in the OLAP structure, build your cubes. Only process the updates if you can.

The biggest mistake I see is people relying on tools and wizards to accomplish this. They end up importing all historical data every day and completely rebuilding their cubes from scratch. They are the ones that also complain that it takes hours and hours for their data to be ready for reporting.

Wizards will only get you so far. If you are planning on building a reliable robust reporting system, you have to get your hands dirty and start writing some SQL code.