The potential false promise of self-service BI

Self ServiceSelf-service BI applications are the new hot thing. Well .. not new exactly, they have been around for some time. But it seems that the embracement of cloud has sparked new life in the marketing machine of many vendors of self-service BI applications.

The topic ”what is self-service BI” deserves a separate post. In short, it is the ability to make your own reports and dashboard with a minimal technical skill set. Many vendors exploit this by marketing their tool as the “one and only”. Buy their tool and magical things will happen.

If not informed properly you may find yourself disappointed by the result. I have witnessed customers choosing the wrong solution because of this. But, can you blame them?

Imagine your company having issues with providing right information at the right time. Imagine that you have been struggling with this for years and out of the blue a vendor promises that all that pain is a thing of the past. Wouldn’t you at least be curious?

The half-truth and nothing but the half-truth
Your curiosity leads to an appointment with a salesperson who further emphasizes that everything is possible. Sometimes they even bring in an “expert” who further answers every question you have with a firm “Yes, it can”. Miraculously they don’t need to know anything about your business except for the number of users. Further along in the meeting they start to flash with slick demo material and references emphasizing that this was all very easy and zero effort was required.

You are missing something
Impressed and excited you initiate a project. Meetings are scheduled labeled “business requirements”. It’s time for the first meeting and you start stating the questions you would need to have answered. You expect the consultant in front of you to open the application and magically generate the right numbers. But no such thing happens. Instead the consultant frowns and busily writes your questions down and continuous with his interview.

After an hour or so the consultant stands up and thanks you for your time. You ask; “what’s next” on which the consultants replies; “Based on these requirements will have to configure the application”. “What do you mean”, you ask. The consultant replies “We need to pre-configure the different data so these questions can be answered properly”. The consultant sees the glare in your eyes and says “We need to create a proper foundation”.

Foundation? What does he mean ? Isn’t the data stored in my systems enough? Didn’t the salesperson promise that all we need is their application?

The foundation, one piece of the puzzle for a better user experience
You can think of the foundation as a layer in which the different ingredients for your questions are stored. But these are already stored in my systems, you may ask. Well, yes and no. To explain I will need to use an example.

Let’s say your question is “Show the number of leads in 2013”. The system in which leads are registered records all the different ingredients needed to answer this question. However, some ingredients need to be transformed. Let me explain. The system records the date on which a lead was created but for you to be able to simply select a year (2013 in this case) we need to separate the year from the date so that you can select the year exclusively making the overall experience better.

This is exactly what the foundation is for, help to support a smooth and overall better user experience. But this is only one piece of the puzzle. The application you use to answer the questions (that which you will use to interact with your data) also needs to fit your preferred way of working.

Take home message
An analogy I often use is mobile. Since the commercial success of the Apple iPhone a new understanding of what provides a good user experience was born. Today most major phone vendors realize that for the user experience to be good you need to provide both good hardware and good software. Moreover, they need to be attuned to each other.

Linking this back to our example, a better user experience is only feasible by providing both a good end tool (like the self-service application in our example) and a good foundation (architecture) which is attuned to the business. This attunement also means involvement from the business, this is something for a future post.


Comparing Pyramid Analytics and Power BI


With so many different BI tooling on the market today it can be daunting to choose the right one for your company. Many things influence your final decision. Your IT strategy, for instance, may enforce a cloud-only policy or may even state that all (BI) products should be from a certain platform (e.g. Oracle, Microsoft etc.). Let’s not forget your most important customer, the user. Their demands and wishes also effect which product is best for your company.

The goal of this blog post is helping you make that decision. This post is inspired by Jen’s article in which she compares Tableau, QlikView and Pivotstream with each other. I encourage you to read that article too.

First off, some background information on Pyramid Analytics and Power BI helps you better interpret the comparison made in this article. Information on Power BI can be found here and here. Information about Pyramid Analytics can be found here.

The comparison made in this article reflect my personal opinion. You may share a different opinion. In any case please share your thoughts (respectfully) with us through the comments section. I’ll start with a detailed comparison followed up by my conclusion.

Detailed comparison

Business criteria

Business criteria

Visualization criteria

Vizualisation criteria

Technical criteria

Technical criteria


Both Pyramid Analytics and Power BI aim at simplifying information discovery through self-service BI. I think both tools succeed in providing a clean and user friendly way of accomplishing this. Of course, user friendly being dependent on what your typical user is comfortable working with. Pyramid Analytics requires a predefined OLAP cube, thus requiring users to understand the concept of dimensional models. Power BI requires Excel and uses Tabular which basically is the concept of relational modeling. Although Power BI can also work with dimensional models it is not mandatory.

Pyramid Analytics relies on an OLAP cube. SSAS OLAP cubes have been around for a while making the product mature. Power BI relies heavily on Excel 2013 making it ideal for Excel (power) users. Both Pyramid Analytics and Power BI work with a subscription based licensing model. Power BI is part of Microsoft’s Office 365 offering.

Below a graphical representation of where I think Power BI and Pyramid Analytics fit. I used the maturity model provided by TDWI. Note that this does not say anything about which tool is “better”. It is where I think both products ideally fit.

Snap 2014-03-30 at 19.10.10

I hope that I have helped you better understand your options. One piece of advice, play with different tooling (most vendors offer trial versions), make sure you are informed by attending demo’s and / or asking peers / co-workers what their experiences are. Of course you are welcome to ask me for further advice on this matter. Good luck!

**** UPDATE ****

I have spoken with various people regarding this post. These conversations led to better insights which I would like to share with you.

Not so BIG data and the enterprise way of things

I think Power BI is excellent in providing ways to connect to “BIG data” sources like Facebook, Hadoop and many more. However, Power BI is not suitable for actually “housing” BIG data. Power BI relies on Tabular in Excel which cannot house trillion rows of data. Moreover, Power BI itself has a restriction on workbook size (currently 250 MB).

Pyramid Analytics works on top of Microsoft SQL Server suite (both TABULAR and OLAP). OLAP knows no technical restriction like maximum size. Moreover, OLAP has excellent scalability features. These features make Pyramid Analytics a good candidate for enterprise solutions.

The “grey area”; a feeding ground for endless discussion

In making my comparison I followed a simple guideline; “What do I get when buying this product”. This led to some big differences mainly caused by the fact that Pyramid Analytics works on top of Microsoft SQL Server suite (Tabular and / or OLAP).

Example: I think Power BI provides more ways of connecting to external data sources. You could be of the opinion that Pyramid Analytics provides the same because it can work on top of the same model. The difference I make is that with buying Power BI that feature is included. It isn’t included with Pyramid Analytics, you need to buy Excel through Office 365.

Simply one of those things I feel you should be aware of.

Final points; “in – depth functionality” versus “for the masses”

I feel I have left out an important part in this post, and that is a proper conclusion. So, without further ado, here it is.

Pyramid Analytics works on top of Microsoft SQL Server. Together they provide excellent extendibility and scalability features. Pyramid Analytics is more an on premise solution then it is cloud. There are ways of “cloud enabling” Pyramid Analytics but it will require third party platforms / applications like Microsoft Azure.

Power BI is a cloud only solution for which you will need an office 365 subscription. Power BI is an all-in solution meaning it does not require other tooling / platforms to unlock the full potential. Power BI is more restricted when it comes to extendibility and scalability.

Power BI is something you can easily start with but will start to feel restricted when more enterprise features are required. Pyramid Analytics offers more on the “enterprise” side. Being an enterprise solution Pyramid Analytics certainly feels that way, there a ton of features to use which at times can feel daunting. The required installation and configuration makes Pyramid Analytics feel less agile.

What is Power BI ?

Much like the power rangers Power BI is packed with lots of features called Power something something :)

Much like the Power Rangers Power BI is packed with lots of features called Power something something 🙂

It has been several months since the release of the preview version of Power BI. Since then lots of features have been added. Recently Microsoft also released its pricing plan for Power BI. With the final release closing in I thought it could be helpful to explain what Power BI is now.

It is important to note that Power BI is still in preview. You can sign up for the preview here.

Power BI is a cloud-based Microsoft Business Intelligence solution which supports on premise data refresh and mobile BI (Windows and iOS devices).
Power BI was first introduced at the 2013 World Wide Partner Conference:

At the heart of Power BI lays Office 365, specifically Excel 2013 and SharePoint 2013 online. Power BI encapsulates three major BI themes:

  • Data Discovery & Access
  • Data modeling
  • Data visualization

Data Discovery & Access

Within Excel 2013 it is possible to extract and transform data from various different sources like social media, the internet, files, CRM online and many more. The feature in Excel that enables you to do this is called Power Query. Apart from external sources Power Query also supports searching on premise organizational data like your CRM system. Selections made through Power Query (called queries) can be shared with others. The discovery of organizational data is al manageable from within Office 365 called the Power BI management dashboard.

Data modeling

Once you have made your selection(s) within Excel 2013 it is possible to further “sculpt” your data so that it can be better consumed by others. This is called “data modeling” and is done within Excel 2013 using a feature called Power Pivot. Power Pivot enables you to extract and enrich billions rows of data without the performance degradation you would normally get using just native Excel. In Power Pivot you can add hierarchies, add extra columns, hide columns, rename columns and add calculations (churn, ROI etc).

Data visualization

Power BI currently supports two kinds of visualization:

  • Excel Pivot tables and charts
  • Power View

I won’t go into detail about the pivot tables and charts. Many of you probably have a good idea of what it is all about.

Power View is a feature within Excel 2013. Power View is essentially an interactive canvas that allows you to display charts, tables, maps, and slicers in one dashboard window. What sets Power View apart is that the components in a Power View dashboard are inherently linked, meaning they all respond to one another. Select a region in one chart, and the other components in the Power View dashboard automatically respond to show you data for only that region. Many of these usability enhancements are “out of the box” but quite a few can be enhanced using the Power Pivot data model. An example; in Power Pivot you can categorize data such as, City, Postal code, Image etc. When using Power View to visualize this data it detects these categories and will act accordingly, in the case of city it will plot data on a map.

Other important features to note

Scheduled Data Refresh is supported in Power BI. Which means that once you have uploaded your Excel workbook its data connections are refreshable regardless of its origin (cloud or on premise).
Q & A is a feature within the Power BI site. It enables users to type in questions which Q & A will interpret and answer using different Power View visualizations.
Power Map is a 3D visualization add-in for Excel for mapping, exploring, and interacting with geographical and temporal data. At this moment is it not supported in Power BI sites. Watch this video to see it in action.

Demo’s you might find interesting

Excel 2013; I want more details!

In Excel 2010 it was possible to increase the number of rows Excel would return after initiating a drillthrough. In Excel 2013 this option seems to be disabled

Excel 2013 connection propperty windowOn the computer I worked on the dutch version was installed 🙂 

I work with many Excel pro’s, so you can imagine the furious reactions I got. It seems other people have found this “issue” too and posted about it on connect.

The good news? There is a workaround (posted by “Kwenix” on the connect item mentioned above) and yes it works!

To safe you time I repeated the described steps below:

  1. Make a backup of your Excel file
  2. Rename xlsx to zip file
  3. Open zip file using 7z
  4. Locate connections.xml file in ‘xl’ folder
  5. Hit F4 in 7z to edit file
  6. Locate rowDrillCount=”1000″ and change to desired value
    upon saving 7z will update file into the zip file
    rename zip to xlsx

And thats it! I urge everyone to vote on this connect item!