Let us take a simple scenario. Let us say we have a countries dimension which has all the countries. If we want to use this dimension table to get a list of countries where we have customers, as well as use it to get a list of countries where we have offices we can create 2 aliases COUNTRY_OFFICE and COUNTRY_CUSTOMER and create objects from the aliases. No issues till now, except if we try to get a list of countries where we have offices. Since we have used the master country table it will list all the countries irrespective of whether we have office there or not.
Let us see how it works below.
In the example above I have a country table and I use it for both client and showroom country. Now if I query for showroom country i.e. countries where I have showrooms here is the query produced.
As you can see it lists all the countries in the countries table which is not what we wanted. In order to get the correct list of countries I have to join with the SHOWROOM table so that the countries list is restricted based on the SHOWROOM dimension table. This is done by specifying that whenever the Showroom Country object is used in a query, the Showroom table must also be inferred in the FROM clause of the SELECT statement. Providing that the Showroom_Country table is joined to the Showroom table the object is then guaranteed to only return countries in which showrooms exist.
Making this change ensures that we always get the correct set of countries when we query for showroom countries.
While this does not seem that critical, however it becomes very important, especially when we want the user to select the showroom country in a prompt for example. We only want those countries to be in the prompt list where we have showrooms and making this change will ensure that we always get the correct list.
