Comparison of Datazen vs SSRS vs Power View

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

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

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 coming soon

+ 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; applicable to non-shared dashboards only)

 

 

Development

 

Datazen

Reporting Services

Power View

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 (Power View was created purposely to have a low 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

Parameterization

3 types:

Time navigator,

Scorecard grid,

Selection list or tree

Parameter pane

Slicers (placed on the report body)

or

Filters pane (scoped for view or 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)

N/A (queries aren’t explicitly written for Power View as it’s a drag and drop interface)

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

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)

Not yet

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)

Supported in Power View for SharePoint (support coming soon to the other ‘flavors’ of Power View)

Support for Analysis Services Tabular

Yes (requires a DAX query to be provided)

Yes

Yes

 

 

Integration

 

Datazen

Reporting Services

Power View

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

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 10 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

Connecting to Analysis Services From Power BI Website, Power BI Designer, and Excel (Part 4)

Welcome to part 4 of a 4-part series on using the SSAS Connector with the new Power BI Preview. This information is as of early June 2015 and will definitely change as the Power BI Preview evolves.

Part I:   Overview of Using Analysis Services Data in the new Power BI Preview

Part II:  What You Need to Set Up the SSAS Connector in the new Power BI Preview

Part III: Monitoring the SSAS Connector in the Power BI Preview

Part IV:  Connecting to Analysis Services From Power BI Website, Power BI Designer, and Excel (you are here)

Comparison of Connectivity Options

Power BI Website: A live query connection to the SSAS Tabular Model which sends DAX statements to the SSAS server for processing. Though it's considered a live connection, to improve performance the data is cached in the Power BI service; this cache is currently refreshed every 10 minutes. Therefore, I tend to think of this as a "near real time" connection. Ideally the 10-minute interval becomes a configurable option at some point because many SSAS models are refreshed less frequently, such as once per day.

Power BI Designer:  A live  connection, like discussed in the previous paragraph, is not yet available in the Power BI Designer. Currently this operates as a data connection where you bring data in via Query to store in the Model. In all likelihood storing in the Model will become a choice rather than a mandate as the product evolves incrementally.

Excel: Depending on how you connect, you can set up a live query connection or bring the data into the Excel Data Model (Power Pivot). Setting up a direct connection is how we have queried SSAS servers for many years now - it keeps the data centralized in one place. The newer alternative is to replicate the data in the Excel Data Model which can be useful if you need to marry up the existing SSAS data with other data not available in the SSAS database and/or need to define new calculations and such.

Connecting to SSAS from the Power BI Website

The interface to connect to an SSAS server from inside the website is very straightforward, but it does require the SSAS Connector to be set up and running already for it to be discoverable. See the previous parts of this SSAS series for more details on setting it up.

Start with the Get Data menu:

 

Select the SQL Server Analysis Services option then Connect.

At this point what you will see are all SSAS servers that have been registered in the organization, regardless if you have permission to it. If you try to choose a server that you don't have rights to via a Role, it'll fail to connect at that point.

 

In my case I only have one SSAS server registered:

Once you have clicked on the server itself, it'll display the name of your SSAS Tabular model. Then you can Connect:

After it's connected, you'll have a new item listed in your Datasets area. It'll have an asterisk next to it to highlight that it's new. Note that if you go through the same process multiple times you'll get duplicate datasets listed (I'm guessing that is a temporary issue). It's a bit hard to see, but my last two Datasets are now duplicates of the same connection:

 
 

At this point if I click on the Dataset name, it opens a new report connected to my model. The field list is on the right that I can start working with to build out the report:

At this point since Excel Services and Reporting Services report types aren't available yet, this report is a Power View report (my suspicion is the Power View will remain the default report type but that's a guess of course).

Note the context of the 'current dashboard' is being displayed in the breadcrumb at the top of my screen (that's the Salesforce Sales Manager dashboard in my screen shot above). That is where a report item will go if you pin it while working inside of your report. If you want to pin a report item to a different dashboard (and in my case I would), you'll want to go back to the main menu, choose the dashboard, then return to your report. Then the context will be changed. Don't forget you can pin items to a dashboard from a variety of reports. And, a report item can be pinned to multiple dashboards.

 

Connecting to SSAS from Power BI Designer

Since the direct connection isn't available yet, the interface for searching for registered SSAS servers isn't currently the same in Power BI Designer as it is in the website.

A prerequisite for connecting to SSAS from the Power BI Designer is installation of the ADOMD.NET data provider. This gets installed on your local machine (where Power BI Designer is installed). Even if you're running SQL Server 2014, it seems that it still wants the SQL Server 2012 provider at this point in time (I installed the 2014 provider first to align with my version of SQL Server but it didn't work). The ADOMD.NET provider can be downloaded from the SQL Server Feature Pack: http://www.microsoft.com/en-gb/download/confirmation.aspx?id=29065.  Expand the Install Instructions section, scroll down to the Microsoft SQL Server 2012 ADOMD.NET section about 1/4 of the way down the page. Choose x86 or the x64 file depending on if you're on a 32 or 64-bit machine.

 

To connect to SSAS, you start with the Get Data menu just like in the web. Under Database, find SQL Server Analysis Services Database then Connect.

 

Here's where things change from doing the connection via the website. Rather than show registered servers, it prompts you for the server name (including instance name if applicable):

 

The first time you make this connection, it'll prompt you for credentials. 

 

The next time you make this same SSAS connection, it won't need to ask you for credentials because it'll have your preferences stored. To review your saved data source credentials, go to File > Options and settings > Data source settings. Here you can edit or delete saved data source settings as needed.

 

After you've authenticated you'll be prompted for which tables of data to pull into the embedded data model. When you make multiple selections, the selected source tables will be flatted out into one single destination table. So in many cases you may want to import them each separately to preserve the categorization (though you'd need to have the keys for each table exposed in order to do that & in SSAS usually keys are hidden). Be sure to click Edit next so you can fine-tune the Query steps before the data gets loaded to the data model.

 

There's a contextual menu called Cube Tools that comes up when you're on the 'Added Items' step. At this point you can proceed with your normal Query steps to refine the output as desired.


Connecting to SSAS from Excel

Here's where I was planning to write up a summary of obtaining SSAS data 3 ways:  (1) Data menu ODC connection, (2) Power Query, (3) Power Pivot. However, while this post was sitting in draft status, John White wrote up a blog post covering this exact topic, so I'll just refer you to John's blog for details:  Using Excel with External Data - What's the Right Tool?    

Keep in mind that in Excel you have the choice as to whether to retain a direct connection or if you wish to store the data in the Excel Data Model. This is a very important decision point. As of right now, only options (2) and (3) are supported in the Power BI Preview, though I expect that'll change. In Excel 2016 these different options will be simplified which will be very nice.

And that's a wrap on this series about the SSAS Connector in the Power BI Preview. Thanks for reading!

Part I:   Overview of Using Analysis Services Data in the new Power BI Preview

Part II:  What You Need to Set Up the SSAS Connector in the new Power BI Preview

Part III: Monitoring the SSAS Connector in the Power BI Preview

Part IV:  Connecting to Analysis Services From Power BI Designer and Excel (you are here)

Prototyping Layout and Design with Datazen

In looking at Datazen recently, there is one feature that jumped out at me as being very useful for prototyping. Datazen calls it the 'Design-First' methodology.

In the Datazen Publisher app (available from the Windows store), you can work in the Layout View prior to having the Data View set up (i.e., the underlying query which retrieves the source data). In this screen shot I have dropped a handful of elements onto the design surface in Layout View pane:

How is it that I am seeing any numbers in the above screen shot? The preview numbers are from the "simulated data" that Datazen sets up for each element after the element has been added to the dashboard layout. In this screen shot I have switched from the Layout View pane to the Data View pane:

Above we see that Datazen has set up 3 tables of simulated data to support the 5 design elements I have added to the design surface.

This is very interesting for the following reasons:

  • We can refine the layout / design / aesthetics of the dashboard before addressing the underlying data view (query). Anyone who has gone back and forth on design many times with several people with different opinions will immediately see the value in this Design-First approach.
  • This makes it possible for a non-technical user, someone who isn't familiar with the SQL language for instance, to handle the front-end layout independently of the data view. The person constructing the data view will be able to have a better understanding of what will be done with the data.
  • The simulated data can be exported and used as a template for what the actual data view should really look like. Someone who understands how to create the underlying query can use the simulated data as a guide for the columns to be output and row granularity. It doesn't guarantee there won't be multiple iterations to get it right, and it doesn't mean you are required to produce the data view the exact same way, but it is nice to have the additional information available when you start to write the query.

When the data view (query) has been published to the Datazen server, then the properties for each element can be updated to refer to the correct table and column. Once all references to a simulated table are gone, the simulated table disappears from the Data View pane.

To me, the biggest risk of this approach is that by reviewing the dashboard layout with simulated data, other team members may think the dashboard is closer to completion than it really is (depending on complexity of the underlying queries). This is often the problem we have with prototyping, which can be handled with good communication and teamwork. 

Filtering in Power Query

Last week I was teaching a Power BI Workshop. On the final day while attendees were building their own solutions, one participant named Linda says to me:  "Melissa, Power Pivot is missing some data."  We proceeded to trace back through her Power Query operations to determine where the data got eliminated.

She shows me the final Power Query and points to the column header and says "See, there's no filters here. So why didn't it end up in Power Pivot?"  Since this was a very interesting observation from a very bright gal, I thought I'd share it.

To illustrate, first let's set up a quick little list of Dept Numbers and Names in the first step of our Query Editor:

In the second step, let's add a filter to remove one Dept Number. 

In our third step after the filter, let's do something. Anything. For grins I merged two columns to produce a concatenated column of Dept Name - Number. Really though, this third step only exists to prove a point that there is no longer an indication in the column header that a filter occurred on this column in a previous step:

I often say that each step in the Query Editor displays the state of the data at that point in time. Since the filter occurred on a previous step, the rows filtered out are now really gone in the next step - the state of the data changed. This is why there's no longer an indication a filter occurred in a previous step. Power Query behavior isn't like Excel where the rows are still there just hidden; rather, the rows are truly filtered out and won't progress further along in the steps or into Power Pivot.

So, having said all that, the two main takeaways are:

  • Filters in Power Query aren't controlling what you see on the screen; they truly exclude the data from progressing any farther.
  • There's not a visual indication in Power Query on steps after a filter operation has occurred, so keep an eye out for that if you are doing some data exploration as you build your steps.