how to implement rollup dimensions in SSAS

Question

I have read in several DWH books about the rollup dimensions. For example when you have sales at month level and stock inventory at day level. The month dimension is a rollup dimension of the standard date dimension used for the stock inventory.

My question is how to model this in SSAS. In the database you can create a view to create the month dimension, but in SSAS eventually I come up with two dimensions, each one linked to its own fact table (month to Sales and date to StockInventory) and therefore the facts don't conform in the date dimension. Is there a way to deal with rollup dimensions in SSAS so that I can join the fact measures in the date dimension?

Thanks!

Answer 1

You could add both the month and date dimensions to StockInventory. This would allow you to drill across on month but not date.

Alternatively you could assign all sales to the first/last day of the month and then fold the two dimensions into one. The problem here is your end users may well expect sales against each entry in the dimension.

Loading aggregated data often raises these sorts of issues. If loading your sales at the daily level is an option I would recommend you investigate this.