Building BiBlend table names, or: a classic tale of XFBR escapology

The Problem

BiBlend creates data tables named according to this structure:

BIB_<ApplicationName>_<WorkflowChannelName>_<Scenario>_<Time>

When building a DataAdapter that connects to such table, the first instinct would be to use Substitution Variables, so that the adapter can automatically point to the right table according to the Workflow the user is looking at. In the Table Name field, we would place a value like this:

BIB_|AppName|_|WFProfile|_|WFScenario|_|WFTime|

This is a great approach! However, you'll soon encounter a catch: WFProfile will typically includes characters (like "." dot) that are not allowed in SQL table names. 

The veteran consultant will now think "I know, I'll fix it with an XFBR" - and he'll be right! But there are a few different ways to tackle a problem like this in an XFBR function, and some work better than others.

For example, a first instinct could be to write a function to escape only that particular value:

This would work in the immediate, but what happens when a user creates a profile with an ampersand (&) ? That's not an allowed character in SQL table names either. You'd have to change the rule, possibly adding another .Replace() call - and everyone knows that developers should follow the Don't Repeat Yourself (DRY) philosophy as much as possible, to ease maintenance and reduce technical debt. 

A better approach is to whitelist characters instead, i.e. to drop everything except "known-good" values. The most effective way to do that is by using a Regular Expression, or "RegEx". This will require an extra Imports line:

This RegEx will match all characters in wfName except what follows the ^, which means it will NOT match anything from a to z (lowercase), from A to Z (uppercase), digits from 0 to 9, the underscore _, at @, and hash # - the allowed characters according to Microsoft documentation. Any matched character will be replaced by an empty value, i.e. will be dropped.

Note: RegExes can run a bit slower than Replace or other low-level string-manipulation techniques, but they are compact and fast to write. You probably shouldn't use them in Member Formulas or other Calculation Rules, but they are perfectly fine for building dashboard adapters.

This is not the best RegEx ever written ("RegEx golfing" is a thing!), but it works well enough and keeps things fairly explicit. So job done, then...? What if I told you that some people use spaces and other weird stuff in their application names...? Those characters will also have to go. Sigh. Should we write a function for every parameter then? Obviously not.

We could be tempted to just deal with the whole BiBlend tablename-building business instead:

We build a list of all our values, join them up with "_", then remove any unwanted character. Note how we broke long lines with the continuation combination (space followed by underscore), also indenting further when dealing with nested calls - this helps developers with smaller screens and improves overall readability.

Alright, job done, surely it's time to grab a refreshing beverage...? Don't hate me now, but... not really. See, BiBlend has an option ("Create Star Schema") which, among other things, will create an SQL View with extra details about dimensions. The name of that view is the same as the regular data table, except it starts with "v" before "BIB". You could hack your way around this in the XFBR or in adapter properties, but by now it feels like the hacks keep piling up, and people will have to remember what this rule does or doesn't do, and what if we have to escape table names from some other feature...? Let's save ourselves some trouble down the line, and instead write a generic utility that is simple, explicit, and reusable.

 

If args.FunctionName.XFEqualsIgnoreCase("StripUnsafeForTableName") Then
	' Generic version to deal with any string
	Return Regex.Replace( _
		args.NameValuePairs.XFGetValue("value"), _
		"[^a-zA-Z0-9_@#]", _
		"")
End If

 

Now we have no BiBlend-specific values, and very little code - always a good thing. We take whichever value parameter is passed to the function, we drop bad characters, and return the result.

So, how do we use actually use this? Let's create a Dashboard Parameter containing the XFBR call, passing a value containing the table name - built with good ol' Substitution Variables like at the start.

Then build a Data Adapter that uses the Parameter:

... and check that the table name is as expected, by testing the adapter:

In my humble opinion, this approach strikes the best balance:

• It does the bare minimum in code that we actually need;
• It maintains transparency and flexibility in actual properties;
• It provides a generic utility that can easily be reused in many different circumstances (BIB vs vBIB etc).

That's it! Pat yourself on the back, and get that well-deserved refreshing beverage, before someone asks you to build another report...