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.
Mr. Richard sounds SO formal ……….
I have been “xxxx” Moffat and (censored) Moffat already so one more wouldn’t hurt my feelings.
I have been thinking about this and might be forced to evolve from being a D**k to a Rich. I am just not a Richard 🙁 but it may have to do. Too bad.
Thanx for the link Bob.
Dick
p.s. Actually my middle name is Robert – maybe I could become a Bob ?? Nah …. taken
You could be Rich Bob, I often get referred to as poor Bob.
“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” – huh?
An easier approach for Office people is to use Access to connect to an XML source. Then get Power Pivot to consume the Access source.
Microsoft forgot long ago that Office is an integrated suite of applications, but we don’t have to forget, do we?
But Ed, that is still going around the houses, two steps to go forward one. I might just as well forget the SSRS, construct an SQL query in PP directly against the warehouse and use that. That solves this problem, but doesn’t give me a simple, universal XML import facility in PP.
What is wrong with MS doing it properly?
Couldn’t you use ADO to import it and then create the pivot table from the recordset? I know that’s not the nice one step solution that it should be but it seems to be a straight forward solution that keeps the data in memory.
There are any number of ways of circumventing it, but why should we need to? It also means that I don’t get the other aspects of PowerPivot that I want top use.
MS jumped into XML with their size 9s some time ago and we were all exhorted to use XML, XML is the application independent way to transport data. Okay, so why don’t they subscribe to that mantra?
I get you Bob.
PP is about data,
Importing is a basic function
XML is a “mega standard”
Why the f### would you not support it out of the box
The issue is with the thinking from the PP devs, not finding a workaround.
Basic functionality is missing, the product is not fit for purpose. Well at least its free 😉
Also see, how copy/paste did not ship on windows phone 7. WHAT?! who made that decision?!?
Having said that, I’m never surprised when any type of file needs work before an “import” , well or after the import. XML is just another CVS file, yeah it self descriptive, but that don’t mean much in the real world! In fact because xml is such a big file, compared to text, it often costs a lots more to transmit, and offers no extra value.
Exactly Ross.
XML is a big disappointment to me. When I first came across it I envisaged it being a universal data wrapper, such that you could open an XML file in Excel and you see it presented one way, in Word another, in SQL Server another. I appreciate this can probably be achieved with schema and the likes, but all I see is it being used as a so-called ‘standard’ file format. I would avoid it if I could as like you I see no benefit.
It’s appealing worth enough for me. In my view, if all website owners and bloggers made good content as you did, the web will be a lot more helpful than ever before.
So does anyone have a workaround? I looked at the first link in the original post, but the target has been removed.