SQL Server Magazine’s Michael Otey and Sheila Molnar visited Microsoft recently to talk with Donald Farmer, principal program manager for SQL Server Analysis Services, about the business intelligence (BI) piece of SQL Server 2008 R2, currently code-named Gemini. Farmer is from Scotland, prefers Donald to Don, and comes to BI as both a subject-matter expert and an IT and analytics expert.
SQL Server Magazine: Can you share your background with us and what led you to Microsoft?
Farmer: My background’s a bit unusual for Microsoft: I studied Celtic history, philosophy, and languages. I worked as an archeologist and historian for several years, but all the time I was working with computers. We started a software lab specifically to develop software for use in archeology. From that I drifted into doing consultancy, and I specialized in rural industries. I was in Scotland, so I ended up working in industries for the two valuable liquids that we export—oil and whisky. From there I worked in a BI consultancy that built rapid development tools for data marts and BI infrastructures. We were Microsoft partners, and it seemed inevitable to come from working on the tools to working on the platforms at Microsoft.
SQL Server Magazine: So you joined Microsoft to work on BI?
Farmer: Yes, I worked on the Analysis Services team; then I went to \[SQL Server\] Integration Services until we shipped SQL Server 2005. After that I came back to Analysis Services as program manager on the data mining team. Then we started the Gemini project. Now I’m working on the client tools for Gemini.
SQL Server Magazine: That’s a good lead-in to our next question: What do our readers need to know about Gemini, and what’s the relationship between Gemini and Analysis Services?
Farmer: For your readers, the easiest way to describe Gemini is that it’s the next generation of business intelligence technology. Gemini is in many ways Analysis Services. Think of it in terms of capabilities rather than technologies. The Gemini capabilities are what we call “self-service analysis.” Self-service analysis means that end users, information workers, knowledge workers—people who currently work in the Excel environment—can use Gemini to perform and collaborate on analytic functions without going to a specialist for advice. Now they can serve themselves and build that analysis themselves, and then share it and collaborate on it. We call this not just self-service analysis but managed self-service analysis. The important part of Gemini for the IT person is that we provide a layer of administration and manageability. So we give power to the end users to do analyses, but we give IT insight and oversight into what they’re doing.
SQL Server Magazine: How exactly are the services managed? And what does that mean for IT and end users?
Farmer: Let’s start with the story of one of the founding legends of Gemini: We were visiting a customer—a freight company. Somebody whispered in the IT manager’s ear, “You need to come and deal with a problem.” So the IT manager left for about an hour. The problem was that the cargo validation application had failed. What’s the cargo validation application? The IT manager had never heard of it. It turns out that this was a mission-critical application. Every cargo manager in the company was using it. The IT manager didn’t know it existed. Why not? It was an Excel spreadsheet. Somebody had built this application and shared it with other cargo managers. Soon every cargo manager in the organization had it. In fact, they wouldn’t ship a piece of cargo without it. And yet IT didn’t know it existed.
Gemini gives end users the power to build analytic applications, but when they deploy, share, and collaborate, IT can see what’s happening. IT will see the usage of any application that someone in the organization publishes. They’ll understand who published it, what the internals of that application are, what data sources it uses, and who else is using it. They’ll understand that it’s mission-critical. So IT understands that this is something they need to manage and control. But also maybe it’s something they need to secure, validate, and audit.
SQL Server Magazine: When you manage mission-critical spreadsheets with Gemini, do you archive them or store them centrally? Do you use SharePoint?
Farmer: The end user works in Excel. Gemini for end users is an add-in to Excel that provides them with analytic capabilities. It allows them to handle potentially unlimited amounts of data. It’s an in-memory system. On my desktop machine, which is an 8GB machine, a hundred million rows of data is simply not a problem; billions of rows of data would be possible. So there’s tremendous analytic power. But for end users it’s still the Excel environment. They also get Expressions, a really rich expressive language that allows them to do BI-style calculations. For example, they can do parallel periods, and they can do contribution to period—calculations that are typically difficult to do in Excel. With Gemini you can do them because under the hood there’s a BI engine that understands these things. But as far as end users are concerned, they’re creating Excel pivot tables and pivot charts.
When they decide to collaborate, they publish to a Gemini server. The Gemini server is SharePoint with SQL Server 2008 R2 and Excel Services. We provide a very simplified setup that the IT department can configure easily. The user saves a spreadsheet, a workbook, onto that server. The data that the user created, even if it’s a hundred million rows, goes along. It’s embedded inside the workbook. That workbook appears as a document in the library. Users can navigate to it. They can see a preview of it. As far as they’re concerned, they’re working with documents. From the IT point of view, however, this is a very special workbook because embedded inside it is an Analysis Services data structure. So on the server, the analysis server can interrogate the document, load the data, refresh it, manage it, create the model, and even serve it to other clients if necessary. As far as the end users are concerned, they’re publishing spreadsheets and workbooks. From the IT point of view they’re publishing a BI solution. Other client applications and \[SQL Server\] Reporting Services can use that solution and build other applications on top of it.
Gemini is the constellation with the twin stars. To us the twin stars are the information workers and the IT professionals. It’s not just self-service. End users serve themselves at that part of the process that IT finds most difficult—which is answering a precise problem. IT still has a very important role; they provide the infrastructure. They largely provision the data. And they also provide the traditional BI. Traditional BI doesn’t go away. You need to have what we used to call "one version of the truth."
SQL Server Magazine: What is the BI professional’s role in this new world?
Farmer: The BI professional’s role is still mission-critical because they still have to provide that centralized system. BI professionals provide three things that self-service won’t provide—standards, scalability, and skills. If anything, the role of IT becomes more important because as users are serving themselves that will increase the demand for IT services.
SQL Server Magazine: Would you say that the typical end users on Gemini will be power users?
Farmer: Think of end users as falling into two categories: information worker producers and information worker consumers. The information worker producer is the power user. Here’s an example: I work on an Analysis Services team. There are only one or two people on our team who build the bug reports, project plans, staffing reports, and schedules. They’re the producers. Even on a team of a hundred analytic experts, only two or three people produce the actual analytics that are consumed by others. And that’s typically the balance you’ll find in any organization. Gemini changes quite a few of the dynamics in an organization. Maybe change isn’t the right word: Gemini reflects a change and clarifies the dynamic that’s already there. Self-service already happens. It just happens in an unmanaged, unaudited, and unadministered way.
SQL Server Magazine: What training do workers need so that they can use the Microsoft stack with SQL Server 2008 R2?
Farmer: I’ll talk about readiness first. To prepare for this technology, the advice would be upgrade to SQL Server 2008 now, and when you’re ready you can take the relatively small incremental step of moving to SQL Server 2008 R2. When it comes to training, our aim is to make it familiar out of box.
I always want what I call a Jurassic Park moment: There’s a bit in Jurassic Park where the computer system is broken and this 14-year-old girl sits down at a machine and says, "Oh! It’s UNIX. I know how to do this." If you know anything about computers, it’s one of the corniest moments in the entire movie. But I want that familiarity. I want people to say, "Hey! It’s Excel. I know how to do this." Same for IT: They’re managing SharePoint. SharePoint is the fastest growing server product in Microsoft’s history. People know how to do this. Analysis Services isn’t new. We’re not introducing a new server architecture. We’re not introducing new protocols. There are a lot of new features to absorb, but we’ve done a lot of work to make this a very familiar environment. And we want that Jurassic Park moment.
SQL Server Magazine: What’s the minimum Microsoft Office version required? Is Gemini targeted at Office 2007?
Farmer: It’s targeted at Office 2010.
SQL Server Magazine: So it won’t work without Excel 2010?
Farmer: It won’t work without Excel 2010. You need that for the small percentage of people who are the power users. When it comes to consuming the analysis, then you’ll be able to consume it with the thin client because Excel Services renders it. So from the point of view of rolling it out with Office 2010 you need SharePoint 2010, SQL Server 2008 R2, and Excel Services 2010 on the server. The power users on the client need Excel 2010. Excel Services gives a very complete and satisfying slicing and dicing experience on the thin client in a web browser. So really the deployment isn’t as challenging as some people might think.
Also, we provide a really nice setup. You run a SQL Server 2008 R2 setup and if you don’t have a SharePoint farm we’ll say “Insert your SharePoint disc and we’ll go ahead and install the farm for you and configure it with all the defaults that you need for Gemini.” So it’s actually very cool. Even if you’re not a SharePoint administrator, the setup will walk you through the process of setting up the SharePoint farm and configuring it, which we know could be a challenge if you’re not familiar with it.
SQL Server Magazine: So it seems that Gemini is split. There’s a server part of it, which is the next version of Analysis Services, and the client part. Tell us a little bit more about the end-user experience.
Farmer: The information worker opens Excel 2010 and the Gemini add-in. In that environment it’s still essentially Analysis Services technology. It’s just a DLL rather than a server. It’s working locally. Users can bring in data from many sources. They can go to the corporate warehouse, bring that data into their environment, and work with millions of rows. Gemini can connect to any database. Flat files—no problem.
The decisions information workers make aren’t driven solely from the corporate data warehouse. They may have information on industry trends that they’ve downloaded from one of the analyst conferences. They may have a table of information they find on the web. How do they integrate that? Today, with an IT-driven environment, it’s very difficult to get that information and integrate it because it’s not in the corporate system. But I can go to Gemini and I can serve myself and create my ad hoc data. I can do my ad hoc analysis. Other data sources Gemini can consume are Atom feeds and XML feeds.
Using Gemini, you can subscribe to data feeds as easily as you can subscribe to a blog in Internet Explorer or \[Microsoft Office\] Outlook. In SQL Server 2008 R2 Reporting Services, every time a report is issued it will be available as a data feed. If an information worker finds a report and wants to use that data in an analysis, the worker can click the Feed button and subscribe to it.
Today, how does an IT administrator make data available to information workers from complex ERP or CRM systems? Are they going to learn the complex operational schema or query language of the ERP system? There may be thousands of reports. All these reports will be available as data sources in Gemini.
How will they be provisioned? The security model is the same as the security model that’s used for the reports, so there’s no new security model to learn. It’s just that the report comes with the data source. Administrators provision data more easily than ever before.
SQL Server Magazine: That’s interesting. End users will be very familiar with their reports, and they know what they want from their reports.
Farmer: Absolutely. And they don’t have to navigate to them. They know how to find them. They’re already familiar with them. And also, even if the report is graphical, it could be a pie chart; it could even be a nested report. We’ll still bring that data in just fine.
SQL Server Magazine: Will SQL Server 2008 R2 Gemini also tie in to Visual Studio 2010? Will Silverlight be available for these reports?
Farmer: No, this is something that you create entirely in the Excel environment. Remember what I was saying about familiarity—I often call it "radical familiarity." An information worker goes into Gemini, brings in data, maybe does some calculations, and now wants to build a report. The reporting environment is not new. It’s not a Silverlight report. You don’t have to open Visual Studio.
We’ve done some additional things that make it nice. We’ve added this feature called “Slicers.” Slicers are visual filters that you can arrange around pivot tables and pivot charts. Slicers allow you to see what’s selected and what’s not selected. It’s a very easy environment. In many ways the most radical thing about Gemini is what you don’t have to learn.
SQL Server Magazine: So the folks that you would recommend upgrade to SQL Server 2008 R2 would be BI users and power users?
Farmer: We recommend that every SQL Server user upgrade to SQL Server 2008. For SQL Server 2008 R2 it’s much more than a BI refresh. Look for more announcements later on in the year. There are advantages for many different types of users. Naturally, I focused on the BI user. SQL Server 2008 R2 is a great environment for people who just haven’t gotten ‘round to doing BI. BI penetration is only about 20 percent to 25 percent. In the Microsoft world, it’s a little bit higher. Lots of enterprises would love to do BI, but they don’t have an IT team in place to do that. They think that there’s a lot of extra provisioning. They think it needs a lot of extra skills. But people who haven’t even considered BI could actually do BI by upgrading to SQL Server 2008 R2. It’s a very compelling point.
SQL Server Magazine: We’ve done instant polls that have looked at the penetration of BI among our readers and seen that about 30 percent of readers are really into BI, and they’re expert BI users. But about two-thirds of readers really aren’t into BI, and they’re struggling with how to adopt it. So what does Gemini offer that can help those readers get started with BI?
Farmer: What these people need to understand is that the number one priority for CIOs according to Gartner last year is BI. \[See "Gartner EXP Worldwide Survey of 1,500 CIOs Shows 85 Percent of CIOs Expect 'Significant Change' Over Next Three Years" for more information.\] A couple of years ago it used to be security. So your CIO has BI as a top priority and two-thirds of your readers are not that into it. There’s a demand!
SQL Server Magazine: They see the value in it but oftentimes they think the skill sets are beyond them.
Farmer: With Gemini, IT provides and manages the infrastructure, but it’s the users who provide the business intelligence. Let’s take a vertical about which I know nothing, which would be banking. To understand banking, you need to understand the business model. I’m not surprised that there’s a barrier to the adoption of BI when you need to have a degree in banking as well as a degree in computer science. With Gemini, I provide what I know. IT provides the infrastructure and the IT support. The banking intelligence comes from the bankers. And they can serve themselves to it. But not in a chaotic way—IT still manages the compliance and security. I think that those two-thirds of your readers will actually find Gemini a huge breakthrough.
SQL Server Magazine: Do you think this lowers the total cost of ownership (TCO) of SQL Server?
Farmer: TCO comes down for a very important reason. TCO in the Microsoft world—we have a great value proposition—is one of the reasons we’re growing so rapidly in the BI world. Actually preparing the infrastructure, the hardware, that’s always been relatively low cost. Self-service BI empowers end users to provide a lot of services themselves, thus lowering that cost.
There’s a peer-to-peer review system for Gemini workbooks published onto the server. It’s actually a social environment. It’s like Facebook for data. There’s a rating and a comment system. People trust a report on sales figures not because they understand the lineage of the data and the calculations but because they trust the person who built it. Taking a set of reports and determining which are good and which are bad based on looking at the detailed lineage and analysis—that’s the IT domain.
SQL Server Magazine: So there’s a SharePoint and a social media aspect of Gemini.
Farmer: SharePoint has workflow built in. You can build workflow in to your Gemini analysis. An information worker does the work—pools all the data, brings it from multiple sources, mashes it up, creates a pivot table, and publishes it to SharePoint. Now there can be an IT-administered workflow that requires the report to be signed off by somebody in IT before other people can collaborate on it.
SQL Server Magazine: Is there any tie-in to the Madison project?
Farmer: Madison is a separate track from Gemini. Madison is this highly scalable environment for IT to serve massive data warehousing environments. The tie-in to Gemini is that it’s all part of Microsoft’s effort to increase scalability and add to the richness of the analytic experience. In the future, some of the very, very smart technologies behind Gemini—the in-memory data stores, for example—we can see them migrating into some of those environments as well and bringing some of those advantages to the relational world, potentially. So there’s certainly a roadmap for some of these technologies to merge in the future. We’re working on that just now—watch this space!
SQL Server Magazine: What other database platforms are supported? Is there a way to use other relational databases with Gemini?
Farmer: Absolutely! Yes. A relational database is to us a resource. SQL Server is a great source and the preferred source, I suppose, emotionally but not technically. We’ll consume from any source that is an OLE DB provider or an ODBC provider. So yes, Gemini is a very integrated environment from that point of view. There’s really no preference there.
SQL Server Magazine: You’ve been telling us there will be more announcements this year. What can our readers look forward to?
Farmer: What comes out after Gemini is the next release of SQL Server. That’s where we do more work to bring self-service BI and traditional BI even closer together. We’ll do more work on traditional BI. And there’ll be a set of features to help Analysis Services users move into a self-service environment. You’ll see the Gemini features become more integrated into the traditional OLAP environment. You’ll see more of the IT capabilities. We think we’ve got a great set of IT capabilities in Gemini, but you’ll see even more of that for people managing heterogeneous environments and that sort of thing. In the next version of SQL Server, we’ll revise the core Analysis Services engine to reflect some of the Gemini capabilities. That’s some of the things we’re doing.
SQL Server Magazine: It sounds like the SQL Server community has a lot of exciting things to look forward to!