skip to Main Content

Wrapping up the series of Five Things the ‘Pro Wants You to Know (About the Show)

#4 Endless BI Solution Possibilities

Andrew Brust beat me to it on this one, prompting me to add him to my Blogroll at right, but I want to amplify it here:  there really is no end to the BI solutions that can be built on SharePoint 2010.  So many developments surprised me.  Here are a few.

Every data source now has a great API

SharePoint Lists.  Excel Services.  Business Connectivity Services.  Reporting Services reports.  Access Services.  PowerPivot itself.  All of those are now exposing rich API’s for fetching data (the Excel Services session alone blew me away – beefed up Web Services, a brand new REST API, and a brand-new client side jscript OM???).

And you are going to want to fetch you some data.  Because there are so many things you can do with it…

So many visualizations, so little time

Web parts, ok, we’ve always had those, and they are getting better.  But take stock of all of the choices, and suddenly it’s staggering.  Reporting Services.  Excel Services.  Visio Services (another in the “where did that come from” department – web-rendered, data bound Visio graphics, including Map controls).  PerformancePoint Services.  Bing Maps.

Mix and match

I’ve long envisioned a “data highway” in SharePoint, where all data sources (some read-only, some read/write) and all applications plug in, and suddenly you get that “n-squared” benefit of near-infinite mix and match.  After one failed attempt back in Office 2003, I realized the problem was too big for one team to tackle, so I moved on to other things.

Well, it’s become a reality while I wasn’t watching.  I left the conference knowing that we will see solutions on SharePoint which evoke a first reaction of “That’s not SharePoint!” and then a second reaction of “OK, how did you do that???”

And the answer will be surprisingly simple.

#5 SharePoint Will Eclipse SQL as BI Center of Gravity

In a way this is just a summary of my other 4 observations.  But here is how it played out for me personally:

I decided to attend the SharePoint conference kinda at the last minute.  Mostly, it was because I knew SharePoint was important to the PowerPivot story, and it couldn’t hurt to learn more – both about SharePoint and its customers.  I expected it to add just another piece of data to my personal puzzle.  “This won’t be as crucial as a SQL conference for me, but I’ve been to a number of those now…” was essentially my thinking.

Boy was I wrong.  The SharePoint community is where it’s at, folks.  BI pros take note:  there’s a new world opening up to us.  Demand for solutions and consulting has historically grown slowly compared to what is coming.  We’ve all been coming at this from the back end side for so long that we almost forget that it’s the end users, the business units, that drive things.  And the backend BI technologies that many of us deliver are just incredibly distant from the biz users’ problems.  It takes too much explanation.  Not only is it hard for them to see the benefit, but it’s hard for them to offer meaningful input on data architecture as a result.

But show them a picture of a map, with conditionally formatted pushpins indicating the sales growth of those stores, where the expressions are defined in Excel, and suddenly, you have an enthusiastic partner/supporter/collaborator/sponsor.  This is going to be a big change for BI pros, but also for the MS SQL Server team itself.  I think most of my colleagues are due for the same revelation that I am sharing here.

Now, I am not in any way saying that SQL Server is fading away as a BI technology.  What I am saying is, the way we will all be talking about it 3 years from now will be in regards to SharePoint.  In other words, SQL now has the rich front-end complement that it has always needed.  And the front end is the way to reach the all-important business users.

Buckle up.

Rob Collie

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has 11 Comments
  1. “All of those are now exposing rich API’s for fetching data (the Excel Services session alone blew me away – beefed up Web Services, a brand new REST API, and a brand-new client side jscript OM???).”

    Which client would that be? Excel developers should be so lucky. The only document I have on this states:

    “JSOM or ECMAScript (JScript or JavaScript object model) – The ECMAScript enables syndication, mash-ups, automation of Excel Services, and the extension of Excel Services by third parties. It also provides a subset of Excel Web Access functionality that lets an administrator or developer insert JavaScript code on a Web page to affect range navigation, cell values, and other grid operations. The ECMAScript mirrors the Excel Services Web Services API functionality; however, it is not a proxy for this API.”

    No mention of a client side jscript OM.

    Excel Services 2010 must be the best kept secret on the planet. No substantial blog discussions, dead Connect newsgroup…

    One thing is clear though. Excel Services isn’t for the “traditional” Excel developer – you know, those folks who use a lot of VBA, form controls, data validation, Tables etc in their client solutions. In other words, everything that Excel Services doesn’t support (or translate). And for folks who hoped that these things were missing because of the version 1.0 thing, well it’s new clear that they aren’t ever going to be supported.

    So my message to Excel developers (myself included) would be: Wake up, smell the coffee, and if you haven’t already done so, get on board with some Web programming. Why? Because there’s a huge wave coming (called SharePoint 2010) and Excel Services is coming along for the ride.

  2. When I said client-side, I meant local in the browser. Not sure if that clears it up.

    Your point about this Excel Services API being a well-kept secret certainly hits home for me, however. I was incredibly surprised, sitting in John Campbell’s session at the conference. I even leaned forward at one point, to say to two other members of the Excel team sitting in front of me, “where have you guys been hiding this stuff?”

    I’ll try to get someone from the Excel Services team to comment here as well.

    And as to your last point – I agree 100%. I said the same thing to Mr. Excel the other day when we met for lunch. A new wave is coming, SharePoint is going to be the new VBA.

  3. “When I said client-side, I meant local in the browser. Not sure if that clears it up.”

    Yes, thanks.

    “I even leaned forward at one point, to say to two other members of the Excel team sitting in front of me, “where have you guys been hiding this stuff?””

    In orbit probably. Under a rock would have been close enough to unhide some information.

    “I’ll try to get someone from the Excel Services team to comment here as well.”

    That would be useful. It would also be useful if they can comment on their own site as well, assuming that they can contain their excitement! What’s puzzling is that during the Office 2007 beta, there was a lot of Excel Services discussion on the Excel blog. Not a peep this time (so far).

    I’m in the process of reviewing my VBA solutions to see how similar solutions might be built on Excel services.

  4. Hi, this is Joe from the Excel team and the guy who posts on the Excel team blog.

    Colin, fear not, we’ll have a lot to say about Excel Services. In fact we’ll be jumping into it this week on the Excel blog and it will carry forward into December.

    There’s a lot of ground to cover between the work done on Excel client and server. We’ve not been intentionally hiding details from you folks. On the contrary, we’ve tried to structure the content and cadence of our postings such that people have time to discover and absorb all the new stuff, and part of that is discussing these features in logical chunks of areas of investment. We started with visualizations, then talked about performance and structural changes, function and solver improvements, and then covered our work in BI. Next up is Excel Services (with a slight detour through a Chart post I need to put up today), and make no mistake, we are super excited to share this stuff with you.

    The truth is, if you’ve been following along on the blog, you’ve already learned a lot about Excel Services. Most of the functionality discussed in previous posts – e.g. visualizaitons like sparklines, BI features like slicers – apply to the server as well. Perhaps we haven’t been making that point loud enough, but it will definitely get cleared up over the next few weeks.


  5. To whet your appetite, here’s a sampling of the stuff we’ll be talking about regarding Excel Services and Excel Web App on the Excel blog at over the next few weeks:

    – Improvements in dashboarding, like “scratchpad” editing
    – Improved support for loading more of Excel’s features
    – Improved publish experience from the Excel client
    – The new REST API – a simple URL-based way to retrieve data from workbooks
    – Improvements to our Web Services API
    – New Javascript Object Model – enables client(browser)-side programming for cool mash-up scenarios
    – PowerPivots on the server (this was already covered recently, but we’ll likely post an additional article about the admin side of things)
    – New spreadsheet editing capabilities, and the subset of Excel’s functionality that comes with it. Also, multi-user, collaborative editing!

    I look forward to hearing your feedback on these topics.


  6. Hi Colin:

    I was the program manager on the Excel Services team who helped design and build the new and updated APIs that will ship with Excel Services 2010. I will be publishing a number of articles on the Excel Services blog as Joe mentioned above – and also some additional in-depth articles on my own web site (to come in the next few days/weeks). Do you have suggestions, requests or particular scenarios in mind (e.g. regarding the brand new Excel Services JavaScript Object Model) that I could address?

  7. I’d love to hear some solutions for those of us who work for companies that support tons of users who aren’t in our AD structure. How does Sharepoint factor in to that? How can we start using these technologies when we are expected to license Sharepoint for 50k or more users that change based on who our customers set up? The technology looks really cool, but the longer Sharepoint remains central to MS reporting solutions, the more likely we are to switch. It pains me to see the really cool features that MS develops that are so focused on internal customers when so much other technology is moving towards the cloud and delivering data to remote users.

    Don’t get me wrong – the technology looks cool and I hope to be able to leverage part of it, but I really feel that MS has turned a deaf ear to customers who work as ASPs or deliver SaaS. When your database contains the data for all sorts of customers mixed together, but secured to them, it’s harder and harder to work with MS Technology.

    Some great examples of how to deliver some sort of partitioned data – using to some extent the internal security that is somewhat common within applications – would be really, really appreciated. A way to build a custom middle-tier that could mask this to the users while allowing a login to be passed would be even better.

    I normally wouldn’t have felt a need to comment, but I think that MS has been pushing so hard for Sharepoint as a necessary component for reporting that they’ve neglected people who just can not use it to deliver a reporting solution. That drives us further away more than it gets us excited. I know that each time MS has a BI announcement, I have a hope that we’ll have been taken into account. Almost every time, the presenter drops the Sharepoint bomb on us and I know that we won’t be able to use that new technology either.

  8. Peter, is it still the key issue that you are looking for a solution that does not require your end users to be in AD? SharePoint can handle that using forms based authentication and an appropriate provider.

    1. It’s a combination of things. We have ~ 50-60k users that are completely defined by our clients. We also start having data-driven security issues (i.e. tables that tell the users what data they can/cannot see based on the internal user table). I don’t see a good way of merging the two of those in a cost-effective manner to allow people access to anything based on Sharepoint. The technology looks really cool, but we start getting into a lot of troubles when we try to extend this beyond the domain.

      I’m definitely open to seeing how we can do this with the appropriate providers. I’d also love to hear how people use data tables/views/procs to limit what information people can see. That’s _got_ to be doable somehow without depending completely on SQL permissions, right? 🙂

      I’ll admit to some ignorance about Sharepoint for external users. I don’t know much about licensing, scalability, or how to handle permission mapping. If we could have a reasonably priced license, decent scalability, and a way to definitively handle permission mapping, this might be doable. However, we work with a multi-tenant model as a SaaS and we often get responses like “you want to do what?” or “wow – I didn’t know you could do that” when we chat with most MS people. 😀

  9. Sounds like there are two core issues here
    1) Securing SharePoint
    2) Securing your BI Datastore
    3) Glueing 1 and 2 together 🙂

    I think it would be extremely hard to come up with a viable solution, plan, etc to “Attempt” to tell you how to do it utilizing SharePoint via blog comments :). As every customer is unique, and every base problem scenario can be unique as well.

    We do this sort of thing quite often in our consulting practice, drop me an email if you like to talk more.

    – Keith

Leave a Reply

Your email address will not be published. Required fields are marked *