Implementing near real time reporting on fact tables

View previous topic View next topic Go down

Implementing near real time reporting on fact tables

Post  platforminc on Fri Aug 10, 2012 10:40 am

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.

platforminc

Posts : 7
Join date : 2012-05-25

View user profile

Back to top Go down

Implementing near real time reporting on fact tables

Post  zoom on Sat Aug 18, 2012 7:31 am

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.

zoom

Posts : 97
Join date : 2010-08-23
Location : Chicago

View user profile

Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum