Keep It Simple? Don’t Be Daft!
I was recently working on a project where we were using SSRS to create XML reports which are then imported into Excel to facilitate further analysis.
Unfortunately, I could not see any option in PowerPivot to connect to an XML source. There is the option for text and RSS, but no XML that I can see. I know I could create an RDL and connect to that, but XML is far more than just SSRS.
I raised this with some colleagues, and one of them pointed me to this Microsoft article, Data Feed XML Syntax. I came away shell-shocked from reading this article. I cannot understand Microsoft’s reasoning here. I thought that RSS used XML as its transport protocol because it (XML) was a recognised data transport standard. RSS may use a particular dialect of XML, that is hardly surprising as it has particular needs, but why would anyone choose to use that dialect for its general XML import? We have a universal standard, and a particular implementation of that standard. So what do Microsoft use in PowerPivot but the RSS specific implementation of XML. Surely, it would have made more sense to build an XML import capability, then modify that for the particular implementation that is RSS.
To create a situation whereby we have write code to change the original XML to conform to some other implementation that is totally irrelevant to our problem is madhouse development. To quote that document … Programmers who use other tools or approaches (such as Microsoft generated XML – my words) will need to know how to structure a valid XML response that can be processed by the data feed reader that is built into the PowerPivot for Excel. This is a long way from my idea of self-service BI.
At times I wonder what planet Microsoft inhabits, it often seems to be a different world to mine. You can (easily) import XML into Excel, so they can do it, have done it. I knew we were in trouble as soon I saw SharePoint mentioned in the article, the bane of Office at the moment is that Microsoft seem intent on subsuming it within SharePoint.
Mr Richard (sorry, I cannot use the name he addresses himself by, this blog sees that as a profanity) Moffatt also makes a an interesting read on Office and SharePoint.