Don't go chasing waterfalls (Or how to use "Insert Filtered Rows" effectively)

[Former Member]

Hi.  You don't know me, but I know you.

That came off as a little creepy.  Sorry.  Before my recent activism on SCN, I contributed to the community via BOB (The Business Objects Board), via the local St. Louis BusinessObjects User Group chapter meetings, and as a member of the Global BusinessObjects Network Steering Team (which no longer exists and was merged into ASUG; take that as you will).  Having done a perfectly not inadequate job at all of that, I was invited to be an SAP Mentor (details Additional SAP Mentors to cover SAP BusinessObjects)and have come on like gangbusters in the SCN Community, earning over a point a day (on average) since that post went official.  Yeah, beat that.

Anyway, I've been reading SCN for a little while, but this is my first blog here (and second ever).  Of course, I'm not writing here today to talk about how benevolent I am (future post material), but rather about one solution that would solve a lot of issues people have in the forums.  I call this solution "waterfalling filters" and I think you should, too, because a) it isn't nearly as narcissistic as me calling it the "Oswald Maneuver" (and yes, I'm looking at you Henry Hudson) and b) it is much simpler than calling it "using the 'insert filtered rows' functionality found in most selector components in Xcelsius 2008 to start with a high level filter criteria and work down to a single row (or at least smaller result set) without querying your database like crazy" which CafePress wouldn't even let me put on a coffee mug in a readable font (full disclosure: I didn't actually try to do that.  Sorry for very nearly disparaging your good name CafePress).

This thing we are trying to do here.

The main idea is to be able to take a big list of data and pare it down to the level of detail you need without hammering away at your QaaWS and database servers every time a user selects one region over another.  Xcelsius 2008 has functionality built into most of its selector components that very quickly can help you cut down the 200 departments in your company into manageable chunks by region, vice president, or anything else.  I'm attaching some screenshots from a sample model I made called "SCN Sample" (admittedly not creative, but still better than "Jamie Pays Homage to Himself in Model Form").

Imagine if you will a retail company who sells multiple product lines in stores around the world.  We want to track and look at the top 10 stores for any given region by product line, but we also want to be able to look at total sales worldwide or any combination thereof.  There are two parts to this trick.  The first is the data, and the second is how we move it around.

Part 1: The data.

So, with Xcelsius we always want to call for/and or process as little data as we possibly can at any given time, right?  Right.  Sometimes anyway.  Sometimes, however, if we can quickly get a lot of data processed and fed back to us, and we can efficiently move it around, it is better to call it all at once and then play with it inside our dashboard so we don't have to keep making more calls.  In order for this to work, you want to bring back data in a format that is exactly how you are going to consume it in the dashboard.  So, if my dashboard is at the company-wide level, I want one row returned (if my query can crunch 4 billion rows down into one in a split second, that is, quite frankly, awesome; if it can't, I need a materialized view or an aggregate table or something of that ilk so that I only need to "crunch" my one row of input into one row of output in a split second).  If it is at the regional level I want one row per region.  If it is at the transaction level, I need to go re-read the whole concept of a dashboard, because I don't get it.

If I need data at the company-wide level, I also need to not be afraid of having data in my result show up multiple times at varying aggregation levels.  So in my final result set, I need to have 10 rows for Glove Sales for North America.  I also need to have 10 rows for Glove Sales for the whole world, and every city in that top 10 for the world should be in a top 10 list for whatever region it is in.  Further, I want the top rows for each Region for all Revenue Types (so, basically, net sales) and I want that for the top 10 cities globally as well.  Some sample data would look like this (of course there is a lot more data in there -- like ranks 2-9 for everything, and a lot more combinations -- but you aren't reading this just to see made up data)...

Region	Revenue Type	City	Amount	Rank
GLOBAL	All Revenue Types	Paris	55000	1
GLOBAL	All Revenue Types	Milan	11957	10
GLOBAL	Belts	Prague	20000	1
GLOBAL	Belts	Walldorf	4874	10
Europe	All Revenue Types	Paris	5000	1
Europe	All Revenue Types	Vienna	1492	10
Europe	Hats	Milan	900	1
Europe	Hats	Barcelona	286	10

Part 2: The how we move the data around.

In order to move data around inside a spreadsheet, you must remember to ALWAYS SAVE YOUR WORK, EVEN IF WRITING A BLOG ON A WEBSITE, OFTEN.  GAARGGH!  As I was saying, in order to move data effectively inside a spreadsheet, we first need to set the spreadsheet up.  For each selection you'll want a Source Area and a Landing Spot, and, in this case, you'll want them to overlap.  So, for our two selecions (Region and Revenue Type) we'll want to start with one original source data that has everything (populated by copy/paste, SAP BusinessObjects products such as Query as a Web Service -- our old friend QaaWS -- or Live Office, XML, a temp with nothing better to do than type in numbers, etc) to be the Source Area for our region selector.  That Region Selector will have a Landing Spot that will then be the Source Area for the Revenue Type Selector, which will also need a landing spot.  I like to put each of these areas in the same tab (across, not down, from each other), give them different background colors, and write a ton of notes above them so I can remember later what I did (I just meet my own personal 3 free best practices quota for this blog; woo-hoo for me).

Then we have to use some actual components on an actual canvas.  Because we only have a handful of options for each selection, I'll use a few Selector components that will allow me to show all of the options at once (bonus best practice; woo-hoo for you).  We are moving data first based on the region they select with the power of inserting filtered rows.  In our Selection Component, we use the data in the Region column of the original Source Area for our labels.  We then we insert a Data Insertion series with an Insertion Type of Filtered Rows and select all of the data in the original Source Area as our Source Data and the designated Landing Spot (also the Source Area for our second component) as our Destination.  I'm not going to change the name of the data insertion series because this is a sample model and I'll probably never look at it again so documentation is pointless, and I've already given you one bonus best practice.  Sorry.

For our Revenue Type (which I probably should have called Product Line, but we are pretty far into this to start changing things now) I'll use a Radio Button selector and I'll choose the values from the Revenue Type column from my second Source Area (and my first landing spot) to be the labels (I also put in some "prime-the-pump" values with my distinct Revenue Types here so it doesn't look weird when you first open it up).  Then I add a Data Insertion series to this component that selects the whole second Source Area (and first Landing Spot) as the Source Data and map the Destination to the second and last landing spot on our spreadsheet. 

Then, all we have to do is slap a chart of some type on the last landing place and it shows us the intersection of our selections. I also like to put a spreadsheet component on here, at least at first, so I can troubleshoot the data movement if anything goes wrong (there I go again handing out free best practices).  As you can see, the whole thing looks as expected whether we see the default view...

Or any combination of selections (which update really, really fast)...

Parting Shots

Obviously, this is a pretty limited example, but it displays some of the power of the "Insert Filtered Rows" functionality to quickly update visualizations when you have a limited amount of data to show but lots of quick options to give users.  A few final best practices I'll give you for free (this is, after all, my first SCN blog).  First, make sure you leave your Source Areas and Landing Spots plenty of room to grow (you never know when they'll want to split out Africa from Asia or start selling Shoes).  Second, always carry over all of the possible data columns with you as you "waterfall down" from Source Data to Selection1 to Selection2 and on and on because A) Xcelsius was forward thinking enough to let you, and B) you'll eventually want to know what selections you've made and it is nice to have one final place to pick them all up from.  Third, if you have questions, please ask them below, or in the forums and post where they are at below.  You are welcome to email me from my business card, and I'll happily send you this model, but if you ask me questions I'll just ask you to post them anyway so we can all discuss it and those delicious dripping bits of our combined knowledge don't go to waste.

Finally, solutions like this will never work for everyone.  You'll either have a million combinations, which is obviously too many for this technique and you'll have to query the database at runtime based on what users pick, or you'll some cockamamie requirement to do something dumb right in the middle of the process, but it is a good exercise to get comfortable with the functionality and can serve as the starting point for a great QaaWS/waterfall hybrid.  Maybe you could call it a "Half-Oswald" in your documentation...