Everyone's Got a Cross(tab) to Bear

Crosstab queries are awesome. We used several of them to create our GWAC Dashboard. But BI Services don't handle them all that well. So we put together a couple tricks to make them work.

What do we want? Conditional Triggers!

Hmm. I think have my titles in the wrong order. See, people, sort methodology is important!

Anyway, on Monday we learned all about how to set up the NOW() function to return the correct current date. I ended with a question: NOW() isn't actually returning the current date and time. It's just close enough for the purposes of a current date displayed on the dashboard. What is it actually returning?

The answer: Pure gold.

When do we want it? NOW()

When building out the GWAC Dashboard, I ran into two separate problems. First, the current date was supposed to appear in the upper right hand corner of the dashboard. Second, I really needed to set up some complex logic for conditional triggers. In a twist that would make M. Night Shyamalan look twice, it turned out that solving the first problem would give me the answer to the second.

I see data sources, and they don't even know they're supposed to refresh.

This post is going to deal with the first, and considerably simpler, question. How do you get your dashboard to do something as simple as display today's date? This should be child's play. Excel even comes with a built-in function to do it, NOW(). Create a cell that contains =NOW(). Format it to the date format you like. Create a label, and point the label to that cell. Presto! Today's date appears on the dashboard. But, like the cake, it is a lie.

Why is my dashboard lying to me? Well, it's less that it's lying to you, and more that it's ignoring you. See, Xcelsius tries to make things run a tinier bit more efficiently on the back end by only updating the cells that reference cells that have changed. Since the =NOW() function doesn't reference anything else, the calculation engine doesn't bother to update the cell. So instead of returning the current date, it is returning the date the last time the data model was updated (i.e., when you exported the dashboard to a SWF file). And that is the only date it will ever display. (How often is a stopped calendar right? Just once, I think. Unless you see this guy.)

How do I fix it? Fortunately, if you were reading closely, the answer is fairly simple. Xcelsius will recalculate cells if they reference a cell that has changed. So that's all we have to do. Find a connection that refreshes when the dashboard loads (hopefully you have at least one of those to fill in prompt values and the like). Let us suppose, for the sake of illustration, that the first cell that this connection fills is A5. We know that the value of this cell is going to change when the dashboard loads. So, we just build a reference to it!

=IF(A5="I Love BO",NOW(),NOW())

The odds are low that A5 will ever actually equal "I Love BO". Which is very sad, when you think about it. But it doesn't really matter. If a miracle occurs and it does equal that, your cell will return NOW(). If the love goes unrequited, then your cell will still return NOW(). Either way, you win. Your label will now return today's date!

But wait! What is it really returning? Ah, grasshopper, you ask too soon. The answer to that question is, of course, the key to answering the second of our original problems. Come back on Wednesday, and we will explore how this peculiarity of calculation allows us to build triggers that fire under very specific circumstances.

Introductions III, The New Technology

Can you tell that my other blog is movie reviews? I promise that this trend will stop soon.

My focus for several years now has been Business Objects. But recently our client expressed a strong interest in Tableau. (Yes, I do believe it was the classic "the CIO read an article, everybody duck" routine.) For those who don't know, Tableau has made some strong strides in the BI market over the last few years. Gartner has had some great things to say about them, and a lot of people have been listening.

Just look at it there, lording it over the rest of them.

So what? Yeah, I know, it's not a big deal. But, hey, you're reading this, so you must care. Or be really bored. I am mostly mentioning this to let you know that I will be mixing some Tableau posts in with by BO posts. As I come across interesting blog posts (check out this wacky method for having both different colors and intensity of color as indicators on your chart!) or nifty ways of solving problems, I'll post about them.

But wait, what *is* Tableau? The short answer is that it's technology that is intended to short-cut a LOT of the work of the data visualization professional. Any business analyst with a good grasp of their data and enough savvy to create decent charts in Excel is supposed to be able to pick up Tableau and create awesome charts. Naturally, the truth falls a bit short of the marketing hype, but not as far short as normal. It actually is really easy to create awesome charts in Tableau. If you have an interest in data visualization or exploration, you should check out their public gallery. And you can download a free version of the software to play around with.

It's pretty darned cool. Almost Joe Cool.

Introductions II, This Time It's Professional

One of the reasons I wanted to kick this blog off was to present a number of the tips, tricks, and lessons learned from one of my projects. In the BI world, it is often tricky to present a portfolio of one's past successes, because it often involves proprietary data that the client/employer doesn't want shared. This time around, I'm fortunate enough to have a project that is public facing.

This is the GWAC Dashboard. I know, it's not much to look at at first glance. But, trust me, there's a LOT going on that isn't immediately obvious. And the visualizations were entirely chosen by the customer, so I didn't get a lot of choice there. Still, I think it's pretty clean, and the results are easy to read. You can check it out for yourself here.

Some of those features that aren't immediately obvious include:

• If you look on the far left, you can see that you can dynamically change the chart type. Yes, this was done by having three different sets of charts that all point to the same data. Nothing fancy there.
• Next to it, you can see that you can choose to hide or show the values. That trick will be one of the upcoming blog posts.
• Above that you can see that the user can select one or more Contract Families. As most experienced Xcelsius developers know, the dashboard will fight you when you try to apply multi-select. For instance, did you know that there is no way to reset the value of a check box? Yeah, that was a challenge.
• If you select only one Contract Family, the charts get kind of boring. We compensated by allowing the user to change the chart to do a breakdown by whether or not the contract was negotiated with GSA assistance. How does one chart work at two different levels of granularity? Patience, young one, and you will see.
• On the right side, you can see that you can filter by Fiscal Year. (As a note to those who may not be aware, the US Government runs on a fiscal year that begins with October 1 instead of January 1.) Pretty simple, right? Except that if you narrow it down to a single Fiscal Year, the charts all change to show data by month. WHAT?!?! Yup, I'll show you how to do that, too.
• You see the little printer icon next to the Fiscal Years? Yeah, that lets you print out a WebI report that mimics the current view, including the filters you have selected. There's not a lot of advanced work under that, but I'll do a couple of posts on how we managed it.
• If you click on one of the Agencies or Industry Partners in the lists below, it will take you to the appropriate tab and filter by your selection. All other filters remain in place.
• If you click on the tab at the top of the screen to change to one of the other views, though, it resets all the filters. How do we keep those actions separate? I'm very glad you asked.
• Each of the tabs has its own set of connections, since the data is filtered very differently. As part of our attempt to improve performance, we only wanted the connections for the currently active tab to kick off when the filters changed. But we also needed the connections to fire when the tab was changed. Getting that kind of fine control over the timing of the connections was definitely a challenge. I'm pretty proud of my solution, as it exploits a non-obvious (and usually frustrating) property of Xcelsius.
• There are some other bits behind the scenes that I'll be talking about later, as well. Crosstabs are a pain to work with. I came up with a pretty good system for making the "data loading" messages work well. Making the current date show up in the upper right was a surprisingly difficult challenge, especially as the issues frequently don't pop up in testing.
• But wait, there's more! If you look, you will see that there is a WHOLE OTHER DASHBOARD on the "Data Feed" tab. What does this one do? Allow you to select what fields you like, apply a couple filters, and export a custom extract of the data to a CSV file. It's kind of spiffy.

If you want to see some of the version history (this is our fifth major release of this bad boy), you can actually see some in action. The OASIS Dashboard was created from a clone of the fourth version, modified for that program office. The Connections II Dashboard was similarly cloned, but from the third version. Much of the functionality is the same, though there was a MAJOR overhaul of the data model between versions 3 and 4 (which will also be its own post).

We are going to do another round of enhancements in the coming year as well. Custom hover text labels (did you know you can put anything in the hover? I'll be doing a series of posts on that). A button to share a link to the dashboard in an email (yeah, it's just a mailto, but I figure someone could benefit from a tutorial). Some tweaks to improve performance. And, most shocking of all, a way to have the Data Feed export to Excel instead of CSV by using a crazy advanced Crystal Reports (fingers crossed that it will work!).

Introductions, Mostly of the Author

Oh, hello there. Congratulations, you have found the very first post of the blog. Which means, I suppose, that I need to tell you a few things.

First things first. (I want to make a sorting joke here, but good sort management is no laughing matter.) (I'm serious, no laughing.) My name is Marshall Smith. I am known on BOB: The Business Objects Board as Lugh. I am totally unknown on Twitter as @MabonReports (because I really don't follow Twitter any more). I am known other places by other names, but that's in my non-professional life. My age is somewhere between college and retirement. Okay, almost exactly halfway between. I graduated from Davidson College back before they had a computer science degree (my one programming class was part of my physics lab course). After some wacky derailing of my career, I ended up in databases. I started with Access, got my Oracle DBA certification, and promptly started building Crystal Reports against SQL Server. Because my life doesn't know from plans. Crystal led to Business Objects, and now I'm a business intelligence professional.

I am currently employed at CRGT, Inc. (by the time you read this, it may be Salient-CRGT, Inc.). My current position is doing BI development using the Business Objects suite (Web Intelligence, SAP Dashboard Designer (nee Xcelsius)) and Tableau. I also have several years of experience on the administration side of Business Objects, and am currently training on the administration side of Tableau.

Wait! I hear a cry of confusion! What the heck is Mabon Solutions, then? Well, the answer is relatively simple. It's my consulting company. Except for the part where it's not actually a company. A while back, a former employer asked me to continue doing some maintenance on a project I had completed. I felt that I needed a company name. So I did some Googling and found a name that was reasonably unique, easy to spell, and felt good to me. (I have kind of a thing for Celtic stuff.) Of course, as I only ever had one client, I never got around to little things like a business license. So I still love it as the name of my company, even if that company is wholly theoretical.

So now what? Well, I guess you look around to see if I have anything else to say. Which is pretty likely, unless you happened to come on the first day this blog was opened. Hopefully you like some of it. If you do, I make the standard requests of any blog: READ. COMMENT. SHARE. These things are very important for a blog to thrive. I'm not doing this just to hear myself ramble. If I wanted to do that, I'd just take a shower. Let me know what you like and don't like. If you find stuff you like, tell your friends. If you don't have friends, tell some strangers and pretend they're your friends. Then maybe we can all be friends together.

THANKS, GUYS! KEEP READING!