Forum Discussion
Helper IIGetting BLANK on my measure
"So in hotels i have a PPA_ID which is foreign but there will be empty rows because not all hotels have a target."
This is a candidate for a Dummy Target member in the Targets dimension. If you want a name for this target, you can call it "No Target" or sth similar. So, the rule of thumb is this. If you have a dimension with meaningful members and there are rows in your fact table where no meaningful member can be referenced, create a dummy member that will signalize the situation of not having a valid member and reference it.
All I'm telling you is to never have a model with RI problems. If you allow this, you'll have a lot of hairs pulled out of your head. I promise you 🙂
Solution SageTo answer your question.... If Hotels and Targets are 2 different business entities, then they belong to different dimensions or separate dimensions they create. If this is not the case and Targets are attributes of Hotels, then you've got 2 choices: 1) if a hotel can have 1 target, then put the target as a column in Hotels, 2) if a hotel can have 2+ targets, then you'll need a bridge table or you'll have to incorporate targets into the fact table, depending on whether the targets are "static" or "dynamic." I don't know much about your business case, so it's hard for me to give you advice. Hotels, as far as I'm concerned, should be a dimension on its own and each row should contain a unique hotel. Targets (what's that?) should most likely be a dimension on its own (but I'm not 100% sure due to lack of domain knowledge) and they should be combined in the main fact table. But it's just my feeling without a good understanding of the business constraints you're working within.
Helper IIHi daxer-almighty ,
Thank you for all the inputs thus far. To give you a bit of an idea, I guess targets are attributes of hotels. We have some hotel partners that we have an agreement that if we reach a specific number of room nights sold, we will get a commission. The target can be more than one depending on the contract. So for example, in Hotel A. We have an agreement that if we reach 500 room nights sold, then we'll get 3% commission, if we reach 750 room nights then we'll get 5% commission instead of 3% and so on depending on the contract. So with that in mind, I created a separate table for the targets and assigned an 'ID' to each hotel then relate it to the hotels initially. So in hotels i have a PPA_ID which is foreign but there will be empty rows because not all hotels have a target. Then here goes the BLANK rows on my matrix. I decided to move the PPA_ID in the fact table itself and relate it to the target table but I got the same result as there would be reservations under a hotel that doesn't have a target so the PPA_ID will be empty as well. As for the video, I watched it. I actually watched it before but I'm not at a level yet that I could fully digest the information ALberto was saying but I'm subscribed to the channel and have been watching their videos. Also, the string column. I will surely keep that in mind and implement it as a rule of thumb.