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.