tag:blogger.com,1999:blog-21163362005874105102024-03-14T10:06:00.630-04:00Mabon SolutionsThis is a small view into my experiences with business intelligence and data visualization, primarily Business Objects, Xclesius, and Tableau. Fair warning, I also try to be funny.Marshall Smithhttp://www.blogger.com/profile/15647111558095583028noreply@blogger.comBlogger6125tag:blogger.com,1999:blog-2116336200587410510.post-35113015543962773072015-10-16T10:46:00.000-04:002015-10-16T10:46:00.191-04:00Everyone's Got a Cross(tab) to BearCrosstab queries are awesome. We used several of them to create our <a href="http://www.gsa.gov/portal/category/103435">GWAC Dashboard</a>. But BI Services don't handle them all that well. So we put together a couple tricks to make them work.<br />
<br />
<a name='more'></a><br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrGk2GMoje6l0YQ76DnCIVIJEWE1CcEgBKl43Fds3lX_lbJbNiufk1t9BV4kFgKA6OlGbtJXdzb4VXPv4VGdCOoJNGiZbcK65uLNpdcB9dMH0Owi8XQcJQZiIDmurGfPpoq_qsKyTxQhy9/s1600/world+domination.jpg" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="200" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjrGk2GMoje6l0YQ76DnCIVIJEWE1CcEgBKl43Fds3lX_lbJbNiufk1t9BV4kFgKA6OlGbtJXdzb4VXPv4VGdCOoJNGiZbcK65uLNpdcB9dMH0Owi8XQcJQZiIDmurGfPpoq_qsKyTxQhy9/s200/world+domination.jpg" width="186" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">I really wanted a funny picture to go with the <br />
title here, but this is what you get instead.</td></tr>
</tbody></table>
<span style="font-size: large;"><b>Right out of the gate,</b></span> I have two confessions to make. First, this solution was mostly implemented by other members of our team, and I just inherited it (and found a few ways to tweak it). Second, it's not even original to our team. The core of this solution comes from <a href="http://blog.davidg.com.au/2012/06/bi-services-with-crosstabs-variable.html">this fantastic blog post</a>. So go read that first. He does a great job of explaining what's going on, so I don't have to.<br />
<br />
<span style="font-size: large;"><b>So what are we going to talk about, then?</b></span> Well, implementing those instructions aren't easy. But I don't think I can make the explanation any more clear. What I can do, though, is offer a couple other tricks I discovered.<br />
<br />
<span style="font-size: large;"><b>First, what happens if you have more than one row header?</b></span> This isn't terribly uncommon, really. Sure, your basic crosstab has something like categories down the side and years across the top. But what if you have categories AND colors? What happens then? Huh? Huh?<br />
<br />
Okay, now I'm getting belligerent, and that's no good. So I'll tell you. All your figures end up one column off, just like the original problem with the crosstab not lining up. This is because the "number of columns" figure does not take the row headers into account. Fortunately, the solution is simple. Check out the second formula:<br />
<br />
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: left; margin-right: 1em; text-align: left;">
<tbody>
<tr>
<td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYzv96vKtUQ0YZAEz4LLTHxzHOLzX9NmoOL65FzsM-071tYDxMtUjDVHhxeH0CS6mL-4vL_sGFFAQsYc2xwGcUJ9NvMGDwMelYVsdxv-nHwSu4snhX7F9N8Wtv67YcPAuNtiNByi3kuuOL/s1600/Second_Formula.png" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="94" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhYzv96vKtUQ0YZAEz4LLTHxzHOLzX9NmoOL65FzsM-071tYDxMtUjDVHhxeH0CS6mL-4vL_sGFFAQsYc2xwGcUJ9NvMGDwMelYVsdxv-nHwSu4snhX7F9N8Wtv67YcPAuNtiNByi3kuuOL/s320/Second_Formula.png" width="320" /></a></td>
<td><div style="text-align: right;">
All we need to do to make this happen is to shift our expectations one more column to the right. So when we check to see if we have enough columns, we just add one: <b>=IF(OR(G3="",<span style="color: red;">G3>$B$2 + 1</span>),"",G3+1)</b></div>
</td></tr>
<tr><td class="tr-caption" style="text-align: center;">Making the columns</td><td></td></tr>
</tbody></table>
<br />
<br />
<span style="font-size: large;"><b>Is that it?</b></span> Well, yeah, pretty much. If you have two column headers, you adjust the third formula in the same way. Check to make sure that you are still generating sequential numbers when it wraps to the new row. And then, of course, run some test data through it to make sure you are getting the correct results.<br />
<br />
<span style="font-size: large;"><b>How do you know how many numbers you need?</b></span> The glib answer is "know your data". If you look at the GWAC Dashboard, I only have a handful of contract families, a handful of fiscal years, and other dimensions are similarly small. But we did run into this problem when we needed to have a crosstab that used Agencies. We might have four. We might have twenty. So you need to make sure your grids for the numbers and the reconstituted crosstab are big enough. At this point, we discovered that it made far more sense for us to put the grid and the numbers side by side rather than one over the other. We were far more likely to get a large number of rows than a large number of columns. So, just a little tip there.<br />
<br />
<span style="font-size: large;"><b>But don't all those formulas hurt performance?</b></span> In a word, yes. But, there are a couple saving graces. It doesn't hurt performance by a lot. Unless you have a LOT of crosstabs (or some really big ones), you probably won't even notice the lag. Also, it doesn't keep doing the calculations all the time (see our recent discussions on NOW()). These calculations will only be done when the connection refreshes. And, ultimately, you just have to suck it up. Everything you do has a performance cost, and there isn't really a better way to handle crosstabs.<br />
<br />
<br />Marshall Smithhttp://www.blogger.com/profile/15647111558095583028noreply@blogger.com0tag:blogger.com,1999:blog-2116336200587410510.post-79227271605198086682015-10-14T10:30:00.000-04:002015-10-14T10:30:02.367-04:00What do we want? Conditional Triggers!Hmm. I think have my titles in the wrong order. See, people, sort methodology <i>is</i> important!<br />
<br />
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 <i>actually</i> returning?<br />
<br />
The answer: Pure gold.<br />
<br />
<a name='more'></a><span style="font-size: large;"><b>As a quick recap</b></span>, we established that the NOW() function won't recalculate on its own. It will only recalculate if the cell references another cell where the value has changed. So we did a little IF/THEN magic against a cell that we knew would be populated by a connection when the dashboard opened. Presto!<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="http://3.bp.blogspot.com/--5NeYNvh_eE/Ttrx4yiZVaI/AAAAAAAAInI/6a4H0tk9zvU/s1600/presto_hat.jpg" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="153" src="http://3.bp.blogspot.com/--5NeYNvh_eE/Ttrx4yiZVaI/AAAAAAAAInI/6a4H0tk9zvU/s1600/presto_hat.jpg" width="200" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Not this guy.</td></tr>
</tbody></table>
<br />
<span style="font-size: large;"><b>So, what is the answer to the question?</b></span> The function is returning the date/time stamp of the last time the target cell was updated. This is huge news, because it means that we can tell if a value has changed since the last time we read it. This is especially important when building conditional triggers for connection refresh.<br />
<div>
<br /></div>
<div>
<span style="font-size: large;"><b>What is a conditional trigger?</b></span> Well, it's pretty much what it sounds like; <i>conditional triggers are functions that only refresh a connection if certain criteria are met</i>. If you look at the <a href="http://www.gsa.gov/portal/category/103435">GWAC Dashboard</a> (our ongoing example of SAP Dashboard work), you will see that it has three tabs: All GWACs, By Federal Agency, and By Industry Partner. Each tab has its own set of connections. We want the connections on a tab to fire with fairly specific criteria:</div>
<div>
<ul>
<li>Connections only fire for the currently active tab.</li>
<li>Connections fire for the new tab when we navigate to it.</li>
<li>Connections <b>do not</b> fire for the old tab when we navigate away from it.</li>
</ul>
<div>
<br /></div>
</div>
<div>
<span style="font-size: large;"><b>That doesn't sound so hard.</b></span> But it is. Particularly that last condition, surprisingly enough. See, here's how some typical conditional trigger work:<br />
<br />
<ul>
<li>Suppose you want a connection to fire when the Contract Family selection changes. You have a cell that has the Contract Family information in it. You point the trigger of the connection to that cell and select the "when value changes" option. Easy peasy.</li>
<li>Suppose you want a connection to fire when any of the prompts change. You create a cell that concatenates the current values of all of the prompts, and point your trigger at that cell.</li>
<li>Suppose you want a connection to fire when any of the prompts change, but <b>only</b> if you are on the right tab. Well, you just take the cell you made in our second scenario here and wrap it in an IIF() function. <b>IF</b> the cell that contains the current tab value is the value you want <b>THEN</b> return the concatenated value of the prompts <b>ELSE</b> return 0. Voila, a trigger that will only change when you are on the correct tab!</li>
</ul>
<div>
<b><span style="font-size: large;">That's what we wanted, right? Are we done?</span></b> Well, no. That third scenario supports our first two conditions quite well. But it fails mightily on the third condition. See, when we navigate away from the tab, the value will change back to 0. Which, obviously, means the value changed. Which triggers the connection. Which is what we <i>don't</i> want to happen. (I spent a <b>long</b> time chasing down various ways to manipulate the logic to get around this. You might have some ideas along these lines. I'm going to skip all that and get to the solution I came up with.)</div>
<div>
<br /></div>
<div>
<span style="font-size: large;"><b>And this is where NOW() comes into play.</b></span> What we want to do is only check the prompt values when the correct tab is active, but tell whether or not they have changed since the last time we looked at them. Since Excel doesn't keep track of prior states, we need timestamps, which NOW() conveniently provides. </div>
<div>
<br /></div>
<table cellpadding="0" cellspacing="0" class="tr-caption-container" style="float: left; margin-right: 1em; text-align: left;">
<tbody>
<tr>
<td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifFDx1rttplgXCell_5x-yXiUHN7JK6NOMtRAolenmoJXmjzSlphkgKBxkNf2IWDXhaev5g0DgdUkL5NcraZAoHSzFOjtkYL28uJTIB7mKKHgtsC8IdCnRKQYRL_Itw5CEjGmfA4b1-Kgz/s1600/Prompts.png" imageanchor="1" style="clear: left; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="140" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEifFDx1rttplgXCell_5x-yXiUHN7JK6NOMtRAolenmoJXmjzSlphkgKBxkNf2IWDXhaev5g0DgdUkL5NcraZAoHSzFOjtkYL28uJTIB7mKKHgtsC8IdCnRKQYRL_Itw5CEjGmfA4b1-Kgz/s320/Prompts.png" width="320" /></a></td>
<td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1imPWo_gGSDRqliO7E28an-TEso8MSJIdwFLPCn0FXpvcD06USjSMV3VE54AR0RMBF7sV-atsbdt9or-KXShVy2TXjMN7xpl_pmzgLp2bu9LwuMvHeMaX0HXoTNurovJ_IKEE9MYRxnJH/s1600/Triggers.png" imageanchor="1" style="clear: right; margin-bottom: 1em; margin-left: auto; margin-right: auto;"><img border="0" height="129" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg1imPWo_gGSDRqliO7E28an-TEso8MSJIdwFLPCn0FXpvcD06USjSMV3VE54AR0RMBF7sV-atsbdt9or-KXShVy2TXjMN7xpl_pmzgLp2bu9LwuMvHeMaX0HXoTNurovJ_IKEE9MYRxnJH/s320/Triggers.png" width="320" /></a></td>
</tr>
<tr>
<td class="tr-caption" style="text-align: center;">Prompts</td>
<td class="tr-caption" style="text-align: center;">Triggers</td>
</tr>
</tbody></table>
<br />
<br />
<br /></div>
<div>
Here are some very simplified version of the Prompts and Triggers worksheets from my dashboard file. I'm going to use these to walk through the formulas you need to create.<br />
<br />
<ul>
<li>On the Prompts sheet, cell C2 needs to have a formula that will create a timestamp when the Contract Family changes. We will use: <b>=IIF(B2 = '',NOW(),NOW())</b> Copy this down to cells C3 through C6. (You may note that Agency and Industry Partner don't have values selected, and have much earlier timestamps. The starting timestamp will always be when the dashboard was exported to a SWF file.)</li>
<li>On the Triggers sheet, we need to know the last time the connection was refreshed. So C2 will need to have this formula: <b>=IIF(D2="Idle",NOW(),NOW())</b> Copy this down to cells C3 through C6. Make sure that each of your connections writes the loading status to the corresponding cell in column D.</li>
<li>On the Triggers sheet, cell B2 needs a formula. Chart 1 is on the All GWACs tab, which is tab 1. It does not matter if the Agency or Industry Partner change. We just need to know if any of the first three prompts have changed since the last time we refreshed the connection. So the formula looks like: <b>=IIF(AND(Tabs!$A$1 = 1,MAX(Prompts!C2:Prompts!C4) > Triggers!C2),1,0)</b> </li>
<li>We need one more minor but critical configuration. This formula will return 1 if the conditions are met, and 0 at all other times. But that means it drops back to 0 as soon as the connection refreshes. If we set the trigger to "when value changes" we would get an endless loop. So, instead, we set the trigger to "when value becomes" and set the matching value to 1.</li>
</ul>
<div>
<span style="font-size: large;"><b>Good God, man, what have you done?</b></span> I've created a trigger that does several specific things. If the active tab matches the one the connection is looking for <i>and</i> one of the relevant prompts changes, the connection will fire. If the active tab <i>does not</i> match, then the connection will not fire, no matter how you change the prompts. If you change from one tab to another, the connections for the new tab will fire <i>if and only if</i> the prompt values have changed since the last time you had this tab active. (Because of other configurations, this actually always happens on this dashboard, but doesn't have to on yours.) But the connections for the old tab <i>will not fire</i> because the value of the formula simply stays 0.</div>
<div>
<br /></div>
<div>
One of the primary concerns when optimizing any dashboard is reducing the number and size of the connection refreshes. By enabling very specific triggers, it is possible to create connections that are pre-filtered or pre-shaped in various ways, and only call them when they are actually needed. You end up with more total connections, but fewer refreshing at any given time. </div>
</div>
Marshall Smithhttp://www.blogger.com/profile/15647111558095583028noreply@blogger.com0tag:blogger.com,1999:blog-2116336200587410510.post-29287544085481518292015-10-12T10:30:00.000-04:002015-10-12T10:30:00.054-04:00When do we want it? NOW()When building out the <a href="http://www.gsa.gov/portal/category/103435">GWAC Dashboard</a>, 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.<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="http://i1.kym-cdn.com/entries/icons/original/000/012/818/Movie_i_see_dead_people-769472.jpg" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" src="http://i1.kym-cdn.com/entries/icons/original/000/012/818/Movie_i_see_dead_people-769472.jpg" height="164" width="320" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">I see data sources, and they don't even know they're supposed to refresh.</td></tr>
</tbody></table>
<br />
<span style="font-size: large;"><b>This post is going to deal with</b></span> 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.<br />
<br />
<span style="font-size: large;"><b>Why is my dashboard lying to me?</b></span> 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.)<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="http://38.media.tumblr.com/923f79d343b7f01d5fd994745ede8297/tumblr_mfvflteukj1r3kokfo1_400.gif" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="http://38.media.tumblr.com/923f79d343b7f01d5fd994745ede8297/tumblr_mfvflteukj1r3kokfo1_400.gif" /></a></div>
<br />
<span style="font-size: large;"><b>How do I fix it? </b></span>Fortunately, if you were reading closely, the answer is fairly simple. Xcelsius <i>will</i> 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 <i>know</i> that the value of this cell is going to change when the dashboard loads. So, we just build a reference to it!<br />
<br />
<div style="text-align: center;">
<span style="font-size: large;"><b>=IF(A5="I Love BO",NOW(),NOW())</b></span></div>
<div style="text-align: left;">
<span style="font-size: large;"><b><br /></b></span></div>
<div style="text-align: left;">
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!</div>
<div style="text-align: left;">
<br /></div>
<div style="text-align: left;">
<span style="font-size: large;"><b>But wait! What is it <i>really</i> returning?</b></span> 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.</div>
Marshall Smithhttp://www.blogger.com/profile/15647111558095583028noreply@blogger.com0tag:blogger.com,1999:blog-2116336200587410510.post-40578900132579493982015-10-09T10:30:00.000-04:002015-10-09T10:30:00.573-04:00Introductions III, The New TechnologyCan you tell that my other blog is movie reviews? I promise that this trend will stop soon.<br />
<br />
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, <a href="http://www.tableau.com/">Tableau</a> has made some <b>strong</b> 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.<br />
<br />
<table align="center" cellpadding="0" cellspacing="0" class="tr-caption-container" style="margin-left: auto; margin-right: auto; text-align: center;"><tbody>
<tr><td style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_kKu88G-cGRxiKEw3JkDnuXiC0Ej30xv8AFNztxr893wtVRS0ZpNDftESh0PT5dGh1x21t9aPGplQ03gdFmPPGDclR3wJHgPL_jYatTQvEehdG00ZLq0QsDl_2N_LV3pZC9FqmbD8IKka/s1600/Gartner-Annotated.png" imageanchor="1" style="margin-left: auto; margin-right: auto;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg_kKu88G-cGRxiKEw3JkDnuXiC0Ej30xv8AFNztxr893wtVRS0ZpNDftESh0PT5dGh1x21t9aPGplQ03gdFmPPGDclR3wJHgPL_jYatTQvEehdG00ZLq0QsDl_2N_LV3pZC9FqmbD8IKka/s320/Gartner-Annotated.png" width="296" /></a></td></tr>
<tr><td class="tr-caption" style="text-align: center;">Just look at it there, lording it over the rest of them.</td></tr>
</tbody></table>
<br />
<b style="font-size: x-large;">So what? </b>Yeah, I know, it's not a big deal. But, hey, you're reading this, so you must care. Or be <i>really</i> 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 (<a href="http://www.theinformationlab.co.uk/2015/09/06/tableau-good-to-great-encoding-on-colour-x2/">check out this wacky method</a> 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.<br />
<br />
<span style="font-size: large;"><b>But wait, what <i>*is*</i> Tableau?</b></span> 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 <i>awesome</i> charts. Naturally, the truth falls a bit short of the marketing hype, but not as far short as normal. It actually is <b>really</b> easy to create awesome charts in Tableau. If you have an interest in data visualization or exploration, you should <a href="https://public.tableau.com/s/gallery">check out their public gallery</a>. And you can download a free version of the software to play around with.<br />
<br />
It's pretty darned cool. Almost Joe Cool.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKsbrawmqpDyoczysJn_VfdfdqjZfoCpWXkh6bg3ePF8PPSAldKxJiPWlbksRenN5Lnkyt9VWZ1RKAoglwOCKo6OaWKxKI_TESgJK5bphz5bc6RqEYx8A4dIihE7V2VkcEtvlhTsN59Z9p/s1600/all-my-friends-are-cool.jpg" imageanchor="1" style="clear: right; float: right; margin-bottom: 1em; margin-left: 1em;"><img border="0" height="240" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKsbrawmqpDyoczysJn_VfdfdqjZfoCpWXkh6bg3ePF8PPSAldKxJiPWlbksRenN5Lnkyt9VWZ1RKAoglwOCKo6OaWKxKI_TESgJK5bphz5bc6RqEYx8A4dIihE7V2VkcEtvlhTsN59Z9p/s320/all-my-friends-are-cool.jpg" width="320" /></a></div>
<br />
<div>
<br /></div>
Marshall Smithhttp://www.blogger.com/profile/15647111558095583028noreply@blogger.com0tag:blogger.com,1999:blog-2116336200587410510.post-73897730916953233372015-10-07T10:49:00.000-04:002015-10-07T10:49:00.135-04:00Introductions II, This Time It's ProfessionalOne 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.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQG6IbIaZV92lDA7jcEb5XIU2BHfhvRQRieVUD9Qc7VXRUdlT1Xj3JlofDuq0qwNX1pFRGkWpevfnvTvL4Avn9tJlFo-u73TZDUSdA43PlCU-3UM6qJ5qw-xcxPCSlwsEp0kKRt2uYmWJK/s1600/GwacDash.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="300" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgQG6IbIaZV92lDA7jcEb5XIU2BHfhvRQRieVUD9Qc7VXRUdlT1Xj3JlofDuq0qwNX1pFRGkWpevfnvTvL4Avn9tJlFo-u73TZDUSdA43PlCU-3UM6qJ5qw-xcxPCSlwsEp0kKRt2uYmWJK/s640/GwacDash.png" width="640" /></a></div>
<br />
<span style="font-size: large;"><b>This is the GWAC Dashboard.</b></span> 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. <a href="http://www.gsa.gov/portal/category/103435#">You can check it out for yourself here.</a><br />
<br />
Some of those features that aren't immediately obvious include:<br />
<br />
<ul>
<li>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.</li>
<li>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.</li>
<li>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.</li>
<li>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.</li>
<li>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.</li>
<li>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.</li>
<li>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.</li>
<li>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.</li>
<li>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.</li>
<li>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.</li>
<li>But wait, there's more! If you look, you will see that there is a <i>WHOLE OTHER DASHBOARD</i> 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.</li>
</ul>
<div>
<br /></div>
<div>
<span style="font-size: large;"><b>If you want to see some of the version history</b></span> (this is our fifth major release of this bad boy), you can actually see some in action. <a href="http://www.gsa.gov/portal/content/161403">The OASIS Dashboard</a> was created from a clone of the fourth version, modified for that program office. <a href="http://www.gsa.gov/portal/content/191611">The Connections II Dashboard</a> 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).</div>
<div>
<br /></div>
<div>
<span style="font-size: large;"><b>We are going to do another round of enhancements</b></span> in the coming year as well. Custom hover text labels (did you know you can put <b>anything</b> 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!).</div>
Marshall Smithhttp://www.blogger.com/profile/15647111558095583028noreply@blogger.com0tag:blogger.com,1999:blog-2116336200587410510.post-11031028635016959622015-10-05T10:30:00.000-04:002015-10-05T10:30:00.466-04:00Introductions, Mostly of the AuthorOh, 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.<br />
<br />
<table>
<tbody>
<tr>
<td><div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjaj9KhJT3auNBuKGmWNyRtlsjGGnDEk0rh7JrTgiSEr_ukv5NDq4MkdprECJcprr3O2RZ2eL-VA6KzC5drUg9XpmVetI8Yii0VJFBBtRZriujPZxKa5PQeUPfS81DfD-o28AFWVTSgDW14/s1600/me_bnw.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjaj9KhJT3auNBuKGmWNyRtlsjGGnDEk0rh7JrTgiSEr_ukv5NDq4MkdprECJcprr3O2RZ2eL-VA6KzC5drUg9XpmVetI8Yii0VJFBBtRZriujPZxKa5PQeUPfS81DfD-o28AFWVTSgDW14/s320/me_bnw.jpg" width="220" /></a></div>
</td>
<td><b><span style="font-size: large;">First things first. </span></b>(I want to make a sorting joke here, but good sort management is no laughing matter.) (I'm serious, no laughing.) My name is <a href="https://www.linkedin.com/pub/marshall-smith/3/160/8aa">Marshall Smith</a>. I am known on <a href="http://www.forumtopics.com/busobj/index.php?sid=e836dcd59c0c3f4f90c9285889719632">BOB: The Business Objects Board</a> as Lugh. I am totally unknown on Twitter as @<a href="https://twitter.com/MabonReports">MabonReports</a> (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.
<br />
<br />
My age is somewhere between college and retirement. Okay, almost exactly halfway between. I graduated from <a href="http://www.davidson.edu/">Davidson College</a> 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.</td>
</tr>
</tbody></table>
<br />
<table>
<tbody>
<tr>
<td><b><span style="font-size: large;">I am currently employed</span></b> at <a href="http://www.crgt.com/">CRGT, Inc.</a> (by the time you read this, <a href="http://www.crgt.com/press-release/salient-federal-solutions-crgt-announce-merger/">it may be Salient-CRGT, Inc.</a>). 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.</td>
<td><div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgM5YBPsklAGVTQi9_Q0lLYTHYz2k08AhyphenhyphenwcxBHz-g8OAhFug2vvMTfGVayj1O9Dbpg6zNhZAvdOM53KIpEJRzTq6Vf2AfWdVJQ8tfttzS0WU3qa8uMfHlqloMtInbvcrtfXhQyduAF3SRQ/s1600/CRGT_RGB_2_inch_logo.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgM5YBPsklAGVTQi9_Q0lLYTHYz2k08AhyphenhyphenwcxBHz-g8OAhFug2vvMTfGVayj1O9Dbpg6zNhZAvdOM53KIpEJRzTq6Vf2AfWdVJQ8tfttzS0WU3qa8uMfHlqloMtInbvcrtfXhQyduAF3SRQ/s1600/CRGT_RGB_2_inch_logo.png" /></a></div>
</td>
</tr>
</tbody></table>
<br />
<b><span style="font-size: large;">Wait! I hear a cry of confusion!</span></b> 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.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSaCZRo_b8RmdQxuSlBlQgpdHUgPdSk15tZ17VjfTYapLyqfyYEHCGPsEuwQ8FbIc12eUlC3ml4iBeWUU8ThUKSFdeUwA6QGJec9wnYQlasJFGQbLxRphJyZmFQODu04ks01x_fat2KSZI/s1600/Shark.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiSaCZRo_b8RmdQxuSlBlQgpdHUgPdSk15tZ17VjfTYapLyqfyYEHCGPsEuwQ8FbIc12eUlC3ml4iBeWUU8ThUKSFdeUwA6QGJec9wnYQlasJFGQbLxRphJyZmFQODu04ks01x_fat2KSZI/s320/Shark.jpg" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: left;">
<span style="font-size: large;"><b>So now what?</b></span> 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: <b style="color: red; font-size: x-large;">READ. COMMENT. SHARE.</b> 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.</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<span style="font-size: x-large;"><b>THANKS, GUYS! KEEP READING!</b></span></div>
<br />Marshall Smithhttp://www.blogger.com/profile/15647111558095583028noreply@blogger.com0