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-premise 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.



Time for a Change

I have joined BlueGranite, a Microsoft Business Intelligence and Data Warehousing firm.  BlueGranite has an excellent reputation, and has a ton of interesting projects for a BI developer like me to grow my skills.  As a Microsoft NSI Partner (National Systems Integrator), BlueGranite delivers enterprise BI and self-service BI solutions, in addition to predictive analytics, cloud and hybrid BI, big data, strategy, architecture, and training.  If you’re not familiar with BlueGranite, you might want to check out their blog which has some great information, and MSBI Academy which is a terrific library of free instructional videos.

Just a few words about my former employer, Intellinet.  They are a great firm with not only smart people, but good-hearted people.  I wouldn’t hesitate to recommend them to a potential customer or employee.  They’ve been very good to me and I will miss them.  To accomplish what I want career-wise, I’ve decided that a BI-centric firm is a better fit for me than a generalist firm.

My husband has started joking that he’s going to claim he doesn’t remember where I work anymore.  I told him that since I’ve kept the same darned husband for 20 years now, I’ve got to change up something else once in a while.  How could he possibly argue with that logic?  

Am really looking forward to my fun change ahead!



Using an SSRS URL Which Does Not Display Breadcrumbs

Overview: Quick tip about how to structure the URL if you wish to render an SSRS report without any breadcrumbs being displayed at the top left of the browser window.  Applicable to Native Mode Reporting Services configuration.

Why We Want a URL Without Breadcrumbs

I have a client who has a Reporting Services implementation.  Since it’s native mode, the Report Manager SSRS application is where reports are stored and rendered. 

Normally breadcrumbs are really helpful for users to navigate various pages and find their way back from where they came.  However, in this situation the requirement is for users to access reports solely from a menu-driven report portal.  Each menu choice in the report portal contains a URL which runs the report.  This means that, although Report Manager is responsible for rendering each report from the custom menu, users should not navigate or browse around Report Manager itself.

The Report Manager URL Shows Breadcrumbs

When you execute a report via Report Manager, the URL you see in the browser will be structured like this:


(The actual URL path will vary based on your environment.)

When this “standard” SSRS URL from Report Manager is used, see how the top left of the browser window has breadcrumbs?  Usually that’s great, but recall that we have a requirement to make sure that the user does not browse around Report Manager but, rather, access reports solely from the menu links made available to them.  So, this won’t work for our purposes.


The Report Server URL Omits Breadcrumbs

Using alternative syntax which utilizes the Report Server Web Service rather than the Report Manager application, the breadcrumbs at the top left are suppressed. Mission accomplished!



Depending on your configuration, if you have a named instance or not, and if you changed the default setting for the virtual directory, the above paths could be different. 

Finding More Information

MSDN – Configure Report Server URLs



Why “Power BI” is a Big Deal

A preview of Power BI will be available later this summer.  Here’s a recap of what I’ve learned so far + why I think this is a very big deal for the world of Microsoft Business Intelligence.

Excel is Primary Tool for Self-Service BI

There are four components to Power BI, and all are delivered via Excel.  Microsoft is capitalizing on the fact that a huge percentage of business users are most comfortable in an Excel environment.  Historically a large portion of Microsoft BI was developed with SQL Server and SharePoint tools – i.e., software predominantly available to IT and BI professionals.

The newest Excel capabilities lower the entry point of BI to anyone who has Excel and an interest in working with data.  SQL Server and SharePoint will continue to be my professional-level tools of choice for creating and delivering Corporate BI solutions.  Meanwhile Excel has emerged as the primary mechanism for creation and delivery of Self-Service BI solutions.  This continues to build upon the Personal BI > Team BI > Corporate BI framework promoted by Microsoft using a combination of Microsoft Office that’s installed on the desktop, Office 365 in the cloud, SQL Server and SharePoint.

Four Components to Power BI

Two of the Power BI components are for data preparation:  Power Pivot and Power Query.  The other two are for data visualization:  Power View and Power Map. 

  • Power Pivot.  Imports and integrates data from various sources for the purpose of creating an in-memory data model.  This allows a functional user to combine various data sources which improves its value – classic examples here are the addition of weather data or demographics data to corporate sales data.  The Power Pivot data model also supports calculations, derived fields, aggregates, hierarchies and key performance indicators.  The ability to create a data model with a relatively low learning curve is a very big deal for data analysts, particularly for one-time analysis type of situations.  It can also be helpful to IT and BI folks for prototyping.
  • Power Query.  Formerly Codename Data Explorer, this tool will be able to search for data (just like a web search experience) online or within your corporate data sources.  The selected data set is immediately imported into an Excel table – this search feature alone is a game-changer.  Some of the native data connectivity it offers are truly distinctive and make data access far easier than it’s been in the past.  It also allows you to do things like merge data, rename columns, replace values and other data modifications in a step-by-step process.  A very big deal here is that the step-by-step data cleansing and shaping process is savable and therefore repeatable.  You can also view the state of the data before and after each step.  Optionally, you can bring the data into your Power Pivot model to continue working with the data even further.
  • Power View.  Power View is an interactive data visualization tool.  It does certain things like highlighting and cross-filtering really well with very little learning curve.  Working with data in Power View bears similarities to working with Excel Pivot Tables as well as to PowerPoint.  In addition to charts, graphs, and table data, Power View also supports maps which pan and zoom via integration with Bing Maps.  The naturally interactive behavior of Power View is what makes it a very big deal.
  • Power Map.  Formerly Codename GeoFlow, Power Map is a 3D mapping tool.  What makes it unique is the ability to save scenes and play them over time in a tour.

Cloud-Based Self-Service BI

Power BI will be delivered in Office 365 (which actually uses SharePoint Online).  Although the preview that starts later this summer will use Office 365, all 4 Power BI tools are available to use as Excel add-ins now (some functionality is not fully supported within on-premises SharePoint).  Considering Microsoft has a cloud-first strategy, it’s not a big surprise that the initial preview will be with Office 365.  It’s unclear currently if all Power BI functionality will be available to on-premises SharePoint sites following the initial cloud-based rollout.  I hope so.

This focus on the cloud is a big deal, especially for small and medium-sized businesses.  It can also be a good thing for business units that need self-service capabilities but don’t have IT support or resources to fire up the required infrastructure.  Even large corporate environments can find some benefits to augmenting their on-premises BI environment with the cloud – especially if they want to upgrade to newer versions in the cloud faster than they can upgrade on-premise versions (i.e., a company-wide upgrade to Office 2013 can be a big task).  Creating the SharePoint and SQL Server server infrastructure to support Excel Services, PowerPivot for SharePoint, Reporting Services, and Analysis Services is not an easy task.  Not having to invest so much time, effort, and cost into setting up this infrastructure will be welcomed by many organizations.  However, there are certainly some obstacles which prevent some companies from using Excel in Office 365 even if they wanted to.  It will be interesting to see to what extent companies embrace the cloud, or utilize a hybrid approach to on-premise and cloud BI.

Mobile Support

Quite obviously mobile support is a big deal … Microsoft has announced native mobile applications for Windows 8, Windows RT, and the iPad.  HTML5 will be used for browser-based mobile delivery on other types of devices.  The way I understand it, to see a report on a mobile device it’ll need to be published to a Power BI workspace in O365, and in the properties of the report you’d set it as a “Mobile Favorite.”  Translation:  companies with Excel and Power View in SharePoint now won’t get to take advantage of this mobile functionality right away.


     Source for Image:  Power BI Demo by Amir Netz available here:

My understanding is Silverlight won’t be immediately removed from the stand-alone Power View application in or the SharePoint PowerPivot Gallery, but I’m guessing that’ll come in time.

Data Search and Refresh

Office 365 will utilize collaborative BI workspaces optimized for BI functionality.  Users will be able to publish Excel workbooks to workspaces and securely share them with coworkers.  Reusable data connections can be published, and data refreshes can be handled on-demand or on a schedule.  Data refreshes can access corporate data on-premises as well as data online (with proper security credentials of course).  Being able to search and access your enterprise data is a very big deal (this sounds like something the IT folks will need to facilitate for self-service users to take advantage of).

Queries created from Power Query will be able to be searched and shared among users.  This sharing of queries is a big deal – anything that promotes reusability & consistency gets a big thumbs-up from me.


     Source for Both Images:  Power BI Demo by Amir Netz available here:

Tracking of Data Usage

A new feature I’m particularly excited about is the tracking of data usage.  Stats re: how often data sources & reports are accessed will be readily accessible to those who build models and reports.  This is currently a feature of Central Administration in SharePoint, or by querying the Power Pivot Management Data tabular model if you don’t want to grant Central Admin access to a non-SharePoint administrator.  I look forward to seeing how easily this information can be reviewed for one report, one data source, or for many reports and data sources.

Easy access to these stats is a big deal because if you see usage going way up, then there’s an awareness that this particular report or data source has become critical.  This might mean a data refresh failure is of greater urgency, or data quality is of even greater concern.  Let’s say it’s a Power Pivot model that’s become critical and it continues to grow larger and more complex – that might be a perfect scenario to upgrade the self-service Power Pivot model to a corporate Tabular model in Analysis Services.

Data Stewardship

Lastly, a big component of Power BI is the concept of data stewardship.  There’s not a lot of public information about this available yet.  This will include management of security, data refresh permissions, tracking data usage, as well as a few other things that we’ll learn more about when the preview is available.  I’ve typically thought of data stewardship with the mindset of data quality and/or master data management, so I’m extremely interested in seeing the direction Microsoft will take with this.  Regardless of how you define it, data stewardship is the kind of thing that can really make a self-service BI initiative more successful and/or less problematic – so I think this concept of data stewardship will end up being a very big deal.

Finding More Information

Register for the “Microsoft Power BI for Office 365” preview – Office Site

Chris Webb’s Blog – Some Thoughts About Power BI

Jen Underwood’s Blog – Microsoft Office 365 Cloud Power BI

Jason Thomas’ Blog – Power BI for Office 365

Jamie Thomson’s Blog – Thoughts on Power BI for Office 365

John White’s Blog – Power BI-What Is It?



Report Builder … the Red-Headed Stepchild of Self-Service BI Tools?

A conversation on Twitter earlier this week has inspired me to pose this question…Is Report Builder the “Red-Headed Stepchild” when it comes to the Microsoft Self-Service BI toolset?  Report Builder doesn’t get much attention these days, for various reasons we’ll discuss below.

(Note:  The perspective of this blog entry is Self-Service BI so Report Builder is the reference – however, everything stated is applicable to full-fledged Reporting Services as well.)

Is Report Builder Deprecated?

Nope!  Report Models, however, are deprecated.  As of SQL Server 2012, you can no longer create or update Report Models (SMDL files).  You can continue to use Report Models currently, but it is recommended to move away from them as time allows.  Report Builder itself is at version 3.0 and is a mature product.

Self-Service BI Tools

The primary set of Microsoft Self-Service BI tools includes Excel (+ add-ins for data modeling such as PowerPivot), Power View, and Report Builder.  Some people actually wouldn’t include Report Builder in this list, but I do believe it has a valid place as one of the Self-Service BI tools (albeit, a smaller audience & used for specific purposes).

Drawbacks of Report Builder

  • Learning curve / ease of use for report designer.  There are a significant number of properties and options.  This offers significant control over the output – the cost for this significant control is ease of use because all the options in Report Builder can be a bit overwhelming for the casual business user.  However, it’s not overly difficult to use for technically adept users who enjoy working with reporting tools and data.
  • Limited interactivity.  While there are some interactive features (such as drill-down, drill-through, sortable columns), each has to be explicitly defined by the report designer.  Report Builder isn’t dynamically interactive like Power View or even Excel – rather, Report Builder is far more suitable for fully formatted reporting needs.
  • Longer to develop.  There’s some things that can be done with Power View or Excel that are inherently more work to do in Report Builder.  The first example that comes to mind is hierarchies – with Power View or Excel, you drop a hierarchy onto the row & the navigation up/down works; with Report Builder you’d have to set up what is shown vs. hidden and the toggle properties.  This is not overly difficult to set up, but could be frustrating for someone just getting started with Report Builder.
  • GUI support is limited.  There is a drag & drop graphical interface for SQL Server and Analysis Services data sources (plus a couple of others).  This limited support leaves the report designer writing query syntax sometimes – which is obviously not the most user-friendly for non-technical folks.  Currently the nicest way for users to work with Report Builder is using a BISM data source (i.e., the data is stored in Analysis Services or PowerPivot).  SQL Server (relational) can be ok for users to work with if the data sources are made to be understandable & easy to work with (ex: with reporting views or stored procedures) – this takes some effort from the IT Dept. or BI Center to make sure it’s made suitable for self-service.

Positives of Report Builder

  • Native connectivity to many data sources, including BISM.  The Microsoft BI framework is very different from other BI tools (such as Cognos or Business Objects) which require a metadata layer – i.e., a report model between the data source & the reporting tool.  Microsoft permits tremendous flexibility here – in fact, you can natively send queries from Report Builder to a variety of databases including non-Microsoft.  Power View is very limited in terms of accepted data sources, and Excel can be somewhat limited (unless you bring the data into an intermediary PowerPivot model first – PowerPivot offers great flexibility in this regard, but do realize you are storing the data redundantly). 
  • Significant formatting control.  If you need a pixel-perfect highly formatted report, Report Builder is the tool for you.  Ironically, this is the inverse of the “learning curve / ease of use” drawback listed above.  With Excel you can exercise a lot of control over the look & feel of your report (except Pivot Tables – you have to use formulas if you need to "break” out of the Pivot Table).  Power View has some text size control and some color schemes to choose from, but overall offers minimal user control over formatting (after all, it’s a data discovery tool meeting an entirely different need – and it is purposely trying to remain simple).
  • Consistent RDL file format.  If a business user starts a report in Report Builder and needs some help with it, one of the IT or BI folks can open the report using their tool of choice (BIDS or SSDT in Visual Studio), make some updates, and send it back to the user with the file format intact.  The consistent format is also helpful if a report is being promoted from the Self-Service environment to the Corporate BI environment.
  • Reusable elements.  To facilitate Self-Service BI using Report Builder, things such as shared data sources (reusable data connections) are obvious but there are others as well.  Shared datasets (reusable queries) can be really helpful to handle common things like Dates and Geography.  Report parts (reusable charts, graphs, tables) can be helpful to display commonly used elements.  Images can also be stored centrally for reuse.  Setting up reusable elements does take some effort from the IT Dept. or BI Center though, but can improve the Self-Service user’s experience tremendously.
  • Parameterization.  Reports with a number of parameters (within reason of course) can sometimes be thought of as “guided ad-hoc analysis” because one report can yield many different combinations of results depending on parameter values.  Report Builder handles parameterization well.
  • Subscriptions and alerts.  If you wish to have reports delivered to you at a predefined frequency or based upon a condition, then Reporting Services is the tool to make that happen.
  • Export and RSS capabilities.  Report Builder can export to many different file formats.  It can also publish an RSS feed – this can be very useful for a business user to consume existing aggregated/calculated data that has been rendered by Report Builder without recalculating or reinventing anything.  Utilizing published report data via RSS also helps with the elusive “one version of the truth” that’s a constant challenge.
  • Integration with SharePoint.  With a Report Builder report, you can view or edit the report directly from the SharePoint document library (with appropriate permissions of course).  Alternatively, a Report Manager portal can be used (although it would be used in isolation from other BI tools and reports).

So, even though sometimes Report Builder seems to be the “Red-Headed Stepchild” I very much appreciate having the tool in our toolbox.  I hope it’s alive and well for a long time. 

Got any thoughts on this subject?  Leave a comment … I’d love to hear your thoughts.



Resources for Installing and Configuring BI with SharePoint 2013 and SQL Server 2012

I’m embarking on a new project to build a brand new squeaky clean VM environment from the ground up, including SharePoint 2013, SQL Server 2012, and Office 2013.  This will serve as my new “on-premises” environment for use with Hyper-V.

image                   image                               image

Below is a list of resources I’ve found so far relating to installing and configuring BI with SharePoint 2013, Office 2013, and SQL Server 2012.

Blogs & Articles

MSDN – Install SQL Server 2012 Business Intelligence Features   <--There’s a ton of sub-pages here with checklists & info

Koen Verbeek – 10-Part Series on Building a SharePoint 2013 BI Demo Environment  <—Really good stuff here!

MSDN - Content Roadmap: Set up and configure SharePoint and SQL Server BI

Bhavik Merchant – Setting Up a SharePoint 2013 BI Demo VM

Simon Lidberg – Installing the BI Features of SharePoint 2013

Lydia Bronze – Step-by-Step Guide to Installing SharePoint with SQL 2012 PowerPivot, Power View, and Reporting Services

Kay Unkroth – Microsoft BI Authentication and Identity Delegation

James Beresford – Build Your Own SQL 2012 Demo Machine

Anthony Sammartino – SharePoint 2013 Installation and Configuration for Power View

James Serra – SQL Server 2012 and SharePoint 2013: Installing on a Virtual Machine


MSBI Academy Videos – 23-Part SharePoint Deployment Crash Course      <—Definitely worth a look

Dave Wickert – Deploying and Managing a PowerPivot for SharePoint Infrastructure Using Microsoft SQL Server 2012

Chuck Heinzelman – Configuring Kerberos for Microsoft SharePoint 2010 BI in 7 Steps (SQL Server 2012) 

Lee Graber – 4-Part Series on PowerPivot for SharePoint Architecture

Know of other great articles or videos?  Please leave a comment so I can add it to the list.