Adhoc AJI

Database
Enthusiast

Adhoc AJI

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?

Thanks,

Joe

1 REPLY
Enthusiast

Re: Adhoc AJI

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.