LinkedIn
Twitter
Knowledge Drop
Last tested: Dec 20, 2019
In LookML Block development, it's common to develop a model without real data populating the underlying connection for various reasons. The database may be empty, sparse, or populated with fake data which leaves a lot to be desired; this allows for model development, but not "nice-looking" dashboards with "realistic" trends. Spoofing takes advantage of table calculations to create the fake data!
Here is an example of a dashboard tile built off of a LookML model with sparse, fake data. The date range is skewed, there are many more series than this dimension would create in production, and the value range is 0 or 1.
This is the same dashboard tile, after being spoofed. The date range is more relevant, we see a realistic distribution of "CLOSED" and "CANCELLED" orders, and a realistic trending of "OPEN" orders over recent weeks. This tile could pass as being modeled off of real data and better demonstrates what the tile, and dashboard, could look like to users.
Measure Spoofing
The following is a general pattern.
spoof = base + variance
This spoofing function will create values in the range of [1000, 1100].
spoof = 1000 + rand() * 100
measure is null?
This spoofing function will create values in the range of [500, 1000] with a trend. We use the IF() statement to convert null or non-null measure values to 1 from which to build our spoof off of. Without this, adding a base or variance to a null value will result in a null value spoof.
spoof = if( ${order.count} = null, 1, 1) + rand() * 500 + 500
Some measure spoofs to try
spoof = base + sin(row()) * rand() * 200
spoof = base + n * row()
spoof = base + n * sin(row())
OK, I understand how to spoof measures. That's easy. What about dimensions?
Dimension Spoofing
Dimensions are a little tricker. Here is a general pattern that I will break down:
if( ${order.created_day_of_week}=null,
"",
index(list("Guac", "Cheese", "Lettuce", "Mayo", "Chorizo", "Pico", "Cilantro"), row()) )
Like spoofing a measure requires a seed measure value, spoofing a dimension will require a seed dimension value. In the case that your query returns no dimensions, you can use a dimension you do have, like any of the date fields. In this example, I'm using day_of_week. This allows me to index into a list of spoofed dimension values that I can define, with the row number acting as the index. The defined list is indexed into because the false path of the if statement is evaluated always, as the seed dimension is never null.
The above example is good for spoofing pie or bar charts with a finite number of dimension values usually under ten.
Infinite qualitative Spoofs
if( ${order.created_month}=null,
"",
index(list("Joe", "Elizabeth", "Pete", "Bernard", "Robert", "Andrew", "Amy", "Mike", "Cory", "Tom"), floor(rand()*10)))
Like the dimension pattern for finite qualitative values, this pattern is based on the premise that the defined list is indexed into because the false path of the if statement is evaluated always, as the seed dimension is never null. However, to create infinite dimension spoofs, we implement a different indexing function. In this case, we round down a random value between 0 and 10.
After applying these techniques to all tiles on a dashboard:
This content is subject to limited support.