Advice: Best tool to use for this data presentation RRS feed

  • General discussion

  • We are implementing SharePoint 2010 in our organisation and I've been asked to look at getting some BI content, since I have SQL Server & Reporting Services knowledge.  My head has been soaking up information for the last couple of weeks as I look into it and try out many scenarios, read blogs/tutorials.  

    There are so many ways to skin Sharepoint BI, and I've been getting to grips with Dashboard Designer and rediscovering my knowledge of Analysis Services!  I have been given an idea of the kind of thing management would like.  Management want somthing like this (which they mocked up in Excel), essentially all their KPI's rolled up into a simple summary screen.

    My initial remit is to deliver something like this, even if it's powered by manually entered data.  Then start looking to power it by real data afterwards, as that will be a huge task, they want something soon, and the detail to follow.  Given that most of those KPI's come from different systems, I will need to do some heavy work afterwardss in SSAS.

    What I've tried to get something similar so far is scorecards, which I like, but see to lack the flexability over their formatting.  I have been using a cube to show them the potential BI within our ICT Helpdesk data, using Scorecards, charts, etc.  But ultimately nothing that 'rolls up' to this sort of summary level.

    I also have a feeling this next part of the question transcends PerformancePoint and touches on SSAS & DW structure.  The summary that they have asked for takes data from many systems (our IT Helpdesk database, Training database, Time-loggingdatabase).  I have currently created a cube which shows only the helpdesk data, I was going to create seperate cubes for the other systems/databases, is that right or wrong?  Should I infact be creating one large Cube which combines all those areas?

    I keep looking at the options, but there are so many ways to skin it that it's mind boggling for someone new to this area.  So I'm after advice on what part of PerformancePoint or what to tool to use to head in the right direction.

    Many thanks for reading, I hope you can help.


    Wednesday, July 18, 2012 10:13 AM

All replies

  • You are right about one thing, there are too many technologies available to do one thing and starting up it is a pain to pick one.

    Taking one step at a time:

    For sure you would need an OLAP cube no matter which Reporting tool you later on opt for. SSAS cube would allow you to utilize any reporting tool: SSRS, PerformancePoint, Excel services, PowerPivot etc. (plus 3rd party tools)

    Personally I would use one cube rather than using multiple small cubes; reason: one is always easier to maintain. But it depends on your requirements if all the other source systems share common measures/dimensions it would be best to use one. 

    Initially you would be spending most of the time with SSIS for ETL operations, mapping and transforming data into one data warehouse. This would be the backbone of your solution, quality of data makes or breaks an implementation.

    Finally once your data is consolidated and ETL working. You may pick any reporting tool of your liking or use all combined. PerformancePoint would have an edge if you are looking for scorecards, interactive reports and fast development.

    Hope it helps.

    Wednesday, July 18, 2012 2:26 PM
  • Thanks for that reply, it does help with regard to the cube design.  But the main thing I still am very unsure about the best tool to display the data though.

    On thing I have noticed is that the Scorecard seems very inflexible on layout and formatting.   Are there ways to do so?

    To get something 'Up' for management, I have tried creating a simple Scorecard, based on a SharePoint list, to do the above, but you can't have some as %'s and some as numbers/decimals, it looks very messy.

    Any suggestions?


    Tuesday, July 24, 2012 3:02 PM
  • Correct. Scorecards are not very flexible in layout and formatting out of the box. But on the other side they provide you with context menu for drill downs and rapid development platform. The thing about PerformancePoint is you can build a complete dashboard in couple of hours which on any other tool would take you a day or two.

    In the same column (metric) you can one formatting.

    If you rule out Scorecards the next closest thing would be SQL Reporting Services reports; full control on formatting/layout but no interactive support out of the box and fair enough development required. You would need to build your own actions and drill down reports, when/if needed.

    Wednesday, July 25, 2012 3:05 PM
  • In looking at your scorecard you could quite easily setup something very similar in PPS with Dashboard Designer and your groups top levels (Objective) would be Performance, Customer, and Staff.  Wouldn't be hard to do at all.

    You should take a look at the following white paper that really does a good job on outlining the different Microsoft BI tools and what you can do with them - http:/

    Devin Knight also did a good 6 part blog series in regards to this with his point of view as well -

    If you are looking for the most flexibility and layout SSRS is probably going to be the route to go.  It will require more work versus PPS and you will have to code any additional functionality like Drillthrough (Show Details) and you won't get the Decomposition Tree functionality or built-in scorecarding features out-of-the-box.  Another option might be to explore some of the PowerPivot setup now with the SQL 2012 version since it has KPI support.  This would be a better option right now for cubes and PPS supports this (you can upgrade to Tabular SSAS when you are ready).  If you look at Excel 2013 you will also get Power View and the newer version will support KPIs as well.  Lots of things to consider, but take the time to see what features are needed by the users and the layout that is required.  Once you have that you can review the options to determine the right tool.

    Good luck!

    Dan English's BI Blog

    Thursday, August 2, 2012 12:14 AM