Levels and Hierarchy

[singhd]

Was wondering if someone could help me with the below

I have a hierarchy where 2 levels reside in 2 different dimension tables. They don't have any direct join, which means that we would need to go through a fact in the data source query. I was wondering if it is a common approach. I have always tried to have the whole hierarchy in a single table.

Secondly, i have a scenario where i have 2 columns, Type and Description.

Type    Description

Country  US
State     CA
City       SD

My hierarchy has to be Country->State->City. Was wondering how i could set it up in transformer. Ideally i would have wanted 3 columns in the database, one for each level instead of a type field used to distinguish between them.

[singhd]

Could someone please help

[redmist]

You could create a sinlge query subject combining the Fact and the 2 Dimensions and build Dimensions off it.It is ok to go through the Fact if there is no other option. The drawback would be that the cube build performance would take a hit.
Could you clarify your second question?

[bloggerman]

Also, there might be a chance of missing some data since it is fact dependent.

[singhd]

On the second question above, normally, when we are to set up levels in transformer, we pull each level from different column in the data source. For e.g., if our hierarchy has Country, State and City we would have separate columns for each in the database. 

In my scenario, instead of having each in a separate column, i have the data available through 2 columns, one type and other its description.

I was thinking if its worth creating a report that would have a column for each level and use it in the transformer. And, if anyone has come across something like this?

[nmcdermaid]

You would have to have another column in your Country/State/City table which correlates a city with something in your fact.

Is it City?

In this case you simply write a query to transform that table into something useful: