Should I use multiple fact tables for each grain or just aggregate from lowest grain?

Solution 1:

Each DataMart will have its own "perspective", which may require an aggregated fact grain.

Star schema modeling is a "top-down" process, where you start from a set of questions or use cases and build a schema that makes those questions easy to answer. Not a "bottom-up" process where you start with the source data and figure out the schema design from there.

You may end up with multiple data marts that share the same granular fact table, but which need to aggregate it in different ways, either for performance, or to have a gran to calculate and store a measure that only makes sense at the aggregated grain.

Eg

SalesFact (store,day,customer,product,quantiy,price,cost)

and

StoreSalesFact(store, week, revenue, payroll_expense, last_year_revenue)