Datazen, Microsoft Technologies, SQL Server, SSRS

Datazen Lives On in SQL Server 2016

Microsoft acquired Datazen back in April 2015, and I explored it and wrote about it a couple months later. To date, Microsoft has mostly left the product as is, although a new version containing bug fixes and a few enhancements was released in September.

While I was at PASS Summit I learned that there is a bright future for Datazen as a significant part of the MSBI reporting offerings. Microsoft has provided a reporting roadmap that looks very promising. They are working to align cloud and on-premises solutions and to harmonize report types. In the MSBI reporting world, there will be 4 report types:

  • Paginated reports (SSRS)
  • Interactive reports (Power BI)
  • Mobile reports (Datazen)
  • Analytical reports (Excel)

For on-premises solutions, you will have one unified SSRS Report manager that supports mobile reports and interactive reports as well as paginated reports.  And it’s much prettier than the old SSRS report manager!

ezgif.com-gif-maker
New vs old SSRS Report Manager, courtesy of @idigdata

There will also be a unified mobile app so there is no need to switch between apps to get Datazen reports and Power BI reports.

You can get the ebook here for free! We plan on updating it as more information comes out on Datazen and SQL Server 2016.

Datazen, Microsoft Technologies, SSRS

What’s The Deal With Datazen?

I’ve been exploring Datazen for the last several weeks, and I’ve had the opportunity to discuss it with some clients. While it doesn’t do everything everyone wants it to, I think it fills some feature gaps in the MSBI mobile story.

What is Datazen?

Datazen was initially released in 2013 and gained popularity and many positive reviews. Microsoft announced the acquisition of Datazen on April 14, 2015. That announcement included the statement that SQL Server Enterprise Edition customers with version 2008 or later and Software Assurance are entitled to download the Datazen Server software for free. So many organizations just got a free mobile BI tool (not counting the cost of up set up, adoption, and maintenance). Microsoft has stated that they will be integrating some Datazen technology into SQL Server 2016 and Power BI, but the current version of Datazen could be useful depending on an organization’s needs.

The Features

Activity Stream
Activity Stream, maps, scorecard. I hate round dial gauges. I didn’t build this. It’s just a demo sample.

Datazen shines in the following areas:

  • Mobile BI: Datazen has feature parity on iOS, Android, and Windows 8. The native apps are optimized for touch gestures and offer useful features such as client-side caching for offline viewing.
  • Maps: There are 70 built-in maps at the state, country and region level. You can also create custom maps using ESRI shapefiles.
  • Activity Streams: Users can add comments with contextual information and interpretations of the numbers for other users to view.
  • Integration into current environment: Datazen allows the use of Active Directory or ADFS for authentication. Datazen can be embedded into SharePoint or custom web apps through an iframe. It also supports custom branding.
  • Security: Datazen encrypts data on the server, while in transit, and on client devices. Application pin locks are supported on all mobile clients.

For more information on these features, see my video on the BlueGranite site or the Datazen website.

The Datazen Suite

Datazen product elements include Datazen Server (control panel, authentication, data sources and views, dashboard and KPI storage), publisher app, and viewer apps.

Where Does Datazen Fit in the MSBI Stack?

Microsoft already has Power BI, which has mobile apps (in the new 2.0 version) for Windows and iOS, with an Android app coming soon. Yes, Datazen adds another tool to an already confusing array of options in the MSBI stack. But the scope and strengths of Power BI are rather different from those of Datazen. Power BI is a good data discovery and self-service BI tool. Datazen doesn’t really do data discovery; it’s more for structured analysis. Organizations that use Android devices, or those who need enterprise-mobile BI access now may look to Datazen (especially if they get the license at no additional cost). Organizations who are not ready to house their data in the cloud, don’t want to deal with the change management of constant updates in Power BI, or those with strong mobile security concerns may also consider Datazen.

Datazen Is…

Datazen is good for dashboards with defined drill-through paths. This is a similar paradigm to Reporting Services. You create the charts and navigators/filters and then define the drill-through destination to access when a user touches/clicks on a chart, passing the location of another dashboard or a custom url along with any parameters.KPI Live Tile

Datazen also allows you to create KPIs that can then be pinned to the start menu in Windows 8+ as a live tile. This is good if you have just a few KPIs that you want to monitor at a glance. without opening an app or website.

The best part of Datazen is that it just works on mobile devices without the developer having to do much of anything special to provide a good mobile experience. And the viewing/interactivity capabilities are the same whether you are on a laptop, phone, or tablet.

Datazen Is NOT…

Datazen isn’t a data manipulation tool like Power Query or a sophisticated calculation engine like Power Pivot.  You must feed your data into Datazen in a very particular way to make the charts you want. It can do simple aggregates like sums and counts and calculate the difference between results and goals in a KPI, but that is about it. Any other calculations must be done in the data view (source query). This isn’t so much a criticism as a statement. I can appreciate that Datazen isn’t trying to be all things to all users but instead chose to focus on a few things (mobile dashboards, rapid dashboard development) and do them well.

While Datazen dashboards could be built by a data savvy business user, I don’t consider it to really be self-service BI. It’s more likely that BI developers and analysts will use it (similar to Report Builder) to build static but refreshable dashboards with the ability to filter data and drill through to more detail. You must publish the dashboard in order to use it or share it with others. I firmly believe that there is still a place for these kinds of reports and dashboards. They can ensure consistent measurement and analysis, and keep important metrics easily available to those who need them. Unless you live in SSRS, Datazen dashboards are quicker and easier to build than SSRS dashboards. Datazen keeps things simple with a grid system for easy alignment and color palettes and background options that are similar to Power View.

You can’t change Datazen dashboards (switch chart types, move graphs around, etc.) on the fly directly from a mobile device. The only way to update a dashboard is to use the publisher app which is available only on Windows 8 laptops/tablets.  Another question I’ve been asked is whether the charts inside a dashboard are reusable. As far as I know, there isn’t a way to reuse a chart from one dashboard in another dashboard, unless you want to create a dashboard that contains just the chart and make it a drillthrough destination in multiple dashboards. When you build a dashboard, you build the master view first. This is the view you see from your desktop in the viewer app or on the website. Then you build the tablet and phone views by placing the existing charts onto the grid in a different configuration to fit the format. You can reuse a chart that you built on the master view in the phone or tablet view (any chart you want to use must be built on the master view). But you can’t use that chart in a completely separate dashboard.Datazen format

Datazen also isn’t an SSRS replacement. SSRS has features such as alerts, subscriptions, printing, exporting, etc. that are not available in Datazen. SSRS also allows more fine-grained control of colors and formatting and has a robust expression language for calculations.

Another Tool in the Belt

Datazen has a place in the MSBI family. Although it does have some overlap with existing MSBI tools, it also brings some capabilities that were missing. My impression is that it is a good solution for mobile BI when data exploration features are not needed. It’s fairly easy to set up and doesn’t take much time to publish a decent dashboard. More time will likely be spent with data preparation than it would with other tools. Right now, Datazen enhances Microsoft’s mobile capabilities and complements Power BI’s features. There will be some organizations that use only Datazen or only Power BI and others that may opt to use both. It’s all about using the right tool for the job.

Further Reading

AW Sales Dashboard Laptop
A demo dashboard that I made. Notice there are no round gauges.

There are several great blog posts out there on Datazen. Here are a handful that I found informative.

Data Visualization, Excel, Microsoft Technologies, Power Map, Power View, SQL Saturday, SSRS

Choosing A Mapping Tool in the SQL Server BI Stack

With the addition of the Power BI suite to the SQL Server BI stack, there are now 3 main options for creating data-driven maps. I have been working on a presentation to help you choose which mapping tool is appropriate for your needs on a given project. I gave the presentation for the first time in SQL Saturday Chicago, and I’ll be presenting it again at the first SQL Saturday – BA Edition in Dallas on May 3. I’ll share the highlights of the information I provide in my presentation in this blog post, and you’ll just have to attend a SQL Saturday where I’m speaking to get the rest of the details.

There are 3 main tools between Excel, SQL Server, and SharePoint that can visualize data on maps:

  • Power View
  • Power Maps
  • Reporting Services

There are also a few apps for Excel 2013 that create maps (Bing Maps, Geographic Heat Maps). I’ll explain the Excel apps in a later blog post, and focus on the more mainstream tools for today. Although you can make maps with each of these tools, there are some significant differences in features, time to delivery, and system requirements for each of these tools. The image below summarizes the pros and cons for each of these tools.

Maps OverviewPower View is the easiest to use, but it has the most limited features. The drag and drop interface allows you to build a map in a matter of minutes. But this ease of use comes at the price of limited customization. The only type of graph you get on a map is a bubble chart, which turns into pie charts if you add a category. You can only build one layer of data on your map. Also, fonts and colors are limited to the themes available within Power View. Power View offers great interactivity and cross-filtering capabilities. You can set up your map so it drills down from country to state to city. You can use slicers and other graphs to filter and highlight data on a map. An example of using a map to filter a related table in Power View can be found here. Power View reports are usually visually appealing and lend themselves well for presentations. And if you are using Power View inside of SharePoint, you can export your Power View to a PowerPoint presentation and retain interactivity.

Power Map’s strongest features are a time axis that enables you to watch trends over time, the ability to show multiple layers at once, and the storytelling capabilities that come from scenes and tours. Power Maps have more choices for graphs, including columns, bubbles, heat maps, and region shading. But there is no drill-down capability to see an increased level of detail. Power Map also lacks the ability to filter data.  For example, if you have sales data for three products and you only want to see sales for Product A, Power Map doesn’t offer a way to filter to just Product A. This means that you may have to add some DAX calculations to your Power Pivot model to facilitate your map creation. DAX has its own learning curve, so this increases the time required to build the map if you have to learn DAX and alter your Power Pivot model first. Update: As of Sept  2014 you now have the ability to filter your data in a Power Map. The great thing about Office365 is that you can get monthly updates.  The inconvenient thing about that is I have to go back and update blog posts when this happens. Power Map can facilitate great storytelling with data through the use of scenes and tours. You can look at a particular geography and time period in each scene and combine scenes into a tour, then you can share that tour to take users on your journey to gain insight.

Although Reporting Services isn’t getting much love from Microsoft’s marketing or engineering groups, it is still a very viable option for creating maps. While SSRS can require more technical expertise and time to deliver a finished product, it allows the most customization of the three tools and offers subscriptions (time-based and data driven) and multiple rendering and export formats. It also allows custom shapes, so you aren’t limited to whatever the Bing Maps API can do. This helps with custom geographical regions within a company or its customers. It also means you can map more than just points on a globe; you can map floor plans and other shapes you can capture with SQL Server spatial data types. Where Power Map and Power View don’t necessarily require a lot of infrastructure to share a report, SSRS reports require a Report Server or SharePoint Server to make a report available to users.

I’ve developed the following high-level categories to be used to evaluate the appropriateness of each tool for your map.

Maps Eval CriteriaI used these evaluation categories to summarize my findings for each tool in the table below. You’ll notice that I split Power View into Power View for Excel and Power View for SharePoint. This is because these two different development environments have some different system requirements, data sources, and methods of consumption for the resulting Power View report. Although there are two different modes of Reporting Services (Native and SharePoint Integrated), this difference only affects system requirements so SSRS was kept as a single category.

Maps SummaryFor system requirements, note that Power View works with Excel 2013 Professional Plus and O365 Pro Plus and a few other versions, but you must have an Office 365 Pro Plus subscription to get the GA version of Power Map.

Although the Power Map release notes state that you can use it with a 32-bit processor, large amounts of data or graphics intensive map layers can slow the computer to halt or cause Excel to crash. If you can help it, it’s best to have a 64-bit computer for Power Map development or consumption in Excel.

You’ll notice that the list of data sources for SSRS is fairly long compared to the other development environments, but don’t let that mislead you. I’m just listing direct data sources. Although Power Pivot only takes up one line, it can connect to a multitude of data sources, and Power Query extends those options even further. SSAS Tabular also has many options for data sources.

Also, be aware that bubble sizing in Power View for small values can be misleading. This issue is compounded by the fact that turning on data labels for a Power View map only labels the location and not the amount related to the size of the bubble.

In my presentation at SQL Saturday Chicago, one of the attendees expressed the opinion that Power Maps tend to be very busy and it’s difficult to tell where to look when you first encounter one.  I don’t disagree with that, so I’ll throw in the warning that it can be very easy to make a Power Map that is visually appealing but doesn’t provide any insight into your data. I think there are some good use cases where Power Map is the best tool to deliver insight, but not every map needs a time axis and multiple layers/multiple graph types. Another limitation of Power Map is that is can only be opened from the Excel file that contains it (there’s no way to view it in a web browser). While I expect that to change in the future, this is the current state today. Outside of exporting a tour to video, the development environment and the viewing environment are one and the same. To create a new Power Map or insert an existing Power Map, you must go to the Insert tab in Excel and choose Map. This will open the Power Map window where you can view and update your map.

I also want to point out that SSRS is the only one of the three tools that can make use of SQL Server spatial data types. If you would like to learn more about spatial data types in SQL Server, Hope Foley has a great presentation on the subject and has her slides and scripts posted on her blog. Jason Thomas has some good blog posts on SSRS maps.

Data Visualization, Excel, Power Map, Power View, SQL Saturday, SSRS

I’m Speaking About Geospatial Data Viz and Data Viz Best Practices in April

I will be speaking at two SQL Saturdays in April.  First, I will be at SQLSaturday #297 in Colorado Springs on April 12.  I’ll be presenting my session on The Accidental Report Designer: Data Visualization Best Practices in SSRS.  See my previous post on this subject to understand why I think it is an important message for all data professionals.  In this session, I share guidance on evaluating the effectiveness of data visualizations and a few good tips and demos to take your data visualizations from chaotic and ineffective to professional and powerful.

Next, I will be at SQL Saturday #291 in Chicago on April 26.  I will be presenting a new session to help you Choose Your Geospatial Mapping Adventure. Here’s the abstract:

You have geospatial data and you know that plotting it on a map will deliver insights. Or maybe you keep hearing “geospatial” as a current BI buzzword and you want to be informed about your options when you get to it. The Microsoft BI stack provides several options for visualizing location data. But how do you know which tool will best serve your purpose, or what factors are important in making this decision? We’ll identify factors to consider when choosing a tool to visualize your data in maps such as system requirements, availability and type of location data, required level of technical understanding, and more. You’ll leave the session confident in your ability to choose the best tool within Excel and SQL Server.

The Microsoft BI stack offers several great options for presenting data on a map, but your decision of which tool (Power View Map, Power Map, SSRS map, free mapping apps in Excel) can limit you in features, accessibility, and time to delivery. Some of the tools require more technical skill than others, but you lose some of the ability to customize when you go with the highest ease of use.  The session will be filled with tips, explanations of features and requirements, and demos.

If you will be in the area on the dates of these events, please stop by to participate in my sessions or just say hello.

Data Visualization, SQL Saturday, SSRS

I’m speaking at SQL Saturday #190 in Denver

sqlsat190speakerI’m looking forward to speaking at SQL Saturday #190 in Denver, Colorado. This will be my 6th SQL Saturday at which I have spoken. It’s becoming a habit! The title of my session is The Accidental Report Designer: Data Visualization Best Practices in SSRS. Here is the description of my session:

Whether you are a DBA, a developer, or an analyst, there is a good chance that you will have to create reports for coworkers, company executives, or clients. As with any UI design, careful consideration should be given to your data visualization design to ensure you are effectively communicating the intended message and providing a good user experience. While the principles are applicable across reporting platforms and tools, this session will contain demos and examples implemented in Reporting Services using SQL Server Data Tools. Learn how to make information (not just data) the focus of your report and provide your audience with something better than just shiny!

I’m passionate about data visualization. Data professionals work so hard to gather, integrate, cleanse, aggregate, and deliver data to users. Creating a good report turns that data into actionable information, taking it the last mile. Consequences of bad report design range from failure to provide appropriate business insights to misleading users and causing them to draw incorrect conclusions. As much as I enjoy my job as a data professional, my job does not exist simply for my enjoyment. My goal is to create solutions that provide timely, accurate, usable information to ultimately help businesses cut costs or create new revenue. I’m missing the mark if I allow poor report design to jeopardize my efforts.

Report design is not just for BI professionals. Pretty much everyone (business users, data professionals, developers, managers) has to create a report at some point in their professional lives, whether it is built in Excel using static data or embedded in a web page using .NET and javascript. While there is no one true way to design a report, research (and some common sense) shows that some methods are more effective than others. I frequently encounter two common mistakes. In our strive to make things visually appealing, we often lose sight of the goal of effective communication of the appropriate information, opting for bling over business insight. On the flip side of that, many people default to a wide table full of hundreds of lines of data rather than spend the time to plan our graphs or charts to highlight the important trends and indicators.

When I began learning data visualization best practices, I felt like my eyes had been opened to a facet of my work that I had been unintentionally ignoring. I realized I had been making several common mistakes simply because I did not understand the user experience I was providing through my design choices. If you are a developer, it is important to realize that reports/dashboards/data visualizations require as much planning and thoughtfulness as any other user interface design. It is not enough to slap the graph on the page and move on.

Here are some online resources for those who would like to learn more about report design and data visualization best practices:

Data Visualization, Microsoft Technologies, SSRS

The other background color property in SSRS

When you make an SSRS report with a non-white background, you may initially notice some white around the edges of the report.  This MSDN forum post helped explain why this occurs.  When you set the background color property on the report body through the wizard-like UI (shown below), you are setting the background color of the report body only. In my example, I have chosen the color WhiteSmoke to provide a light gray background and remove the harshness of the plain white.

Report Body Properties

This does not change the background color around the report (below and to the right)  in the case that the report does not take up your entire screen.  So you end up with something like this:

Whitespace Behind Report

There is another property you must set for the entire background around the report to be the same color.  You can access it in the report properties grid (select the report and hit F4).

Report Background Properties

Once I change the BackgroundColor property on the report under the Page group to WhiteSmoke, I get a complete background of light gray.  The white around the sides is gone.

Data Visualization, Data Warehousing, SSAS, SSIS, SSRS

Some business intelligence links I revisit and send to others

I need to get some chores done, but instead I am cleaning up my bookmarks and re-reading bookmarked articles. Hopefully this is beneficial for you, since you now get to have a small collection of useful links. These are just a small sample of articles that I find myself going back to either for my own reference or to send to a client or colleague.

Indexing The Data Warehouse by Michelle A. Poolet; 06/30/2008

Importing Multiple Flat Files with SSIS by Amit Singh; 04/06/2010

SSIS: Case-sensitivity in Lookup component by Jamie Thomson; 02/12/2008

Removing Identical Duplicate Rows by William Assaf; 02/21/2012

When to Use NVARCHAR by Melinda Cole; 09/09/2011

The Data Quality Audit by Michael L. Gonzales; 06/10/2004

SSAS 2008R2 Performance Guide from SQLCAT

Passing SSRS T-SQL values to SSRS MDX Parameters by Patrick LeBlanc; 12/01/2010

Color Has Meaning by Juice Analytics