Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

Menu

Skip to content
  • Home
  • My Books
  • Video Tutorials
  • Presentations
  • Training & Consulting Services
  • Visualizations
  • About/Bio
  • Paul’s Bio
  • Note to SPAMers

Tag Archives: TechEd

2010 Microsoft BI Conference: Directions in Microsoft BI

June 21, 2010 by Paul Turley

0

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.

Share this:

  • Click to email (Opens in new window)
  • Click to print (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Share on Facebook (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to share on Google+ (Opens in new window)

Like this:

Like Loading...
Posted in Microsoft BI Platform, SQL Syndication. Tagged BI Conference, Conference review, TechEd

Post navigation

Credentials

759,386 total unique views.  Recent views:

Enter your email address to subscribe to this blog and receive notifications of new posts by email.

Join 3,600 other followers

Recent Posts

  • Facebook Live Pop-up Session Recording
  • PASS Facebook Live Pop-up Expert Series
  • How to add KPI indicators to a Table in Power BI
  • Tour of the Power BI Solution Advisor
  • Using Power Query “M” To Encode Text As Numbers

BI Industry BI Projects Business Analytics Microsoft BI Platform MVP Community PASS Power BI PowerPivot Power View Self-service BI SolidQ SQL Server SQLServerPedia SQL Server Pro Magazine SQL Syndication SSAS Administration SSAS Design SSRS Administration SSRS Design Tabular Models

Blogroll

  • James Serra's Blog 0
  • Arnie Rowland 0
  • Paras Doshi's Blog 0
  • Chris Webb 0
  • Rob Farley 0
  • Tomislav Piasevoli 0
  • BI Monkey 0
  • Dan English 0
  • Pinal Dave 0
  • SolidQ Mentor Blog 0

Community Sites

  • Oregon SQL (PASS Chapter) 0
  • MSDN Forums 0
  • Paul's Weekly News Feed 0
  • Microsoft Connect 0
  • Professional Assn for SQL Server (PASS) 0

Archives

  • April 2018 (2)
  • March 2018 (3)
  • February 2018 (3)
  • January 2018 (3)
  • December 2017 (3)
  • November 2017 (4)
  • October 2017 (1)
  • September 2017 (2)
  • August 2017 (1)
  • July 2017 (1)
  • June 2017 (4)
  • April 2017 (8)
  • March 2017 (1)
  • February 2017 (2)
  • January 2017 (8)
  • December 2016 (4)
  • November 2016 (3)
  • October 2016 (2)
  • September 2016 (1)
  • July 2016 (3)
  • June 2016 (3)
  • May 2016 (3)
  • March 2016 (6)
  • February 2016 (5)
  • January 2016 (2)
  • December 2015 (4)
  • November 2015 (3)
  • October 2015 (2)
  • September 2015 (2)
  • August 2015 (3)
  • July 2015 (6)
  • June 2015 (1)
  • May 2015 (5)
  • April 2015 (4)
  • March 2015 (1)
  • February 2015 (2)
  • January 2015 (4)
  • December 2014 (3)
  • November 2014 (1)
  • October 2014 (4)
  • September 2014 (1)
  • August 2014 (2)
  • July 2014 (5)
  • June 2014 (4)
  • May 2014 (2)
  • April 2014 (6)
  • March 2014 (3)
  • February 2014 (7)
  • January 2014 (5)
  • December 2013 (2)
  • November 2013 (1)
  • October 2013 (1)
  • September 2013 (2)
  • July 2013 (4)
  • June 2013 (5)
  • April 2013 (1)
  • March 2013 (4)
  • February 2013 (3)
  • January 2013 (1)
  • December 2012 (4)
  • November 2012 (4)
  • October 2012 (3)
  • September 2012 (3)
  • August 2012 (2)
  • July 2012 (2)
  • June 2012 (2)
  • May 2012 (3)
  • March 2012 (2)
  • February 2012 (3)
  • December 2011 (1)
  • November 2011 (3)
  • October 2011 (11)
  • September 2011 (7)
  • August 2011 (4)
  • July 2011 (2)
  • June 2011 (4)
  • May 2011 (5)
  • April 2011 (5)
  • March 2011 (4)
  • February 2011 (2)
  • January 2011 (4)
  • December 2010 (4)
  • November 2010 (4)
  • October 2010 (1)
  • September 2010 (1)
  • August 2010 (2)
  • June 2010 (1)
  • May 2010 (2)
  • April 2010 (1)
  • March 2010 (19)
  • December 2009 (1)
  • June 2009 (1)

Tag Cloud

Ad-hoc reporting Add columns Alternate row colors best practices BI Center of Excellence BI Conference Bill Gates Breakcrumb links Browser settings Business scorecard Checkbox in report Checkbox list Check mark Column chart Conditional formatting Conference presentation Conference review Conference session Datazen Date parameters DAX Drill-down Drill-through Dynamic column visibility European PASS Filter by user Greenbar report Hide columns KPI indicator Login prompt Manually starting subscription Master-detail report MDX datasets MDX queries Microsoft Architecture Journal Microsoft MVP Nested tables Parameterize Parameters PASS Conference PASS Summit PASS Summit 2017 Pop-up window; Java script Portland OR PowerPivot Power View Recipes Remove columns Repeating list report dependencies Report deployment Report navigation Report parameters Report recipe book Reports for MDX Return specific row Self-service reporting SolidQ SolidQ Journal SQL Saturday SQL Server community SQL Server MVP SSRS dynamic columns SSRS version control Start subscription Strip line Subscription Tabular report design TechEd Threshold line Top values User-related report content User authentication User prompted to login Wrox book
RSS
RSS Feed
Blog at WordPress.com.
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.
%d bloggers like this: