I have a fact tabel that has a PK that is tied to another table with
the same PK that is a subset of the first fact table because it is used
for users to writeback reasons to late shipments. I want to create a
dimension by counting all the lates in the fact table, but also showing
those in the other table that have a reason assigned(not all lates will
have a reason assigned, so I was attempting to use the unknown visible
property option to show those with no reason as unknown.

Here is a sample of my tables.


Shipments Table Late Table Reasons table
Shipment_ID(PK) Shipment_ID(PK) Late_type_id
Late_type_Id Late_type_Id Reason1_cd
Reason1_cd Reason2_cd
Reason2_cd Reason1_dsc
Reason2_dsc


I'd like my dimension to look like this:
Reason Dimension
Late_Type_ID
Reason1_dsc
Reason2_dsc


I have my table linked correctly so I'm still getting a total count of
all the facts in my fact table, but when I create the above dimension,
it doesn't show all the reason1_dsc or reason2_dsc that are in the Late
Table.

Tried various scenarios and it acts like I need a unique key that will
tie directly to the fact table from the reason table, but seems like
you should be able to create the dimension by using multiple tables
linked to the fact table instead of the simple one dim table to fact
table. I know I used to do this in 2000, but it would have limited the
results by the values in the shipment late table, but with the new
unknown parameter, I was expecting it to work like a left join, which
it does when processed, it's just not giving me the hiearachy that I
expect. When I copied the sql from the successful processin of the
dimension(the last sql box), and ran it agains the database, it showed
exactly what I wanted, but something is preventing it from showing up
that way in the dimension.

Any help would be greatly appreciated.

Thanks!