Hello all,
I am currently facing an interesting set of problems that I have found laborious workarounds for. Here's the basic problem statement:
We are measuring the data quality of our warehouse. The basic measurement is the number of NULL values for each [Dimension] in each [Table] we wish to monitor. We'd then like to chart these as simple charts, two values per [Dimension], 'NULL' and 'Not NULL', and a count for each of those, based on a row count for the [Table].
One way I accomplished this was loading each dimension as an if statement, if(isnull([Dimension]),'NULL','Not NULL') AS N_[Dimension]
This works, but I have to manually (via simple excel formula) write all this out for each attribute. Not ideal. The first ask, how do I configure a Loop that does:
For each FieldName in Table
if(isnull(FieldName),'NULL','Not NULL') AS 'N_' & FieldName
Next FieldName
Or something similar to that. That would give me a duplicate set of Dimensions with only Null and Not Null as values, instead of the thousands they currently contain.
The next part of this is charting and coloring this - I will create one chart per Dimension, and we have hundreds over many tables, so this takes a while. The goal is to copy them and change AS LITTLE AS POSSIBLE in each chart to make it work correctly. Currently, I change the Dimension (easy, drag and drop replace), but the color has to be modified. I use a colormix1([Dimension],color1,color2) since it allowed me to double-click the [Dimension] and copy past the name from the title block of the chart. Is there a better way to do this part, too? What about something that automatically pulls the [Dimension] name from another chart value, like title, [Dimension], data, etc?
If there is a better way to get these metrics, including restructuring the data, I'm all ears. Thanks in advance for your help.
