Next release of PowerView

Microsoft isn’t great at prognosticating.. here is an interesting tidbit on when we might see the next release of PowerView.  2014??? I sure hope not….as much as I love this technology, there are several glaring holes in it that have to be addressed ASAP if Microsoft were to lead the Business Intelligence quadrant again like it did last year… HTML5, hierarchies, multidimensional model support are just a few items on a long list of opportunities to improve…I am hoping to see something new to come out in Q3 of 2012… if we don’t see it this year, I am afraid that Microsoft will miss a lot of momentum ….

Microsoft SQL Azure labs – interesting new technology for the cloud

There are several interesting concepts that Microsoft is working on available here.  I am currently taking a deep dive into the Microsoft Data Explorer.  However, any B2C company should definitely take a look at the Social AnalyticsData Hub is looking pretty interesting as well…

Columnstore index vs MOLAP

I have been trying to wrap my brain around the various options that Microsoft offers around near real-time analytical experience for the end users.  Clearly, a semantic model is needed to isolate the end from the complexity of SQL/Relational Data Warehouse.  So I see three options to explore:

  1. traditional MOLAP model for Analysis Services
  2. New Tabular mode for Analysis Services (uses xVelocity for compression and in-memory technology)
  3. multidimensional Analysis Services in ROLAP storage mode on top of a relational set of tables that use Columnstore index for compression and in-memory technology

I am particularly intrigued by #1 vs #3.  I have a pretty good data set available for some testing.  Hoping to be able to post some early results tomorrow.

Stay tuned :)

xVelocity and Columnstore Indexes review

Today I decided to take xVelocity and columnstore indexes for a spin and see whether this technology lives up to all the hype.  I tested it on a 2Gb fact table with about 21M rows.  First I wrote a query that joined the fact table with a Customer dimension and returned the total revenue grouped by a customer occupation. On my laptop equipped with a solid state hard drive, the query consistently run in about 10 seconds.  After that I created a Nonclustered Columnstore Index on the fact table (took about 2 minutes to complete with a size of the index at about 10% of the data size).  After the columnstore index was created, the query ran under a second every single time.  I can definitely see that 10x-100x increase in query performance that is advertised by Microsoft.  I am sure that on a larger data set I would probably need to spend a little bit more time tuning my columnstore index performance, but I have to say I was really impressed with the dramatic increase in performance delivered by xVelocity and columnstore indexes.

How to expose Master Data Services in SharePoint

I have been hacking around this for a while trying to expose MDS web application in SharePoint.  Apparently, the trick to remove the non-functional UI elements is to use hosted=true query parameter as in

http://MDSWebAPP:PortNumber/MDS/Explorer/AttributeSL.aspx?MID=10&VID=23&EID=44&hosted=true#/ExplorerEntity?MID=10&VID=23&EID=44&hosted=true

you can find more details on how to do it here

Using Reporting Services Charts with PerformancePoint reports in a SharePoint Dashboard

One of the greatest strengths of SharePoint as it pertains to the world of Microsoft Business Intelligence is its ability to bring together various components of the Microsoft BI stack into a seemless user experience. When it works, it works great, however, when it does not quite work, it gets very annoying.. Recently, I was working on a dashboard mixing various types of web parts – PerformancePoint reports, scorecards and filters, Reporting Services reports and SharePoint Lists…The dashboard worked great until I started linking and filtering all these web parts.  Once the links/filters were added, the reporting services reports started to sporadically disappear from the page which presented a very annoying problem.  Hitting F5 would bring the reports back (eventually), but obviously, this behavior would have been unacceptable for users.  Neither exhausting Binging nor comprehensive Googling yielded any viable options. So, unfortunately, I cannot offer a solution to this problem, however, we did find a work around.  We wrapped the necessary Reporting Services reports inside a PerformancePoint Reporting Services report and then exposed those as PerformancePoint content without using ReportViewer web part.  Now the dashboard works reasonably well, although, now users would have to open the SSRS charts in a new window in order to set up custom data alerts (new feature of SQL Server 2012) or invoide other SSRS actions.

Choosing between PerformancePoint, PowerView, Excel Services and Reporting Services for your data visualizations

Even though PowerView is the only Microsoft Data Visualization tool that is new on that list, it appears that there is some confusion as to how these four options compare with each other.  The topic is pretty big, so it will probably take me a few tries to get it done right, but here is the first shot at the comparison.

There are several angles one might take as he/she tries to compare these four technologies. I am going to examine the following:

  • Authoring
  • End user experience (high tech vs. low tech)
  • Flexibility (in the broadest sense of the word)

Authoring

How technical does a user need to be to build data visualizations? There seems to be two distinct audiences that Microsoft is targeting here.  The first one is technical (IT developers).  This audience is most likely to gravitate towards Reporting Services as its tool of choice. SSRS is fairly simple to use provided the developer has a decent knowledge of SQL or MDX. Developers would create data connections and data sets and then wire them up to various report components.  This process is highly technical in nature which means it will be completely unacceptable for a business power user.  Microsoft also offers its ReportBuilder tools that simplifies a lot of technical aspects for a report developer and allows developers to break up reports into individual report parts which then can be subsequently reused to create new reports, but I would still argue that by and large these sets of activities are well outside of the self-service realm.

Excel Services on the other hand is a very power user-friendly technology.  Those familiar with Excel and PivotTables should take very little time to be able to build very sophisticated reports.  SharePoint 2010 renders Excel reports and dashboards as web pages which makes this technology very easy to deploy.  The IT audience generally does not take Excel Services seriously, which is very unfortunate particularly after the  Excel team has added a number of CUBEXXX() functions that allow users very granular access to the data and report layout.

PerformancePoint sits somewhere in between.  Microsoft has made some claims that this technology is focused on a power user.  Judging from my personal experience, I would say that in principle I could see power users build their content using the tool.  In reality, however, it does not seem to happen that often, particularly after PerformancePoint services became an integral part of SharePoint.  Although I could see a power user build some PPS reports, putting together and configuring dashboards (and linking together filters, scorecards and reports) is too technical and will not be appropriate for a power user.

PowerView is the ultimate self-service BI tool, suitable to both power and not so power users.  Only very basic training to necessary to jump-start the development and the deployment of final visualizations is as simple as saving documents to SharePoint.

End user experience

I would say that there are two trends here.  First is a highly interactive experience that is enabled by PerfrormancePoint content.  Right clicking on a PerformancePoint report gives user a plethora of options (funny fact, plethora sometimes means “too many”, you can read whatever you want into it if you want).  A users who is willing to learn the product and the available data set will be rewarded with incredibly powerful options for analysis such as changing dimensionality and measures on the fly and ability to drill up and slice and dice. Those users who are not willing to learn will be left dazed and confused and are probably going to be frustrated by having too many options.

These right-click haters will be much more comfortable with visualizations developed in Reporting Services or Excel Services.  Although the two are wildly different from the development perspective, in terms of the final content they are fairly similar.  Both allow sophisticated filtering (with Excel Services getting a nod for the new filtering feature -Slicers) but overall, the interactivity is fairly limited.

PowerView adds an interesting angle to this.  On one hand, PowerView charts are not drillable (much like Excel or Reporting Services) on the other hand, PowerView introduces a revolutionary way to filter content by clicking on the desired report element which in turn filters the rest of the visualization components on the selected element.  I find this feature to be extremely powerful, particularly for Low-Tech users.

Flexibility

This section of the review is really about the strengths and tradeoffs between the aforementioned four technologies:

  • SSRS is particularly robust around report distribution options and is probably easiest to scale for a large deployment. It has become comoditized in terms of the skill set
  • Excel Services is a remarkably good choice for a Self Service BI scenarios. The ease of development and the flexibility of the UI options are great.  The greatest drawback is inability to interact with charts and graphs on the Excel Services rendered web page.  Also, if you are trying to delight the end-user with you data visualization, it can be a challenge as the final product at the end still looks very much like Excel
  • PerformancePoint is a great fit for a shop that is trying to integrate structured and unstructured content for a seemless experience. The only real drawback (other than requiring some user training for end users) is the fact that this technology does not seem like it has gotten much love from Microsoft.  Although Microsoft has been very vocal about stressing the strategic importance of PerformancePoint on is BI stack, one can clearly see that PPS has not changed my in five years.  Also there is a very limited ability to customize the look and feel of the reports and dashboards which is very unfortunate.
  • PowerView is a great product. Takes little time to learn and interact, and the visualizations look great. There are, unfortunately, a couple of areas of concern:
    • SilverLight – does not run on anything other than Windows
    • Poor SharePoint integration. PowerView is meant to occupy the entire page which makes it impossible to expose PowerView content along side with other web part in a SharePoint page.