Thank you to Unisys and Melanie Marks for providing the pizza and prizes for the evening.
Doug kicked off the meeting with a quick demo of the DTS Integration Services Data Flow interface in the new 2005 Reporting Services Reports Builder UI (part of Whidbey’s 2005 Visual Studio interface). In Doug’s quick demo, he showed how easy it would be to take a flat file, pipe the input from the flat file to a pair of tables using Data Flow sources (flat file), transformations (to pipe the raw input to one table and the summarization of the raw input to another table), and destinations (database tables in this example). One of the really neat things about the demo was how the drag and drop tool turned from the design environment to a graphic representation of the progress of the DTS program in the debugger. During this quick preview of SSIS data integration, a question came up about the differences between SSIS and BizTalk. The results of the discussion were that BizTalk is assured delivery across a heterogeneous environment where SSIS is called integration services but usually functions as a data movement engine.
The main presentation was an introduction to SQL Server 2005’s Reporting Services Report Builder. The core changes to RS 2005 include some tighter integration with Sharepoint and the end to end BI. Rich Client printing has been added to RS. The Expression editor has been enhanced with VB.Net functions complete with Intellisense. Multi-Valued parameters have been added – the report processing will create SQL. the ASP.Net date picker has been included. Interactive sorting has been added so sorting can occur without requerying the data. Floating header (like Excel) so you don’t have to build paged tables with header rows. Custom report items allow the API to be exposed, so that 3rd party vendors can provide charts, maps, etc. Analysis Services support now integrates MDX parameter support and data mining query builders. Management Studio integration functions as a single point of management for all SQL Server components, a supersert of Report Manager functions. SharePoint Web Parts have been added – now you can integrate your reports into Sharepoint Services and Sharepoint portal server. Visual Studio’s integration is better. The Report Controls in Visual Studio make it easier to embed reporting functionality into applications (both web and winform).
The Report Builder fills the need between the report manger (for report consumers) and the report designer (for power users and developers). The business users would be using the report builder. Caution – report builder requires SQL Server Enterprise Edition. Report Builder is an ad-hoc report design tool for SQL Server RS. This is targeted at business users who want to find and share answers. This is not an analytical client or a replacement for pivot tables. Report builder doesn’t query SQL objects, but rather queries a semantic model of the data making is so that the business users don’t need to know SQL to write the reports. The report builder is a smart client application downloaded directly from the Report Manager web application. To build a report, you pick a template (chart, matrix, table) and drag table fields onto the design surface. When you build the report, you can page through the report and click on any detail to look in more detail (infinite drill).
Next we took a look at the semantic modeling tool. We used Visual Studio to build a report model for the Northwind database. You would use this to build a semantic model the business user would use to generate Report Builder reports. You start out with a Data Source View – use the wizard to create one if one’s not already been created to meet your needs. Once you have a data source view, the report modeling wizard makes some assumptions about the data (you can override these assumptions, but the wizard is pretty powerful) and generates a set of metadata in 2 passes. Once your model is completely processed and you have an SMDL (semantic model definition language) file, you can publish it to the reports server, or you can add expressions and calculations to the report model. Now that the SMDL file has been published, and you have your new report model, you can build new reports with the Report Builder tool.
My personal impression of the tool is this – this is a great tool which has great potential. I can easily see savvy business users creating their own reports to explore details that are smaller and less sophisticated than full RS or Analysis Services reports. I especially like the infinite drill capabilities and the ability to save any drilled down view to the report server as a stand-alone report. However, currently you can’t reverse-engineer the data connections of a report builder report in Visual Studio, so you’ll have a hard time taking a report builder report (also RDL like Visual Studio’s Report Designer’s reports) and turning them into true Report Designer reports (with true SQL as the data source, rather than the semantic model of the SQL objects). Another problem is the enterprise edition requirement – drastically increasing the price of the tool for small shops.
I asked a couple of people at the UG meeting what they thought of the presentation and our presenter. Based on the questions I heard from the group during the presentation, there seemed to be a good deal of excitement around the possibility of granting the users the ability to generate their own reports via infinite drill, but there seemed to be several questions concerning the underlying technology. I got the feeling that a lot of the developers/DBAs were uncomfortable with the semantic model’s removal of SQL – perhaps the feeling of a loss of control? Otherwise, people were very positive about both the presenter and presentation. Great job Doug!
— Matt Ranlett
posted with BlogJet