We are in the process of creating a star schema to be used in an adhoc environment. I have one fact table and five small dimensions of which one of them is the date dimension. I created the fact table with Soft RI constraints on the foreign keys of all the dimension tables. I created a broad AJI over the top of all of this involving all of the dimensions. When I execute a query where all of the dimensions are joined to the fact table, the AJI is utilized quite nicely. Since this will be adhoc, I have been trying to create a group by query in which only 2 or 3 of the dimension tables are used hoping to also utilize the AJI. I thought the Soft RI would enable this but I have not been able to get it to work. Has anyone else been able to accomplish this or am I confusing this with some other concept? :) If I am way off base with this, has anyone come up with a good adhoc star schema strategy they would be willing to share?
I will admit... I got a little lazy and was looking for a quick answer! :) Anyway, I found my answer in the 'Database Design' manual. For those that are interested in the solution it can be found in Chapter 12: Join and Hash Indexes. In a nutshell, with Soft RI applied to the fact table a query with fewer tables than the AJI can still utilize the AJI. Without an AJI, this still gives one the advantage of join elimination.