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.

Weblog by Paul Turley and SQL Server BI Blog.