Things to Consider When Planning a BI System / Report Conversion Effort

Overview:  It’s common for companies to consider moving from another BI platform to Microsoft BI in order to reduce licensing costs, to take additional advantage of familiar tools, or to expand the body of workers capable of developing and supporting the Microsoft BI system.  Following are some considerations when embarking on such a project.

Fact-Finding about Existing Reports

One of the first things you may wish to tackle is creating an inventory of reports to be converted.  Good thinking.  You may want to do this in parallel with some of the other items listed in the other sections.

  • Consolidation opportunities (for example, could 8 reports be consolidated to 1 with use of appropriate parameter selections?)

  • Level of complexity for each report (so that perhaps you can try to start with easier reports before tackling more difficult ones)

  • Priority level of each report (start with: high value, less effort)

  • Category of each report (such as by subject area, or by data source)

  • Data source(s) required for each report (and will all data sources exist going forward?)

  • Interactivity requirements for each report (such as hyperlinks, drill-down, drill-through)

  • Decision of which reporting tool is most suitable for each report (SSRS and Excel and Power View and PerformancePoint all have their particular strengths)

  • Any cosmetic changes, improvements, or fixes permitted during conversion (if any changes are permitted during conversion that is)

  • Export requirements (this may drive decision on which reporting tool in the Microsoft BI toolset is most appropriate)

  • Subscription and alerting requirements (this may drive decision on which reporting tool in the Microsoft BI toolset is most appropriate)

Planning - BI Change Management

This section includes some of the more broad items to consider which can affect the scope and timing of the project.

  • Identify business rules from the previous toolset's metadata layer (here we are looking for logic embedded in another reporting tool’s metadata layer that needs to be replicated in ETL or elsewhere in the BI system – reproducing a Business Object Universe or a Cognos Framework Manager Model can be done with the BI Semantic Model, but it could take you by surprise and expand the scope & the schedule)

  • Identify data movement and ETL processes used by the previous toolset (here we are looking for any data sources that will no longer exist, or need to be replicated in the new system)

  • Understand security constraints for report access and data access (obviously very important – how much work this is depends on the particulars of your environment and the role the old BI system may have played with regard to security)

  • Develop process for testing & validating converted reports (is a comparison of new results vs. old results acceptable for testing, and does the testing require sign-off from a functional user?)

  • Determine if an automated conversion tool will be used, or if all reports will be redesigned from scratch (this is a big decision – no tool can convert every single report 100% perfectly, but it might save some time especially for basic reports)

  • Identify needs and requirements for governance, auditing and change management (such as approvals required and version history to retain, among many other things)

Planning - BI Training and Documentation

You can’t spend too much time on training and documentation.  Trust me on this.

  • Determine how report developers will get trained on new set of tools (perhaps a combination of classroom training, on-the-job training, and assistance from a consulting firm that specializes in Microsoft BI)

  • Determine the extent of user training that will be optimal

  • Finalize naming conventions & reporting standards (to facilitate a consistent user experience & more efficient development experience)

  • Finalize documentation requirements (this may or may not change with the introduction of a new system)

Planning - BI Environment

These items are focused on the technical components of the BI infrastructure.

  • Planning for the new BI portal (this is the delivery piece – I can’t overstate how important it is to plan this out well)

    • Structure of the overall site (often this is a SharePoint BI Center which includes the enterprise site and team/departmental sites)

    • Structure of report libraries (often by subject, by user base, and/or by report type)

    • Structure for supporting objects (such as data sources, starter reports)

    • Structure for training and documentation materials (such as quick start guides, FAQs, how-to videos)

    • Need for custom metadata columns (such as purpose, description, report owner, who to contact for help)

  • Plan for the backend BI environment (this is the ETL and database piece – critical to ensure performance is optimal)

    • Server architecture (including whether you wish to build it yourself, take advantage of a reference architecture, or if an appliance is most suitable for the workload)

    • Software editions & versions (drives which capabilities are available)

    • On-premises vs. cloud vs. hybrid solution

  • Security for the BI environment, including access to ad-hoc reporting tools and data source connections (the structure of the new BI portal will often be very inter-mingled with security requirements)

  • Template(s) for standardization of report sets (to speed developer productivity and ensure a consistent user experience)

  • Preparation for ongoing monitoring of query loads from new system and resources required to continue to monitor

  • Plan for how Self-Service BI will complement the Corporate BI system, if applicable

ROI and Cost/Benefit of New BI System

This type of analysis can be difficult.  The organizations that do formal ROI and payback calculations already have formulas to do this, so I’ll just list a few things to consider with a BI system.

  • Costs: Include things like hardware, software licensing, cloud services, consulting services, IT support time, developer time, administrator time, time invested in training, ongoing software maintenance costs, etc

  • Benefits: Include things like cost savings, improved productivity, increase in sales, increase in customer retention, new capabilities, increase in agility, support for strategic goals, faster access to information, broader access to information, etc

Strategies for User Adoption of New BI System

Here you want to consider things that will encourage adoption of the new system.  If a user has a poor initial experience with a system it can take a long time to win them over again.

  • Communication plan (such as timing of rollout, what is expected during testing cycles, schedule of reports to be converted, what users should do during the interim)

  • Support plan (first level support, second level, and if “power users” will get involved with support at all)

  • Training plan (if applicable – at a minimum, some helpful documentation is usually a great idea)

Hope this gives you some things to think about.


Documenting a Reporting Project

Documentation:  everyone’s favorite thing to do on a project, right?  Especially the developers – they can’t get enough of it.  Okay, okay, back to the real world.  Technical documentation is thought of as a necessary evil by most IT folks.  In my opinion, if it’s useful then it’s great.  What makes it useful?  Read on.

I was asked on Twitter how I go about documenting my SSRS projects.  Seeing as I have a lot more to say than 140 characters (what a shocker!), here’s my thoughts & experience on the subject.  Thanks to Cody Konior  ( Blog | Twitter ) for the inspiration.


This entry proposes the following types of documentation for a reporting project:

For the overall reporting project

     1.  Inventory of Reports           {updated as needed}

     2.  Report Design Guide            {updated as needed}

     3.  User Guides and Tutorials     {updated as needed}

For individual reports

     1.  Requirements                     {temporary}

     2.  Report Definition                 {updated as needed}

     3.  Testing Results                   {temporary}

Keep reading for more information on the above 6 suggestions…

Purpose & Audience

When deciding what documentation you need, the first thing to ask is: Who is the audience for this document?  Knowing the audience helps leads into what the purpose for the document is.  For example, documentation could be used for:

  • Reference Guide (For yourself or others?  As part of a library of reports?  As part of a standardized set of reports?)
  • Training or Tutorial (Perhaps someone else will support the reporting project?  A new employee in a similar role as you?)
  • Communications and confirmations with business users
  • etc…

Typically I state the audience and purpose at the top of each document, just before the executive summary if it’s a very long document.

Goals of Documentation

I tend to have the following goals for documenting a reporting project:

  • Standardization.  When certain things are standardized, I like to have them listed out.  The Report Design Guide is an example of this.
  • Efficiency.  Provide info about business rules/calculations/derivations without having to open up the report (or an underlying stored procedure, or function, etc).  (As a sidenote:  sometimes when I am documenting/explaining something, it dawns on me that I have a hole in my logic – it really does force you to think through your code.)
  • Understanding.  Explain why certain decisions were made.  The goal here is usually to get this type of information on paper – otherwise it’s just in someone’s head.

Useful Types of Documentation for the Overall Reporting Project

1.  Inventory of Reports

The report inventory lists each report, as well as other metadata about the reports.  This is a great candidate for a data-driven solution (as opposed to a manually maintained document).  Table(s), SharePoint Lists, or another data-driven mechanism to store the report metadata works really well – if it’s associated with the report header (ex:  Title; Subtitle) and/or footer (ex:  Path; Version #), you are forced to maintain this inventory which serves as an added bonus.  Things you may want to include:

  • Report title
  • Report number
  • Report description
  • Path of report (relative URL)
  • Intended audience
  • Version number
  • Support contact
  • Active (Y/N)

2.  Report Design Guide

A Report Design Guide, or whatever you choose to call it, is your standardized list of things a report developer should know in order to ensure the same look & feel is maintained throughout the entire set of reports.  What we don’t want, even in a small organization, is for one person to develop a report with brown & maroon colors in a 12 point font & hyperlinks underlined, and another person to develop with blue & tan in a 10 point font & hyperlinks in blue but not underlined – you get the drift.  In my opinion, a consistent look & feel - both cosmetically & with how interactivity takes place – significantly enhances the usability of reports.

This type of documentation may be subdivided up by different types of reports:  tabular, chart/graph, and scorecards.  You might choose to include items such as:

  • BIDS solution and project guidelines (i.e., how projects are logically divided; project naming conventions)
  • Data access guidelines (SSRS embedded code; stored procedures; views)   (See this entry for more considerations)
  • Standardized report header contents (ex: title; subtitle; parameter values selected; company logo) and placement of each
  • Standardized report footer contents (ex: page 1 of x; report path; datetime executed; name of user executing report—name is particularly helpful if data is confidential) and placement of each
  • Requirements of a “Data As Of” date (i.e., to let user know age of the data being displayed)
  • Inclusion of logo (example:  include on more formal or external reports; omit on internal reports)
  • Report naming conventions
  • Report numbering conventions
  • Font name, size, and weight (header, footer, table header row, tabular data, subtotals, grand totals, etc)
  • Vertical alignment (ex: table header row is bottom justified; all other data is top justified)
  • Horizontal alignment (ex: amounts are right justified; text is left justified; some integers may be centered)
  • Usage of borders (ex: suppress vertical borders but display horizontal borders for readability)
  • Parameterization standards (ex: dates sorted descending; usage of defaults & if they may differ per sets of users; standard parameter labels)
  • Orientation default (portrait or landscape)
  • Physical page size (height, width, and margin defaults for printing purposes)
  • Interactive page size (if allowed to differ from physical page size)
  • Report properties which should be completed (ex: author and description)
  • Formatting of hyperlinks (ex:  blue and underlined)
  • Standardized “no data” message and its formatting (a Tablix property)
  • International currency, date, or language considerations
  • Rounding (ex:  if summary report, decimals may be rounded; if detail (drill-through) report, display decimals)
  • Scaling (ex: to 10s or 100s or 1000s)
  • Conditional display of amounts (example:  red if < 0)
  • Display of signs on debit and credit balances (i.e., should credits be shown as negative numbers or not)
  • Display of percentages (how many decimal places)
  • Display of zeros (suppress 0 or display 0s; are hyperlinks enabled on 0 amounts)
  • Display of negative numbers (with parentheses or – sign)
  • Usage of backcolor or shading on every other row
  • Usage of $ symbol (ex: just on grand total)
  • Drill-through behavior to another report (i.e., launch a new window or remain in same window)
  • How images are handled (in database, project, or embedded within individual report)
  • Usage of shared datasets for reusability among various reports (requires SQL Server 2008 R2)
  • Requirement to repeat table header row on each page of report and/or remain fixed when scrolling  (See this entry for how this is done)
  • Standardized usage of color, particularly for red/yellow/green indicators (ex: RGB values)
  • Standardized indicators utilized to display status or trend

3.  User Guides and Tutorials

Some companies choose to publish things like Quick Start Guides, User Guides, or other types of tutorials, to help users get started with a new system.  These may or may not include hands-on labs or exercises.  This type of documentation could cover things such as:

  • Accessing the system
  • Login questions & security issues
  • System requirements
  • Navigation of screens, folders, libraries
  • Using toolbars
  • Printing
  • Saving
  • Exporting (ex: to PDF or Excel)
  • Searching
  • Frequently Asked Questions (FAQs)
  • Data dictionary
  • Glossary
  • How to create reports (if tools like Report Builder or Excel Services is offered in addition to ‘canned’ or ‘menu’ reports)
  • Help desk or support contact information

Useful Types of Documentation for Each Individual Report

1.  Requirements

Ideally, the requirements are handed to you & include all details needed to develop the report.  If the organization takes their requirements gathering process pretty seriously, it may also include use cases.  The contents of the requirements is so similar to item 2 next, that I won’t list them here.  Read on.

2.  Report Definition

I prefer an individual report definition file to be in the format of a quick reference guide, and as short & to the point as possible.  Although there’s some overlap in this document from the requirements, the Report Definition can continue to be updated over time whereas the requirements typically aren’t in my experience.  Also, the Report Definition ends up being a lot more detailed (especially if you create it during development rather than after – hint, hint).  If you do have something like a Report Design Guide (discussed above) in place, don’t repeat those items here – just reference that one “master” document.  Report-specific things you might want to include:

  • Owner of report
  • Subject matter expert(s)
  • Data source(s)
  • Fields (including where the source is, or if it should be derived)
  • Business rules
  • Calculations
  • Parameters (including defaults)
  • Filters
  • Grouping
  • Sorting
  • Page breaks
  • Intentions for printing vs. online viewing
  • References to embedded or external code
  • References to related database objects (stored procedures, views, functions)
  • Sample of the report (screen shot)
  • Security considerations
  • Intended delivery mechanism (run on demand vs. delivered by subscription)
  • Usage of caching and/or snapshot
  • Future maintenance tasks or manually maintained items
  • Relationship of report to a specific ETL process
  • Departures from the Report Design Guide (with a quick note as to why)
  • Change log (who requested what change & when; who executed the change)

3.  Testing Results

This is essentially a checklist of the tests performed, and the pass/fail results.  Depending upon the procedures required in your organization, this could be some quick unit tests for accuracy & functionality, or it could be a huge major deal that also includes system testing & integration testing as well.  Testing of an SSRS report may includes things like:

  • Data accuracy
  • Acceptable performance
  • Data level security accuracy
  • Functionality of report parameters
  • Export functionality
  • Print functionality
  • User acceptance
  • etc…

Have fun writing your documentation!  If you have any suggestions or additions, I’d sure appreciate if you’d leave me a comment.