Craig Utley
SQL Down Under Show 31 - Guest: Craig Utley - Published: 12 Feb 2008
In this show Craig Utley provides tips on using Reporting Services and what's expected for SSRS in 2008.
Details About Our Guest
Craig Utley is a mentor with Solid Quality Mentors and a former Program Manager on the SQL Server Customer Advisory Team at Microsoft. He’s been working with Microsoft’s BI products since the beginning of SQL Server 7, helping to open the Microsoft first data warehousing course and teaching at Microsoft and many other companies. Craig now spends his time helping companies implement BI solutions using Microsoft and related technologies covering all aspects from architecture and design to implementation and training. He is also involved in the dot.net space and was a C Sharp MVP before he joined Microsoft. He is a published author, conference speaker and member of the INETA Speakers Bureau. His most recent book, Business Intelligence with Microsoft Office Performance Points Server 2007, started shipping last week.
Show Notes And Links
Show Transcript
Greg Low: Introducing Show 31 with guest Craig Utley.
Our guest today is Craig Utley. Craig is a mentor with Solid Quality Mentors and a former Program Manager on the SQL Server Customer Advisory Team at Microsoft. He’s been working with Microsoft’s BI products since the beginning of SQL Server 7, helping to open the Microsoft first data warehousing course and teaching at Microsoft and many other companies. Craig now spends his time helping companies implement BI solutions using Microsoft and related technologies covering all aspects from architecture and design to implementation and training. He is also involved in the dot.net space and was a C Sharp MVP before he joined Microsoft. He is a published author, conference speaker and member of the INETA Speakers Bureau. His most recent book, Business Intelligence with Microsoft Office Performance Points Server 2007, started shipping last week. Welcome Craig
Craig Utley: Thank you very much, I appreciate it.
Greg Low: As we do with everyone, I will get you to first describe how do you come to be involved with SQL Server and what’s your background?
Craig Utley: I was originally in charge of an application development company. Focused on Microsoft technology and did everything with SQL Server. One of my passions was SQL Server and how the company could make better decisions about data. I was very interested in the data warehousing arena. When the beta of 7 came out I got very involved learning about it. From there I have been involved with BI tools. Then came SQL 2000 and after 2000 shipped but before 2005, they came out with Reporting Services. At the time, Crystals Reports was the one that everyone mentioned. It was a server based reporting tool it fit well with doing both relational and multi dimensional reports. It had tools that were very good. I saw as something that would be popular. Of course the price was right. If you already had a SQL license it didn’t cost you anything else. So I became involved with it and I have used it now with many clients in a variety of manners both against relational and cube data. I find it to be a very, very good tool. That’s kind of how I got involved and what I have been doing with it a little bit.
Greg Low: That’s great. And previously you spent some time at Microsoft on the CAT team.
Craig Utley: Yes I was on the SQL CAT Team. I understand you had Richard Tkachuk on another podcast. In an unofficial kind of way that Rich took the job I had there. I was the first hire there on the Customer Advisory Team focused strictly on business intelligence. I covered integration services, analysis services, and Reporting Services. Most time was in analysis services but I did have a very large customer in the southern hemisphere of the world that did a lot of analysis service and Reporting Services. I focused on BI with the Customer Advisory Team and dealt with some extremely large warehouses around the world. It was a very good learning opportunity. Before I took the job they told me I would learn things that I could never imagine because there were things that you just didn’t see until people were throwing 20TB of data at the products and sure enough it was a very good learning experience.
Greg Low: So if you had a score card on Reporting Services and we had to give it a one to ten, where do you think its sitting?
Craig Utley: That’s a very good question. Overall I would give it an eight. Room for improvement, but an incredibly strong offering. I’ve had a number of people at customer sites when it first came out telling me they couldn’t believe it was the first version of the product by Microsoft. It was incredibly mature and robust.
Greg Low: It must have been. When it came out I was bit torn loving it and disliking some of the design decisions. I thought it was incredibly productive and just the right of adoption is testimony to that. It’s certainly very cool. I think one of things that I get a little annoyed with is I kind of like to see some purity in the API's and the design. Part of me really cringed a bit at the API design in that. For example text boxes that now had a value property rather than a text property. Things like that. I thought a key was a dot.net framework brought was that it tidied all that stuff up. I sort of felt that Reporting Services took us back down a different path and I just sort of wondered why they did some of that. I pinged some product guys and asked why we have a text box with a value property and not a text property? They said we might not want to store text in it. Oh, it’s a text box.
Craig Utley: The name would seem to imply yes.
Greg Low: So that sort of thing I struggle with. Maybe we want to store a number in it and use it in a calculation. It was more like variant box. Even then I think probably if I have to look at areas that I probably am not that keen on it’s really some of the typing and lack of strong typing in some of those areas. I find the same in parameters. For example, one of the questions I came up with is why isn’t there an exact numerical type for the parameters? I would think the main things the product would be used is for financial things and reports. I wondered why there wasn’t a decimal data type for the parameters, only a float. I pinged the product guys and said what’s the deal here and they said it doesn’t matter because we ignore the type and take what comes from the underlying provider. Oh! It’s more those things that I had issues with. If I look at the productivity and what I can achieve with the product it’s outstanding.
Craig Utley: I can’t address why some of those decisions were made. The first version that came out didn’t have any kind of ad hoc capability. From a productivity stand point if the person has Visual Studio they are great at designing reports but were the only person that could do it. So from that stand point, made a great improvement with Report Builder and the ability to do ad hoc. From a programmability stand point probably why I don’t give it a 10 is for some of the reasons you mentioned Not a lot of customers are getting deep in that area. The SDK doesn’t mean anything to them; they use the built in functionality. You’re right that there are inconsistencies for those that are familiar with the dot.net framework. Now it’s not textbox.text but textbox.value. Why? You could argue that to users it looks like read only, it looks like a label to them. No matter what you say it’s not going to make everybody happy.
Greg Low: Even dot.net framework labels now have a text property, where they used to have a caption property.
Craig Utley: I don’t know what 2008 holds in store in that specific area. I kind of doubt it will change because obviously those would be breaking changes from the existing. Once you step outside the kind of SDK area I think that the product is very strong.
Greg Low: I think it’s outrageously good in terms of what you can achieve. I suppose one of the question I would take first up that I know I have a bit of a philosophical discussion with Adam Cogan a local Regional Director periodically, who was also on the show doing Reporting Services quite a while back. What’s your take on exposing the standard browser based UI to end users versus maybe wrapping it in SharePoint or rolling your own UI for the interaction with the Reporting Server?
Craig Utley: That’s kind of a corporate direction. I just finished a large project with a company that people would recognize the name of the company. They’re not SharePoint users. For them the standard interface was just fine. In a lot of places where companies aren’t using SharePoint, I see them modifying standard interface just a little bit. They will put their logo on, might change colors, basically use that interface. For companies that are SharePoint whether moving toward SharePoint or already have it implemented and use it, I think that delivering reports through SharePoint is very common. It’s something done at those companies like the ability to view the report through SharePoint. Nice way to mange. Hides some of the, not idiosyncrasies, but some of the funny things about Reporting Services sometimes that people see in the interface.
Greg Low: Some of the questions that come up are more about the degree of customization. The parameter input. If you try to get something a little more flexible in terms of how you ask for parameters on reports.
Craig Utley: In the standard interface I have seen cases where there are so many parameters such a certain size especially drop down where you have two or three rows of them you have a scrollable region on the web site where it becomes very confusing. People end up trying to do some customization either building their own front end completely or modifying what it is the user sees in order to work with that. To me is challenge. I would like to customize more often how parameters are presented to users.
Greg Low: I think that’s why I think two things. I have tended to build it into my own application for two reasons. One to get customization with parameters. The second to make it feel like part of the same application rather than pushing it off to a different website just for the reports.
Craig Utley: I actually see that a lot. You’re right. There is a very definite difference in the look and feel versus pretty much anything else, especially if they are used to a SharePoint or any other web application. Embedding those reports is great. If you don’t have any parameters it’s easy, Not much to do at that point. I have worked with customers who have built their own simple ASPX screen that accept all parameters passed that, and renders the report the user never sees any of the Reporting Services pieces except the regular report when it’s done.
Greg Low: Yes. Commonly what I have done. Another reason that I have done that myself is I have made the default format when I am sending things to a printer typically via PDF instead of trying to work out how to paginate the html or set page orientation and all that. Most people tend to have some PDF viewer in place and most of those PDF viewers auto do landscape, portrait, all those kind of things so long as I send a page that has the right proportions, it will automatically make it landscape or portrait. One of the questions that does come up all the time is how do you deal with that if you’re using a standard UI and the standard web interface?
Craig Utley: There is no really good answer for a lot of those. You almost have to be doing some custom things at that point. That’s a challenge as well. Orientation is one of those big areas.
Greg Low: I find that in generating reports, the ones that have to go to a printer via a PDF tend to work incredibly well because it just deals with that automatically. The beauty of the html is that we can do drill throughs and things like that.
Craig Utley: I see that done quite a bit, either linking two reports or building reports in such a way that the user can expand a region and drill down to lower levels of detail. Especially when I am doing reports against Analysis Services that’s a really common thing to do with Reporting Services.
Greg Low: That’s a really good point. Anything that smells of pivotish tables and things like that. You’re going to be able be drastically more flexible if you’re doing that with a web layout.
Craig Utley: That’s correct.
Greg Low: What about Excel rendering? My personal take on it is that I have to write a report twice almost every time because I find that people when want Excel they just want the data, even though the team has gone to a great job making the Excel report look like the printed one. In fact, almost sad as invariably people don’t want it formatted like that.
Craig Utley: That is correct and I have done exactly the same thing. Two reports, one that is formatted for the screen and one that has no formatting at all.
Greg Low: It’s just a big table with lots of columns that all the data is thrown into.
Craig Utley: That’s right. I had a case recently where a customer wanted to dump everything to Excel for additional analysis. It automatically word wrapped some text fields and was messing up formulas they had built in. There was no way to turn it off. That was a challenge too. We actually had to design the reports where the fields were really wide on the screen.
Greg Low: Really good point. Another issue is a sort of funkiness between sending numeric or currency or different values in and out of Excel rendering. Have had to have a different rendering because the default Excel rendering has gone and interpreted the values in a different way. I don’t know if you’ve come across that.
Craig Utley: I have. But like you you’re making another report. I don’t know what the issue is. Sometimes numeric’s come out looking not quite like what you expected. It doesn’t honor all the formatting you put into the report in some cases with numerics.
Greg Low: I have also seen it with currencies a bit. I tend to have to format the whole thing so it really gets treated as text at the other end. It ends up looking right but it really is text which causes a problem when I’m trying to do numeric operations in Excel.
Craig Utley: Unfortunately, there is not a good way to get into the middle of that. I’ve been looking to do occasionally is interpret what is going to Excel and let me modify it. Unfortunately there is not a hook into that process
Greg Low: One of the nice things with 2008 which hopefully will get out the door, is much stronger Excel renderings coming, including ones that are just the data. 2008 we’ll talk about in the second half of the show. What have you see, that would make good advice for the current shipping version of Reporting Services?
Craig Utley: One of the things that we have been talking about, the whole export to Excel. It would be great to be able to do some additional pieces with that. In the current version you also have the report builder piece; the kind of ad hoc piece that users can use to make their own reports. Don’t have to call up someone in IT and use some Development Studio to create reports. The ad hoc reporting piece does some things for you automatically out of the box. Generate additional reports so that you can just have all this drill through, an infinite drill through with ability to look at the relationships and lets you jump around. Lets the developer who built the models in the first place, do queries and some reports, but don’t have full control over that. I have seen users tying themselves up in knots with that feature. If they are doing this against cubes, performance kind of goes out the window and never comes back. Known things that need to be worked on in the product. I would like to see the API cleaned up but don’t see any breaking changes coming. I don’t know exactly how much they’re able to do on that.
Greg Low: In addition I’ve found a few things fit and finish UI type things I find in the product. Two good examples, one is when a dialog pops up asking for a list of parameters. Frustrating that I enter the first parameter while running in the Designer and going to tab to the next cell and tab takes me out of the entry box somewhere else. A few and finish things need work on. Again I have to click cell by cell with a mouse. That really isn’t how it’s meant to be. Funniest things I find in the Designer the little black dot things in the top left hand area. If it’s clicked it’s there and when you right click it you get a different set of options. I do find that confuses people.
Craig Utley: I agree. One thing I have done when I’m teaching is tell people then need to select the matrix control or the table control. Don’t click in it because that selects the text box that you happen to click in. How to select that and then how to select the whole report. I talk how to do that in the properties windows using the drop down box if you can’t figure out where to click in the UI. They need better guides on where to click to find properties you’re looking for.
Greg Low: I think actually you mentioned tables. Disconcerting is that you click on it, it has a big outside border but when you right click it the outside border disappears and it looks like your clicking in the middle of space. That’s the thing you’re then choosing the options on. Disconcerting.
Craig Utley: I have no doubt that you and I are not the only two noticing those inconsistencies. Now whether those will be gone in the next version? There is a new Report Designer in 2008 which we can talk about later. Hopefully it will address some or all those types of UI issues. I’m sure it will introduce a few of its own. But then that’s any new product.
Greg Low: Another one I would be interested in getting your guidance on. When I am working on reports personal preference is that I tend to keep all the logic inside a proc in the database end and I just use the reporting tool to kind of format what appears on the screen. Try and avoid embedding any logic in the reports. Don’t know if you have a strong preference either way on that.
Craig Utley: I have a very strong preference on that. I absolutely agree with you. I like to keep as much in the storage procedure as possible as much of the business logic, to a point. There are times when I think that some kind of business component makes more sense but with reporting I put as much on the database engine as possible. Can be reused, consistent across multiple reports and you only have to change it one place. Performance is another reason. Third, you don’t run into all the typing issues when dealing with the API.
Greg Low: That’s a good point. I subconsciously do that because I’ve tended to generate two versions of every report. One for Excel, one for printing. Having a proc there simplifies that.
Craig Utley: Absolutely! Some people build everything as a dot.net component, business layer that sits between any application and the data. Will do calling of that for Reporting Services. You can do that but not the easiest thing in the world; so much easier to call a stored procedure in the database. I think that’s a really good mechanism. We won’t even get into the whole point of building dot.net assemblies in the database and worry about all that.
Greg Low: Actually, you’re not going to get way with that. One of my questions is the whole thing of using the code window versus referencing and assembly. What I tend to do, for all my basic formatting functions and things, I use the code window, simply because of deployment reasons. It’s just brain dead simple. It’s in the report and it runs with it. Downside of that is a whole lot of spots where that would need to be modified if I want to change it. What’s your feeling?
Craig Utley: For formatting in the report I am OK with code window. That’s what it’s for in my opinion. Hey, let’s do some real simple stuff in this report. There is no business logic; you’re just formatting the report. I don’t like to put anything beyond that in the code window. If I put any logic in there, debug it, etc., it becomes a real issue. You have codes scattered in a lot of places. I like to use an assembly if there is any real business type logic beyond just formatting a report.
Greg Low: I should go look at that on the connect side. I’d like a way to have a common code window across the reports without having to reference an assembly. A simple code window for my formatting functions that automatically appears in every report. That would be neat.
Craig Utley: A product idea for you. I’d have to think about that too. I don’t know if there is a mechanism to do that. I’m sure it’s possible but don’t know the effort required.
Greg Low: In the end I’d love to keep the code in one spot. To modify yet, completely avoid the deployment issues when it’s a small amount of formatting.
Craig Utley: That’s always is a challenge. Now I have something else to deploy and get registered. There are additional challenges there.
Greg Low: A lot of people I know haven’t tackled that. Any idea or input on what you have to do to deploy an assembly?
Craig Utley: Beyond the standard of deploying middle tier in an application, of having it on the server somewhere, deployed and callable, I don’t have any guidance beyond that. Where I have worked with calling assemblies there you are already in use by other applications. They are calling them as a way to retrieve data that need some kind of extra manipulation so they couldn’t just call database directly.
Greg Low: Yeah, that’s cool. Other things worth mentioning for the current version that might be any tips for people?
Craig Utley: I’ve seen people using this in end production then they say they need to scale this because due to demand or the really need highly available because it’s become critical for what we’re doing. Reporting Services team put out a really good white paper on that. A lot of people don’t know it exists. It’s a scalability reliability paper for Reporting Services. One of the things they talk about is they haven’t even done official testing beyond a four processor box. It was really designed to run on commodity machines. You don’t need a 64 way superdome box to run a big Reporting Services installation. It scales out very well. The challenge becomes, where to put my catalog? Usually becomes centralized SQL Server machine talking to multiple Reporting Services boxes in a web farm. People don’t even know to read the book so they put eight way or 16 way boxes and they put these big boxes and they would be better off scaling out. When they do scale out they don’t tune their SQL box that all of these boxes are calling so that becomes their bottleneck. Just some things to watch out for with the current version that I see customers doing periodically.
Greg Low: Without getting too far into licensing, the way you lay that out with multiple machines and or different number of processors all of those things are going to have licensing implications as well.
Craig Utley: That’s right. A lot of times when I see customers doing this they have an enterprise agreement so they are big users and have really large installations. There definitely are some licensing issues with that.
Greg Low: That question always comes up. Should people really be able to separate the Reporting Server from the SQL Server with a single license?
Craig Utley: I am not even going to attempt to answer that. I don’t sell licenses. I don’t get involved on that end. I don’t want to tell people something that might not be correct.
Greg Low: Absolutely! Something that comes up regularly is exposing Reporting Services to both anonymous users via the Internet and internal users via the Intranet and the security implications.
Craig Utley: Just last week I was with a customer who was doing that. Challenging piece of the puzzle. They have customers worldwide that are using this system and they are outside their domain. What do they do? They chose to build a custom security module. You can certainly do that; it is documented; CodePlex has an example where you use form based authentication.
Greg Low: For the data folk listening that might not be totally across that. Forms authentication is where we roll our own authentication model where we store our own user names and passwords against some provider. The asp.net application bases its security and authentication off that provider that we build.
Craig Utley: Yes. Basically a web page where the person enters user ID and password and behind that a component reads that user ID and password from a database. The password is stored in encrypted format. In that scenario you have to set this up to be anonymous as the user on the web, but once authenticated their credentials are passed back and forth with each call. Inside a report, if you ask who the user is, you get who they actually logged in as, not the anonymous user from IIS. Not an uncommon thing to have to do. It’s nice as you don’t have to add all those users into your domain as actual domain users. Is that officially supported? Well it’s documented and there is a project on CodePlex that shows you how to do that. Whether supported or not is a grey area. It’s possible it works but you know it’s a little more challenging to say that it’s officially supported. Leads to other challenges as well maintaining that in an actual production scenario. I did just see that working and we looked at some challenges with it. One challenge and a quick tip for anybody that has to do this. Once they switched it to using the mechanism they couldn’t deploy anymore. They would attempt to deploy from BI desk studio, or new report or new model and it would fail I gave them a login prompt, but user ID and password would fail. It needed a domain/user name. They didn’t have a domain so they were just entering user name. When they put something in the domain, didn’t matter what it was, it worked fine. Had to be in domain/user name format which was really bizarre, but once figured out they were happy. It was very strange.
Greg Low: That is a little weird. One question that comes up, something that I found that I had to do, invariably I did not have Development Studio or similar installed anywhere where I’m deploying reports in a production environment. It surprised me that people end up putting it in to do report deployment. What I have do for customers or end user customers, I tend to always build into my own application something that had the ability to upload new reports into the report server. Using the web service calls, it’s actually very simple to do. Is there a best practice? Do you use command line tools? I presume not normal to have development studio in production environment.
Craig Utley: No, it’s exactly what you described. Calling the Reporting Services web service, rather simple calls, to register a report so that it becomes available for everyone else. Best practice. Where you get the report and how you get it up, there are a variety of mechanisms for that. But simply calling web service is the best practice. A complement to that is a customer who wanted to kick off scheduled reports on demand and how did they do that? There is a way through those web services; commands that say I know this is scheduled to run at noon each Saturday but go ahead and kick it off now. There are right ways and wrong ways to do it. Through the web service is the only supported way.
Greg Low: If I look at the product… the Visual Designer is what most developers see and are most impacted by. When I look at the different parts of the products, looking at the quality bar, I would have rated it lower. The report engine and the web server feel like they are at a higher bar than the Visual Designer.
Craig Utley: Had they initially released the product with a report designer that was NOT in Developer Studio, just a standalone designer, more reporting developers would have become familiar with the web server and the engine itself. That is an incredibly robust and mature piece of the product. The UI designer has some challenges; where to find the properties, the magic spot, etc. From a maturity standpoint and robustness I think that the engine and web service interface to it are incredibly well done.
Greg Low: Do you find you need to edit the RDL?
Craig Utley: It’s pretty rare that I have to edit it. There have been some occasions where due to strange things with custom security we have to edit them by hand to imbed some of the data source information. Not real common.
Greg Low: When I’ve ended up doing it is when I am making substantial changes to a report. II find it much quicker to do it there than in the designer. Visual note pad is a wonderful tool. Like back in the day of VB6, if I had to build a new program similar to an existing one, I always copied the VB file to a new file, opened it up in notepad, do a lot of global changes, do the basic stuff, then bring it into VB. I’d have a program that was nearly what I. If I started with the design tools it was so long winded. I find that some of the table type stuff I am trying to do in Reporting Services that by copious amounts of copying and pasting, things like that, I can often achieve an enormous productivity gain over doing it in the development tools. However, I have pointed that out to the development guys asking them to make this stuff simpler. Like in Excel, I like that I can enter January, February, grab the boxes, drag them down and they fill all the way through December for me. It’s those sorts of things where I think I should be able to imply that I want Excel style as I am laying things out. I really wish it had a lot of those features. Auto fill is the word I’m looking for.
Craig Utley: I think part of that is while we talk about it being mature and robust and so forth, we’re really only on the second version of the product. These are some of the growth areas that it has to do. You remind me that I have had several cases where colleagues had said I need to do x, y, z, they never could figure it out through the UI so have the RDL so it works. They ask how they should have done this through the UI. Not uncommon to have to do that.
Greg Low: I know in the first version Adam Cogan showed me how he was editing the RDL. A way to put a page break in a certain spot that worked perfectly but there didn’t seem to be a way to do it in the UI. RDL is a richer language than necessarily what’s exposed.
Craig Utley: Not only is RDL rich and maybe not fully exposed, but just in what you see printing or exporting, all that is extensible. You can go write some dot.net code and have the ability to send straight to a printer or export to some bizarre format specific to that business or industry or what have you. Richness is there; they’ve given you all the hooks. You just have to figure out what to do. I’m not going to lie and tell you every tag in RDL. It’s pretty big A lot of richness.
Greg Low: Again as I get everyone to do Craig, is there a life outside of SQL Server?
Craig Utley: I don’t know? Is there? Yes, actually I do. I am married and have one daughter and three dogs, one of which is sitting in my lap as we speak. I’ve had him a year now and I have never heard him bark. So I figured he would be safe for this. In my spare time I like to do some photography as well as reading and some outdoor stuff occasionally. That’s about it.
Greg Low: That’s good. Roughly where are you based?
Craig Utley: I live in Louisville, Kentucky. For those not in the U.S., it’s considered Midwest, but it’s not really. I’m still the Eastern Time Zone just like New York or Florida, but I’m not near an ocean. It’s the city Mohammad Ali is from and everyone seems to know who he is.
Greg Low: Everybody knows of Mohammad Ali. It’s funny you mention not being near an ocean too. I’ve always lived in cities that are on or near the oceans. Something you take so much for granted and you never think of it as a differentiator. I used to travel a lot within Queensland in absolutely the middle of nowhere. I mean really, the middle of nowhere. What amazed me was talking to people and mentioning what would happen at a beach or the ocean. They’d sit there looking really fascinated and I could never work it out. A guy I grew up with next door to me moved to Claremont which is a long way from anywhere. The girl he married was sitting there and what struck me when I was talking to her is that she’d seen the ocean once in her life. It’s something you can really take for granted when you live in areas nearby to oceans.
Craig Utley: Yeah, I hop in the car and I can be at the ocean in about 12 or 13 hours. That’s as quickly as I can get to one.
Greg Low: She could have been there in six or seven, so you’re actually further than she was. One thing Claremont is famous for is the number of gold mines they’ve had over the years. They were telling me a while ago that the biggest piece of gold ever found in the town was found by a school child who’s parent was up talking to the teacher after school. The kid was out running around in the school yard area and came back with this huge piece of gold. That started a new little gold rush in the town.
Craig Utley: I could drive down the road about half an hour and I’m at Fort Knox which you’ve heard of. That’s where they store a lot of their gold. They don’t let you go in and take any. I haven’t figured that out yet.
Greg Low: It’s amazing. Anyway, with SQL Server 2008 coming, Q3 of this year is the current intent, what do we see differently for Reporting Services?
Craig Utley: There are a number of differences. One that got one of the biggest responses is that Reporting Services 2008 is no longer dependent upon Internet Information Services. IIS is not required to run it anymore. It works off asp.net and the framework. or it can just use HTTP.sys. Kind of like SQL does for native HTTP access today. Has a lot of people excited. Many companies don’t want to put reporting services into practice or production because they will not put IIS on one of their SQL boxes. They look at it as what their having to do and that bothers them for whatever reason.
Greg Low: The HTTP.sys that you mention. This is from the last couple of versions of IIS where they had taken the product and broken it into two parts where it used to be all one thing. They’ve separated out the HTTP.sys driver at the kernel level and so IIS became something now that is just one client of the HTTP.sys driver. SQL Server is another. Reporting Services can talk directly to that. One of the things about that is that all of the things that you might configure with a virtual directory or things like that in IIS, you need to somehow configure in SQL Server.
Craig Utley: That’s correct. The additional work has just been moved into when you’re setting up your Reporting Services you have to do some configuration through there. Of course they have a configuration tool that’s changed dramatically because it has to support some of these new capabilities.
Greg Low: That’s worth mentioning that in 2005 there is a configuration tool you can run that gives little tips and indicates the health of the configuration of the system. I find an awful lot of people don’t know about that and/or run it but it’s a very important tool. Another thing to mention is the whole thing about encrypted content and back up of that and keys.
Craig Utley: That’s right. When you first install Reporting Services regardless of what version, let’s just assume 2005, it does run through and there is a configuration step and you can set some directories and so forth. One of the things Microsoft recommends is using secure SLL or HTTPS, however you want to look at it, but basically encrypting the content as it flows. This could be financial data. It could be very mission critical or propriety information. They do recommend using SSL or an equivalent encryption technology. It generates a key and there is a process to back this key up and store it safely. I have actually seen people lose that key and there is really not anything you can do at that point. There is no way to recover.
Greg Low: It’s interesting because one of the install steps it says you should really back those keys up. But it is something I have seen people miss regularly.
Craig Utley: It’s not uncommon, unfortunately. You mentioned the configuration tools and that a lot people don’t know about it. It’s done by whoever is installing it and those that never have to go in and modify things and change it don’t even know the tool exists. It can switch a Reporting Services instance between kind of normal or negative mode and working with SharePoint. If you have a sever running SharePoint which wants to take over 480 and be the default application. You type in the server name/reports and suddenly it doesn’t play nicely anymore, or you can put it in a SharePoint mode so they get along and play well in the same sand box.
Greg Low: The tools there now, and the big change, is the move to being independent of IIS and going straight to the driver. Do you tend to find that people using IIS for Reporting Services ever change the configuration of standard virtual directories and things like that or the ones that it sets up?
Craig Utley: I didn’t see if often because most of the time this was an internal application. People were authenticated on the network were fine and knew who they were because they used integrated security and therefore they could set up all the security that they needed. In some cases the authentication of external parties, you had to go in and switch that to use anonymous authentication in IIS and make some changes there. Occasionally I have seen people go in and mess with the application pools in IIS but again that’s not real common to have to do. From that stand point there were some changes I saw in IIS sometimes but it wasn’t terribly common.
Greg Low: Now if we look going forward do you think that’s going to provide any upgrade challenges? When I look at everything else in the product and upgrade wise it doesn’t look like that big of deal really. All that complicated. I am wondering about this move from where you’ve currently have IIS to where to not having it.
Craig Utley: I think most people that will not make a huge difference. Again for people who are kind of using it right out of the box today. My client from last week is using their custom security scheme that they have written and they are using anonymous. They asked if 2008 was going to change any of this for them. The answer is yes, it probably is. It may be for the better. There is a whole new way of handling authentication before IIS was handling that for them. Now IIS isn’t in the picture any more or doesn’t have to be. Basically there is a new authentication sub-system in Reporting Services itself that will require some changes to what they are doing. I think that’s going to be a very interesting upgrade path for them.
Greg Low: I have seen some people who are concerned about using integrated security with Reporting Services. The argument is that they’re concerned about the concept of a Trojan report. Code that you are executing on a SQL box using your identity. Ever seen any sort of concerns in that area?
Craig Utley: I haven’t but now that you mention it I can see why someone might be concerned about that. When you consider that you’re calling the web service and it has the ability to add reports, remove reports, basically do anything you can do - everything you do when you deploy or whatever - it’s just calling the web service. I see where that might cause some concerns.
Greg Low: Again like any other thing if you received a report from somewhere else, it isn’t just a case of uploading it and running it. If you’re using integrated security that’s happening in your context. So like anything else, you need to be sure where it came from.
Craig Utley: That’s true.
Greg Low: Any other things with 2008?
Craig Utley: One big change is the inclusion of some of the Dundas controls. Dundas is a third party; some of their controls have been licensed to work so you have much richer controls as far as some charting capabilities, the use of some controls such as dials and possibly thermometers. and so forth. You get a much richer experience. There are a lot of people excited about the inclusion of those extra controls. Can just go right on a report, they will render fine in a thin client browser but with a much richer look and feel to the application.
Greg Low: I’ve seen some and they really do look quite impressive.
Craig Utley: They look impressive and from anyone who has done any kind of ASP.net applications and used those controls, they’re very rich and very easy to work with. Incredibly powerful. A lot of people are very excited about that possibility.
Greg Low: One in particular I love the look of, I must admit, is the kind of split axis type things in some of the graphing things. I thought that looked quite impressive.
Craig Utley: You get all kinds of 3D charting. It’s very rich in what it’s going to provide. Not all the Dundas pieces will be in there, but Dundas chart and I think a number of their controls are in, so that’s of great interest. Unfortunately I have heard both yes and no to whether the Dundas mapping piece will be in, so I don’t know which is right. I have heard both from reliable sources. So there you have it.
Greg Low: One of the others I have heard kicked around is having to do with the calendaring ones or something as well.
Craig Utley: That’s another one that I have heard yes and no to, so I don’t know what the answer is. We’ll have to see. Unfortunately early in product cycles sometimes more gets promised than eventually makes it. We’ll have to see.
Greg Low: Well speaking of that. Word rendering is another topic. Rendering in rich text format or Microsoft Word format. I get the impression that was one of the number one requests in the previous version of the products, that we didn’t have an output to Microsoft Word.
Craig Utley: You just have to ask that, don’t you? I don’t know the answer to that.
Greg Low: What I heard is that they had purchased a product, but basically it did Word 2003 so the discussion was whether or not it was OK to release it without 2007. They kind of thought that might not be a great idea. Then of course the argument was whether the work could get done or not in time. The good news is I watched the SQL Jump Start sessions from Redmond yesterday, and Word 2007 rendering was one of the features listed as recently as yesterday.
Craig Utley: Well that’s good. I had not heard that specifically. I hate to admit that several years ago I did do something with Reporting Services where I would dump data out to a HTML file and save that file on the server, then simply change the extension to .doc, then send it to the client browser which would then fire up Word on their machine in a nicely formatted way. They thought I was brilliant because I was able to export to Word. I am glad that there is actually going to be a real export to Word. I didn’t know where that stood. That’s good to know.
Greg Low: Yeah. Up until a few weeks ago I kept hearing it was still a bit dubious. It was listed in the early features as hopefully would be there and then I was hearing it was kind of on the brink either way. But as I said the sessions yesterday most definitely had it listed. Interestingly, they definitely used “Word 2007” in the slides yesterday. We can only wait and see. I would love to think that it will be there.
Craig Utley: I hope so as well and you know speaking of Office 2007, one of the changes that’s coming in 2008 is a brand new Report Designer and it’s stand alone. So that is a big change. I don’t have to fire up Visual Studio anymore. Report Designer now has a ribbon so it looks very similar to Office 2007 applications. I got feedback from a colleague a day or two ago that he is using Reporting Services 2008 on a project. He really likes the new Report Designer and says that it has some nice enhancements to it over what we’re used to.
Greg Low: What level of person do you think that’s targeted at?
Craig Utley: It’s still aimed at the developers, just like the Report Designer in BI studio today. I still have to create my data sources and build my queries and those kind of things and then put the report together. It’s the ability to break it out of Visual Studio. You can still do them there but it’s just a standalone tool that is hopefully going to give us a little more power and start marching us towards some of the things you were talking about, doing things for me more often when it should be able to interpret what I’m trying to do, and make it more flexible to work with. There is a whole new designer available for the developers. You still are going to have the ad hoc piece, the report builder, for end users.
Greg Low: Very much a designer thing instead. Interesting. Do you think that expresses some frustration with what they able to do within the existing shell?
Craig Utley: I think so. When you look at it, Visual Studio is great for writing code and you can do all your form design via the web forms or Windows forms or what have you. They use that same metaphor for dragging and dropping and building reports. Yet reports are still a little bit different. They have data sources; you have to be able to generate queries for them; preview to see what it looks like. Then it has to spin up a little web browser instance kind of like ASP.net. It’s still not as code based if you’re not getting in and using with the RDL on a regular basis. It’s a slightly different mentality. You take someone who might be good at one of the existing reporting tools but don’t consider themselves developers. They may have been really good at building reports but now you throw them in Visual Studio and that was a frightening thing for a lot of people. They’re just a reporting guy. What is this all about?
Greg Low: Yeah the approach for everything else at the moment still is more and more into the Visual Studio shell. It’s interesting to see that moving out of there a bit. So I wondered direction wise where that sits.
Craig Utley: I don’t know. I can’t say definitely why that is. But that is the direction they have gone at least with 2008. Right now in the CTPs, I assume this will change, but I don’t know when or how, but there is a new control that will probably be the primary control people use called the Tablex.
Greg Low: In fact that is a very important change in 2008.
Craig Utley: Yes. Today you have the table, the matrix and the list controls. Those are three different controls for basically repeated data. The Tablex merges those three together. You get the features that you had with those three separate ones, now in one control. Important for a couple of reasons but in particular, I’ve used the matrix control more than the table control because I am usually dealing with data and analysis services but I deal with both. There were features of the matrix control that when I was using the table I missed and then I would go back to the matrix and realize that features over the table control was kind of nice, and couldn’t do that exactly the same way. It’s nice to have one merged control where you get everything you want regardless of how you decide to lay it out
Greg Low: To avoid scaring people, the table and matrix are still in the tool box. When you pull them out it actually puts a Tablex on the form but configures it like a table if you pull the table out and like a matrix if you pull the matrix out.
Craig Utley: Yes. It’s really the same control. It’s just how it’s rendered when the developers working with it and the end user sees it.
Greg Low: Yeah. Another thing I have seen discussion around is the scalability changes in Reporting Services for 2009. They seem to have rewritten a lot of the engine so that you really can generate that 20,000 page PDF if you want to.
Craig Utley: That’s right. They have done several things in that arena. One is you have a lot more memory management exposed that you can tune in order to do those things. You can set a maximum limit on the memory just overall. You can set interim thresholds and then you can see how it responds as it hits the different thresholds. What do you do? Do you allow fewer threads when memory starts getting compressed or getting tight; what do you do in those scenarios? So they have done a lot with trying to tune it that way. What I don’t know yet in this particular version, I don’t know if you can tie it to just certain processers or cores depending on how you want to look at it as you can with SQL.
Greg Low: I noticed that if they start to run short of memory they simply scroll it out off on the disk which they didn’t do in the previous version. With gigantic PDF’s and things you could start to run out of memory.
Craig Utley: That’s important, not just with a particularly giant PDF. I had a customer that would run through three to four thousand reports in a batch an equivalent of a batch process it was all done through scheduling but they would kick off these three to four thousand reports over the weekend. It was all for email delivery, but they would save these as files. It was a constant struggle to not run too many of those at once or suddenly the server would just kind of stop for some reason. That became a problem for them. Spooling the disk and the memory configuration issues are certainly an important improvement coming in 2008.
Greg Low: Even though it’s not a specific Reporting Services improvement, the introduction of the resource governor will have a fairly profound effect in the reporting area as well. It will be able to put priorities on reporting workloads.
Craig Utley: Well that is a key thing to point out. When you are processing reports, there is some caching that occurs; some of that stuff is done in SQL, so there are some potential issues there as well, being able to restrict how much usage Reporting Services is getting. Not just the caching but even just pulling up the reports, reading the catalog reports. Those are reads against SQL Server. The ability to lessen the impact of Reporting Services when needed is an important feature
Greg Low: That’s good. So anything else for 2008 that you know of at this point?
Craig Utley: Yeah. There are definitely some additional features as well. There is a new logging mechanism that’s available that lets you do logging from end to end as soon as the requests come in; what does it send to the database; what comes back; how long does it take? You can get some very robust logging from that stand point. In the new report designer there are lot of new features. A new set of tabs, so it doesn’t work kind of the way you’re used to today. There actually things like an insert tab and a view tab so if you need to insert a new data region or if you need to insert a set report you need to go to the insert tab. There is a home tab which is where you change properties and fonts and those kinds of things. It’s a very different look and feel to it as well. There are a number of other changes coming. Some are engine things underneath the covers that most people won’t see.
Greg Low: I noticed there seemed to be a lot of work on things to do with soft versus hard pagination; work in and around that. Where I did see that described fairly well was on the Connect side. There is a live meeting recording there with the Reporting Services. I think it’s called Enterprises Reporting with 2008 or something like that. There is quite a bit of discussion there about the changes to the rendering engine and as I said things like soft versus hard pagination and different types of reporting
Craig Utley: Pagination for some reason has been problematic in 2005 for a lot of people. They work hard to get pagination working great. They deploy it to a different server and suddenly all the pagination changes. Little things like that become a challenge. Exporting to a PDF or Excel or whatever, pagination can cause problems there. That’s one area that has needed a lot of work. You have things like page sections and pages and page breaks and those are all elements in RDL, but they are UI representations of those as well that you can work with.
Greg Low: Actually one that I haven’t tried much yet that I’m wondering if there’s any difference is more to do with the preview versus print preview? Do you know if there is anything there? Just in the normal designer?
Craig Utley: As far as I know, you have the ability to throw up a quick preview of how it’s going to be on the web. From the designer I think you’re going to be able to do a file and print preview of what you see. But don’t hold me to that. I think that’s something that’s going to be there.
Greg Low: I find in the current designer the preview often looks, shall we say, not a lot like the report is going end up looking like. When I use the print preview it often is quite a lot closer to what the thing will actually end up looking like. It’s not as convenient to use all the time as the normal preview but for anything that kinds of a little odd perhaps in what your trying to render I find it does a much more accurate rendering than what you get in the preview window.
Craig Utley: One of the challenges with the preview window is its just running at a lower level of HTML parser than modern browsers do. I’ve seen cases where I get something that looks perfect in preview window and when I publish it, the lines overlap, or it just doesn’t look right, or it doesn’t lay it out exactly the same.
Greg Low: One area where I’ve had frustration was with multi-column reports. The preview looked nothing like what the report ended up. Trying to get it to render properly was a significant challenge. I did put a connect item up and I said, hey the preview for these multi-column things looks not what I had hoped for. They closed it and said “by design” and I thought.. OK. Not really what I was hoping for.
Craig Utley: By design. Well that’s always a great answer I guess.
Greg Low: That brings up pretty much to time Craig. Is there anywhere else where we are going to see you? You’ve got the book out we mentioned earlier. Is there anywhere else we’re going to see you in upcoming times?
Craig Utley: I am giving a preconference at TechEd here in the U.S. But I will be talking about giving a day session on just the whole spectrum of Microsoft BI offerings. I focus a lot of that time on if you build a warehouse, how do you then deliver the value to the organization? That’s one reason I spend so much time with Reporting Services with Performance Point Server which is what the book is about. I have some articles coming out in SQL Server magazine about that. I’m working on some white papers at the moment. Of course the book. I will be speaking for a couple of SQL groups. I have never been to Australia so if you ever have the need, you know, just call me and I will come right over.
Greg Low: I hear that a lot actually from people. What I often suggest is if they are ever looking at a good time to take a holiday in Australia, October is the perfect time. The weather is fabulous and we have the SQL Down Under Code Camp that runs in Wagga Wagga in October. A whole weekend of SQL people. It’s a really good time to come and visit. Kevin Kline did that last year and it was absolutely great he was out.
Craig Utley: October. I’ll have to mark that down. This is an official invitation I take it? You’re paying for the airline tickets.
Greg Low: I wish. I’ll want to say thanks very much for your time today Craig. It’s been most interesting to catch up with where things are with Reporting Services.
Craig Utley: Well I appreciate the time to talk to you
Greg Low: Thank you.
Phone: 1300 SQL SQL (1300 775 775) l International +61 1300 775 775 l Fax: +61 3 8676-4913
Copyright 2017 by SQL Down Under | Terms Of Use | Privacy Statement