Video streamVideo streamVideo stream

Question indexQuestion indexQuestion index

Interview transcriptInterview transcriptInterview transcript

Discuss this interviewDiscuss this interviewDiscuss this interview

Jason Carlson - Product Unit Manager for Microsoft SQL Server Reporting Services Jason is the Product Unit Manager for SQL Server Reporting Services. He joined Microsoft in 1996 as a Program Manger for Visual Source Safe and Repository. In 1997 the Repository team joined SQL Server and Jason became the development manager for Meta Data Services. In 2001 he built a team and started work on V1 of Reporting Services. Before joining Microsoft Jason owned and operated an independent software development company. Jason enjoys recreational sports and is the captain of a football team in the Microsoft flag football league.

Jason, tell us a little bit about yourself, what you do, your role at Microsoft? Who are you?

I'm the Product Unit Manager for Microsoft SQL Server Reporting Services. What that means, in Microsoft terms, is that I own the development, the design, the testing staff for the product. So basically, I come up with the scope for the product. What it is going to do, when it is going to do it, taking in feedback from customers, from the market, and trying to determine exactly what is the right product to produce and get it out in the marketplace. Then there is the technical aspects of actually putting together the product, the features, the architecture, working with my key developers and designers to come up with exactly what the services are going to be, and then scheduling them and putting them together and actually delivering them with the actual end product.

Reporting services, is that going to ship as part of, what was code named Yukon, which is now SQL Server 2005? Is this a separate product? Tell us a little bit about how you are going to deliver it?

Reporting Services is a component of Microsoft SQL Server. The initial version of Microsoft SQL Server 2000 Reporting Services shipped as part of SQL Server 2000. It shipped several years after SQL Server 2000, actually shipped, but it is part of the overall licensing. Going forward it will ship with the next version with Yukon, with SQL Server 2005, as an integral component of the overall product,. So just like analysis services, just like DTS; it'll be something you can choose to install just like the relational database. Something you can choose to install as part of the product once you have actually received the CD.

We have to ask the question. What is Reporting Services? It's a great name. What does that concretely mean? Is this just doing ad hoc reports? Is there more to it. What is there?

That's a very important question and it will change. Reporting Services is the ability for you to deliver information from your data systems, whatever they might be, and in the first version it really includes a development environment for building enterprise reports, manage reports, reports that are put together, they are not completely static. They are designed to deliver some set of information. They have some interactivity build into them. You can drill down, you can drill through. So the end user has some experience with the data and they can be interactive with it, but it mostly a process for setting up line-of-business reports, operation reports, some type of BI reports. One thing to keep in mind is it is not an analytical tool. This is not something that you are going to go ahead and wander through your data randomly. It's somebody is going to sit down and design a path through the data that they find interesting and that you can use.

TSS: You said BI reports. What's that BI?

Business intelligence.

TSS: Ok. That's a really good vague term. What is that in concrete?

It means different things to different people. To me business intelligence is the ability to take and make better decisions, use the information that you have in you data systems to improve your company.

When you say reporting, right, reporting on what? Are we talking specifically just, I want to suck data out of a relational database and put it into some format, or is there more to it beyond that?

Reporting Services is an open architecture. We believe that you can get data from anywhere. In fact, the data you need to make your business run better could come from just about any source that you are pushing data into and need to get data back out from. So, the most common things certainly are relational databases today, but you can imagine getting information out of multidimensional sources, XML sources, Web services. There is data just about everywhere in the organization and you want to be able to take that and you want to able to deliver it to your employees, customers, partners. That's what Reporting Services does. It allows you to take that information, transform it, mold it into some visualization and then deliver it out to whoever needs to use it to make those decisions to make your company run more efficiently.

So when you say Web services, lets dwell on that for a second, I have a WSDL endpoint it is serving back some kind of data and you are saying I can consume, I can run reports based on what comes back from the WSDL endpoint?

Yes. You would actually have to write code in the first version. What the architecture allows you to do is actually consume data from any source. Natively built in the product, we support OLEDB, ODBC, ADO, SQL Server, Oracle, DB2, but realistically if you write some code you can get data from anywhere. So you could write a small amount of code that goes out to a specific Web service end point, collect that data, turns in to a table-based format, i.e., columns and rows, that you can then structure a report off of.

So when you say I can report, assuming I can report to the various managerial formats, like HTML and PDF and all that stuff. Can I report to other kinds of formats, can I report to XML for example, could I use that then as a source for something else, could I make this a Web service? Is that doable? Can you render it to anything?

Reporting Service architecture allows you to not only consume data from just about any type of source, but also push it out in many different ways. As you notice, you definitely need to support the managerial formats, the print page formats like PDF or images, but you also want interactive formats, like HTML and like Excel. But another aspect of data and getting information out is actually being able to produce it as a source of data to another report, to another tool; being able to take an XML file for example and be able to put that into Microsoft InfoPath or actually deliver that into the XML features of Microsoft Office 2003. So, yes there are many different types of data formats including XML, CSV and of course there is another extensible part of the system, just like data sources where you can write code to act as data from any system, you can write code to output reports in any format.

Interesting. So outputting into a separate format like XML I presume.

Yes, XML is supported actually out of the box, but if you had a very specific XML format that wasn't more general purpose, our XML formatter does handle many of the different cases, but if you have a specific type of schema and you wanted to write code to do that you can.

Earlier you had said that Reporting Services integrates with Visual Studio, plugs into Visual Studio and you had used the word development. Do I need to be a developer to use what you guys have got here, or is this something that I can hand off to a boss or an analyst or somebody and say, Go off, produce your own report. Is this something that is non-developer friendly?

We get asked that question a lot. Reporting Services, SQL Server 2000 version, the development environment for reports is inside Visual Studio 2003. You do not necessarily need to be a developer to use the tool, but of course, Visual Studio has a developer flavor to it. It is designed for developers. When building reports, I have demonstrated this many times you do not actually have to write any code. There is no code necessary, it's a drag and drop metaphor . We have found from our usability studies that people who use Access, for example, find it very comfortable to build reports using the designer as part of Visual Studio. Is it targeted at non-developers? Because of the Visual Studio integration, you do need to understand some of the interesting aspects of development. But it also provides a lot of power. So the ability to check things in and out of your source code control system, integrated with your application, would definitely cater to the developer. That is not to say the architecture is actually built for the developer. The Reporting Services architecture, the server, the client, the output formats are all targeted at people to be able to consume reports, and build reports, from many different types of tools, and in fact we have third parties that provide tools that are not inside Visual Studio.

Report design is in XML format. The actual definition of a report is an XML file. We expect there to be many different types of design tools. Microsoft has focused on the developer to begin with. While I said you don't have to be a developer, it's very easy to use drag and drop type metaphor or build report. We do cater to the developer in that as a developer you can write as much code behind the reports that you want. You have the full power of the CLR of the framework to be able to build extensions, code, expressions into your report to do just about anything that you need to do to satisfy the need of those reports.

Obviously with the next generation SQL Server 2005. There is a lot of interesting developer functionality coming there, in the sense that we're embedding the CLR as part of the database, and that in turn offers us some and unique possibilities, such as user defined types in Yukon. Is that something that you can take advantage of, is that something you have parallels to?

Yes. You will be able to definitely use those user defined types. Anything you can do in an underlying database, especially a powerful one like SQL Server, you will be able to go ahead and leverage that inside your report. Actually, inside the report you have very analogous features. Because we give you the power of the CLR, you can actually define your own classes, your own types, start to use those as part of building your report. As a developer, we give you the full range of power to utilize in building your report.

So, for example,if I define a user defined type inside SQL Server2005, create my personal type, or whatever you want to call it, I can then use RS to create reports based around that personal type?

Yes, absolutely, you will be able to issue queries, return that personal type and then be able to base reports on that.

Interesting, okay, very cool. When we're talking about Reporting Services, at a sort of lower level, at a physical topological level, what is this? Is this a store product inside SQL Server 2005 that somebody is calling into? Is this is a standalone executable engine service, Web service, requires ASP.NET, as an architect? How do I factor RS into my topology?

Reporting Services is actually a couple of things. It is a Web service, a completely separable Web service that runs within IIS on your Web service machine, completely separable from the relational database from SQL Server. In fact we mentioned before that data can come from just about any source, it does not necessarily come from SQL Server. Your business doesn't always have all of your data stored in one particular source, and it is also an NT service. So it is a Windows service. The differences basically are the Web service provides you the interactive experience, an end user request that something be done immediately, it goes ahead and provides that interface for you. That is the API. The Windows service actually performs all the background tasks. So it performs the scheduling activities that processes reports in the background. It delivers the reports on a particular schedule or based on certain events. So you combine these two together and what you have is a complete managed reporting infrastructure for any data to output to any format.

TSS: So Web service just runs on top of ASP.NET?

Yes the Web service deals on top of IIS and ASP.NET.

So, then effectively, I would want to run this topologically on top of the same machine, thus running my SQL Server instance?

That's a choice that everybody needs to make. In general, when we talk to our customers, as they grow up, they separate those two machines. Clearly they compete for resources. The reports that are being generated consume processor time, they consume memory. Your database, obviously is consuming a lot of resources to go ahead and return that data, licensing wise you put them on the same machine at the same cost, there is no additional cost. As you separate them, you need to buy another SQL Server license for your reports server, as well as if you are using SQL Server for the SQL Server license. But based on that contention for resources, as you want to service more and more users or process more and more reports, you need to go ahead and provide more hardware resources, and generally that includes separating those two machines.

We've been tossing around in the industry, today, we have been tossing around the term SOA, service-oriented architecture, we are hearing about the Web service revolution and all that good stuff. In some respects Reporting Services seems to sort of fit in with some of that service-oriented nature. You are saying give me a request and I'll hand you back some data, it's a coarse grained concept. Do you guys see yourself integrating more into a service-oriented enterprise as time goes on? Do you guys see Reporting Services taking on more of the service-oriented flavor to it? Obviously, this is a big deal, what is RS' future I guess?

We don't have specific goals in that area. What we want to do is provide a completely open architecture for you to incorporate or basically be able to embed reporting type to functionality, information delivery into your applications. We do that by being completely open, having a Web service API ,allowing you to use any platform to go ahead and request information from reports, completely embed the application, the services, the Reporting Services into your application via this Web services and also a huge bet on XML as the definition for the report as well as the source of data coming in and the source of data going out.

It almost sounds as if, correct me if I am wrong, but it almost sounds as if I could use Reporting Services as a Web service technology. Can I publish WSDL documents out of Reporting Service?

Reporting Services has a WSDL. It has a WSDL that defines all of the APIs for the Reporting Services architecture for all of the different functions that is our API, that's how we allow developers to interact with the overall service. When we produce a report as an XML, we give you an XSD. We don't give you a new WSDL endpoint. What we do is allow you to go ahead and use our WSDL, which has a method Render(), which returns you in this case if you requested XML, an XML file with its appropriate XSD. So you have kind of a built-in Web service, a generic Web service.

TSS: Can I customize those XSDs?

Yes, you can actually define within the report definition what that XSD will look like based on the structure of the report you put together. It's kind of interesting actually, thinking of a visualization, a report layout in terms of the data you want to expose, not in terms of the actual visual aspects of it, whether it is rows and columns or a chart, but actually thinking: I want to have this data element coming from my database, and I want its element name to be X, or its attribute name to Y, I want it to be element centric or attribute centric, I want to eliminate this level in the hierarchy, I want to add this new level or collection in the hierarchy, and think about it in those terms and then deliver that XSD in that XML file. The good thing is you can actually do both at the same time. You can define a report as both visual, delivering something very valuable to a manager or a business person as well as providing very rich data, maybe to your partners.

So it is almost like the product is misnamed, it should be Transformation Services?

We actually have a product called Data Transformation Services, DTS. It is focused on transforming data from one data system to another data system in general. You want to move data from Oracle to SQL Server, or from a flat file to SQL Server, or from a SQL Server to another SQL Server, or from some OLEDB source to some other OLEDB source, it does not really matter. So we actually have a very, very, powerful engine, hugely enhanced in Yukon, but in SQL Server 2000. Reporting Services is very much designed in delivering this information out to consumers, either partners or customers, not between data engines.

Interesting, okay, so then the idea of Reporting Services as a Web service is missing the point?

I have heard this stereotype many times, but I don't actually think it's true. I do know that a lot of times if the programmer gets good, while he is getting good, he learns that most people do not understand what he does. So that's really separating for programmers. Programmers are kind of driven apart by that realization that they are dealing with things that most people do not understand, and after a while they give up trying to communicate.

It's not a general purpose Web service, it is not a place where you build new Web services, for that you want to use ASP. NET and that is the way you want to go ahead and build your specific company web service. But using Reporting Services as your Web services architecture, it is absolutely key to the technology. You have this open infrastructure, you have this ability to go ahead and produce data and have it be part of your corporate data infrastructure, and we want to play a key part in that.

Obviously you guys have plans beyond the current release. Can you give us a little hint, some of the things you guys are working on in terms of future features. What can we expect to see in the SQL Server 2005 timeframe? Do you have releases before then, after then?

I will give you an update, a kind of a road map. We've just released SQL Server 2000 Reporting Services; we did that this year in January. We will be releasing the next version with Yukon, with SQL Server 2005,. And we have got many features lined out. Because we just shipped in January we actually haven't nailed down the exact feature list, but I can give you an idea of where we are going. One of the things that I mentioned earlier is this XML format that is kind of a basis for finding a report, and the fact that we expect it in many different types of offering tools. We want to further expand, not only what Microsoft delivers, but also what partners are able to deliver with us in terms of experience for end users to be able to build reports. We don't want to target just developers. We want to be able to target all types of users, from developers, to knowledge workers, to end users and there are many ways in which you can do that.

Clearly we have a great integration with Microsoft Visual Studio, we are going to continue to enhance that. The Visual Studio integration would just get better. The ability for you to have IntelliSense and all the other types of activities that you would have in a robust development tool. We're working with Visual Studio to just start to build in a great product offering. In fact we just announced the ability for you to have report control, actual client side controls, and web form controls that you can put into your app, you won't even need to use the server. Reporting should be a part of what all developers are able to do out of the box with Microsoft technology.

The next step, of course, is to start integrating all of the assets we actually have across the SQL Server box, being able to integrate better with analysis services, another great component of SQL Server 2000 and SQL Server in general, which allows you to model your information and give more business oriented terms to it, but also provide access to it very, very, quickly. It will give you a drag and drop metaphor. Right now, within our report designer, for example, you need to write SQL or write MDX, you have to know where the data is coming from. MDX is the language for analysis services. It SQLish like but it's actually a lot more powerful for dimensional based modeling and retrieval of data.

We are actually going to allow you to build MDX now, just drag and drop, you are going to be able to go to your analysis services, find your measures, find your dimensions and just drag them in and bring data right into your report from analysis services. Integration with the data transformation services that I talked about, tighter integration with the new SQL Server features, and just getting better with the Developer, and being able to get better with Office, being able to produce more rich Office documents, adding features to our Excel output, for example. And we are considering lots of new features integration with SharePoint and deeper integration across the office suite. As to how much will actually get done really depends on development schedules, road blocks that we run into and everything else that goes into the development process.