I’ve just been told that fieldwork has begun on the BI Survey 9; if you’d like to participate you can find all the details below.

Full disclosure: by posting this here I’ve been promised a free copy of the research when it’s published – and I promise to blog the juicy details (as I have done in the past) when I get it.

The BI Survey 9: The Customer Verdict

We would very much welcome your participation in 'The BI Survey 9: The Customer Verdict', the world's largest survey of business intelligence (BI) and performance management (PM) users (formerly known as The OLAP Survey).

As a participant, you will:

- Receive a summary of the results from the full survey

- Be entered into a draw to win one of ten $50 Amazon vouchers

- Ensure that your experiences are included in the final analyses.

To take part in the survey on-line, visit:

http://digiumenterprise.com/answer?link=249-KP9DYABR

BARC's annual survey obtains input from a large number of organizations in order to better understand their buying decisions, the implementation cycle and the business benefits achieved.

Both business and technical users, as well as vendors and consultants, are welcome to participate. If you are answering as a consultant, please answer the questions (including the demographic questions) from your client's perspective; we will ask you separately about your own firm.

The BI Survey has always adopted a vendor-independent stance. While vendors assist by inviting users to participate in the Survey, Business Application Research Center (BARC) - the publisher - does not accept vendor sponsorship of the Survey, and the results are analyzed and published without any vendor involvement.

You will be able to answer questions on your usage of a BI product from any vendor. Your answers will only be used anonymously, and your personal details will never be passed on to vendors or other third parties.

* BARC (Business Application Research Center) is a leading independent software industry analyst specializing in Data Management and Business Intelligence. For more information on BARC please visit The BARC website and www.BI-Verdict.com.

Today Google announced an interesting new product: Google Squared. Here are some links:
http://www.theregister.co.uk/2009/05/13/google_squared/
http://www.computerworld.com/action/article.do?command=viewArticleBasic&articleId=9132972
Basically it will return data from search results in spreadsheet format. And of course, when you’ve got data in Google spreadsheet format you can do all kinds of cool stuff with it, like stick Panorama’s pivot table gadget on top of it.

This, plus moves towards support of RDFa also announced today:
http://www.techcrunch.com/2009/05/12/google-takes-steps-towards-a-more-structured-web/
means that there’s going to be some really interesting possibilities for doing BI direct from data sourced from the web.

Oh, and let’s not forget about Wolfram Alpha, also coming soon and equally exciting from a web/data/BI point of view. Imagine, instead of it being able to tell you things like the distance between the Earth and the Moon right now, having your business modelled in it and then letting end users query this model using a search-engine interface.

In part one of today’s ‘Interesting BI-related stuff I saw on the web today’ posts…

After MDM finally reared its head, it seems like Microsoft is working on some kind of metadata tool as well:
http://www.tdwi.org/News/display.aspx?ID=9434
More news later this year apparently. Interesting comment:
”He did disclose that Microsoft's still-percolating metadata management effort will encompass both its MDM and search assets.”

AND it seems like Microsoft is entering the Complex Event Processing market:
http://www.biztalkgurus.com/blogs/biztalksyn/archive/2009/05/11/microsoft-announces-entry-into-complex-event-processing-cep-market.aspx
Since other CEP vendors support some kind of OLAP on top of their data (eg SQLStream/Mondrian, Aleri) I wonder if Microsoft have a story for SSAS and CEP?

UPDATE: more details on MS CEP here:
http://www.dbms2.com/2009/05/13/microsoft-announced-cep-this-week-too/

So the announcements are starting to flow at TechEd – for instance, Microsoft’s long-awaited master data managment solution, now called Master Data Services, will be available as part of the SQL2008 R2 (what was known as Kilimanjaro) release. More details on this and other BI-related features can be found here:
http://www.microsoft.com/sqlserver/2008/en/us/R2.aspx

Looks like SSRS will be getting some new stuff - certainly the collaboration features brought in by the 90 Degree Software acquisition look like they’re going to be added to Report Builder. Perhaps we’ll finally see the Officewriter/SSRS functionality too?

UPDATE: one other thing, mentioned by Teo here:
http://prologika.com/CS/blogs/blog/archive/2009/05/11/transmissions-for-teched-usa-2009-day-1.aspx

Gemini will be able to source data from SSRS reports, and SSRS will be able to expose data as 'data feeds' (ie have a new RSS/ATOM rendering extension?).

UPDATE #2: Rob Kerr has a very good write-up and analysis of what was shown of Gemini here:
http://www.robkerr.com/post/2009/05/Microsoft-BI---Gemini.aspx

It looks like there's been the official announcement of a feature I've heard rumours about, namely that Gemini will have its own language for defining multidimensional calculations called DAX. As Rob says, it'll be interesting to see whether it suffers the same fate as that other attempt to simplify MDX, PEL...

I was reading the Guardian (a UK newspaper) online today and saw that they have just launched something called Open Platform, basically a set of tools that allow you to access and build applications on top of their data and content. The thing that really caught my eye was the Data Store, which makes available all of the numeric data they would usually publish in tables and graphs in the paper in Google Spreadsheet format. Being a data guy I find free, interesting data irresistible: I work with data all day long, and building systems to help other people analyse data is what I do for a living, but usually I'm not that interested in analysing the data I work with myself because it's just a company's sales figures or something equally dull. However give me information on the best-selling singles of 2008 or crime stats for example, I start thinking of the fun stuff I could do with it. If you saw Donald Farmer's fascinating presentation at PASS 2008 where he used data mining to analyse the Titantic passenger list to see if he could work out the rules governing who survived and who didn't, you'll know what I mean.

Given that all the data's in Google Spreadsheets anyway, the first thing I thought of doing was using Panorama's free pivot table gadget to analyse the data OLAP-style (incidentally, if you saw it when it first came out and thought it was a bit slow, like I did, take another look - it's got a lot better in the last few months). Using the data I mentioned above on best-selling singles, here's what I did to get the gadget working:

1. Opened the link to the spreadsheet: http://spreadsheets.google.com/pub?key=phNtm3LmDZEP4i_r7RdVhUg
2. Followed the link at the very bottom of the page to edit the page.
3. On the new window, clicked File/Create a Copy on the menu to open yet another window, this time with a version of the data that can be edited (the previous window contained only read-only data)
4. Right-clicked on column J and selected Insert 1 Right, to create a new column on the right-hand side.
5. Added a column header, typed Count in the header row, and then filled the entire column with the value 1 by typing 1 into the first row and then dragging it down. I needed this column to create a new measure for the pivot table.
6. Edited the 'Artist(s)' column to be named 'Artist' because apparently Panorama doesn't like brackets
7. Selected the whole data set (the range I used was Sheet1!B2:K102) and then went to Insert/Gadget and chose Analytics for Google Spreadsheets. It took me a moment to work out I had to scroll to the top of the sheet to see the Panorama dialog that appeared.
8. Clicked Apply and Close, waited a few seconds while the cube was built, ignored the tutorial that started, spent a few minutes learning how to use the tool the hard way having ignored the tutorial, and bingo! I had my pivot table open. Here's a screenshot showing the count of singles broken down by gender and country of origin.

Of course, this isn't the only way you can analyse data in Google spreadsheets. Sisense Prism, which I reviewed here a few months ago, has a free version which can connect to Google spreadsheets and work with limited amounts of data. I still have it installed on my laptop, so I had a go connecting - it was pretty easy so I won't go through the steps, although I didn't work out how to get it to recognise the column headers as column headers and that polluted the data a bit. Here's a screenshot of a dashboard I put together very quickly:

Lastly, having mentioned Donald Farmer's Titanic demo I thought it would be good to do some data mining. The easiest way for me was obviously to use the Microsoft Excel data mining addin: there are two flavours of this: the version (available here) that needs to be able to connect to an instance of Analysis Services, and the version that can connect to an instance of Analysis Services in the cloud (available here; Jamie MacLennan and Brent Ozar's blog entries on this are worth reading, and there's even a limited web-based interface for it too). Here's what I did:

1. Installed the data mining addin, obviously
2. In the copy of the spreadsheet, I clicked File/Export/.xls to export to Excel, then clicked Open
3. In Excel, selected the data and on the Home tab on the ribbon clicked the Format as a Table button
4. The Table Tools tab having appeared on the ribbon automatically, I then pressed the Analyze Key Influencers button
5. In the dialog that appeared, I chose Genre from the dropdown to try to work out which of the other columns influenced the genre of the music
6. Clicked I Agree and Do Not Remind Me Again on the Connecting to the Internet dialog
7. Added a report comparing Pop to Rock

Here's what I got out:

From this we can see very clearly that if you're from the UK or under 25 you're much more likely to be producing Pop, Groups are more likely to produce Rock, and various other interesting facts.

So, lots of fun certainly (at least for a data geek like me), but everything I've shown here is intended as a serious business tool. It's not hard to imagine that, in a few years time when more and more data is available online through spreadsheets or cloud-based databases, we'll be doing exactly what I've demonstrated here with that boring business data you and I have to deal with in our day jobs.

One of my new year's resolutions - or at least, something that got added to my list of stuff to do in the unlikely event I've got some time spare and can be bothered - was to learn more about statistics. I've only got a very basic grasp of the subject but, like data mining, it's one of those things that seems to promise to be incredibly useful in my line of work. However it's interesting to ponder that I've been working in BI for almost a decade and never so far needed to learn much beyond basic stats; my theory is that stats, like data mining, only tends to be used by highly skilled quantitative analysts, whereas the people I work with are business people whose maths skills are very average and who quite rightly don't trust analysis done using methods they can't understand.

Anyway, in my browsing on the subject I came across the all-of-a-sudden popular topic of R (see http://www.r-project.org/), the statistical programming language. I thought it might make an interesting blog entry, but today I saw John Brookmyre beat me too it so I'll just link to him instead:
http://blogs.conchango.com/johnbrookmyre/archive/2009/01/14/are-you-r-ing-yet.aspx

I also got interested in learning about F#, the functional programming language that will be included in VS2010 (for a good overview, see http://www.developer.com/net/net/article.php/3784961). I was struck by some similarities with MDX and began to wonder about how it could be applied to BI; and yet again, a quick Google revealed Aaron Erickson had had the same idea and blogged extensively and intelligently on the subject:
http://blog.magenic.com/blogs/aarone/archive/2008/04/23/On-Business-Intelligence-and-F_2300_.aspx
http://blog.magenic.com/blogs/aarone/archive/2008/09/07/F_2300_-Business-Intelligence-Case-Study-_2D00_-XBox-Live-Trueskill.aspx
http://blog.magenic.com/blogs/aarone/archive/2008/12/11/One-step-closer-to-F_2300_-for-Business-Intelligence.aspx

It'll be interesting to watch the uptake of F# in BI; from what I can see there's already a lot of activity in the area of data manipulation and stats for F# (see for example Luca Bolognese's blog) and I'm sure it's only going to grow. The only complaint I've got is that here's yet another addition to the Microsoft BI toolset and I'm yet to be convinced there's any kind of company-wide strategy aimed at shaping all these tools into a coherent BI strategy. F# won't be the language of BI in the way that Aaron wants; it's more likely to end up as a technology island in the way Aaron specifically doesn't want. But hey, the .NET guys have arrived at the party! The more the merrier.

Interesting press release from Netezza here:
http://www.netezza.com/releases/2009/release010609.htm

At the moment it only looks like there's an OLE DB provider available, but the release says this is only the first part of the strategy. I wonder if Netezza is being considered as a supported data source for Analysis Services so it could be used with cubes in ROLAP mode, as with Teradata today?

Just had one of my occasional looks at Richard Tkachuk's site, http://www.sqlserveranalysisservices.com and there's some interesting new information on the home page. There's an article on how to handle time intervals in AS (I've got some ideas on a different way of handling this, but I'd need to test them out to see how they perform), a note on how the Aggregate function works with calculated measures, and a draft of the AS 2008 Performance Guide that is a must-read:
http://sqlcat.com/files/folders/401/download.aspx

So the SQL marketing people have put together a website to plug SQL2008 share the SQL Server 2008 experience:
http://www.sqlserverexperience.com/

Actually, there are some quite interesting (in a Channel 9 sort of way) videos up there in the 'For Developers' section, including a few interviews with members of the AS dev team. Worth a look.

As you may have already seen, Microsoft has just announced it has closed its acquisition of DATAllegro:
http://www.microsoft.com/presspass/press/2008/sep08/09-16DAPR.mspx?rss_fdn=Press%20Releases

More details will be forthcoming at the BI Conference, CTPs will be available in the next year or so and the product is slated for release in the first half of 2010 (for SQL2010 then?). It'll be interesting to see what form it actually takes exactly. As I've said before I suspect it would be easier to get Analysis Services working with this technology than many people think, and I also still wonder whether MS might also be thinking about buying or building a column-oriented database too.

As a counter to this euphoria, here are some interesting links... First, take a look in the discussion in the comments here:
http://www.dbms2.com/2008/07/24/how-will-oracle-save-its-data-warehouse-business/
Plenty of people with an axe to grind, I'm sure, but some points worth considering. Also worth checking out for a dissenting point of view is Kevin Closson's Oracle blog, which discusses DATAllegro a few times, for example:
http://kevinclosson.wordpress.com/2008/07/07/i-know-nothing-about-data-warehouse-appliances-and-now-so-wont-you-part-ii-datallegro-supercharges-fibre-channel-performance/

It's always good to take the blog-equivalent of a cold shower before getting too worked up about a new feature or technology. I'm sure that the guys at MS have done their due diligence on DATAllegro, and that come 2010 we'll have a solution that is way, way more scalable than what we have right now, but let's also set our expectations appropriately - it is going to be a version 1.0, and the competition isn't going to stay still in the next two years either.

Here's the press release from Kalido:
http://www.kalido.com/5a04ea36-c50e-4617-b25d-85ea56a22690/news-and-events-press-center-press-releases-detail.htm

I don't have any direct experience with Kalido's products (although I've heard good things) but I'd be interested to see the cubes it generates. I wonder to what extent it's possible to optimise automatically generated cubes?

For your listening pleasure: the latest edition of the SQL Down Under Show is me talking to Greg Low about MDX and Analysis Services. You can download it here -

http://www.sqldownunder.com/PreviousShows/tabid/98/Default.aspx

Is it that time of year again? Yes, here's the link for the latest BI Survey:
http://www.eu-survey.com/BI_8/BI.asp?lan=1&lin=23

Here's the blurb:

We would very much welcome your participation in The BI Survey, conducted annually by Nigel Pendse. This is the largest independent survey of OLAP users worldwide. The Survey will obtain input from a large number of organizations to better understand their buying decisions, the implementation cycle and the business success achieved. Both business and technical users, as well as vendors and consultants, are welcome.

The BI Survey is strictly independent. While vendors assist by inviting users to participate in the Survey, the vendors do not sponsor the survey, nor influence the questionnaire design or survey results. You will be able to answer questions on your usage of a BI product from any vendor. Your data will only be used anonymously, and no personal details will be passed to vendors or other third parties.

As a participant, you will not only have the opportunity to ensure your experiences are included in the analyses, but you will also receive a summary of the results from the full survey. You will also have a chance of winning one of ten $50 Amazon vouchers.

I see Google have announced their own web application platform:
http://www.techcrunch.com/2008/04/07/google-jumps-head-first-into-web-services-with-google-app-engine/#comments
which includes BigTable as part of it (remember this post?). There's speculation over whether Microsoft has something similar up its sleeve:
http://blogs.zdnet.com/microsoft/?p=1320

Possibly a little early to blog as this startup seems to be a while away from RTM, but it looks cool and BI as a service is very topical...

Yesterday a guy called Roman Stanek linked to my blog, so naturally I checked out his blog and his company:
http://www.gooddata.com/

What they've got is a "a complete, on-demand business intelligence platform combining analytics, reporting, data warehousing and data integration". The workflow seems to be that you upload your data, then you have an online environment where you have OLAP and collaboration tools; similar, I guess, to what Panorama are working on with Google docs? I guess they're going to be making their money designing the initial data warehouse/ETL/OLAP design for each customer as well as subscription costs?

This raises some fundamental questions about BI as a service in my mind:

• Will companies be willing to upload their most valuable and secret data to a third party on the web? If they're willing to use online data stores like Amazon S3 or SQL Server Data Services or other hosted solutions then I suppose so, but that's a big if - and these online data stores aren't necessarily positioned to hold the kind of ultra-sensitive data that you work with in a BI system.
• However complete a platform you build, the old adage that all data ends up in Excel needs to be considered. Remember it's Excel I said here, not any old online spreadsheet. Will the guys in Finance abandon Excel to do spreadsheets on the net? I'm not sure. This is where Microsoft have a locked-in advantage, but it's not an insurmountable problem for BI service-providers. Why not expose an XMLA interface for your hosted OLAP and let people connect to it directly from Excel? It's possible for Analysis Services and SAP BW, and I'm sure companies like Simba will be only too happy to Excel-enable other OLAP tools.
• Unless BI-as-a-service companies let customers or partners design and build their own solutions on their platform, will these companies be able to scale out to meet the demand of designing enough apps for potentially hundreds of customers? That's a lot of BI projects for any one consultancy to run. More importantly, will they be able to do an adequate job of designing each app? One of the arguments that's always used against outsourcing BI projects is that face-to-face contact between designer and business users is essential, and that's a view I tend to agree with.

Clever old Panorama... just when you thought that it was about time for them to roll over and die, having been jilted by Microsoft and SAP, they team up with Google to do BI. From their blog, here's the press release:
http://www.panorama.com/blog/?p=98
and more information is here:
http://google-pivot-tables.blogspot.com/
and there's a tutorial here:
http://www.panorama.com/google/pivot-table/tutorial/

This is part of the wider Google announcements on Google Gadgets:
http://googledocs.blogspot.com/2008/03/collaboration-goes-one-level-deeper.html
and the Google Visualization API:
http://google-code-updates.blogspot.com/2008/03/introducing-google-visualization-api.html
http://code.google.com/apis/visualization/documentation/gadgetgallery.html

I need some time to digest this, but my first impression is that this is BIG. The PowerApps stuff sounds very interesting indeed.

One of the benefits of being a blogger is all the free stuff that gets sent your way by people looking for some publicity. Unfortunately in my case the stuff I get isn't all that sexy, ie no free XBoxes, but it's still interesting - the books are always welcome, the free licences for AS client tools are useful too, and yesterday I got a freebie copy of the results of Nigel Pendse's "BI Survey 7" with an invitation to blog about the contents. Now I'm sure over the next few weeks you'll see the marketing machines of all the BI vendors crank into action, cherry-picking the findings so they can say things like "XSoft is the most reliable BI platform" and "YWare offers the best query performance" etc etc. But what does it actually have to say about Analysis Services?

Firstly, some characteristics of Analysis Services deployments. This year 34% of respondents were still using AS2K, with 66% now on AS2005 (with no-one using OLAP Services any more). This is almost a complete inversion of the usage figures from the previous survey, indicating most people migrated from AS2K to AS2005 in the last year. This is certainly what I've seen in my consultancy work, but I'm still surprised so many people have migrated given that the two platforms are so different. Another interesting point made is that while you'd expect AS to be the dominant OLAP tool when SQL Server is the data source, it's also the most common OLAP tool for Oracle, Sybase and DB2 sites and it comes in second (behind Microstrategy) for Teradata. Oracle I can understand, because I work with Oracle data sources as much as I work with SQL Server (though in the past I used to work more often with Oracle, in fact) but I've never yet worked with anyone using DB2 or Teradata and only one person using Sybase, which isn't in fact officially supported as a data source for AS. Maybe I don't get around enough. I bet this is uncomfortable reading for Oracle, especially, since the only Oracle BI product that features in the top ten BI tools used against an Oracle data source is Essbase and that's at #10.

One of the things that people often bring up about AS is that people only use it because it's bundled free with SQL Server. That's certainly a big part of why it's chosen, and the survey shows it's very common that when it's used it doesn't go through a formal evaluation process or that it's the only product evaluated. But that certainly doesn't explain all the Oracle, IBM and Sybase shops that use it and the survey also shows that when a formal, multi-product eval is conducted then AS wins 75% of the time - putting it ahead of all of its major competitors. Again, that's consistent with my experience: I was working with a customer recently where they'd done an eval comparing AS with Oracle's OLAP option and Essbase, and neither of the latter could handle the data volumes and dimension sizes that AS2005 could.

Regarding the problems faced by BI projects, it's not surprising that slow query performance is the most common across the board for all products. AS is slightly above average in that 20% of respondents complained about this (maybe they need to get a specialised consultant on board to do some tuning? Now, who could do a job like that?); TM1 does the best at only 6.6%, SAP BI does worst at 37.5%. One thing that AS does worse on than average is 'Security Weaknesses', which to be honest is a bit strange given that I've never found any holes in the product that would lead to values being shown to people who shouldn't see them. What I suspect is happening here is that although AS has a great set of options for security it's still way too difficult to configure, especially for complex scenarios (see here for my experiences, although I wonder whether other products could meet these requirements at all) and for large numbers of users. There's a real need for a better, more end-user friendly interface for managing roles, and perhaps it would be good to be able to use dynamic security right out of the box rather than have to implement it manually.

Lastly, there's a good section on the client tools used with AS. I cannot believe that as many as 6.6% of people are still using Data Analyzer as a client tool - what planet are they living on? The high percentage using pivot tables I can understand, and there's a very long tail of people using tools that have only got 0-4% of market share. The survey makes the interesting point that fewer AS users use any kind of Excel front-end than users of TM1, Essbase or SAP BI; maybe that'll change as more companies move to Office 2007.

Anyway, hopefully I won't get into trouble for divulging too many details but I can honestly say it's a fascinating read and at 420 pages long there's a lot that I haven't mentioned here, and it's more substantial than a lot of IT books. If you're a vendor of any kind I'm sure you'll be buying this anyway, but I'd also recommend it if you're a consultancy (lots of juicy facts to quote to prospective customers to trash the competition) or you're about to embark on a BI project in-house. Overall, AS comes out of it very well which is obviously good news for me and I guess the majority of people who read this blog. Of course Nigel has long been very positive about the MS BI stack, much more so than other analysts although Gartner has suddenly got very positive too. I don't know about anyone else, but sometimes I get a bit miffed by the way some BI journalists talk about Cognos and Business Objects as serious platforms but never mention Microsoft at all... hopefully that's starting to change.