Microsoft IT’s Experience with Reporting Services 2005

Practical findings straight from the dog’s mouth

Experience is the best teacher, especially when you're deploying new software. But when a software product is newly available, how do you find somebody who has already deployed it so that you can learn from his or her experience? Well, for Microsoft software, you can learn from the company's own IT organization, which works with all Microsoft products while they are in development. Take SQL Server 2005 Reporting Services, for example. What lessons did Microsoft IT learn by deploying and using Reporting Services during the product's development? We took that question to Microsoft executives Ron Markezich, chief information officer and vice president of managed solutions, and Stuart Scott, corporate vice president and chief information officer. For the technical perspective on deploying Reporting Services 2005, we also talked with Microsoft Business Unit IT Development Manager Frank Schneider.

Double Jeopardy IT

KF: Running an IT organization for a major company is a big job in itself, but Microsoft IT also has to be "the first and best customer" of Microsoft's software products. Ron, like any CIO, you and your team are responsible for using Microsoft technology to increase Microsoft employees' productivity while decreasing the cost of IT. Unlike most CIOs, you're also responsible for ensuring that Microsoft's customers benefit from your experience with the company's products. How does that work?

Ron: My main responsibility is to make sure I'm running business \[i.e., IT\] on our products before we ship them.We call that "eating our own dog food," or "dogfooding" our products. Microsoft will not ship a product unless we sign off that the product is ready and is high quality before it ships.

KF: Does that mean the company's expectations for IT productivity are adjusted when you're dogfooding a product, such as SQL Server?

Ron: Even when we dogfood a product, I don't get more budget or reductions of my Service Level Agreements (SLAs). I don't have fewer apps to create. The business expectations are the same as when we're not dogfooding. The company still expects me to take down cost per app, add more value to the business, reduce support costs so I can spend more money on development, and have a secure environment. Expectations increase every year; and can increase when we're dogfooding, as well.

At the same time, I work closely with the product development teams. The SQL Server developers, for instance, might call us and say they have a new build coming next week and want it deployed within a day to get feedback. So when we receive a new build, we need to deploy it quickly across our environment.

Meanwhile, our people are doing development work for the business, so we have to balance that. People in IT work extremely hard during the dog food season. They really do two jobs—they're testing a product and meeting business needs.

I don't think we're any different from any other IT shop. One thing dogfooding helps me do though—it aligns my IT organization closely with the mission of the company because IT is not a cost center; it's part of the process for shipping our products to customers. That's wonderful for any IT organization because people become part of the product. We add a whole new level of value to the company, and that's why people are willing to work so hard during dogfooding.

KF: Stuart, as someone who just joined Microsoft in July 2005, what was your impression of the dogfooding process?

Stuart: I was surprised at how much we used SQL Server 2005 in production with high-profile, business-critical applications. I was surprised by what we dogfood—SAP, CRM, and ERP systems. Couldn't we find something a little easier?

I came from a non-Microsoft environment. I realized we had worked awfully hard there to pull together an integrated solution through a lot of open source and other third-party tools. But the productivity of my engineers here versus my previous experience has made me a convert. Our development cycles here are much shorter. I wasn't looking for that, but it struck me the first time I did a project review. Developers were talking days and weeks, whereas in that scale of system, I'm used to months and months of development.

The Environment for Serving Dog Food

KF: Stuart, you're responsible for design, development, and deployment of IT systems. Can you describe Microsoft's IT environment?

Stuart:We have 2,500 applications, and the infrastructure includes more than 220,000 networked devices supporting over 90,000 employees and contractors, making it one of the largest corporate technology systems in the world.

KF: How do you roll out a new version of a major product such as SQL Server 2005 in that environment?

Ron: When a new version is in development, we start using it in test labs in the Alpha stage and roll it out to production at Beta 1. But even before that, we sit with the product team and define shared goals.We will not ship the product until those goals are met.

In the case of SQL Server, it is the only database we use. IT manages and runs roughly 1,500 LOB applications—anything from a sales force automation application to a payroll application—on SQL Server. We put our SAP production application on SQL Server 2005 in August 2004. By the time the product shipped, it had been running for 15 months in production. This is the same production system that does all of our US payroll and benefits, recognizes all of our revenue, does a lot of our management reporting, and all of our general ledger and accounting.We trusted SQL Server 2005 on our most critical applications.

Reporting Services 2005: Lessons Learned

KF: You mentioned management reporting. Tell me about Reporting Services 2005.

Ron: Reporting Services is huge for us. Microsoft is a data-driven company. Our reporting environment uses a lot of Excel templates and standard reports. In the mid-1990s we consolidated our disparate financial and reporting systems into a common platform, which exposed a lot of data through Excel reports. The problem was managing these reports. It was hard to drill from one report to another and to manage them. Reporting Services makes managing standard reports easier and allows drill-down across reports tied directly to the database, so you're always getting the data off the database.

KF: Can you give examples of how Microsoft uses Reporting Services 2005?

Ron: We have thousands of P&Ls because we look at finances by geography, as well as by product and channel. We let Microsoft business units in a particular country look at P&Ls by their geography and then drill down to their country and view by channel, by product, by customer. That capability used to require a lot of different standard Excel reports. So files got very large. You had to pass them around the network.

We've moved all those Excel reports to Reporting Services, which uses less bandwidth because you're not sucking down those 40MB Excel reports. Also, we used to manage all the versioning, templates, and generation of those reports, and now Reporting Services does that for us.

Now we can integrate reports and drill down from one report to another. Users can choose to get a report in the Reporting Services browser or in Excel, and they still have the drill-down capabilities in Excel. People like Excel as a front end because sometimes they like to go outside the report and do some calcs directly in Excel.

KF: How did you roll out Reporting Services 2005 in your department?

Ron:When we roll out new technology, we work through our IT teams that support individual functions such as finance, HR, sales, and marketing. We set up multiple sessions with the developers from each IT team and explain what's in the product and the value they'll get. They take the information we provide back to their business and start mapping the product's components to the problems they're trying to solve.

So in the Excel example I gave you: All of our broadly used and most critical reporting systems are using Reporting Services—like our in-house Management Reporting System (MARS).

KF: Frank, you're the development manager for the IT team that is responsible for MARS, right?

Frank: Yes, we are the IT team that supports finance in addition to corporate accounting. Because the company's reporting is broken down by region and function (primarily for security reasons), you had to have multiple reports querying the SQL database. We had more than 100 reports. With Analysis Services and the security policies we applied, we can now have one report of each type serve all regions or all functions, and Analysis Services handles the security credentials of the report owners and gives them only the data that their division should see. So you don't need 20 different reports, which were basically one report sliced 20 different ways. So that's a big improvement.


KF: Can you describe the security measures you mentioned? Is all security at the data level, or are you using layers of security at each tier (application, Reporting Services, Analysis Services, Database Services, etc.)?

Frank: We have security at the data level, and we have an intermediate security application that we wrote. The security is at the data level on the server, and we have two sets of roles: function roles and geography roles. The function role describes the organizational function in the company. Basically, the company's business divisions are the function slice. The geography role is the person's location (Europe, Asia, Latin America, North America). So a person is in one or both roles, or security groups. Say a user is a comptroller in the Windows Client Division and he works in Europe. Membership in the Client Division function and Europe geography roles means he will only see the data that applies to that function and that geography, and it works up and down the hierarchy.

Because of the construction of the security schema in Analysis Services, the hierarchies will make sure that a person only sees the data that appears as an intersection of function and geography appropriate to that person's assigned role. This would be impossible in the spreadsheet world. Here's why: Let's say we have 10 or 12 functions and 10 or 12 geographies.With Excel, you'd have to create hundreds of roles to make this all work. You'd need a spreadsheet for each set of intersections.With Analysis Services, you just create the major roles and assign people in each grouping.

That's the base security level.The intermediate security is an application we wrote to limit people to certain perspectives. Perspectives are slices of data not connected with function or geography. For example, there's a marketing perspective. People want to look at marketing costs as opposed to general P&L data. So we have perspectives for just marketing data. A much wider group of people can look at that data than can look at the core revenue data,which is the most restricted data.

Before a user even gets to make the query against the database on the server, we're saying you can or cannot look at this perspective, and that's done at the Reporting Services Level. This is a prefilter to the data security. The data security still applies, but you can only see certain pictures of the data to begin with. Let's say we have 250 attributes of data and the marketing perspective only contains half of those. Before you can even make the query, you're limited by the perspective. Then further, you're restricted by the security group you're in.

KF: Based on your experience, what are your security recommendations?

Frank: We believe security should be at the data level for two reasons. First, even if you have middle-tier reporting, a person's credentials are passed to the query so the system knows exactly who that person is. A person is defined in a role, the roles are stored on the server, and the domain controllers know that each person is in a particular role. Second, other tools besides Reporting Services may be available to query the Analysis Services data, and if the security is at the data level, then no matter how users query (let's say they query through Excel), they still only get the data they should see. You don't have to enforce the security on the middle tier or through some third level of security because you have it at the data level.

We've been looking for this as the way to absolutely secure the data. Remember, this is the financial data, so we have to be very careful about who sees it. We believe that there's no way that anybody could get at this data unless they had specifically been granted permission into these roles.

Ron: Think about compliance and governance requirements. The ability to lock security at a more granular level is very important to us.

Frank: Under the procedures for maintaining financial data, you have to scrub this data on a quarterly basis, I believe, so anybody that has access to this data is reviewed. But we know that the individual user is a member of these roles and therefore cannot see anything beyond what she should see. Even a database administrator or owner of the server would still have to be in the correct role to see this data.

Also, you now have two ways to do logging: You have logging at the source, and you have logging at the Reporting Services level. Analysis Services will log the queries. Remember, if a person is directly connecting to SQL Server with, let's say, an Excel spreadsheet, in previous versions of SQL Server you'd have to write intricate code to get the query to be logged as the user was querying. You could easily get logging to make updates, but you couldn't get logging just to select. However, with Analysis Services 2005, you can log every access. Not only is the data secure, but you know what the person asked for. And Reporting Services as the reporting vehicle is also logging. It guarantees you know who was making queries.

KF: How are you managing data security in your 2005 reporting environment?

Frank: The security is in an XML schema, as is the schema for the actual database design, so security is relatively easy to manage. We don't have a separate tool for it yet, but to manage the security we use a script that we can readily apply to the source.

You don't need a lot of manual effort. If you wanted to go into the raw front-end tool that we used in SQL Server 2000 Management Studio, it would take a long time to set up every value. We have upwards of 600 possible reporting users in the cube, and 12 function and 12 geography roles, so managing that number of roles would be difficult if you wanted to do it one click at a time in SQL Server Management Studio. So using a script is good. Remember, the person is not in the script; only the role is. The person is assigned into the role through all the normal AD tools, so when a new person comes on board, the administrator adds him or her to a security group and they're in the role. The users themselves are not put in the server, only the role is, and the membership of the person in that role determines his or her actions.


KF: What can readers learn from Microsoft IT's experience with SQL Server 2005 and Reporting Services? Frank, what are the four most important aspects of Reporting Services, specifically?

Frank: First, Reporting Services is for pre-built reports, not ad hoc reports. So if you want to build reports for a wide range of users, Reporting Services is great—either with Report Builder or Report Designer, depending on the complexity of your reports. If you have experienced report writers or developers, you can produce reports very fast—as long as you know the schemas. Not only is it a central source of reports, but it's a fast way to get reports. Second is security: You can keep individuals' credentials and marry that to a security role. Third is scheduling: You can schedule reports to run, and they can run under blanket credentials or under user credentials. Fourth is drill-down and drill-through: If you have other sources that users need to go to, or even within the cube itself, you can build links into the report and users can even go to another data source and find out what a particular cell is worth. For our users, that's probably the number one thing. Being able to go from the same report that had the summary information back to actual transaction details is very valuable—even into a different data source.

KF: Stuart, was anything surprising to you during the dogfood period?

Stuart: I'll speak as a CIO who came from outside of Microsoft. My general experience with any new release from most software companies is that you get new features, but performance is a secondary focus and future service packs deliver performance tuning. What surprised me is that we got all the performance in this release. Without any tuning, we got a 25 to 30 percent performance improvement, not only in our SAP environment but also in our Siebel deployment and some of our internally developed applications. We really worked hard with the product team on performance, recoverability, and scalability. We got that without a lot of tuning, or developing indexes.

KF: Ron, what did you find most valuable with your SQL Server 2005 deployment?

Ron: I liked the database consolidation. Our storage utilization was about 30 percent because everyone buys the amount of storage they'll need for 3 to 5 years. With the SQL Server 2005 deployment, we consolidated our storage into a storage service. Now we only do central storage purchasing, and we can drive storage utilization up to about 80 percent. I liked the fact that I could build broader skills in my people. I liked the fact that I could better manage my compliance efforts, including the security components. And I liked that my users are empowered.

For us, empower, simplify, and protect are three things we want to do, so we could tie our SQL Server 2005 deployment to those three pillars.

Ron Markezich
Stuart Scott
Frank Schneider

Karen Forster ([email protected]) is editorial and strategy director for Windows IT Pro and SQL Server Magazine and former director of Windows Server User Assistance at Microsoft.

Hide comments


  • Allowed HTML tags: <em> <strong> <blockquote> <br> <p>

Plain text

  • No HTML tags allowed.
  • Web page addresses and e-mail addresses turn into links automatically.
  • Lines and paragraphs break automatically.