Session recordings from SQLBits 8 and TechEd

The recordings of the sessions from the SQLBits 8 in Brighton are just starting to come online, so if you’re bored at work you may want to take a look at them here:

We’ve now got over 200 hours of great SQL Server video content on the site, including lots of SSAS-related sessions. It is, of course, free to view and download with no registration required…

Similarly, the session recordings for TechEd are also available, and again there’s lots of interesting BI content there too. If you’re interested in learning more about the future of SSAS and BISM, the session on “What’s new in Denali for Analysis Services and PowerPivot” is a must-see.

Microsoft BI Labs

Hmm, look at this – a new Microsoft BI site called “BI Labs”:

The welcome video explains it’s a home for experimental or internal tools that aren’t supported products but still good enough to share. There’s some stuff on there that I’ve seen before, such as the Pivot Viewer control, but also an MDX and DAX formatter:

BI Survey 10

The fieldwork for the BI Survey 10 (the world’s largest survey of BI and performance management users) is kicking off, and you can take part by clicking on this link:

Business and technical users, as well as vendors and consultants, are welcome to take part; you’ll get the chance to win one of 10 $50 Amazon vouchers if you do.

UPDATE: the deadline has now been extended to June 18th.

Microsoft Technical Computing Initiative

An interesting announcement here from Microsoft about its new Technical Computing Initiative:

Lots of the usual PR-speak and vagueness, but from the post above here are the main points:

In terms of technology, the initiative will focus on three key areas:

  1. Technical computing to the cloud: Microsoft will help lead the way in giving scientists, engineers and analysts the computing power of the cloud.  We’re also working to give existing high-performance computing users the ability to augment their on-premises systems with cloud resources that enable ‘just-in-time’ processing. This platform will help ensure processing resources are available whenever they are needed—reliably, consistently and quickly. 
  2. Simplify parallel development: Today, computers are shipping with more processing power than ever, including multiple cores. But most modern software only uses a small amount of the available processing power. Parallel programs are extremely difficult to write, test, and troubleshoot.  We know that a consistent model for parallel programming can help more developers unlock the tremendous power in today’s computers and enable a new generation of technical computing. We’re focused on delivering new tools to automate and simplify writing software through parallel processing from the desktop… to the cluster… to the cloud.    
  3. Develop powerful new technical computing tools and applications: Scientists, engineers and analysts are pushing common tools (i.e., spreadsheets and databases) to the limits with complex, data-intensive models. They need easy access to more computing power using simpler tools to increase the speed of their work, and we’re building a platform with this objective in mind. We expect that these efforts will yield new, easy-to-use tools and applications that automate data acquisition, modeling, simulation, visualization, workflow and collaboration.

And from this article on the Wall Street Journal, here’s a practical example of what will be delivered:

Muglia offers an example of how Microsoft plans to make high-performance computing more accessible: Today many financial services firms use the company’s Excel spreadsheet application to develop financial models, but if the firms need the power of a supercomputer to crunch numbers, they often have to write specialized applications in programming languages like Fortran that a much smaller group of users are fluent in.

Microsoft’s Technical Computing group is working on software that will allow a program like Excel to run in parallel on thousands of machines so the application can be used to tackle monster financial computing chores on its own, Muglia says.

It’s been a while since there was any wild speculation on this blog but I can’t resist it – all this talk of running Excel in parallel on multiple machines and the cloud makes me wonder if this is going to work with PowerPivot too? Or rather, will this work with whatever PowerPivot/Vertipaq becomes when it grows up into a corporate BI tool?

Microsoft BI Indexing Connector

Just seen this on the Sharepoint BI blog, the Microsoft BI Indexing Connector:

From the blog post:

With this new Indexing connector, users have a dedicated Report tab where they can find the reports they are looking for, use refiners to further narrow their searches, and even get a preview of the report before opening it in the browser or client…

…In addition to discovering the documents, the MSBIIC will also crawl the data sources revealing items that are not necessary in the report itself but critical to the user’s discovery and as part of the refiners.

BI Survey 9 – Invitation to Participate

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:

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

Google Squared

Today Google announced an interesting new product: Google Squared. Here are some links:
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:
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.

Metadata? Complex Event Processing?

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:
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:
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:

SQL2008 R2 Site Live

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:

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:

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:—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…


Guardian Data Store – free data, and some ideas on how to play with it

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:
  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.

%d bloggers like this: