Attributes from a dimension appearing in other dimensions; multiple-inheritance in hierarchies.

I wonder if you can help me with some design questions.

Is it OK to have an attribute of a dimension be reused in other dimensions?

Picture it like this (for a marketing dimensional model):

1. advertisement_dimension has attributes: name; campaign it belongs to; brand it belongs to; owner (who is entitled to see the brand marketing results), start_time_for_advertisement, end_time_for_advertisement, etc.

But one can imagine that campaign and brand are a pretty common concepts/entities and would appear in other places in the data model other than advertisements_dimension (they could appear on contracts about a brand, etc). How to refer-to or point-to a brand now from a contract dimension if the brand is inside the advertisement dimension? Snowflake it? I mean have both advertisement_dimension and contract_dimension refer to a brand dimension? If using views to hide the snowflaking, the advertisement_dimension would seem to contain the brand dimension, likewise for the contract dimension.

Is it OK that brand dimension has an owner for the brand? And that this owner is in itself also a dimension? Or should the owner with all attributes be fully contained within the brand dimension rows that refer to it? Again something that could be snowflaked? I am not always interested on the owner so I am not sure why it has to be inline in each advertisment or campaign or brand dimension row with all its attributes. Owner can own many other different entities and it would cost too much to inline it everywhere. Owner can be the one creating them, delegating them to a marketing agency (has even different roles each time it appears).

How to represent the information that a brand may be delegated to different agencies for marketing purposes? So that the delegated to agencies can advertise about this brand? In the dimension rows I only have space for one brand owner and one brand delegated marketing agency. Multiple delegated agencies would require multiple rows or a variable number of columns. If I represent this as a fact (say fact that a brand has been delegated to an agency on a date, is it OK to just put the date it was delegated? What happens when this delegate action is revoked? I mean the permission to advertise is revoked from the agency? Should I update a column on the fact row to state it is no longer valid? I have not seen designs with facts happening on some days, but not happening on other days that often. This typically goes into a dimension, but then dimensions do not allow multiple rows unless I say the dimension is semantically called: brand_delegate_dimension and it would be a SCD type 2 with validity periods for the delegation, but then I am back to my initial question, the brand (and the delegated agencies) would appear again in the brand_delegate_dimension (brand already appeared in its full glory with all attributes in the advertisement dimension above). Again, snowflake it and make it look like a single dimension with a view? I guess it would depend on how many brands I have and how much space I would save in snowflaking right and CPU would waste on joins from advertisement_dim with brand_dim and from brand_delegate_dimension with brand_dim?

2. Another similar question. What if a campaign can belong to multiple brands, like a joint campaign with two brands being advertised at the same time on the same advertisement? This would invalidate design 1 above again calling again for the multiple rows to represent multiple associations of campaigns and brands. I have not seen multiple inheritance hierarchies that often in designs.

Best regards, André Cesta

aacesta wrote:Is it OK to have an attribute of a dimension be reused in other dimensions?

The short answer is yes. However Advertisement and Contract seem to be facts themselves. So the imaginary snowflaking may just be a normal star schema.

Whether you can denormalise brand or owner into other dimension depends on the nature of that dimension. It looks like in your case, a brand represents a product and its associated attributes. If so, you don't snowflake it in other dimension, instead, you reference it as FK in other fact tables. So your brand-agency relationship is a factual relationship, or you may call it delegation factless (or coverage) fact table.

Sometimes limited snowflaking is permissible in dimensional modelling and Kimball call it outrigger. It depends on how many attributes are repeated how many times in dimension tables. If a group of 5 attributes are repeated twice or more times in dimension tables, I would treat it as outrigger in other dimensions. Especially when the group has type 2 implication within itself, denormalising its attributes in multiple places will risk creating multiple versions of relationship for the same group.

Thanks hang, it is a good reply. Please see my comments below:

>However Advertisement and Contract seem to be facts themselves. So the imaginary snowflaking may just be a normal star schema.
Maybe not, please help me check this. In my case, advertisement and campaign and contract are entities that have configurations with start timestamp and end_timestamp (on the second or minute). They do not have a grain like daily, or valid today and not tomorrow. They usually are configured to hold for a period of time and may change attributes/configuration generating another period with another configuration (typical of SCD Type 2) The facts that we have are the hits or events (marketing events) for these entities (advertisements, campaigns), these happen at a certain time and can be rolled-up as daily sums of events (also facts), etc. THese advert hits/impressions are factless facts with just keys to the advert_dimension that was hit, timestmap of event, etc.

About brand delegation being a possible factless fact table or coverage fact table (http://www.kimballgroup.com/html/designtipsPDF/DesignTips2003/KimballDT50FactlessFact.pdf). Yes, you are probably right.... But I still have problems with the grain as delegation will start at a given second of a day and continue until the second/timestamp it is revoked. THat is far too granular to store for every second that brand b is delegated to agency a.

Ok, I guess in your case, Advertisement and Contract could be modelled as type 2 dimension. You could denormalise brand and owner attributes into advertisement dimension if it is the only host dimension while still creating aggregate dimensions for the faceless fact tables that only hold dimension keys. The idea is, when the fact table references Advertisement dimension, you don't need to include the aggregate dimensions as they are parts of the main dimensions without snowflaking.

With delegation coverage table, you may take daily snapshot even if the change happens at any time of the day, as all you need to know is coverage picture at the end of each day or even month depending on the timeframe for other fact tables. You may also include the timestamp along with date key in the coverage table for more granular analysis if needed.

Yes, thank you. That is it. Just to complete my thank you I'd like to post that I found the last two paragraphs from Article below very helpfull:
http://www.kimballgroup.com/html/designtipsPDF/DesignTips2003/KimballDT50FactlessFact.pdf

This article was specially important for me, as I have many slowly changing dimensions type 2 that qualify an advertisement (brand is one of these dimensions) and it is hard to keep track of all changes in all these related slowly changing dimensions type 2 at any moment in time which sort of gives a profile in time for the advertisement and its attributes. This is exactly what the last two paragraphas from the article above teach doing for a customer dimension and mini-dimensions that qualify the customer such as: demographics, preferences, etc. Very nice article! And very nice comments/replies hang, thank you all.