Implementing near real time reporting on fact tables

[platforminc]

Hi All,


I would like to find out how opther users implement near real time reporting on a DW, what is the best way to build summary tables ? I find that my fact table is going to be queried quite heavily from the front end, although mainly on very recent data only, so I’m looking at whether SQL server partitioning will help here. However, I’m also thinking whether a cooked up summary table willl also suffice, as the data will be canned already and ready to consume. Any ideas on the best practice approach to set it up.

Thanks in advance.

[zoom]

Define near real time with the business user and get their sign off. Usually real time reporting happens within 20 minutes or half an hour interval. ETL needs sometime to crunch data so it cannot be done in seconds. You need two sets of fact tables... one TEMP fact table for those records whose information changed on the source system and you need to crunch their data or new records you did not receive before. You need another PERM fact table for your full population of records whose information did not change from your last data load.
Now it is up to you to update/insert PERM fact table with TEMP fact or Join those two fact table in a view and let people use that view for real time reporting.