Hi guys. I've got a data model with two central fact tables. One is sort of a parent of the other. So one table is say, Categories, and the other is Items. A Category has a number of metadata fields associated with it, and an Item has its own. I have a number of dimensions relating to these tables in my schema, but some of them relate to both Categories and Items. This has been causing me a lot of problems because of synthetic keys and circular references, so I wanted to try merging the Categories and Items into one central table, so I don't have to duplicate dimension tables. I'm not sure how to do this though.
So say I've got the Category table with fields CategoryID, C1, C2, C3 and Item table with fields ItemID, I1, I2, I3. I want to merge the two tables such that there's a single RecordID field with a RecordType field that's either "Category" or "Item". That way I can select by RecordType and my counts will still work. I also want the Item rows to inherit the values of their parent Category, so my table would look something like this:
Is the category table like an aggregate table which has aggregations at category level while the item table has data at more granular level?
If yes, then you can create a dimension table which has all attributes of category. Next, the fact table should have data at item level and the category id. This fact should be linked to category dimension with category id. This might help you in resolving circular references and synthetic keys.
Your table layout looks logical to me, at least on the surface. While I don't think I have any in QlikView, we have tables that look just like that in our business system. Typically, we'd have the lower level item pointing to its parent's record ID, but there doesn't seem to be a need for that here.
I'm not sure what you mean by aggregate table.. The Category is a container basically. A Category owns a number of Items. I don't think I can just pull the Category fields into a dimension though, because I need to do Counts and such on Categories as well as Items. So I may want to do something like, find the count of Items where the Category type is X. I may also want to find the count of Categories where the type is Y. That's why I thought to merge the two into a single table. So I can just flip the switch by RecordType and all my counts would work. If I pick a CategoryType, I can then select RecordType as either Category or Item and get my counts. What do you think?
