The Catch 22 of business intelligence is that to understand your business, you have to deal with vendors who want to “understand your business”, better known as “see how much we can charge you”. You then have to hope that something you don’t know might be really valuable. The most useful results are surprises not suited to the …
... or so I thought until I read the phrase "the data importing wizards resolutely point you at Access".
Anything worthwhile would resolutely point you at Control Panel->Add/Remove Programs->Access.
knock-on effects as usual
So the business decide they want to use PowerPivot to find out some stuff. Okay, cool.
PowerPivot requires Excel 2010. So you spend god-knows-what upgrading the desktops of at least all the BI SMEs. (Yes I know it's supposed to be accessible to everyone, but BI stuff is still usually done by a semi-geek team member for everyone else to use).
The SMEs create the PowerPivot workbooks, then they want to share the results with their colleagues. Assuming that you didn't upgrade *everyone* to Office 2010, you're probably going to use SharePoint, since you can host the content without the other desktops being upgraded.
Hang on, PowerPivot (2010, remember) requires SharePoint 2010. So you upgrade the existing 2007 portal to that, if you didn't already.
Then you notice that the document file size limit in SharePoint 2010 is 2GB. You can set it lower, but you just know that setting users loose on data is going to produce some monster speadsheets (they're bad enough already). Then it occurs to you that the current 1Gb ethernet LAN is busy enough as it is - 10GbE to-the-desktop upgrade anyone?!
Then you realise that all that BI effort to obtain the holy grail - the fabled 'single source of truth', means that these PowerPivot workbooks contain some seriously confidential data. Object-level endpoint encyption it is then...
Or, you could host the Excel 2010/PowerPivot client on Citrix, forget the upgrades to the desktops, ethernet and end-point crypto, stick your fingers in your ears and pretend that the users won't use local budget to obtain Office 2010 and save the data locally anyway...
Reminds me of this:
@dan10 : Of course MS wants you to upgrade this is part of of them addressing the "problem" that most people are happy enough with older versions.
But I think it will only require a very few people to upgrade their LANs, from what I see, most SME LANs aren't that overstressed, though some file severs have had had their upgrades delayed for a bit too long.
You're bang on with with the "truth", and you can go further. Years back I was working on a seriously important project at IBM and had a model that was quite good at calculating when we'd actually reach certain points. Management was not happy because I was a contractor and as "non-IBM professional" it ought to have meant that I wasn't allowed to read my own data. They solved this by ignoring what the model said.
its actually a good toolset
Im looking at deploying this for some of our key power users who do analysis as we can
1) push the powerpivot model they generate into sharepoint and let powerpivot in sharepoint do the work/refresh after that.
2) Via sharepoint control how often data refreshes and ensure that the business isnt full of dead out of data excel spreadsheet in the org.
3) allow our Excel 2007 business users access that data via Excel Services within sharepoint. No need for Excel 2010 on their desktop at all.
4) via this route allows IS to begin oversight and understanding of the data that being hacked by business units.
Also from that stepping point we can then pull a "powerpivot in sharepoint" model and generate a cube from it. This allows the crap excel to fall to the bottom, and the valuable data getting used to eventually be accepted by the IS department and re-engineered correctly. Its not perfect but breaks the bottom down bottlenecks of business waiting for data, also it allows IS to see the sort of data business units would usually hide away on their desktops.
Better to import the data
Than to hand over the keys to a linked in spreadsheet with two way communication. My greatest concern ever while working as a dba was people claiming they needed "live" data, they always used to get data pulled in from a replicated server which needed frequent rebuilding, but at least it wasn't the live db being ground to a halt by "group by" queries. I really do not "get it", this pivot point sound better than most because it doesn't compromise the integrity off the critical systems, but it still seems that business intelligence is based on an unqualified person looking at simplistic data analysis made easier so that they can "prove" their long held beliefs. What was that quote about lies and statistics?