The combined Microsoft BI Conference and TechEd in New Orleans last week was a great experience.  The shear magnitude of the conference was impressive with about 10,500 attendees.  Most of the BI sessions I attended were full or overbooked – much like the airplanes on my trip to and from the event.  The organizers quickly responded on Tuesday by providing overflow rooms for many of the sessions.  The MS BI Conference has been the odd duck in the Microsoft conference circuit.  Now in its fourth year, amid the economic recession, Microsoft has been pairing this event with other conferences.  Last year it was part of the SharePoint Conference in Las Vegas.  Given this year’s record attendance, I’m hopeful that the BI Conference will stand on its own in 2011.

Reporting Services Feedback from the Exhibit Floor

I had the opportunity to spend several hours on the Microsoft product exhibit floor, at the Reporting Services station, fielding questions and doing demos.  Thiago Silva and I, from Hitachi Consulting, took on booth duty to support the SSRS product team.  The station was so busy most of the time that it took three or four of us to handle all the inquiries.  This was a great opportunity to get the pulse of SSRS users and to surface their priorities, impressions and concerns. 

Before I continue, let me just say that this is not at all a marketing message.  I love this product.  I often have the privilege to work along side members of the product team and sincerely appreciate the good work they do.  That said, SSRS has its strengths and weaknesses evidenced by many of the questions and comments we received from several avid users at the conference.  The following list isn’t based on any kind of statistical poll or formal survey but it does validate feedback I get from many consulting clients and MSDN forums users.

So What’s Up with Report Models?

This was by far the most consistent question asked by several experienced report solution designers.  For nearly all, the problem was the same… the business wanted to have a user reporting solution for operational data sources.  Most of these businesses have been using solutions like the Business Objects Universe to build a metadata layer to simplify access to real-time, complex data structures.  Since the Microsoft SQL Server platform is more cost-effective to implement and support, they want to migrate existing reports and applications.  Microsoft has never promised that report model architecture was on par with some of the established players like SAP Business Objects, IBM Cognos or Oracle Hyperion; but the fact that a semantic metadata option exists, suggests to some that it’s comparable.  It’s not.

The report model semantic layer architecture, acquired by Microsoft in 2006 was a good first step but has not been significantly improved since it was first released for SSRS 2005.  It serves its purpose when used with simple data structures in one data source and with relatively low volumes of data.  It doesn’t scale particularly well, can’t be used to federate data from multiple sources nor to resolve complex business rules through embedded functions, stored procedures, multi-table and non-equi joins.  We’re hopeful to see a replacement or major revision soon (keep reading about PowerPivot below.) 

What’s Microsoft’s Strategy for Self-Service Metadata?

For analytical reporting, Analysis Services is an impressive technology for building a user-browsable metadata layer and in many cases, cubes can be used with frequent incremental ETL loads and features – such as proactive caching – to present analytical data in near-real time. SSAS does not, however, meet the need for businesses who want to provide business users with self-service reporting over live, transactional data.  ROLAP cubes can present relational data sources with a consistent OLAP presentation but are often slow and inefficient.  The new PowerPivot integration in Excel and SharePoint have a lot of promise and SSAS continues to be a strong OLAP offering for latent, decision-support data.

A seasoned report expert will tell you that there is no silver bullet for this type of solution and the problem is more logistical than technical.  Analytical reporting is not typically performed on transactional data for good reason and data must be consolidated, validated and reconciled before it can be considered accurate and reliable.  However, the fact remains that some businesses demand user-browsable, real-time reporting. 

Report Parts and Shared Datasets are Awesome!

They are.  These new features in the SSRS 2008 R2 report designers (BIDS and Report Builder 3.0) make it incredibly easy for a business user to assemble reports from ready-made report fragments saved to the server and available for selection in the report part gallery.  Shared datasets allow a new report to be created from a named dataset query, complete with ready-made parameters and parameter list datasets.  Using shared datasets and report parts, the casual user can drag-and-drop to create a dashboard report from working components or a more sophisticated user can use these objects as a starting point for more advanced design.

I’m Overwhelmed with BI Reporting Tool Choices.  What Should I Use?

With all of the report and visualization options available in the SQL Server and Office BI suite, which one is right for our reporting needs?  What criteria is used to select one option over another?  The following short list should provide some guidance.  There is no clear separation between these tools as each was developed with different goals and by different product teams.

SSRS reports

Reporting Services is by far the most capable and flexible all of the reporting tools and is an excellent choice for creating online and print-ready reports.  SSRS is a good choice for formatting complex reports and for combining different report styles and elements.  Business users can use Report Builder 3.0 to create self-service report using report parts, shared datasets, cubes and views prepared by IT professionals SSRS may also be used by IT staff to create custom report solutions using embedded expressions, custom code and custom programming extensions to the server architecture.  SSRS supports dynamic interactivity but these features must be explicitly designed, which requires specialized skills and development cycles.  Reporting Services includes table, list, matrix (pivot table), chart, gauge and map data regions.  The advanced data visualization components are licensed from Dundas Software.

Custom Report Items from Dundas Software

Microsoft and Dundas have a partnership to provide the chart, gauge and map components as native features in SSRS reports.  The code base for these components is updated with major SQL Server product versions.  Dundas offers more advanced versions of these and other components for a separate licensing cost.

Excel and Excel Services

Excel may be used to create desktop reports that are either connected to business data sources or use copies of the data stored in the workbook.  Data visualization feature enhancements in later product versions have made Excel a good tool for user to create charts, pivot reports and scorecards with conditional formatting features.  The classic problem with Excel is that is promotes “data sprawl” by allowing users to keep old copies of important data in their documents.  Excel Services in SharePoint provides an avenue to solve this problem by hosting a shared copy of the file in a centrally-managed document library.  Excel, by design, is not an enterprise reporting platform but for many business users Excel is the end-all, be-all data viewing tool.  With extensions and improvements in recent product versions we’ve seen evidence that Microsoft product decision-makers are saying “if you can’t beat ’em, join ’em” and making Excel a more enterprise-ready suite of tools.

PowerPivot

It’s nearly impossible to miss all the hype about PowerPivot.  It’s really two things:

1. PowerPivot (internally named Project Gemini) is a platform that will likely be extended and incorporated into other products.  My crystal ball isn’t entirely clear on this but this may be the reason we haven’t yet seen traction around report model improvements.  I don’t think that PowerPivot, in it’s current form, is a complete replacement for semantic report models, but it may be a big part of Microsoft’s next-generation universal data abstraction layer.  Stay tuned.

2. PowerPivot is a client-side add-in for Excel 2010 on the desktop that can also run on the server in SharePoint 2010, that utilized the Analysis Services OLAP engine to cross-pivot and aggregate data.  In the current v.1 implementation, PP is a lean-mean tool that performs as promised but it lacks some security and management capabilities that IT pros will need to make enterprise solutions manageable.

There were a number of questions and concerns voiced by conference attendees, particularly system administrators, about how to prevent misuse and data sprawl as a result of supporting PowerPivot.  PP seems to be a good prototyping tool for Analysis Services cubes but there is no migration path or process.

Here’s a very good article summarizing the PowerPivot: http://ebs.dk/en-US/aboutbi/Pages/PowerPivot-isitgood.aspx

PerformancePoint

It’s taken a few years for this product to come together – literally… it’s the combination of three separate products.  In a nutshell, PerformancePoint is the business monitoring & analytic reporting tool built-into SharePoint 2010.  It’s an interactive reporting tool that allows users to browse, cross-drill and navigate business data structures stored in Analysis Services cubes.  PerformancePoint for Microsoft Office SharePoint Services 2007 had some gaping holes in the feature set but the new version, now shipping with SharePoint 2010, has some substantial bragging rights, is easy to use and relatively bug-free.  PerformancePoint is not a comprehansive report design tool like Reporting Services.  It’s a great tool for users to browse cube data and discover patterns and trends.  Users get dynamic, online reports with hover-over effects, drill-down and cross-drill features driven by right-click menus without the need to write code or use advanced report design techniques.

Proclarity

For several years, this was the best user interface for browsing and visualizing data in an Analysis Services cube.  About three years ago, Microsoft bought the Boise, Idaho-based company and began selling their products under the Microsoft brand but they don’t plan to do it for long.  Proclarity Desktop Professional (the stand-alone Windows version), Proclarity Web Standard (purely thin-client browser app) and Proclarity Web Professional (uses Active-x controls to give more functionality – much like the Desktop Pro app) were all written using Borland Delphi and are not perfectly aligned with the rest of the Microsoft BI suite.  Our friends at Microsoft have been working on copying the Proclarity features – one at a time – into their SharePoint-based PerformancePoint offering.   

Should We Integrate SSRS with SharePoint or Leave it in Native Mode?

SharePoint integration is an excellent choice for companies that have already invested in SharePoint and have integrated it into their culture.  SSRS integration with SharePoint works well but it does add another layer of complexity to the equation.  By far, security configuration is the most common challenge for integrated environments.

If SharePoint isn’t part of the environment and users don’t recognize the need for things like document collaboration, workflows and other SharePoint offerings; they probably won’t gain much value by configuring Reporting Services in integrated mode.  I don’t recommend implementing a new Reporting Services and SharePoint platform at the same time.  Give leaders and users a chance to learn the value of SharePoint and then add SSRS integration later.  Reports can be integrated into SharePoint pages without using integrated mode by using the ReportViewer web part.

How Do I Manage Hand-written MDX Queries?

Reporting Services works well with SSAS cube data.  Simple queries and reports are easy to design by using the drag-and-drop MDX query design window.  The designer will build parameters and parameter lists out-of-the-box.  Static MDX queries can be written in SSMS and pasted into the SSRS query designer in text mode. There are some minor restrictions and caveats but this works well in nearly all cases.

If you need to parameterize and hand-written query or make script modifications to a machine-generated query, you can easily enter the abyss of MDX designer hell.  For customized queries, the designer is delicate at best.  Those like yours truly – who’ve spent countless late nights futzing with MDX queries to see what works and doesn’t through endless cycles of trial-and-error – have learned to step lightly and to use a short list of work-around techniques.  I’ve blogged about some of these techniques and have included walk-through examples in the Wrox report recipe book.  In short:

• Build the original query using the graphical designer or hand-written queries without any parameters and run it once to establish the field metadata for the report.  Keep this query in a script file in case you need to use it in the future.
• Keep a copy of custom-written queries in an SSMS script file.  You may need to restore it to the report designer at some point.
• To add parameters and additional MDX functions, build the query text as a string expression or custom function.

What Are Best Practices for Migrating from Crystal Reports?

Hitachi Consulting developers created a conversion tool back in 2003 and have added features to it over the years.  We got a lot of consulting business from clients who needed several reports migrated to SSRS.  Since then, several report conversion services have surfaced.  Because most solutions use the Crystal API to parse the rpt reports, selling or installing software that automates the components could violate Crystal/SAP/BO software licensing so this is usually offered as a service rather than a product.  Converted reports are not optimal, primarily because the design approach for the products are different.  Crystal promotes the use of several, similar reports and Reporting Services makes it much easier to create fewer reports that do more.  A conversion project is usually a great opportunity for report consolidation, improvement and some redesign.  Crystal also promotes the use of subreports, a feature supported by SSRS but is often best avoided if possible.  I’ve worked on many of these projects and have seen cases were 1,000 Crystal reports were replaced by 50 SSRS reports.

In short, in most cases, a report converted or designed the same way it was in Crystal is often a bad SSRS report.  After automating report conversion, we usually review these designs and then start rewriting.  SSRS offers several “better ways” to visualize the same data more effectively.  Effective report migration is a process performed by tenured professionals with years of field experience.  A report conversion utility is one of a few tools to make this process more efficient.

How Do We Load & Store Geographical Data for Mapping?

(best practices are evolving – look for a future post on this topic)

It was apparent that a lot of people are using SQL Server to manage geographic, spatial and GIS data.  The new mapping report component can be used to visualize this data in very creative ways.  Most of the questions we entertained had to do with how to load this data into SQL Server.