I've been testing 3rd-party query-building solutions for OLAP data warehouses with a few of our product's mostly non-technical business users, and I've had trouble finding any that are simple enough for our users. To give you an idea of the challenge, most of our users have never built a Pivot Table in Excel.

Our users' reporting use-cases are simple, e.g. basic tabular reports, e.g. "Sales and Returns by Salesperson in 2014" or "Sales, Purchases, and Inventory, by product, in the last month".

For our users and these use cases, OLAP query builders I've tested seem like overkill. Here's one example below for finding and choosing columns to add to a query:

enter image description here

Our users report a few problems here:

  • "I don't like having to dig into a tree to find anything. Everything is hidden. I know the name of a column I want, but I don't know where to find it."
  • "I don't understand what 'measures' means. I just want columns to add to my report."
  • "Why is 'Sales Orders' under Measures but 'Product' is not?
  • "When I drag columns over to my report, it creates a multi-level table [example below]. I just want a regular table without any levels."

enter image description here

Translating these users' concerns into tech-speak, these users were baffled by basic BI concepts:

  • the difference between facts (measures) and dimensions
  • dimension hierarchies
  • the concept of a "database table", especially the idea that there are some limitations in how data from different tables can be combined/joined.
  • pivot tables (in general) and specifically the interaction between pivoting and aggregation
  • how to get a simple tabular view from a more complex pivot-table view

We could abandon the project and simply build a lot of canned reports instead. But we know from user interviews that the ability to add and remove columns from reports is at the top of our users' priority lists-- and is an important differentiator from our competitors who have limited reporting flexibility.

I'm sure I'm not the first person to discover that even simple OLAP query builders aren't simple enough for some users, so was hoping that the UX experts here might be able to point me in the right direction.

Are there well-known really-simple UX patterns for making tabular reports from OLAP and/or Dimensionally-Modelled relational sources? Is there good user research about conceptually simpler ways to build OLAP queries?

I've done lots of Googling and reading dry usability research reports without much luck so far.

  • Take a look at Sisense. It's a very powerful reporting engine with self-service dashboard builder tools. I was going to implement it recently with all fully customized UI, using it's API to create pre-configured views. We ended up going with a custom stack, but not because Sisense wasn't amazing! – plainclothes May 6 '15 at 21:12




Sounds to me like your nested categories are doing more harm than good.

Why not let them pick columns with a suggestive search box like this? You can still group by category, and the whole list will still show (when the box is clicked). After a few keystrokes, I'm guessing most people will find exactly what they want.

enter image description here

(via Select2)


Given scope of question the following heuristics may be useful, indeed some may have already been applied

Education of BI domain? most UI's are representing a domain model or world view that a user is familiar with. This makes learning curve low, and thus a good UX. So UI's do need to educate user on a new domain, or details of a domain (for example everyone understand government Tax schemes broadly, but details can be surprising)

Does UI have to educate users on BI terminology, or is it possible build UI that operates without revealing the levers behind the curtain.

Reveal the User Domain a corollary to the above, but specifically it is common in reporting services is a layer of abstraction where the Users Domain Objects and their natural relationships can be extracted into a high level Model. So that users do not have to deal with "Tables & Joins"

e.g. Users never have to build links between Invoices, Sales Person, Customer, etc. This is preconfigured. If you have a "Sales Person" this offers a list of "Invoices"

Do not start with blank it is quite a stark choice between building something from scratch, and modifying something in place. From research you know that "the ability to add and remove columns from reports" is key, then do that (and only that, consider an Agile UI approach). So ship canned reports with small degree of customisability. As commonly people learn by example, not only theory.

Low cost experimentation if a user is confident they can experiment without risk or cost in terms loss of work or effort, then will learn rather than abandoning the task. With user testing and 'show-and-tell' beware of reading too much into "knee-jerk" question , behaviour is more important than words. Is the user actually stuck? What will they try and do?

For example does user really need a flat list or in real world they happily adapt to a pivoted views as can get all data they need.

Build from simple and familiar Assuming users know basic Excel functionality then a easy start is list of columns populated from database. So allow users to do that, with first column and in turn second column, etc. Then extend with ability to group, sum and split the data later. I've actually seen a UI like this, and it was highly user preferred to the "classic report designer" style UI The problems with building a UI that simple are mostly in the BI domain, not the user domain. Those are levers that can remain hidden.

To not contradict other guidance above you'd have some simple pre-canned lists. e.g. "Clients for 2014 by Net revenue". UI should make it easy to add operation that the user would want to do, and offer it in their language e.g. "[See this list] Split by Region". The operations offered would be restricted to logical legal operations, determined from the abstracted data-model.

TL:DR; Good UX means hiding the mechanisms of BI behind a user focussed abstraction. Widgets alone won't make much difference.

  • Jason makes a very good point about not starting with a blank and forcing the user to find everything they need – GrantB Oct 6 '15 at 3:31

Jason makes a very good point about not starting with a blank. Sounds like tools you have looked at start with a deep tree view of ‘stuff’ and force the user to find everything they need to get started.

I used to work at a company that produces easy-to-use tools for this kind of analysis. Power users could use a deep tree view of categorised data and make complex selections. Selecting was assisted by fast filtering/search built in to the tree view. To assist the power users, as they made selections from the tree view on the left hand of the screen, their selections were built up on the right hand side of the screen in a form that gave some context to the selection. For instance if you have selected 10 dimensions with each dimension having 50 items, the number of cells in the resulting analysis would be huge (and probably meaningless), so feedback on selected size was given. If they had selected dimensions, but no measures, then a default would be pulled in etc. Trying to create a trend analysis?, then a time period had to be selected or we selected a default for you.

If there is sufficient meta-data about the selections, then you could provide wizards to lead the intermediate users through picking [a date range] for X axis, then [geographic area|product category], then measures/values for display in a pre-selected style of analysis.

Finally, and as a good start point for new users, and in particular as you say “Our users' reporting use-cases are simple, e.g. basic tabular reports, e.g. "Sales and Returns by Salesperson in 2014", then the best solution is simply pre-built ‘cubes’ of data with reports that show the data as tables & graphs. The tool should then allow the user to take that predefined cube, look at it, then interact with the report to manipulate it into how they want it – i.e. change the Sales report from the 2014 period to 2015. Users find it easier to look at something that is almost what they want, then change it to how they want it, than start from a big pile of items and have to build it into a logical structure and learn about dimensions, measures, joins and aggregation.

Your Answer

