ODS as hot partition of the data warehouse
2 posters
Page 1 of 1
ODS as hot partition of the data warehouse
Have requirements for near real time reporting which need historical data as well as todays data. We have considered two options
1. replicating the entire production data base so that real time reporting can have access to historical data. However we then would not have all that dimensional models have to offer.
2. Create dimensional, current day view of Production systems and create views over tables to combine current day and history.
I would like to explore the second option.
Additionally we are considering to update the cube with current day data and flush and reprocess this data over night.
The concept sounds novel but completely new to us and we are afraid of pitfalls that we don't know about.
Has anyone done something similar? Can anyone provides some guidance or reference on best practices. Can we do away with views and use table partitioning?
Any help or even opinion will be appreciated.
1. replicating the entire production data base so that real time reporting can have access to historical data. However we then would not have all that dimensional models have to offer.
2. Create dimensional, current day view of Production systems and create views over tables to combine current day and history.
I would like to explore the second option.
Additionally we are considering to update the cube with current day data and flush and reprocess this data over night.
The concept sounds novel but completely new to us and we are afraid of pitfalls that we don't know about.
Has anyone done something similar? Can anyone provides some guidance or reference on best practices. Can we do away with views and use table partitioning?
Any help or even opinion will be appreciated.
Ramtin- Posts : 12
Join date : 2011-03-10
Cube with refreshing partition
Hi Ramtin,
I've had success with this challenge using a dimensional datamart schema and SSAS cubes.
An SSIS package runs every minute during the working day which looks for source data that has been added or modified (this relies on the source system having reliable timestamping). This new data is refreshed into the datamart dimension and fact tables.
The last step of the SSIS package kicks off SSAS processes to refresh the Dimension and then reprocess a cube partition for the current day's data. Note this is full MOLAP - the other built-in options (e.g. Automatic MOLAP, Realtime HOLAP etc) seemed too complex and fragile.
The largest fact table I've done this with is not huge (<2m rows) with around 1,000-3,000 rows being added or modified on a daily basis. The entire SSIS package described above runs in 2 seconds when there is no new data, or about 15 seconds when there are changes. Overnight there is a full refresh (just in case the source system timestamping is flawed).
Cube users don't notice any disruptions - they just refresh their Excel pivottable a minute or two after posting their data in the source system to analyse it in the cube.
Good luck!
Mike
I've had success with this challenge using a dimensional datamart schema and SSAS cubes.
An SSIS package runs every minute during the working day which looks for source data that has been added or modified (this relies on the source system having reliable timestamping). This new data is refreshed into the datamart dimension and fact tables.
The last step of the SSIS package kicks off SSAS processes to refresh the Dimension and then reprocess a cube partition for the current day's data. Note this is full MOLAP - the other built-in options (e.g. Automatic MOLAP, Realtime HOLAP etc) seemed too complex and fragile.
The largest fact table I've done this with is not huge (<2m rows) with around 1,000-3,000 rows being added or modified on a daily basis. The entire SSIS package described above runs in 2 seconds when there is no new data, or about 15 seconds when there are changes. Overnight there is a full refresh (just in case the source system timestamping is flawed).
Cube users don't notice any disruptions - they just refresh their Excel pivottable a minute or two after posting their data in the source system to analyse it in the cube.
Good luck!
Mike
ODS as hot partition of Data warehouse
Thanks for the response Mike and while that is great news that it can be done, I am wondering if anyone has done this in Oracle and DataStage and cube combination as this is our then mix that makes up our current architecture.
Ramtin- Posts : 12
Join date : 2011-03-10
Similar topics
» Ebook The Data Warehouse Lifecycle Toolkit, 2nd Edition: Practical Techniques for Building Data Warehouse and Business Intelligence Systems
» data warehouse or not ? when is it okay to use OLAP without a data warehouse database
» data warehouse and data warehouse system
» difference between data mart and data warehouse at logical/physical level
» Data warehouse / data retention strategy - ERP upgrade and consolidation
» data warehouse or not ? when is it okay to use OLAP without a data warehouse database
» data warehouse and data warehouse system
» difference between data mart and data warehouse at logical/physical level
» Data warehouse / data retention strategy - ERP upgrade and consolidation
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum