I suspect this is a seriously rookie question but I've not managed to find an answer on it so hoping someone can help.
I have an olap dimension, Order Date, which is based on a role-play view, dim_order_date_vw, based on a table, dim_date, holding 50 years of dates from 01/01/1970. I have an Orders cube, which is based on a fact table, fact_orders, with 10 years of transactions in it starting 01/01/2000. When browsing the cube in Excel and filtering by order_date, the dimension filter presents me with all dates from 1970. Is there a way of restricting this to just those that have corresponding orders?
One approach would be to tackle this in the warehouse by basing the view, dim_order_date_vw, on a join between dim_date and fact_orders. This has various downsides and I'm hoping there's a simple way of achieving this in SSAS?
Thanks in advance,