Good morning from Sweden everyone,
We just started to break out the columns regarding our campaign fields from the fact table to its own dimension.
What Im struggling with now it that I want the regular Calendar dimension to be the main “date filter” for the end users. So when the for example filter on January 2021 on the regular calendar dimension we want that filter to propagate trough dimCampaign and further on to factSales. Does this make sense or is it a better way?
We was thinking of using crossfilter, but not sure if that is the best approach over a snapshot table for example? or even a inactive relationship between dimCal and dimCampaign
As you mentioned there are multiple ways to do that depending upon your data in the table. If you want your filter to propagate from dimCalendar to dimCampaign and further to factSales then you should see if you can map dimCalendar with dimCampaign and then map factSales through CAMPAIGN_ID if you have unique data else try to create unique id and then map it.
If your aim is to propagate filter from calendar to campaign and then to factsales then you delete your relationship between dimcalendar and factsales and make active relationships between dimCalendar and dimCampaign and it should be single direction not bi-directional
Okey, hmm then that will not do it since there are many other dependencies regarding the relationship between Calendar and Sales so we cant remove it. I need to come of with some other alternative
@MK3010 is spot on here. The problem with your current structure is that it creates an ambiguous path from the date table to the fact table – it can get there directly or through the campaign table. These sorts of ambiguous paths wreak havoc on your DAX and create results that are either completely inaccurate and/or very difficult to interpret.
The way I see it, I think there are two basic alternatives – the first being the snowflake structure that @MK3010 laid out. You should still be able to filter your sales table via the date table under the structure, since every record in the fact table will be associated with a particular campaign ID. Personally though, I find these snowflake structures difficult to deal with and try to avoid them if possible, doing everything I can to stay within the bounds of a strict star schema.
Another alternative would be to de-normalize your fact table by not pulling campaign into a separate dimension. This is certainly going to blow up the size of your fact table, but the Vertipaq engine does a great job compressing/handling duplicate values. In trading off size (and possibly some performance), your model will be much simpler and more intuitive and your DAX substantially easier IMO.
As Brain mentioned the best way is to have a Star Schema Model for your report. Try to normalize your data between dim and 1 fact table to create model. It will make your report faster as well as easy to write DAX. You will only need to propagate from dim table to fact table. But sometime we can’t achieve that then we go for snowflake and propagate from multiple dim table with final destination to fact table.
These are all assumptions as you know your requirement, so we leave upon you unless, you share your sample pbix file with details description for the forum experts feedback.
