31 Designing A Real Time Partition

www.kimballgroup.com Number 31, November 19, 2001 Kimball Design Tip #31: Designing A Real Time Partition By Ralph Kimball Even though the time gap between the production OLTP systems and the data warehouse has shrunk in most cases to 24 hours, the rapacious needs of our marketing users require the data warehouse to fill this gap with real time data. Most data warehouse designers are skeptical that the existing ETL (extract-transform-load) jobs can simply be sped up from a 24 hour cycle time to a 15 minute cycle time. Data warehouse designers are responding to this crunch by building a real time partition in front of the conventional static data warehouse. Requirements for the Real Time Partition To achieve real time reporting we build a special partition that is physically and administratively separated from the conventional static data warehouse tables. The real time partition is actually a separate table subject to special update rules and special query rules. The real time partition ideally should meet the following tough set of requirements. It must: contain all the activity that has occurred since the last update of the static data warehouse. link as seamlessly as possible to the grain and content of the static data warehouse fact tables be so lightly indexed that incoming data can be continuously "dribbled in" support high performance querying In the dimensional modeling world there are three main types of fact ...