Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

ODS as hot partition of the data warehouse

2 posters

Go down

ODS as hot partition of the data warehouse Empty ODS as hot partition of the data warehouse

Post  Ramtin 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

Back to top Go down

ODS as hot partition of the data warehouse Empty Cube with refreshing partition

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

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

http://www.mangasolutions.com

Back to top Go down

ODS as hot partition of the data warehouse Empty ODS as hot partition of Data warehouse

Post  Ramtin 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

Back to top Go down

ODS as hot partition of the data warehouse Empty Re: ODS as hot partition of the data warehouse

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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