Using a dimension in multiple fact tables with different grain and support SCD

Hello users,

i've a question about using a dimension in fact tables with different granularity and at the same time retain support for SCD.
My scenario:

I have one Dimension called Apartment. This Dimension cosists of the following related attributes Company --> Management Unit --> Regional Management Unit --> Building --> Apartment.
Every apartment, building and management unit has attributes which are SCD type 2 candidates. Here is a short overview of the dimension table.

Create table dimApartment
(
PK_Apartment -- Surrogate Key
BK_Apartment -- Business Key
Building
RegionalMU
MU
Organisation
KindOfUse -- Used for indicating if the flat is used in private or business manner. --> SCD Type 2
.... -- Some more attributes like KindOfUse applying to MU, RegionalMU or Organisation
ScdFrom
ScdTo
ScdIsValid
)

Now I would like to use this dimension in two fact tables. Rents and Budgeting. The Rents fact table tracks the measures at the Apartment level. The Budgeting process assigns it budgets at the Regional Management Unit.
When I do an lookup during my Budgeting ETL process to find the corresponding Surrogate Key for the Regional Management Unit I'll get more then one row. SSAS offers the possibility to join a dimension with a fact table at different grains by using multiple key columns but this seems to be problematic when using a single surrogate key. I want to avoid desinging the Dimension multiple times at different grains.

I hope I described my problem good enough to get some imput from you.

Regards Michael

I wouldn't implement a type 2 as you desribe. It's not the apartment that's changing, but rather how it is used. Implement usage as a separate dimension attached to the rental facts. You may also spin off certain attributes, such as size, number of rooms, etc to its own dimension as well if it is something that changes (probably more so in commercial rather than residential). If you do, I usually carry the foreign key of the current dimension reference on the department dimension, not to be used as a snowflake, but rather to simplify ETL when loading the fact table. You may also maintain a separate factless fact table that maintains the historical relationship between apartment and it's dependent dimensions (ie. size dimension, use dimension, etc...).

The rest of it sounds like a hierarchy.

Hi ngalemmo,

thank you for your fast response. Your recommendation to model the attributes as seperate dimensions makes sense to me and I'll think about it in greater detail today. Are there any arguments against combine certain attributes together in one dimension? The attributes contain only a small range of distinct values so the dimension will not blow up. I want to avoid to much "single attribute" dimension as long as I can combine the attributes in a meaningfull context.
Can you explain your suggestion with the factless fact table in more detail? I thought the relation between Apartment and the size, color etc. dimension will be tracked through each entry in the fact table which has Date, Apartment and the site etc. dimensions as foreign keys. Perhaps I didn't fully understand your advice.
To use the department dimension (This will be the dimension with the organization hierarchie) in different fact tables with another grain level I would like to use the Building Key (for example) as the granularity attribute when joining the fact table with the dimension and set correct relationships between the attributes. Do you see any pitfalls in this aproach? How would you implement the department dimension for use in fact tables with different grain?

Thank you for your efforts!

Michael

Combining multiple attributes into a single "junk" (Ralph's term, not mine... but I like it.) dimension is common practice. You just need to be aware of cardinality and correlation. But, as you said, the cardinalities are not high, so it should not be an issue.

The idea behind the factless fact table is to track events relating to changes to the apartment. It is not necessary, but a nice thing to have if you need to deal with "when was the last time xxx changed?" queries. You are still capturing it in the rental facts, but I assume the rental fact table is there for other purposes and would be too "noisey" for such queries.

For the hierarchy, create a bridge table to associate all combinations parents and children. Capture facts at the lowest level that makes sense and use the bridge to aggregate measures to whatever level required.

Hi ngalemmo,

thanks again for your fast response. I will do some testing in the next days and test the different approaches. I'll then come back and write about my experiences.
Am I right that using a bridge table is only the recommend choice when the relationships between the elements can change? This would result in a single dimension for every level in the hierarchie which are then linked through the bridge table?
In case of elements are not moving between the nodes, would you use bridge tables for only aggregate measures to the required level in the hierarchy instead of joining the fact and dimension table at a different level of granularity?

Have a nice sunday

Michael

Hello,

durring the last days I tested the different approaches and the suggestion to model the usage of the appartments (and some other attributes) in it own (junk) dimension seems to work good. Thanks for your input.
But I'm still struggeling how to model the organisation hierarchie. I have four levels in this hierarchie at a business point of view. SubCompanies, ManagementUnits, RegionalUnits and Appartments. All subelements are in an 1:n relationship and will not move between nodes. I need to have a hierarchie where I can drill down this path. Because every level has tons of attributes at it special grain level I didn't know how to model it in the best way. The 2nd requirement is that I want to be able to use the dimension(s) with fact table at different grains. I worked out different solutions which all have it disadvantages. Perhaps someone could give me some tips which of them are more problematic than others.

1) Model every level in the organisation hierarchie as an independent dimension. Doing it this way I could handel classic SCD attributs in a "normal" way. My problem with this solution is that there will be 4 different dimensions and no hierarchie the user can easily drill down. This is currently not acceptable by the business users. On the other side this solution easily allows me to join the dimensions with fact tables at their supported grain level and using a bridge table and attach the other dimensions (which are at a higher level) by m:n relationships. But this makes me thougthful in terms of performance.

2) Model everything in one big dimensions. This would solve the problem of solution 1 but will result in a very very big table with hunderts of columns and about 500.000 1.500.000 rows. At the olap level I could arrange the attributes in display folders but for some reason I not feeling well with this solution. One reason may be that I'll have to combine attributs in one table which have a big difference in their grain. Another the way I have to attach the dimension to fact tables with different grains. In my opinion I can't use the surrogate key because of its different granularity but I need it to distinguish between rows added by SCD 2 changes.

3) Using outtrigger dimensions because of the different grains applying to the different levels. I the first moment this sounds good to me but I've a bad premonition in handeling SCD2 changes in the different relational tables and how to model the etl process and keep it not to complex. I'm not sure if this solution will solve my problem with different grain levels I described in solution 2.

I've someone can give me some tips or annotations from personell experience or if you need some more input please let me know.

Regards Michael

Hello it's me again with one last post

Am I thinking in the completly wrong direction or doing something extrem special? I thought my problem is a very common one and has to be handled in most BI projects.
Perhaps someone can give me a link where I can find some white papers or similar. I looked at all the Kimball books but only find the problems I mentioned discussed seperatly. I'm having problems combining the pattern to one solution as I tried to explain in the last posts.

Regards Michael

I'm not sure how common your problem is. Most folks don't want to wade through 1.5M rows to identify the specific leaf level Apartments to run a report. How would that work in a standard BI tool?