How to design a fact table to keep track of active dimensions?
I would like to design a classic OLAP facts table using a star scheme. The SQL model of the facts table should be independent of any concrete RDBMS technology and portable between different systems.
The problem is this: users should be able to select subsets of the facts based on conjunctive queries on the dimension values defined for the facts. However, the program that provides the interface for doing this to the user should only present those dimensions where anything is still selectable at all. For example, if a user selected year 2001 and for dimension contract code there is only a single value for all records in the fact table for that year, this dimension should not be shown to the user any more.
How can this be achieved in the most performant way? Is there a "classical" way of how to approach this problem?
Any help or pointers to where one could find out more about this would be greatly apreciated - thank you!