Data warehousing relation between 2 fact tables with different grain for drill down in report tool?

Question

I've read through the DWH Toolkit and searched here on Stack Overflow. There are different stores and HQ wants to have a report with their monthly revenue. All data is normalized in the DWH and a periodic fact table is created for the reporting tool so that HQ can see the monthly revenue.

After half a year there is a request for change. HQ want to be able to drill down in the report to see more details for each store.

Since this is aggregated data a drill down is not possible. How to solve this?

• Create a fact with a lower grain and join via the DATE dimension to the lower grain (Kimball shows on p.81 in the DWH toolkit a date dimension) what I think is possbile to use to go from a higher level grain fact to a lower level grain fact with a join.
• Create a new transactional star schema and do the aggregation in the reporting tool (for example PowerBI). Since there is a lower grain aggregation can be easily done.

Answer 1

You create a fact table at the lower grain. It is best practice to always create fact tables at the lowest possible grain - so that you don't get the exact issue you are now facing.

Your BI tool can do the aggregation or, if you are facing performance issues, you can create your own aggregated fact tables. How you drill down from from one fact table to another is normally dependent on your BI tool