Latest Enterprise DNA Initiatives


Dimension table 0/1 meaning yes/no for multiple fact columns

Hi all,

I was wondering how you would handle a case in which your fact table includes multiple columns, such as “has email” “has adres” “has city” and every column contains either a 0 or a 1. These 0 always means: NO and the always means YES.

Would you create seperate dimension tables for every column in your fact table? Or have you other ideas?
Would love to know that!

Thanks in advance

Hi @DashboardingMeesters,

I saw in practice different approaches.
Generally ,it depends what is your intention (and granularity of fact table) - to sum indicators has email, has address, has city - so you use as measures in fact tables or to use some dimension which has those indicators as details (like Customers data) and you use as details for customer raw (for Example to filter those who has no e-mail and maybe need to contact them in different way and you could not include them in i.E,. mail campaign).

Best regards,
Maja

Thanks for your Answer @mspanic !

In this particular example I am working on, it’s showing the quality of the data. So, how qualitative is our (marketing) data if we miss a lot of email adresses, for example. So, for nowI just used pie chart to show the percentage that has or has no email.

You would say, just create two measures 1 for the “with” and 1 for the “without”, if I understand you correctly?

@DashboardingMeesters,

First, I would like to point out that the approach depends what you try to achieve.

Second, it’s ok to have degenerate dimension (dimension key / in your case indicator without dimension table). If you use it just this “has email”, “has address” , “has city” indicator there is no need for snowflaking (just to have separate dimension table with key and value you would like to show in fact table).

More about degenerate dimension you can read at:

Third, if you need you can have both. In Challenge 19 I use this aproach:

image

So I have both if it is Within SLA / Outside (Dimension Text) and Ind_WithinSLA (0,1). One I use for details info and one for counting.

So if you need - you can use it both - one as degenerate dimension and one as measure.There is no need to duplicate in the model “with” and “without” mesures - simple pick what you want to show as measure in your model. Regarding what you write - maybe “Ind_no_Email” ( or something like that ). Later you can calculate with DAX number of those with no emails, maybe add % no Emails ( percentage of total)

What I did for this challenge you can see in pbix file available at:

Hope it helps.

Best regards,
Maja

Hi @DashboardingMeesters, did the response provided by @mspanic help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query. Thanks!

@mspanic
Thanks for this elaborate answer. I think I now enough. Have marked your answers as solution!