Hierarchy Blues

[Lynn]

I've got the hierarchy blues, though I don't think B.B. King ever sang about it....

My hierarchy has 5 levels:

Major Category > Minor Category > Category > Class > Item

The bottom three levels (category, class, and item) are in my database and I have a report as the data source for transformer. The two top levels (major and minor) exist only in a spreadsheet along with category and class, but does not go to the Item level of detail. So category and class overlaps between the two sources. Item is unique.

I am trying to create a dimension in Transformer that brings the two together. I was hoping Transformer would just magically understand, based on the common column names, how to bring them together but it complains when I try to generate the categories.

I get TR0516 which mentions invalid data, low memory or low disk space. I am experimenting with tiny little bits so I don't think it is a resource issue. I have the data type specified as text for both sources.

I've never tried to build a dimension with half the story coming from one place and half from another, so not sure if my approach is even viable. I was trying to avoid creating a datasource for the spreadsheet and modeling in FM (which would then allow me to merge it with the database data)....

Any thoughts or ideas will be much appreciated! Thanks.
Lynn

[cognostechie]

Lynn -

Yes, it does work with half the story from one source and the other half from other source. I sometimes do this to avoid orphans. When orphans get created because the fact source has some Items that are missing in the Item dimension, I create a seperate source to get the Items from the Fact source before the fact data goes into Transformer.

In order to connect it properly, you have the mark the common levels unique. So in this case, I would mark Category, Class and Item as unique. It would definately require for the data to be structured in a way that uniqueness violation is not detected. So as long as there is only one path from top to bottom for the category, ti would work.

[Lynn]

Fantastic! I set the uniqueness verification property for my spreadsheet source to "maximize data access speed" instead of verify category uniqueness and then set those three levels as unique -- seems to work exactly as I'd hoped.

Thanks a million!!