We have a cube with a regular dimension and a reference dimension. The reference dimension is connected through the fact table through the regular dimension (there are many other dimensions as well, but I'll simplify the situation here). Both the regular dimension and the reference dimension have hierarchies that that include as their top level a level called "System". For both of the dimensions, the "System" level is snowflaked in from the same table. So the regular dimension table contains a column called System_key, and a corresponding attribute. Similarly, the reference dimension table also contains a column called System_key, and a corresponding attribute. Both keys point in to the same table. Naturally, the regular dimension table also has a column that points to the reference dimension table, as you would expect with a reference dimension.
OK, I hope that's clear (if a little pathological). Essentially, the "System" attribute is present on both dimensions, and both dimensions get that value by snowflaking in to the same table.
So the problem comes when looking at measures broken down by System. The regular dimension has a "System" user hierarchy which contains a single level, the System level. When I drag that hierarchy on to a pivot chart, I get the wrong results.
From what I can tell, when processing the dimension it is populating the system attribute for the regular dimension based on the key value stored in the reference dimension table, not the regular dimension table. I have no idea why it would do that. But I look at the SQL statement generated during processing of the key attribute for the regular dimension, and it includes in the where statement a join between the reference dimension System_key column and the System table, rather than the regular dimension System_key column as I would expect. The result is totally wrong values.
Does this make any kind of sense? Any idea what could be causing this, and what might fix it (note that totally redoing all the incestuous table relationships at this point is probably not an option).What if, in the Data Source View, you create another instance of the "System" attribute table, using a simple Named Query like: "select * from System"? Then you snowflake one instance with the regular dimension table, and the other instance with the reference dimension table. My guess (maybe someone can confirm this?) is that, unless you explicitly use role-playing dimensions (like Date in Adventure Works), a single snowflaked table in the DSV is interpreted as a single instance, even if it's incorporated into the schema of multiple dimensions. And in your case, I'm assuming that breaking the "System" attribute out into its own role-playing dimension is not an option?|||Deekpak,
Thanks for the suggestion. That does seem to solve the problem.
However, it does seem like a bit of a hack. It would be nice to know if this is a bug in SSAS that may be fixed in the future, or if there's something fundamental that I don't understand here. We snowflake in this table in multiple other dimensions as well (though none others with reference dimensions currently), so it would be good to know if we're setting ourselves for more trouble later.
Any 'Softies out there care to comment?
No comments:
Post a Comment