Microsoft BI Integration for the Four Primary Report Types

We are currently experiencing a major evolution of the MSBI reporting toolset in accordance with the Microsoft BI Roadmap announced in October 2015. This diagram depicts current state the way I understand it.

(Updated as of mid-June 2017)

Also, here's a short video walking through the diagram:  http://www.blue-granite.com/blog/video-overview-of-microsoft-bi-reporting-tool-integration

  • Analytical reports:  produced in Excel
  • Interactive reports: produced in Power BI Desktop
  • Paginated reports: produced in Reporting Services (SSDT) or Report Builder
  • Mobile reports: produced in the Mobile Report Publisher (previously Datazen)

The four primary report types are in the process of becoming more deeply integrated into:

  • The Power BI Service (a cloud service), and
  • Power BI Report Server (on-premises portal option - includes 3 report types)
  • Reporting Services portal (on-premises portal option - includes 2 report types; requires SQL Server 2016 in native mode). 

As you'll see on the above diagram, every report type isn't compatible everywhere yet though I expect we'll see that over time. As things evolve, I'll keep the diagram updated. Hope you find it useful.

Following is how each of the report types look in the SSRS portal and Power BI when this post was originally written. Since then, there have been quite a few changes (i.e., Power BI Reports can only be rendered in Power BI Report Server -- not in a traditional SSRS portal).

Comparison of Datazen vs SSRS vs Power View (Power BI)

My first thoughts when I heard about the Datazen acquisition were…How is it different? When do I choose to use it? What are its strengths?  I summarized some high level use cases in this blog entry I published on the BlueGranite site. In this post I would like to go through some detailed comparisons between Datazen, Reporting Services, and Power View. Note that I’ve chosen to focus on Power View rather than Power BI as a whole because Power View is the primary visualization component within Power BI (whereas Power Query and Power Pivot are more about data shaping and data modeling which isn’t as directly relevant when compared to Datazen and Reporting Services). For brevity I’ve omitted Excel and Performance Point from the comparison as well. Also, Power BI for Office 365 is *not* addressed here since it’s being phased out for Power BI v2 – any Power BI references here refer to the Power BI Preview also commonly known as v2.

References below are applicable to:
-Datazen Version 3.0
-Reporting Services in SQL Server 2014
-Power View for Excel 2013
-Power View for SharePoint 2013
-Power View in Power BI (v2)

This analysis is as of mid-June 2015, and will certainly evolve and change as Datazen becomes more integrated in the Microsoft ecosystem, as Power BI evolves, and with SQL Server 2016 coming out soon. 

Logo - Datazen.png

Overview

 

Datazen

Reporting Services

Power View / Power BI

Primary purpose

Traditional structured reporting (pre-defined queries and drill paths)

Traditional structured reporting (pre-defined queries and drill paths)

Data discovery / interactive exploration

Architectural mode

Standalone (supports Excel file sources only)

or

Datazen Server (opens up many more data sources, sharing, mobile delivery, refreshing, use of live tiles, use of KPIs)

Native Mode

or

SharePoint Mode

Power View for Excel

or

Power BI Designer

or

Power View for SharePoint

 

Both Excel and Power BI Design can be used standalone or in conjunction with the Power BI portal (SaaS cloud app)

Installation components

Server: Datazen Server (core service repository is built on RavenDB)

 

Client: Datazen Publisher (available in Windows store)

 

Mobile viewer apps: Available for iOS 7+,

Android 4.1+,

Win8+,

Win Phone 7.1+

Native Mode: Report Manager with ReportServer

 

SharePoint Mode: SharePoint with ReportServer

 

Client: Visual Studio with SSDT or BIDS

and/or

Report Builder (click-once app)

Power View for Excel (enabled in Excel)

or

Power BI Designer (standalone download)

or

Power View for SharePoint (part of SSRS in SharePoint)

 

 

 

Deployment and Delivery

 

Datazen

Reporting Services

Power View / Power BI

Delivery approach

On-premises (or IaaS)

On-premises (or IaaS)

Hybrid (full functionality)

or

On-premises (subset of functionality)

Access types

Native mobile apps (calls REST APIs)

or

Web browser viewer (HTML5)

or

Mobile device chrome shell

or

Embedding in a custom application

Report Manager web app (Native mode)

or

SharePoint

Excel (client)

or

SharePoint

or

Power BI portal (SaaS cloud app)

Mobile delivery capabilities

Native apps:

iOS,

Android,

Windows

+ HTML5 in browser

No native apps

 

Partial support across mobile devices and browsers (requires SQL Server 2012 SP1+)

Native apps:

iOS,

Windows,

Android

+ HTML5 in browser

 

(Excel and SharePoint still based on Silverlight)

Organization of content

Hub in the Datazen Enterprise Server

Native mode: folders in Report Manager

 

SharePoint mode: sites, document libraries, folders in SharePoint

Structuring content across functional areas and/or user groups is not yet released in Power BI v2

Support for anonymous users (public)

Yes (guest account)

Yes (custom authentication)

Not yet

Support for row-level security

Yes (personalized data views)

Yes (if data source is using Windows authentication; not applicable to subscriptions which require ID/PW to be specified)

Yes (only if using SSAS Tabular data source in Power BI v2 which passes EffectiveUserName)

 

 

Development

 

Datazen

Reporting Services

Power View / Power BI

Design approach

Design-First (layout with simulated data to refine design before associating layout elements to data views)

or

If preferred, Data-First

Data-First (report connects to an embedded or shared dataset query)

Data-First (report connects to an existing model or data source)

Development environment

Dashboards: Datazen Publisher (a Win 8+ app also known as Datazen Designer)

 

KPIs, Data Connections & Data Views: Control Panel (web app)

Report Designer (Visual Studio/SSDT/BIDS)

or

Report Builder (click-once app)

Power BI Designer

or

Power View for Excel

or

Power View for SharePoint (web)

Typical content creator

Dashboards:

IT developer

or

Power user

 

Data Connections & Data Views: IT developer

or

Proficient power user (need to be able to write SQL, MDX, or DAX queries as there’s no drag & drop UI)

IT developer

or

Proficient power user

Data analyst (created purposely to have a minimal learning curve)

Creation of underlying report query

Need to write query which is contained within a data view (need to write syntax)

Need to create query which is contained within a dataset (can write syntax or use a UI)

Drag and drop (cannot write specific underlying query)

Designer

‘Snap to Grid’ system (ex: 5 rows x 6 columns)

 

3 layouts: Master, Tablet, and Phone

Free-form layout (with precise alignment ability in body, header, and footer)

Free-form layout (without precise alignment ability)

File extension

.datazen

.RDL

.XLSX (Excel)

or

.PBIX (Power BI Designer)

or

.RDLX (Power View for SharePoint)

 

 

Features

 

Datazen

Reporting Services

Power View / Power BI

Parameterization

3 types:

Time navigator,

Scorecard grid,

Selection list or tree

Parameter pane

Slicers (placed on the report body)

or

Filters pane (page level or for individual table/chart)

Color palette

Style selections applicable to entire dashboard

 

A custom color palette can be defined using configuration files if desired

Standard color selections for individual charts

 

Can customize colors using custom color palettes, expressions, or custom code

Style selections applicable to entire report

Pixel-perfect formatting control

No

Yes

No

Switch chart types on the fly

No

No (potential workaround with parameterization and show/hide properties)

Yes

Interactive cross-filtering and highlighting behavior

No

No

Yes

Calculations and expressions

Straightforward options (Sum, Avg, Count, Min, Max, First, Last)

Many options within the dataset, expressions, and custom code

Straightforward options (Sum, Avg, Min, Max, Count)

Built-in maps

Bubble map,

Gradient heat map,

Range stop heat map,

Custom ESRI shapefile

Bubble map,

Filled map,

Line map,

Marker map,

Custom ESRI shapefile

Bubble map,

Filled map

 

Uses Bing Maps API

Multiple data sources allowed per individual report / dashboard

Yes (needs a join key in some cases)

Yes (one data source per dataset is general rule, though a tablix expression can reference another dataset and/or lookups can be utilized)

No (workaround is to integrate data first in underlying data model)

Shared datasets (i.e., reusable queries across multiple reports / dashboards)

Yes (data views on the server can be shared within the scope of a hub)

Yes (embedded dataset can be promoted to be a shared dataset)

Yes (In V2, datasets published to the Power BI service are independent objects from reports and dashboards

Shared report elements (i.e., reusable charts and tables across multiple reports / dashboards)

Report elements: Not shareable across multiple master views or multiple dashboards (tablet and phone views utilize some or all elements from a single master view)

 

KPIs:  KPIs are independent of dashboard elements; a KPI can drill to a related dashboard

Yes (report parts which are elements such as tables, charts, gauges, images, maps, parameters, etc. published for reuse)

Yes (Power BI v2 portal  supports the ability to ‘pin’ a report element onto one or more dashboards after the report has been published to the Power BI site)

KPI repository

Yes (defined in the Control Panel web app)

Indirectly (a KPI can be used from the underlying SSAS or Power Pivot data model, or an indicator can be defined inside of an individual report)

Indirectly (a KPI can be referenced from the underlying SSAS or Power Pivot data model)

Drill-down (additional detail on the same report)

No

Yes

Yes (basic; affects single object on page only)

Drill-through (additional detail on a different report)

To another dashboard

or

To custom URL (incl to other reporting tools such as SSRS or Power View for SharePoint)

 

Requires the dashboard to be published & not in standalone mode

To another report

or

To custom URL (incl to other reporting tools)

Custom URL (only on Dashboard tiles)

Alerts

No

Yes (data-driven alerts in SharePoint mode only)

Yes (simple high/low alerting on a single numeric tile - available in v2 mobile app only)

Subscriptions / automated report delivery

No

Yes

(E-mail,

file share,

SharePoint doc library,

preload a cache)

No

Snapshot reporting

No

Yes (report execution snapshot to improve performance, or report history snapshot to store report as of a point in time)

No (even a Power Point export from SharePoint retains a live connection so it’s not an ideal tool for a point-in-time snapshot)

Pinning of report elements

KPIs can be pinned to Windows 8 start page as a live tile

 

Dashboards can be pinned to Windows 8 start page as a shortcut to open dashboard

No

Yes (in Power BI v2 portal, an item on a report can be pinned to one or more dashboard pages)

Support for Analysis Services Multidimensional

Yes (requires a flattened MDX query to be provided; Datazen ignores items on the rows-workaround can be calculated members for display of attributes or use of DIMENSION PROPERTIES MEMBER_CAPTION)

Yes (requires a flattened dataset with only two axes)

Yes if data brought into embedded model first (i.e., not direct connect in Power BI V2). Live connectivity supported in Power View for SharePoint

Support for Analysis Services Tabular

Yes (requires a DAX query to be provided)

Yes

Yes

 

 

Integration

 

Datazen

Reporting Services

Power View / Power BI

URL-based report launching

Yes

Yes

Yes (if published to SharePoint or to the Power BI portal)

Embedded BI in a custom app

Yes

Yes

Not yet

Embedded in SharePoint

Yes

Yes

Yes (if using Power View for SharePoint)

 

 

 

Performance and System Monitoring

 

Datazen

Reporting Services

Power View / Power BI

System administration

Datazen Server Control Panel (web-based)

Report Server,

Report Manager,

SharePoint

N/A for now

Server-side caching

Cached data views (vs. real-time data views) can be created on a schedule

 

Dashboard thumbnails and definitions cached

 

A shared cache service, such as Azure Cache, can be employed

Reports can be cached on a schedule to improve performance of longer-running reports

Caching in place for continued display of dashboard tiles (thumbnails) in Power BI portal (refreshed automatically every ~15 minutes)

Mobile app caching

Offline mobile access of dashboard with background sync at specified intervals (an alternative to real-time which does not cache locally)

N/A

Data saved in the app cache for offline mobile access

Redundant storage of data along with report itself

No (if using a data source from the Enterprise Server)

 

Yes (stores copy of data if running in standalone mode & Excel is the data source, this data is imported & stored in the report definition)

No

No (if utilizing Power View in conjunction with supported direct-connection sources)

 

Yes (stores copy of data if utilizing Power View in conjunction with an embedded Excel data model in the same file)

Execution logging (reports executed by whom, run times, etc)

Not built-in (workaround is to build a custom activity tracking table)

Yes (report server DB execution logs)

Not available

Scale-out

Yes (most of the Enterprise Server services can be distributed)

Yes (multiple distributed report server instances with a single report server DB)

No

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.

 

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:

http://sqlreport/Reports/Pages/Report.aspx?ItemPath=%2fBI+Reports%2fMarketing%2fDealerDashboard_Internal  

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

     image

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!

http://sqlreport/ReportServer/Pages/ReportViewer.aspx?%2fBI+Reports%2fMarketing%2fDealerDashboard_Internal&rs:Command=Render

     image

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