cross process(data marts) ratios

View previous topic View next topic Go down

cross process(data marts) ratios

Post  KevinP on Thu Jul 05, 2012 3:45 pm

We have 8 data marts in production and we are now designing Enterprise Data Warehouse(EDW). We will now like to identify & develop second line data marts, ex. profitability dara mart created from Revenue and Cost star schemas. This useful cross process metric may be derived from multiple fact tables either from same subject area or different subject area (say Finance & Inventory).

What are the industry best practices/guidelines in accomplishing this?
- when to develop them?
- when to create physical tables vs drill across?
- where to store them - separate schema?
- ETL issues to watch

Hope you will share your experience & thoughts on this.

Thanks

KevinP

Posts : 3
Join date : 2012-07-05

View user profile

Back to top Go down

Re: cross process(data marts) ratios

Post  BoxesAndLines on Fri Jul 06, 2012 8:55 am

Building fact tables from other fact tables is not a problem. That is in fact what all aggregation tables are. Same for accumulating snapshots in that there is usually a transaction fact providing the source events.

The decision to drill across vs. build should be simple. Is the metric available? That is without a simple sum or group by. If not, build the fact table.

Storing the tables is personal preference. I prefer all dimensions and facts in a single schema. I put staging and work tables in a differenct schema. All warehouse tables go on the same database.

With regards to ETL, you now have introduced dependencies on fact tables. This will cause the ETL window to lengthen. With 8 fact tables currently, I don't foresee issues unless the volume is extreme.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

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