SQL Server - Reporting
Because everybody reports to somebody else
Are you Datamart Builder with few bones to pick? Are your cubes too slow?
Ok, so I can't do anything to help you with that. But you can help Microsoft help you. You see, they are in the planning phase for the next version of SQL Server. Your input counts! Visit the SQLTIPs blog for a link to the online surveys.
There's a Zen proverb that accurately describes this book: "If you understand, things are just as they are; if you do not understand, things are just as they are."
What do I mean by this? If you already understand writing OLAP queries, this book doesn't really introduce anything that likely to be new to you. If don't, Joe does a decent job of explaining the basics and demonstrating useful techniques in standard SQL and even, albeit briefly, Microsoft's MDX language. He presents things just as they are. There's a few things I like about this book and some I don't. Let's start with the negative.
- Its Joe being Joe. Okay, that's not bad or wrong per se, but here again he goes on his traditional rants about vendor-specific extensions, the differences between records and rows and the inappropriateness of identity based primary keys. If you are used to reading or listening to Joe, you expect this. If not, then you'll probably find these tangents interrupt the reading flow significantly.
- Its not T-SQL: If you're buying this book specifically to become a better T-SQL Developer, it can certainly help you do that. However, understand that Joe writes standard SQL, not Microsoft's T-SQL, so not all of his examples are directly usable. This is especially true for one of his ROW_NUMBER() examples. On page 73, he has a query that uses this function to compute the median of vector and references the computed values in the where clause. SQL Server 2005 doesn't support this. The example at the end of this post shows how to do write a similar query in SQL Server 2005.
- There might be a bug or two: Joe states on pages 51 and 52 that the MERGE INTO statement exists in SQL Server 2005. So far, I've been unable to prove this, at least as far the RTM version goes. I'm working with Niels Berglund to see if it was a feature in early versions (e.g., Alphas, Betas and CTPs).
Those negatives granted, what was good about this book -- and why do I suggest buy it?
- Its Joe being Joe. Personally, I appreciate his get to the point and show me code style. This is a book you can pick up and read in a few hours and come out with a better understanding of how to use SQL to do analytic queries. Its also a good -- but limited -- primer on OLAP concepts and terminology. If you're looking for a good place to take your first steps into this world, this book is a good choice.
- Chapter 11 on computing and using Correlations is outstanding: A while ago, there was another book on the market -- Data Mining and Statistical Analysis Using SQL by Lovett and Trueblood -- that was the book on doing statistical computing with SQL Server. I'm not sure that APRESS is still printing it, but I see that its available on EBay and Amazon Marketplace. Joe does a nice job covering this particular topic in five pages.
- There's lots of other chapters worth noting: Chapters 12 and 14 on distributions and regressions respectively are also very good. Chapter 18 on MDX is a too-brief but solid introduction to that topic. Joe doesn't try to teach you everything you should know on this complex topic, but again, its a great place to get your first foothold.
I think this book is a solid choice to make if you are interested in learning more about OLAP and Analytics from the SQL language point of view. It helps you understand these concepts "just as they are."
Median-by-RowNumber() Example for SQL Server 2005
-- Prepare table and fill with data
drop table dbo.foobar
go
create table dbo.foobar(X smallint not null)
go
set nocount on
declare @i int
set @i = 0
while(@i < 1001) begin
insert into dbo.foobar values (cast(rand() * 32767 as smallint))
set @i = @i + 1
end
go
-- Get the computed average
select avg(X) as Average from dbo.foobar
go
/* Replica of Joe's query
select avg(X),
row_number() over (order by X asc) as hi,
row_number() over (order by X desc) as lo
from dbo.foobar
where hi in (lo,lo+1,lo-1)
*/
-- Find the median values
with f as (
select X,
row_number() over (order by X asc) as hi,
row_number() over (order by X desc) as lo
from dbo.foobar)
select X as median from f
where hi in (lo,lo+1,lo-1)
go
"order has shipped."
My copy of Bob and Dan's "A Developer's Guide to SQL Server 2005 has finally departed from whatever cave Amazon loaded them into. Can't wait to see how different it is. Its going to be a lot of reading too, its accompanied by Johnson's ADO.NET Advanced Topics book that I'm also looking forward too.
Is it time for me to take up the writing effort again? Maybe. I've got ideas, but what would you be interested in?
Doh! Sorry to be late getting this on the wire but the Heartland Developers' Conference for 2006 is has been announced. This year its in Omaha and runs from 26 October and 27 October. Hopefully I won't find myself in China again...
In its 3rd year, HDC06 is the largest independent annual professional Microsoft development conference in the nation, organized by user groups, and focused on .NET development and other emerging Microsoft technologies. HDC provides regional developers the opportunity to experience sessions usually reserved for Tech-Ed and PDC at a much lower cost while retaining the same nationally known presenters. Over 2 days, 23 sessions, 2 tracks, and several networking opportunities, HDC06 is where you’re find top knowledge experts that can prepare you for next level development.
For only $125 Early Bird/$175 standard, each Attendee Pass includes:
1 Conference pass to 2 keynotes and all 23 ninety minute sessions
- Meals including breakfast, lunch, snacks, and coffee breaks
- Access to the “Developer Lounge” expo and activity center
- Wireless internet access throughout conference area
- Pre conference networking party including drinks and food
- Thursday night “Developer Jam” including food, drink, and a casino for prizes!
- Attendee Kit and a chance to win a monster 64 Bit Alienware computer
The speaker list is looking awesome too: Microsoft's own Jeff Brand, Don Bryner and Jacob Cynamon. MVPs Dave Donaldson, Robert Hurlbut, Rocky Lhotka, Javier Lozano and Andrew Troelsen and community rockstars Craig Utley, Phil Wolfe, Robert Boedigheimer, Tim Gifford, Matt Milner and Nick Parker. I'm planning of giving a couple of talks on SQL Server 2005 for Developers.
Register here. And remember, we're talking a mere $125 one of the best Microsoft-focused technical conferences in the Midwest.
LAMP: Linux, Apache, MySQL and PHP. A pretty nice combination of technologies for easily building interactive and Database-backed Websites. I love the concept, its one that Microsoft didn't really captialize on with .NET 1.x. Sure, you had Windows as the OS, not free, but affordable for businesses. You also have IIS which is easy enough to configure and has improved considerable with the introduction of IIS6. And, yes, ASP.NET 1.x was most certainly a great web applicaiton platform. But where was the bit that that competed with MySQL? Well, that was MSDE. Enough said. Little wonder why there's been a lot of interest in SQL Server 2005 Express Edition.
But something about that offering just didn't make out the gate in time. Namely:
- Like the MSDE story, there wasn't a free GUI for working with yet. For better or worse, XM didn't make it, and SSMSEE wasn't ready at RTM. I'm glad they didn't hold up for it, but its still somewhat holding the ASP.NET 2.0 platform for going head-to-head with LAMP, IMHO.
- Towards the end of the development cycle of SQL Server 2005, the SQL Team decided that Express Edition really should support Reporting Services and Full-Text search. However, that decision was really made too late in the cycle to include them in the RTM. Microsoft said these features would be available with SP1.
And so they are. Mission almost accomplished. It turns out that a CTP version of SSMSEE is now available at [0], so you can start getting a feel for how tool will help you write great applications. Books On-Line has also been updated to reflect the changes in it. You can download that from [1]. You also download the first CTP for Express Edition with Advanced Services from [2].
I believe the March 2006 CTP version of SQL Server 2005 will show as 9.0.2040.0. Don't worry, it still runs with .NET FX 2.0.50727.42.
[0]: http://www.microsoft.com/downloads/details.aspx?familyid=82afbd59-57a4-455e-a2d6-1d4c98d40f6e&displaylang=en
[1]: http://www.microsoft.com/downloads/thankyou.aspx?familyId=19db0b42-a5b2-456f-9c5c-f295cdd58d7a&displayLang=en&oRef=http%3a%2f%2fwww.microsoft.com%2fsql%2fctp_sp1.mspx
[2]: http://www.microsoft.com/downloads/details.aspx?familyid=57856cdd-da9b-4ad0-9a8a-f193ae8410ad&displaylang=en
Oh, did I mention that if you want to win yourself $10,000 in the Made In Express contest, you can? Sounds like a heck of an opportunity for somebody not only write some really cool code but also put some green in bank.
As Greg and Wally have reported, I've been thinking about bringing back Take Outs as a PodCast. I'm pleased to announce with the the hosting support offered by Steve Wynkoop of SSWUG, its now a reality. The need-to-knows:
The RSS feed: http://feeds.feedburner.com/sswugorgradio
iTunes friendly feed: http://feeds.feedburner.com/SswugorgRadio?format=pcast
Episode one show notes and download file.
Let's roll!
The start of the new year always makes me think I want to do new things. This year I'm trying to eat more healthily, drink less and blog more. While you probably don't care so much about the first two of those, you're probably interested in the third. Afterall, consider what your doing right now...
Effective today, I'm shifting my blog focus from SQLJunkies (what you're reading right now) over to GeeksWithBlogs. Here's the new feeds and their RSS URLs.
I'll keep posting references to stuff about .NET and SQL Server on this site for some time to come, of course.
I've been spending a fair amount of time recently working on Reporting Services stuff. Last year, Teo was kind enough to have Manning Press send me a copy of his Reporting Services in Action book. Quite honestly, this is one of the best books on any technology I've ever read. But not merely because it's technically solid, but because its completely readable and to the point. I find myself diving into it regularly, as I did yesterday morning. About two hours later, I'm having lunch with our local SQL Server Specialist -- Don Bryner -- from Microsoft and we're talking about books for SQL Server 2005. Teo's name comes up. It was amazing here somebody else say exactly the same thing I've been thinking about Teo. But Don was talking about Teo's new book on Analysis Services. I had to laugh. My darling Janell was very good to me for Christmas with a Border's gift card and I redeemed for that book. It arrived Monday and I'd already consumed the first chapter of it.
But it doesn't end there: yesterday I put a bit of code up talking about how to do simple object serialization and then today Teo posts about Object Serialization in SQLCLR. While the post stands on its own technical merit, its yet another example of why I dig Teo's writing style: What it is; Why it matters; Why it does that; What to do next and how and finally, a good conclusion.
If you're not subscribed to Teo's Blog, you're missing out. If you need really good books that will help you wrap your head around Reporting Services or Analysis Services, you won't go wrong with his books.
So you probably saw those black-shirted guys (one of them was Mike
Hall) driving around on Segways at TechEd 2005, but what were they
doing? Interview folks! and
Peter DeBetta,
Rick Heiges and I got our
seven minutes and 52 seconds of fame with them. Here's
the video
(Sorry, Windows Format only... Grrr...) and the
source posting.
And for what its worth, here's the new features of SQL Server 2005 Mobile Edition:
- Direct usabilty from SSMS
- Upgraded to a cost-based query optimizer
- Force feed Mobile databases with BCP
- Multi-user support
- Configurable compression levels of database files with auto-shrink support
- Managed APIs for synchronization via merge replication
- Exposes the synchronization progress status through a managed API
Much more information on it at http://www.microsoft.com/sql/ce/productinfo/SQLMobile.asp
Isn't community great?!
Key Functional Enhancements
- SharePoint Web parts enable you to explore and view reports located on a report server by using Microsoft Windows SharePoint Services or SharePoint Portal Server.
- Reports can now be printed directly from within Internet Explorer. A Microsoft ActiveX control is provided to support a rich client-side printing experience including full page preview.
Go get it from http://www.microsoft.com/downloads/details.aspx?FamilyId=502C0D89-1308-4662-8F58-CEC55EF1235B&displaylang=en
So I’m sitting at gate C11 at O’Hare Airport waiting for my final flight of the week – back home to Omaha. Having a few minutes, I thought I’d crank out a quick post about these events and my experiences at them.
The nice thing (at least from the presenter’s point of view) is that we have both a fixed list of content and times -- I do ~75 minute talks on SQLCLR, Service Broker and SQLXML in SQL Server 2005 -- as well as an hour of “as the experts” Q&A as the show wraps up. We’ve had some really good questions in the Q&A part, like:
- Can one call a UDF on a linked, remote Server with SQL Server 20005, which no, doesn’t appear to work.
- Can a function based on a SQLCLR hosted assembly query data on a linked-to server, which, yes, does appear to work.
- Have there been any improvements to calling T-SQL-based UDFs in terms of performance, which I’m still testing out.
Bill Baker emcees the Q&A session and he’s thrown out a few interesting answers of his own:
- On Thursday he mentioned that we should see SQL Server 2000 Reporting Services Service Pack 2 released and available for download by Monday.
- The SQL Server Team is working on an enhancement to the networking layers of Service Broker that will let you interact with it using the “Indigo” technologies as well as the traditional SNAC and HTTP Endpoint features of SQL Server 2005.
- He mentioned that he believes the plan is to allow SQL Management Studio to be used against instances of SQL Express, which is a big improvement over what we have with MSDE and Enterprise Manager today.
- Reporting Services 2005 should be able to consume an Integration Services package as a Data Source.
Following these sessions, we’ve been having a SQL PASS function, which has been good chances to meet with other SQL Server User Group Leads like Rick Heiges (who is quite a character himself) and Joe Lax in New York, as well as other SQL Server addicts. If you are coming to event in other cities, it’s very much worth staying for this IMHO.
For Boston, we did the event on Tuesday at the Marriott Copley. I arrived Monday afternoon. That was fine, but I overlooked that the Boston Marathon was being run that day. Well, okay I though, no big deal, it’ll just take us longer to get to the hotel. Well, I right about that at least – however, the path to the hotel intersected last mile or so of the Marathon route and the rest area. Bad news. Turns out that I got to lug about 50 pounds of luggage the last two miles to the hotel... on foot. Oh well, its not like I didn’t need the exercise, right? That was enough of a marathon for me!
The next day we started off with our Executive meeting breakfast and I got to meet Raj Gill and Stacia Misner – the other presenters for the event, as well as Bill Baker, the keynote speaker (and MS Executive who owns the BI parts of SQL Server). All three of them really make quite and impression! Following breakfast, I hooked up with DevelopMentor’s Mike Crowley out our exhibition booth. Eventually Adam Mechanic and Andrew Kelly – SQL Server MVPs – wandered by. I heard that Aaron Bertrand was at the show too but I didn’t get to see him. After the show, Aaron and I wandered over to the hole in the wall bar and grill called Bukowski’s and enjoyed their amazing selection of beers. Adam had an Imperial Porter (oddly in an unlabelled bottle) and this amazing looking Dunkelwiesen. Selection was a no brainier for me – they have AS Urbock Rauchbier!
Wednesday morning I took the United – err US Airways Shuttle – to New York’s LaGuardia Airport. Just a note about that if you ever have to do: don’t go to Terminal “C” expecting to fly United if your itinerary says terminal “B” since it’s a pretty good walk between the two and I’m lead to believe its always going to be a US Airways flight. After arriving in New York, I had a few hours to work on the presentations (it seems I’m always fine tuning them and working on improving the demos), then met up with DevelopMentor’s Martha Garron, Scott Deadrick, Jon Flanders and Mark Thompson for dinner. It was indeed a fun group to be with. We went to a place called Dawat (pronounced like Du-vat) on 58th street. I had the Goat Briyani which was good and some Kulfi. Jon suggested getting Naan and I’m glad he did! That place has the Best Garlic Naan I’ve ever had. The dinner conversation focused on folks catching up and current events within DevelopMentor.
As usual, the New York Road show event started with the Executive Briefing breakfast. My friend and fellow SQL Server MVP Hilary Cotter joined us and it was good to catch up with him again. He talked about some of his current writing projects like a follow-up to his Transactional Replication book – this time talking about Merge Replication. He’s also reaching materials for a SharePoint Portal Searching Service book.
Well, they just called my seating zone and since I’m way at the back of the bus, I’d better get going…
Fellow SQL Server MVP Jasper Smith has come with a really neat tool named "Reporting Services Scripter" that makes managing multiple Reporting Services installations easier. It enables scripting of all Microsoft SQL Server Reporting Services catalog items to aid in transferring them from one server to another. It can also be used to easily move items on mass from one Reporting Services folder to another on the same server. Depending on the scripting options chosen, Reporting Services Scripter can also transfer all catalog item properties such as Descriptions, History options, Execution options (including report specific and shared schedules) and server side report parameters.
You can download the Reporting Services Scripter here.
Another commonly asked question that I promised a link to in the web cast was a to this webcast from Brian Welcker on Reporting Services for SQL Server 2005.
One of the most asked questions from my Webcast was "where do you get Reporting Service from?" and a I promised a link to where you go to find them. Here you go: http://www.microsoft.com/sql/reporting/howtobuy/default.asp
Make sure to
read the FAQs too.
Thanks to the fine folks over at SSWUG, you can now watch and listen my Reporting Services Kickstart webcast from yesterday on-demand. It looks like the slides aren't scrolling, though, so if you're interested in following along, but sure to download PDF for that. The download link for that is a button on the bottom of the viewer Window.
I'm still waiting for the list of questions from them, so look for that follow-up early next week.
Questions, comments and suggestions welcomed here, of course.
I was blessed with the opportunity to follow one of the best .NET brains in Omaha last night -- the Phil Canarski (sp?) -- with a quick presentation on using Reporting Services's Web Services as part of a Service Oriented Integration effort. Its just amazing some the stuff that folks are doing with Reporting Services and Phil is one of them.
The highlight of night had to be my awful demonstration of C# coding skills. Maybe VB.NET really has inflicited a certain amount of “drain bamage“ on me afterall. However, it did meet my goal of saying the word “Service“ or “Services“ 93 times in an hour.
You can download the deck here (from GoSQLServer.org) if you like, and you're welcome to post any questions, comments or other feedback to this post if you wish. Splitting them up is just a bandwidth thing. I'm thinking that this presentation will likely become part of my regular series of talks so you might get to see it again in the future, maybe even as a SSWUG webcast.
And yes, I really can code in C#... despite my lackluster efforts at doing it live. :)
Who: The "Best of the best of the best" from Blue Cross/Blue Shield of Nebraska - Al Zyck and Ravi Thoutireddy
What: Overview of BCBSNE's multi-Terabyte, 64bit, SQL Server 2000/Informatica Enterprise Data Warehouse
When: 2005-03-17 18:30:00-06:00 (that's 6:30PM Central Time, of course)
Where: Vatterott College, 11818 "I" Street, Omaha
Why: Al and/or Ravi will be covering this warehouse's Basic Design, Implementation Issues and Lessons Learned. They will be also be providing brief feedback on their evaluation of SQL Server 2005.
How: Please sign up for membership on GoSQLServer.org and respond to this poll to RSVP.
SSWUG's daily newsletter announced it this morning:
Getting Started with SQL Server Reporting Services
Our next webcast, all about SQL Server Reporting Services, is just days away. Please register ASAP. Here's the description on this great opportunity to learn more about RS and get started quickly with your own reports:
Kent Tegels, SQL Server MVP, will be joining us for this webcast - Kent's a long-time devotee of RS and has extensive experience building solutions using Reporting Services. He'll be presenting and taking your questions as we present all sorts of outstanding information about RS. We'll review tips for installation, how to get your first report going and how the overall system of report generation works. You'll be all set to start building your own first reports after this webcast!
Register Now (webcast is free for everyone, you must pre-register)
Feb 24, 2005 11:00a PST
Not that'll be able to answer every question about RS. See you there!
Jeff Brand posts a groan about the possibility of Yet Another Workflow Framework (YAWF). And he wonders if anybody remembers “Grizzly,” the code name for a workflow system that used SQL Server 7.0 as the back end.
Continues here.