Hi all, I have query subject dim_calendar, I have requirements to always restrict report fact data to <=3 months (in any used fact table). I have several query subject that represent fact tables. I have created corresponding filter for dim_calendar. I created corresponding relationships between dim_calendar and query subjects with fact (by time_sid).
Now, when I try use one query subject with fact in query studio (without using dim_calenda), I get all my data.
I would like to know what is the best approach to apply filtering of fact data regardless of that dim_calendar items are not selected in the report/query.
Thanks in advance
If I understand correctly, you can put the date filter expression on your fact query subject instead of your dimension query subject.
Typically I would do this in the middle layer of the model (as opposed to the physical layer). Referencing the date criteria in a required filter on the query subject will force the join and where clause condition to limit the fact result set as specified in the filter. This will happen if the user selects dimension items or if they don't.
You would need this filter on all the fact query subjects when modeling for minimized SQL.
Lynn, thanks a lot for your answer,
I've just noticed that I create one filter and use it in every query subject
How I didn't notice this before?)...
Thanks again.