ODS as hot partition of the data warehouse

View previous topic View next topic Go down

ODS as hot partition of the data warehouse

Post  Ramtin on Thu Mar 10, 2011 3:12 pm

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.


Ramtin

Posts : 12
Join date : 2011-03-10

View user profile

Back to top Go down

Cube with refreshing partition

Post  Mike Honey on Sun Mar 13, 2011 7:09 pm

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
avatar
Mike Honey

Posts : 185
Join date : 2010-08-04
Location : Melbourne, Australia

View user profile http://www.mangasolutions.com

Back to top Go down

ODS as hot partition of Data warehouse

Post  Ramtin on Tue Mar 15, 2011 11:55 pm

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

View user profile

Back to top Go down

Re: ODS as hot partition of the data warehouse

Post  Sponsored content


Sponsored content


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