cross process(data marts) ratios

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

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.