TDWI | Training & Research | Business Intelligence, Analytics, Big Data, Data Warehousing

Microsoft's Analytic Workbench Takes Shape

Microsoft's Donald Farmer talks about what's to like in Redmond's forthcoming Project Gemini, especially when it comes to sharing data analysis.

• By Stephen Swoyer
• April 1, 2009

Even folks who downplay Microsoft's BI ambitions respect Donald Farmer, who has much to say about Project Gemini, Microsoft's in-memory-columnar-data-store-on-the-desktop.

For one thing, Farmer rejects the suggestion that Gemini -- which (on its face) seems to neatly invert the data warehouse-driven BI model -- is a stop-gap or Band-Aid measure. In fact, Farmer contends, Gemini isn't an either/or proposition: business users get to have their Excel-driven self-service cake even as enterprise IT organizations can bring Gemini-based analysis back into the DW.

"All [Gemini] data gets embedded in this highly compressed form, because it's columnar, inside the Excel spreadsheet. When you save that Excel spreadsheet to SharePoint, when you publish it to SharePoint, the data moves with you. That compressed blob of data is actually Analysis Services data," Farmer indicates.

"Analysis Services knows how to read that data out of compression, so now when I connect to a thin client … and say, 'Show me Donalds_Analysis.XLSX,' we have this technology called the Redirector which looks at that XLSX file and says, 'That's a Gemini file; let's load that into Analysis Services and render it.' From the point of view of the business user, it just looks as if they're working with a [local] XLSX file."

In this respect, Farmer continues, it isn't so much an issue of sharing the data sets that users are working with as it is a question of exposing -- via the centralized SharePoint model (with its hooks back into SQL Server 2008 and Analysis Services) -- the product of those data sets (or of a user's analytic interactions with those data sets): namely, the analysis itself.

"In general, you produce the analysis that they produce. In the Gemini architecture … [connecting to] data that's compressed inside a spreadsheet … [is] just like connecting to a cube. The [difference] is that the connection string is an XLSX file, not a server name and database, so any ad hoc business intelligence client, [like] Tableau, … or even Business Objects … can connect to those [i.e., Gemini's "cubes"] just in the same way it can connect to an Analysis Services cube."

In spite of its desktop-centric deployment model, Gemini isn't something that, in and of itself, must unfold outside of IT's purview, Farmer continues. Ideally, IT will be tightly involved in Gemini's deployment and usage; moreover, he stresses, IT has an important role to play in promoting and instantiating Gemini's more prolific products.

"[IT] can get insight into what the user is doing. People often talk about Excel Hell, with all of these disparate spreadsheets. What happens is that people build all of these Excel spreadsheets, which then become pervasive in a completely unmanaged way. I can actually give it to you on a stick! But [a spreadsheet] can become a critical application, with many people using it. Not only are many people using it, but they may be using many different versions of 'it,' with nobody on the same page," Farmer explains.

"Whereas in the Gemini scenario, when you share your application and you share [your] analysis [of it], it's an XLSX file. If you publish that to a server, then IT has a complete dashboard of what's going on, and they can look at certain objects and say, 'This Gemini spreadsheet, this is becoming really, really important. Dozens of people are using it, it's being refreshed every day, it seems to be an important part of our mission-critical process.' Let's just promote that into Analysis Services and make it a full cube, make it a fully-managed IT solution, rather than something that's just being shared by the users. That gives them that sense of this isn't happening in the shadows; they can bring it into the light."

There's a further upside to this model, Farmer concludes: in the vanilla Excel Hell scenario, while consumers can open up a spreadsheet and see the pivot points of a user's analysis, they can't see the underlying data itself. Gemini and its SharePoint integration eliminate that model.