I don’t know about all of you, but I convince myself I’m a complete idiot about twice a week.
On a completely unrelated note, I often have an Xcelsius
dashboard visualization (sorry, SAP marketers) that works just fine in DEV and TEST but then totally poops out in PROD. Worse yet, I have no idea why it is pooping out. Worse still, I have no way to see what part of the model is pooping out (by the way, I’m gonna stop saying “pooping out” here, lest I start turning up in the wrong kind of Google searches). In any event, while Xcelsius gives us some great out of the box trouble shooting tools in the Designer (File > Snapshot, as a for-an-example) once the .swf is created, you are pretty much on your own.
If you are like me (tall, pretty, athletic, and with just a hint of 1990’s Bobby Brown hair if you believe my profile picture) you have no doubt seen development environments act painfully inconsistently. If you are and if you have, you should consider building yourself a Developer’s Button!
I know what you are thinking; what is a Developer’s Button? It’s a way to allow a developer to troubleshoot all sorts of things that can go wrong in a visualization that’s as easy to understand as the plot for “Hot Tub Time Machine”. What do we need to do when we need to look at what data is flowing in and out of your published and promoted visualization, how the Excel is interpreting it, and where these movements break down?
We do the same things we do when we are designing them. We drag a spreadsheet selector onto the canvas. We put funny labels and test gauges and all manner of things we would never, ever leave on the canvas before promoting it. So why don’t we just leave them on there when we promote it? Because people will see them and we’ll feel stupid, that’s why. And nobody likes to feel stupid. So instead of feeling stupid, why don’t we just leave these “troubleshooting components” on there forever, but not let anyone see them but us? Well, that’s not actually a half-bad idea.
There are three main components to this solution: the bizarre troubleshooting components you build in there but are embarrassed to let anyone see, a button to show/hide those components, and a way to know who should and shouldn’t see that button.
The Troubleshooting Components
The components are easy. I just do a tab set and put a spreadsheet selector or two on each tab that show the “trouble areas” of my spreadsheet. No 3,000 cell ranges here, folks, they’ll crush your performance. Just show enough (and make sure it is enough) of any spots that may cause you trouble. I always make sure to include something for each data call (to see at least a sample of what data is coming back and prompts are being used to call it), a small area for each “data processing” thing that I do (cut up or concatenate, middle parts of equations, etc.), my history area (I always put this in every model and update it before I republish. I know we’ve all invested hours looking at things that we’ve already fixed and thought were promoted but really weren’t. Don’t judge me.), and anything else I think might “break”.
A Show/Hide Button
The show hide button should be small, off to the side, and non-intrusive. Its only purpose is to give you a way to access this tab set. You’ll want a small area of your spreadsheet dedicated to managing it. I simply drag a Toggle Button on, set its Labels to “Dev Info” for off and “X” for on (so that when it is “on” I know that is where I close it), And leave the default Source Data On (0 for Off, 1 for on). Then I bind it to a cell (color-coded and labeled, naturally) and set the default item to “Off”. I then use that cell to hide my Troubleshooting Components by setting their Dynamic Visibility Status to the bound cell from my Show/Hide button and set the Key to 1 (or whatever “on” value you’ve selected).
Who Should/Shouldn’t See this?
Because all of my
dashboards visualizations (I was doing so well, too) connect to an SAP BusinessObjects (BOBJ) Universe via Query as a Web Service (QaaWS), knowing whether a person should or shouldn’t see the dashboard is easy as well. I simply create a Universe Object called “Is this person a developer or a lower level being?” (pretty long by common Universe standards, but I believe the object name should be pretty transparent) and put the following code in that object. (you may have to tweak that syntax based on your DBMS, or change the query based on a table; I can’t do all of the work for you).
case when UPPER(@VARIABLE(‘BOUSER’)) in (‘JAMIE’,’LEBRONJAMES’,’DEANWINCHESTER’)
then ‘DEVELOPER’ else ‘PROBABLY NOT WORTH IT’ end
I then attach that object to an existing query that runs in the model “On Load” (preferably one that always returns data no matter what). If I don’t have a query like that, I make one. Once that query result is bound to a cell, I use that cell as the Dynamic Visibility Status for the Show/Hide Button and set the Key to “DEVELOPER”.
How does SAP security work with this solution? I have no idea. You will have to work that out in the comments. I’ve never worked with SAP, BW, or any of your other acronyms. 🙁
How would you do this if you didn’t have any data connection? I suppose you could always just hide a button down in some corner, set it to be invisible, and hope no user ever found it.
Wrap Up (because I never know when to quit)
Hopefully this trick will better enable you to deal with an account number that is 4 digits long in DEV, 6 digits in TEST, and a string in PROD. This can be an especially powerful tool in a world where we have limited access to PROD before we develop an Xcelsius model, in a tool which really lends itself to reformatting, concatenating and cutting up strings because of access to various Excel formulas.