11 Posts

A recent blog by Heather Clancy on ZDNet asked the question: Is cloud computing really greener or just passing the buck? This is a valuable question because it forces one to think more deeply about the impacts of their green activities. Green activities at any level, from cutting down on printer output or changing to energy efficient light bulbs to changing fleets to electronic vehicles or instituting work-from-home policies, have the potential to bring negative impacts as well as positive. Hopefully, however, the activity has an overall positive benefit.

On the cloud computing question, Heather wonders about the environmental costs of decommissioned servers as one moves from on-premise to cloud-based computing. This question is both difficult and easy to answer.

First, the question is difficult to answer because it implies a comparison between CO2 emissions saved (outsourcing server hosting leading to energy reduction) and environmental pollution (environmental impact of servers components such as hazardous metals that enter the waste stream). Most companies don’t attempt to conflate these two measures – they report on different categories including CO2, Water, and Waste reduction. An overall “green” figure would be meaningless because who’s to say incrementally adding to one is worse than another. A company’s goal should be to drive down a range of environmental measures – CO2 emissions, water consumption, waste generation. SAP’s approach, for both our internal programs and in terms of the solutions it offers customers, extends the definition of sustainability even more broadly to include sustainable strategy, operational risk management, sustainable consumption, resource productivity, sustainable workforce. CO2, Water and Waste are but components within a larger strategy. This holistic approach avoids the trap that Heather describes.

Second, decommissioning an old server is not something that only occurs when considering the move to a cloud-based solution. So the issue of putting unused equipment into the waste stream will occur whether or not you decide to move to the cloud. The technical lifespan of a server is probably 8-10 years, at least for the CPU component of the server (although fans, power supply, hard drive, etc. may fail several times during that period). The utility lifespan, the duration of the technology before the benefits of newer servers becomes a compelling business consideration, may be 3-4 years. Since Microsoft’s research clearly indicates a strong CO2 reduction (per employee) when moving to a cloud-based solution, the timing of the move could coincide with the lifespan of your servers. At a point where a number of servers are approaching the end of their life-spans, it might be time to consider a move to the cloud.

My recent WebI Tricks: Bars embedded into rows showed how to create a visual representation of bars inside the rows of a table. It received quite a few views and a few questions. While I created the document containing this visualization with Web Intelligence Rich Client, I’ve confirmed that you can also view the same content with the dHTML option (i.e. over the web, not just as a local webi file). The bars look the same.

Some readers posted messages asking why they couldn’t get the same “bar” look and feel (for some reason, adding the character called “Full bar” wasn’t working). While I’ve explained how to do this in the comments section of that post, you can also substitute a right bracket or a "vertical bar" (unicode 006C) for the "full bar". It looks pretty nice too. And the same visual benefits are there as with the “full bar” character. See below.



Once you get the right look, let’s consider a few other visual displays that this “trick” enables. First, butterfly charts are a commonly used visual display for the distribution of two values around a dimension. I used the same variables as described in the post linked above, but simply applied to some values of male/female employees for different age ranges in a fictitious company. Then I did a bit of formatting to get the left hand column’s “bars” to build towards the left from the cells’ right edge.


The only real “trick” above is to keep the right and left cells proportional to the largest value in the table from either measure (# of Female & Male employees). This ensures that the cells' widths enable a consistent visual comparison of Female to Male employees.

You can also display the actual value in the cell, along with the bar itself. This is done by simply appending the measure to the formula as below where [Female] is the actual measure value to display alongside the variables for proportionally drawing the width of the bar. (The “ “ is just used to put some space between the value and the bar.)

=[Female]+ " "+ [Female Bars: Display Lefthand]


Have fun!

This week, I spoke to a Web Intelligence customer who claimed to have found the perfect workflow for his ad-hoc users. His perfect workflow meant that almost none of his Web Intelligence users created their own queries but still were able to conduct ad-hoc analysis and ad-hoc reporting. Unfortunately, this evolution away from the historic “ad-hoc query” approach, while obvious to many WebI customers, is still painfully slow for others to embrace. I’ll explain what this customer’s perfect workflow is further below, but first some background.

For years industry analysts have categorized Web Intelligence as an “ad-hoc query” tool.  This term unfortunately leads some customers to pigeon-hole the use-cases that WebI supports. Ad-hoc query implies that WebI users must create a query to retrieve specific data when they don’t have a report to respond to their business questions. As a result, BI administrators target certain business users and train them to use WebI to define ad-hoc queries.

Here’s why this categorization is less than helpful. At the very least, query is an incomplete concept in terms of actual requirements. Rarely does someone generate a query then stop. Invariably, the real goal is to make sense of the data retrieved, to answer business questions and then apply that knowledge somewhere else (e.g. sharing through copy/paste to building a dashboard or report). So to categorize a product as an ad-hoc query tool is like saying a fishing boat’s goal is only to locate fish (and not catch it, freeze it, bring it to shore, etc).


But more importantly, the act of defining a query is a significant barrier for most users. In all but the most simple query needs (which likely have reports that already exist) it is just hard to define a query. It requires deep knowledge of the data source – the data behind the measures, dimensions and details – as well as the mechanics of the user experience – how to define filters, subqueries, intersections, etc. And if you understand the data and how to use the query features, it also requires defining the logic for a query – which, sadly, can often be beyond the conceptual grasp of many business users. Even simple business questions can involve relatively complex queries, e.g. “What is store revenue for states with $50M+ in total sales?” Why do we expect the average business user should be capable of overcoming such hurdles?

“Well,” you might ask, “if they don’t define query, where do these non-querying users get their data from?” The answer is: Reports that others – experts from IT and power users from the Lines of Business – create to answer a broad set of questions. These reports are not narrowly defined like an ad-hoc query might be, but enable a broad range of questions to be answered. Lines of Business users who have more narrow questions can then use the reports they receive to conduct ad-hoc analysis and then ad-hoc reporting on top of the documents and analytics they receive.

So, this was the magic use case I referred to in the opening paragraph: BI content is authored by the few capable of identifying broad sets of requirements for Lines of Business and skilled at the science of query creation and the art of laying out reports for easy interactivity. The end-users consuming that content are not encouraged to create net-new queries, but instead to use input controls, formula functions, sorts, the ranking button, turn tables to different charts, etc. to answer their questions. My experience, and I have years of research to back it up, shows that interactivity on top of pre-defined reports is a much more successful way to introduce fact-based decision-making to business users.

I hope analysts finally change the name of the category. I’d love to see an “interactive consumption” tool” category emerge to replace it. I’m open to other terms, but “ad-hoc query” is a term that should just go away.

Many Web Intelligence customers know that the next release will integrate a new charting engine with new charts and many new parameters. I just spent a morning exploring the new charting features and I can say without hyperbole that it provides arguably the most significant breakthrough improvements – both in terms of ease of use and depth of capabilities – for content creation and consumption that Web Intelligence users have ever experienced. There will be lots of material and discussions about this in the coming months as Web Intelligence 4.0 is released.

But most of us are using Web Intelligence R2 or 3.x, at least for the near future. Until then, I want to describe a couple tricks to create some very compelling visual representations for WebI displays that effectively extend WebI's visualization options and simultaneously provide end-user interactivity. This example takes about 3 minutes. [Hat tip to my colleague Didier Mazoue, who originally built this example and can make just about anything happen in a WebI report.].


Yes, this is an actual WebI table with bars in the cells that proportionally display the sales revenue values for each row. You might ask, why not just display sales revenue as a horizontal chart? This display is not a gimmick, but brings some big benefits for content consumers. Thanks to the bars, you can quickly assess both ranking and variation among the sales revenue values. But at the same time, you can display other details in the same rows as the bars. In the table above, I’ve got a calculation to determine the % change in Sales Revenue quarter on quarter. You can also see in the same row the Quantity sold. Charts are great at displaying messages around a theme, such as ranking and distribution. Tables can display lots of details. Bar charts in tables give the benefits of both.

This really was simple. It took 4 easy-to-define variables. Note that the variables below can be combined into a big formula, but it's just easier to manage and conceptualize is you break it down into components.

1. You'll need to define the generic filler that is used to draw the "bars". Create a variable named "Chart filler". Qualify it as a Detail in the variable editor, and in the formula text add 100 characters between quotes. You can choose the filler you want to use -- whether "]" or "|" or another symbol. (I know you know this but to populate 100 characters please don't hit the return key 100 times, type 10 characters, then copy-paste 9 times ;-) )


2. Then create a variable that calculates the maximum value in the table. This will be used to define the maximum length for a bar in the column, against which the other values will be proportionally displayed. Qualify it as a measure and call it "Max Sales rev from block" and use the following formula:

=(Max([Sales revenue]) In Block)

3. Now calculate the size of the bar for a given value based on the relative proportion to the maximum value in the table. Name the variable "Sales Revenue Bars: Calc Size of Bar" and qualify as a measure.

=Round((([Sales revenue]/[Max Sales rev from block])*100);0)

This is simply sizing a row's value in proportion to the maximum value in the row. For example, if the maximum value is 100, and the actual value being evaluated is 75, this formula will output “75”.

4. Finally, define the variable you'll use in the actual table to draw the bars. The Substr function simply removes the characters in the [Chart filler] string you defined to show the proportional size of the bar. Call this variable “Sales Revenue Bars” and qualify as a measure.

=Substr([Chart filler];1;[Sales Revenue Bars: Calc Size of Bar])

Drop the "Sales Revenue Bars" measure into a table. It's now super easy to scan down a column to identify not only the largest values, but also the variability among the values.

It really is a nice display. Of course, like all WebI content, it also enables users with the appropriate interactive rights to slice and dice and otherwise drag additional dimensions into the table – the calculations for [Sales revenue] and the related bars is dynamic. So let your users drag and drop away.

There's a little, unnamed feature in Web Intelligence's Query Panel that often goes unnoticed. OK, it does have a name - it's called "Change Source". But that name only shows up in documentation. On the Query Properties pane in the Query Panel, the feature is invoked from a button labeled "...". This post will show how no-name features can be surprisingly useful, and perhaps point to a completely different usage scenario from its typical use case.



For those unfamiliar with the feature, its usage is critical when you move WebI documents between Development, Test and Production environments that each have different versions of the universes for a particular data source. Here's how easy it is to use this feature.

  1. Open the Web Intelligence document
  2. Click Edit Query
  3. View Query Properties
  4. Click the Change Source button to view the "Change Source" dialog
  5. Ensure that the relevant objects from the source universe are mapped to those in the target universe
  6. Click Run Query

The report, using the context of the new objects from the newly invoked source universe, maintains the integrity of the report layout, its calculations and variables, the sections, the ranking, conditional formatting ("Alerters"), the input controls, etc.

In step 5 above, note that the objects are automatically mapped between source and target universe by their names.


“Quantity sold” measure from the source universe gets mapped to the “Quantity sold” from the destination universe. Occasionally, the source universe relies on some objects that are named differently than the target universe’s objects. Of course, the user experience suggests the appropriate objects in the target universe based on the names, but if the name doesn’t match between the source and target universes, the dialog suggests that the objects are "ignored" -- meaning the report is modified to ignore the existence of those objects in the report based on the new, target source. However, notice the new "..." in this dialog.


The user can define the objects that make sense. As an example, while the object "State", used in the source universe, is no longer a name used in the target, the user can still point to a relevant object in the target. Simply click on the ellipses to the right of "remove result object" and select, for example, "Country".

This gives quite a bit of flexibility to the Change Source feature. In fact, it opens up interesting possibilities. Let's say that the report using that source universe was written for a universe on top of the Sales Database. It's got a bunch of formatted, aggregate tables and charts, some ranks, and two or three report tabs of details sliced in different ways and sum calculations. In other words, a kind of generic report. But nonetheless a report that involved quite a bit of thought and formatting effort.Now, think about changing from the source universe used to define the report, not to a variant of the source universe (e.g. from eFashion Test to eFashion Production), but to a completely different universe. Why not take that fine-tuned WebI document created for the Customer Database and remap it to the Sales Database? Or the HR database to the marketing database? After all, there are often MANY types of content which are conceptually the same - dealing with geography-related objects, time-related objects, sales measures, etc. Like with DNA in humans, this is a kind of BI content "DNA" that is shared across the many report domains, oftentimes with only minor differences.

Admittedly, instead of remapping a handful of objects because of some name changes, all the objects are different and need to be remapped to the target universe. And true that some from the source universe might no longer be relevant. For example, if the document used "Day of Week" from the source universe, but the destination universe only has the concept of "Month", then it will have to be "ignored" during the change source.

Keep in mind, however, that the DNA of the reports is often more similar than it is different. Not to mention that there is a lot of work that goes into the layout and formatting. If you have to rework 20% of the report - add in new objects that fill out the context of the new source, change or remove certain calculations, put in a different header/footer, expose prompts a bit differently, etc. - it will often be worth the small updates, rather than starting from a blank slate. It might also provide new insights for new metrics across business domains. A variable from a customer report that is used to measure their "churn rate" for customers can, once mapped to a universe from the HR database, be used to measure "churn rate" for employees. With Change Source, the objects used for the functions just get slotted into the appropriate place. You'd be surprised how many calculations are common between documents serving different domains.

Another use for this feature might be for very fast BI prototyping. The Supplier department wants to get more fact-based information for their decisions, but doesn't yet know what they want to track? Grab that report for Manufacturing, do a quick mapping of objects to the Supplier universe, and give them a nicely formatted report to solicit their requirements for a more formal report.

Will this always work? Of course not. Moreover, this approach to remapping universe objects between domains will sometimes be completely irrelevant. But next time you embark on a blank slate creation of a report that reminds you of something you've already done, find that pre-existing report and see if you can save some time.

Last week during a customer meeting, a customer (from IT) insisted that the query itself provides the answer and reporting is often superfluous. “The query already contains the answer they need,” he stated, all puffily chested. While an interesting perspective – and I shudder to think of the number of narrow queries the customer must be forced to build and maintain – this just doesn’t work in practice.

Reports consist of cross tabs, tables and charts and calculations that provide answers to numerous questions. Even one 40-row, 5-column crosstab can provide enough detail to answer dozens or even hundreds of questions. But, with the 10 minutes that a user might have to glean a take-away from the table, it’s often difficult for them to have the same reactions that the author had attempted to expose through building the report. Typically, consumers need help to understand what a report’s data is telling them. That’s why authors, even Web Intelligence users building ad-hoc reports, spend time formatting tables and charts, laying out the tables and charts in a certain way, adding single cells for titles and guides to interactions, changing cell highlighting colors, using conditional formatting (“alerters” in WebI), adding input controls to enable simple customization, etc. 

We’ve even seen reports with images captured from WebI’s user experience, such a button, to guide the user to a certain feature to help their interactivity.


Sometimes these efforts, while valuable, miss the most basic challenge consumers have when trying to understand a report’s content. A BI project owner once told me “At meetings, when comparing what are seemingly identical reports, my users would nearly break out into fist fights over whose numbers were right. They spent too much time arguing about calculations and not enough about what to do about the results.”  Ironically, both sides were almost always “right”. It just came down to a difference between the prompt/parameter or report-side filters used, or when the refresh occured. Without a way to see this on the report, they thought any deltas resulted from the calculations they had chosen.

I thought about that comment the other day after I received a WebI doc from a customer. It contained a great example of a simple step that authors can do to help consumers more efficiently understand, interact with, and make decisions on the content they receive. It probably took no more than 120 seconds to set up. They used these templates (from WebI's left hand panel) and simply dragged them into a report tab:


Once they’re placed in the report, they automatically generate the relevant description. Here’s a snapshot of the output from one of these cells – the prompt summary (known as parameters to BEx users).


(Note that this document, to preserve the customer’s confidentiality, has redacted the references to the customer name and replaced with ACME.)

This particular report has a lot of optional prompts that were “ignored” by the person consuming the content. But, what if the prompt “Profit Center Hierarchies Name” had “Division X” and “Division Y” as a response? Knowing that Division Z was not included in the report’s output helps explain the totals.

The Report Filter Summary works the same – except it summarizes the filters the author has placed on the tables and charts after the query results had been retrieved. You can imagine how useful it is for an end-user to know, at a glance, how the data in the report has been transformed. Especially when the report is, as is frequently the case, printed to be shared with other users who don't see it online.

As a bonus, the author of the sample report also had added a link to some documentation describing more in detail about the report – its goals, ways to use it, who owns it, etc – on a centralized server. Such efforts are well appreciated by end-users, but also helps reduce the numbers of questions authors must respond to. So everyone benefits.


I’ve come across many authors who insist that the consumers of their content know that content very deeply, and such metadata is needless overhead. I don’t buy that for a second. Not everyone is as conversant with the data as the analyst who provided the original requirements. A new hire, for example, would be a LOT happier with the background info provided on this tab than making assumptions about why this or that calculation seems lower than expected. Or a consumer looking at an archived report with content that has long since evolved. Or the colleague who printed the report out and reads it on the train. All would benefit from being able to glance back through the metadata -- dragged into a coverpage, a header, or anywhere in the document -- that describes the content.

I think two minutes total of drag and drop and a little formatting seems a small price to pay for ensuring just a bit more consumer understanding. And maybe less office violence.

In a typical scenario, Web Intelligence is used by report authors who want to deliver interactive content to consumers. Sometimes authors have pressure to create extremely large WebI documents that ideally will provide answers to any possible question a consumer might have. One WebI customer is delivering documents spanning nearly 200,000 pages. It works, but the delivery is pretty delicately controlled. In general, however, this tendency to build documents with huge data volumes is the same affliction that causes someone to buy a house with 2 extra bedrooms on the chance that someday somehow both sets of grandparents will come calling the same weekend. It might just happen. Whew! What peace of mind to have shelved out an extra $150k for two rooms you might simultaneously fill once a decade!

In the WebI world, the desire to build reports with large data volumes pushes inevitably the limits that the Webi server (for dHTML or Java clients) or Webi Rich Client can handle. WebI memory use, which of course increases as the document size increases, is limited by the processor. As of XI 3.1, at 32 bits, the processor limit for Webi doc size is around 2 gigabytes. This memory is consumed by the storage of the DP results, calculations and the report engine.

In practice, from the perspective of data in the data provider(s) results, WebI can support until a maximum of around 500 megabytes, being roughly 25 million values (e.g. 5 cols * 5 million rows, 25 columns by 1 million rows, etc.). In this case, the document will consume all memory available to the process. For the Rich Client, the content can be consumed offline on each user’s machine, so this memory is not shared. For online clients, the process must be shared by each concurrent client on a given server, so divide the document size limit by number of concurrent users (e.g. 10 concurrent users on a doc of 2.5 million values could max out the server).

Again, it’s important to note that these are rules of thumbs, not absolutes. You might find the server performing adequately even with such gigantic documents. However, the size of a WebI document in terms of rows/cells is not the only variable in play. Synchronization of dimensions between multiple data sources and the number of variables also has an impact, as does the complexity of the layout. So, a 10 million value document with multiple sources and lots of complex variables and calculations and a lot of charts and tables and conditional formatting might put pressure on the server as much as a table-dump with 25 million values.

But before you start categorizing WebI as a client that is best for small documents, let’s step back and think about what a document with 25 million values means to report authoring and interactive consumption. First, it’s absurdly large. Just for reference, a 5 million word MS Word document could easily be more than 10,000 pages. Second, it’s absurdly large. Take the example of a query that retrieves 500,000 rows and 50 columns – 25 million cell values. Among those columns, you might have Region, Country, City, Year, Quarter, Month, Product Level 1… Product Level n, Industry Code, Customer Name, Address…. And then of course there are measures like Sales, Units sold, various Counts, etc. Maybe these columns/rows are fed by another source or two – a customer support site plus maybe even an Excel file with forecast data. This report is great! It contains the sandbox for answering any question at any levels. Just which of the dozen tabs should I click on and how long should I scroll to get my answer? Third, it’s absurdly large. Maintaining a document this large – with all of its different analytical views and web of interdependent variables and calculations – is always going to be painful. You better hope the author never leaves the organization, because untangling the intentions and interdependencies within such a large document will be next to impossible.

How will users, concretely, consume this volume of data? A handful of aggregation in a table with options to drill to 4 or 5 levels of details at any class of dimensions – Geography, Time, Product Levels, Customers, etc.? In this case, since the author is not adding value to the content – either through formatting, calculations and variables, juxtaposition of content to tell a story or synchronization of data between different sources, etc. – this is a clear case where Explorer would bring value to this scenario. If the expectation is for consumers to explore the data “as is”, then give them Explorer to let them find their factoid answers at any detail of data.

Often in these scenarios, the author does try to add value by defining a multi-tab WebI document with dozens of different detailed views of the data organized in different themes. These documents take weeks to create and validate and could take days to go through, but in theory they enable users to have any of their possible questions answered, no matter how detailed and unlikely they may be. For the vast majority of users, putting content detailed as this into one document is like buying flood insurance if you live in the Sahara. Yes, it is possible that someone, one day, might want access to those details in one session. And over years, a user might access quite a few details related to numerous dimensions. However, is it worth the price of performance to buy such insurance with a document that size? Instead of building such a monstrosity as one document, consider alternatives for delivering the same content in a couple, linked documents, using features such as query drill or prompts (optional prompts might help), using queries to pull specific levels of aggregation instead of in one giant chunk, allowing more ad-hoc query and document creation with compulsory prompts turned on, etc.

Rest assured, however, that help is on the way for customers insisting on such humongous documents. In an upcoming release, we plan on making WebI 64 bit, effectively removing the process limit and enabling the addition of more physical memory on the server to improve the handling of larger reports/throughput. The Rich Client on an end-user’s machine, which uses a “local server” implementation, will also become 64 bit in a future release. (Note that the Rich Client has essentially repatriated some Webi server components to be part of the Rich Client installation. This is what enables WebI content to be taken offline from BOE.)

But in the meantime, authors should constantly check the trade-offs involved with building extremely large documents. And ultimately, it comes down to understanding what end users (consumers) really want to and need to do with the content. Next time you get pressure to create a 12-tab, 50 dimension, one-size-fits-all report, push back a little. The consumers are not always right. Ask how many 10,000 page word documents they use.

More tips and tricks for Authors wanting to build rich, interactive reports with input controls (Web Intelligence 3.1 SP2). Last time, I wrote about using input controls to create a Input Controls to dynamically rank tables for consumers. This time, I’ll describe a way to enable users to more easily scan the values in tables with large numbers of rows.

Customers over the years have asked for features that enable users to optionally freeze the headers of large tables. This helps those poor users who are sent massive tables with hundreds or even thousands of rows and dozens of columns. In order to identify this or that salient value, users need to be able to keep the sight of the column label as they scroll. Otherwise, they can’t discern which value belongs to which column.

There are countless ways to solve this issue. The best is to ask why users should be forced to scroll huge tables in the first place. Forcing a “needle in the haystack” scrolling session often guarantees the user will overlook one salient detail while looking for another. The most obvious response is for Authors to look for ways to break up the table into more easily viewable “chunks” – sections, breaks, multiple tables with different themes. The authors can also pre-identify the thresholds that would make one value or another interesting (e.g. values that are greater or less than 30% of average), then build some conditional formatting (called “Alerters” in Webi).

Authors can also guide the user to do their own interactivity on the report. If a consumer has the rights, let them create their own section groupings or breaks (simply a right-click). Allow consumers to delete a column, or define their own thresholds for conditional formatting.  Authors can never pre-identify all of the most important values to a range of consumers – the market and business situation changes so frequently. So let them consumers define them on their own.

OK, now you’re saying, “Stop preaching. My users want big tables of data and that’s that.” Fine, here’s a simple way to build an input control to facilitate their navigation (scrolling) so at least they can get to their answers and then out as efficiently as possible. I can’t believe that any user really wants to spend their time inefficiently scrolling around a giant table.

First, it’s based on a simple variable using the rowindex() function. This function simply provides the number for each Row in the table. Note it works across sections so the rows will be counted, not as rows in individual tables within each section, but as rows for the overall table that has been broken into sections.

So, create a variable named “Row#” that has the following formula.


Second, create an input control that uses this new variable “Row#” as the input dimension.

Fill in the parameters as shown below, note that you are using a “simple slider” widget as the input control. Note that the size of the increment is defined as 20. You can adjust this as needed, depending on the size of the screen and window most users will consume the report.


Then you should assign it to the appropriate table in the report. [Note: In the below document, there is only one table so that’s the only choice you have.]


When a user consumes the document, the slider then enables them to jump 20 rows at a time, while the header maintains its place at the top of the table. [Note: You can validate the behavior of the control by adding the variable “row#” as a new column to the table.]

Compare this to a split window or freeze column feature which still requires users to scroll for minutes at a time. With one click on the input control, the user can jump very quickly in 20 row increments. Pretty efficient!

(next time, I’ll show a more sophisticated version)

In Web Intelligence 3.1 SP2 (summer 2009), we released a new feature called Input Controls. Coy’s column did a good job of describing the general use of these controls Web Intelligence SP2: Input Controls and Web Intelligence Input Controls - Part 2. What I like about them, and we use them internally on our Web Intelligence content, is that in 30 seconds you can expose the most common and likely filters that enable consumers to quickly personalize their BI content. (Actually, it takes less than 30 seconds if you use default parameters.)

We’ve received a number of questions about possible use cases and output that Input Controls can support.  To start, I’ll contribute one about ranking. This was a sample proposed by one of Web Intelligence’s fantastic program managers, in fact, the one who designed the feature.

To start, ranking of Top n is one of the most common report outputs. Everyone benefits from views of large lists ranked by the products with the largest sales, customers with the longest overdue payments, employees with the most years experience, etc. But "hardcoded" outputs, such as Top 10 products by revenue, can be frustrating for a consumer when the value they want to view is in the top 20 or top 30, and not the top 10. I'm going to describe how report authors can enable consumers of their content to control their own definition of top n, without having to do anything more than move a slider.

First, consider customer satisfaction survey data with dimension [Customer Name] and two measures: [Satisfaction] (as in “satisfaction rating”) and [Net Sales]. If you build a table, sort descending by [Satisfaction], you get potentially 100s of rows.


For the sake of the report, let’s put a rank on the top n Customers by satisfaction rating but build an input control to enable consumers to control the number of top customers visible.

Next, create a variable called “Rank Customers by Satisfaction”, set as a measure and use the following formula: =Rank([Satisfaction];([Customer Name]);Top) 

This uses Web Intelligence Rank function. You define the dimensional context for [Satisfaction] as the [Customer Name] to which is appears. Note this would have been the default even if you didn’t put the [Customer Name] in the context, but it is sometimes useful to lock in the context when there are multiple dimensions in table rather than let the tables column layout define the context.

Now, create a value for a rank which will serve to define the starting value for the Input control. Let’s set this variable as a dimension and label it Top n and simply make the formula =10. Note you can set this to 0 through 20, it doesn’t matter what number you set because it will be dynamically overwritten by the slider in the input control.Pretty simple so far? The 3rd variable is simply an “If” flag to read the status of the Top n variable you just created. Call this variable “Flag rank” and set as a measure. The calculation is also simple:=If([Rank customers by Satisfaction]>[Top n];0;1)

This has taken about 2 minutes so far. The last step is to use the Flag to create a FILTER on the table you’ve defined above. Make sure the filter pane is open and the table is selected. Drag the “Flag rank” variable into the filter pane, and set the filter “Equal to constant” and type “1” in the text entry field like below.


Now that the variables are set up, the only thing left to do is to define the input control. Reveal the input controls in the right-hand pane and click new. From the Input Control wizard select the “TOP n” variable as the dimension to filter on, then click NEXT. Fill in the next step like this:


Then you have an input control slider that can change the numbers of customers appearing in the ranking table.


Moving the slider dynamically changes the number of customers displayed in the table.



So, here’s a summary of what was added to the report:


Output               Details
1 Create a ranking calculationVariable named: “Rank Customers by Satisfaction”=Rank([Satisfaction];([Customer Name]);Top)
2. Default value for sliderVariable named: “Top N”=10
3.  Detection of Rank valueVariable named: “Flag rank”=If([Rank customers by Satisfaction]>[Top n];0;1)
4. Set a filter on the table using the “Flag rank” variableConstant value=1
5. Define your input control on top of Top nTop nMin value = 0, Max = 30

 With a few more calculations, you not only enable end-users to rank, but to see the impact of customer satisfaction on revenue. The table below is completely dynamic as well, all text changes according the value selected in the input control


When you add up the time to define the variables, filter and parameters for this input control, it was perhaps 3 minutes. That’s a worthwhile investment for report authors to spend when you think of the many different questions that now can be answered by consumers of this report.

Many new to BusinessObjects Web Intelligence ask about the ability to mashup, or merge, data that comes from multiple sources. More suprisingly, others simply ignore the feature because it's unlike anything they've seen before. But, for those users who know about it, this feature is one of the heavily relied on in any given report. So, why is data mashup in WebI so valuable?


Use Cases

There are countless applications for mashing up data between sources. Let’s simply use one measure -- “sales” -- from a Sales DB. Many dozens of questions could be answered by combining this measure with data from other source domains. How are changes to sales correlated with amount of time sales people spent in the training course (Training DB) on effective sales? Does customer satisfaction (Customer Sat DB) have a positive effect on sales? How does seniority (HR DB) impact sales? How do sales changes relate to regional economic data (public economics data)? Etc. In fact, take any measure from any business domain and there are many ways this metric could be complemented and elucidated by mashing up with shared dimensional content between different data domains. 

Why Business Users Need This Control

Some in IT might answer, “Such mashing up of data is not for business users, but is a job for IT through data cleansing and ETL processes.” There is no doubt that the value of IT processes to combine and cleanse data brings huge value to the organization.

We must recognize, however, that these IT processes take time, and while waiting for the final approved combined sources, the organization suffers from lack of analytical nimbleness. Many business questions don’t need 100% accurate data to get a valuable answer. Plus, business questions need to be answered in time frames of hours, days, weeks and not in months and quarters – the minimum time frame for an IT process.  

Additionally, WebI exposes a relatively simple User Interface to enable the mashing up of related dimensions. Then combining those merged dimensions and related measures is automatically supported when building a report.


The image below shows how easy it is to merge two common dimensions from the list of data objects from two different sources in the same report: Ctrl-click and right click to merge.





A concrete example:


Fictitious Company A acquires Company B. Each company had their own data sources -- let’s say, BW and non-BW.  Below's a snapshot of the customer sales data. Note some customers are shared between the two lists.

image   image

Imagine if this were 1000s of customers instead of a few dozen. Not so easy to eye-ball your way to insights nor to get common analytics out of the shared values between the lists.

Before any data integration projects had even been sketched out, let alone started, a non-technical business user is able to use WebI to build reports of customers from company A and combine with customers from company B sources. Just with the above columns, s/he can answer questions as such as which of the customers from Company A, and vice versa, are not customers from the other company? Which customers are the customers from both companies A and B? Which are the top customers from A that are also customers from B and vice versa?

Below you can see the resulting table showing just the 10 shared customers, with a total for the combined revenue. After the right-click mashup of the customer dimensions, this table was created with exactly 3 drag and drops and one simple calculation.


Even in this simplified example, this combined data becomes actionable right away -- perhaps leading to the identification of some new sales opportunities targeting these combined customers.

The IT project and processes can do all the magic behind the scenes to cleanse and integrate this data. But such a multiple month IT project shouldn't prevent a business from taking action on combined content today.

NEXT: What kinds of data sources can be mashed up? (And why personal data files are key!)

My first blog post here will be a look back at 2008. While 2008 was a fantastic year in many areas for Web Intelligence customers, partners and users, if I had to choose just one significant "event", it would be the below.

Homecoming, of sorts

2008 was "déjà vu all over again" for many users of Business Objects software. In the XI 3.0 release last summer, we announced Web Intelligence Rich Client, which enables BI content to be taken offline. For Web Intelligence customers, this provides them an unparalleled capability: Identical workflows whether using the dHTML, Java or Rich Client deployment options, whether working while connected to the BOE platform or working disconnected on the train or in the airport lounge.

For those of you knew to Web Intelligence, let me make it perfectly clear: Rich Client users can be connected to their BI content online, take that content "offline" for analysis and building reports, then resync that content for sharing with web-based consumers. Then, other users, using the Java Report Panel or the dHTML web-based deployment options, can access that same content and edit, analyze and format reports all online. Three deployment options. Same workflows. Same capabilities. Online or offline.

For many Business Objects customers, the Rich Client's offline capabilities may have felt like a "homecoming". The reason is, Desktop Intelligence and its precursors (aka "Full Client") have had offline editing capabilities for many years.  Desktop Intelligence was built as a Windows application, so many BI usage scenarios grew out of a dependency on and expectation of offline capabilities.

But there is one huge advantage that Web Intelligence offers. Desktop Intelligence content does not provide web-based users with interactivity. So no online sorting, filtering, formatting, swapping columns/rows, changing a table to a chart, etc. It was never architected to do so. Desktop Intelligence users might say, "As long as I can download the content locally and edit with my trusty Deski, who cares about online editing?"

Well, if you're a report author who has ever been bothered by a host of users requesting incremental modifications to your content, you sure would appreciate the web-based interactivity for consumers of the content you create. "Let them add their own sorts, filters and ranking, I'll focus on the hard stuff!" And if you're in IT and you want to deploy to a wider internal audience or partners or customers in an extranet scenario, web-based interactivity sure is cheaper as well as enticing as a value-added differentiator to keep the users coming back.

Web Intelligence, if I can be so painfully obvious, was built as a web-based tool for analysis and reporting. Web-based interactivity -- free-form layout, sorting, filtering, calculations, turning tables to charts, etc. -- is built in. Web Intelligence Rich Client complements this by providing offline capabilities for both professional authors and analysts and (bonus!) it supports Excel as a data source that can be merged with corporate data.

I assert that this online-offline duality makes Web Intelligence one of the most unique offerings in the market, and I'm proud to see the results of our engineering team's hard work and vision in delivering this capability.


Filter Blog

By date: