Power BI, Tableau and Cognos Analytics. You’ve heard about today’s most popular enterprise ready tools, but when have you been able to assess them in action, side by side? Here is your chance!
In this on-demand webinar, see a unique dashboarding show and tell. Our experts in these three market leading solutions review the dashboarding capabilities of each. After you watch this recording, you will have a good handle on the most current features of each tool. You’ll be better prepared to discuss missed opportunities in your organization’s current landscape and make plans for the future. You may even be inspired to add new skills to your own portfolio!
What to expect from this overview of Power BI, Tableau and Cognos Analytics
- A walk through of each tool’s look and feel
- A discussion of the pros and cons of each
- A demo and comparison of data loading, data prep and building of dashboard visualizations
- A quick look at each tool, presenting the same data in a dashboard visualization
Finally! A vendor-agnostic take on the BI tools you’ve been hearing about. Unvarnished, objective and from trusted leaders in BI.
Practice Area Director and Solutions Architect
Michael’s team is responsible for the development of the Senturus Analytics Connector. He also heads up our Tableau Practice. Before Senturus, Michael spent over 20 years in different roles at Oracle, IBM and SAP acquiring a wealth of hands-on, practical BI and big data experience.
Senior BI Architect
Pedro joined Senturus in 2010 and brings over 20 years of BI and data warehousing experience to his role. Pedro has been instrumental in implementing data warehousing systems from scratch and has experienced the evolution of the BI industry through several iterations of BI products like Cognos, MicroStrategy and Tableau.
Tableau Solutions Architect
Kyle has 20 years of experience in data analytics and 10 years working with Tableau. Kyle is focused on helping clients use Tableau in novel and efficient ways by helping them see and interact with their data in ways they hadn’t before.
Senior BI Architect
Shawn Alpay is a Business Intelligence Architect at Senturus and has spent the last 15 years designing and implementing Microsoft-centric BI solutions for clients. He has built ETL, data warehouse, reporting and analysis solutions from the ground up. In his various development and architecture roles, he often serves as the project manager and business analyst, partnering directly with clients to gather requirements and deliver insight.Read more
Q: Will this webinar recording be shared later?
A: Yes, view the recording now.
Q: Does this only compare the client side or also the server side of analytics?
A: This webinar and associated demos are based on dashboarding or end user reporting. Our detailed matrix includes enterprise/server functionality (full BI stack).
Q: How are maps integrated in each tool? Are they using a google maps interface or does each tool have built-in maps functionality?
A: Power BI has out-of-the-box functionality with Bing maps. You can get Google Maps working, but it’s not straightforward. Get more info.
Q: Do you have information about the costs of Power BI on Azure vs. Tableau on Azure?
A: Costs are unique to the tool, user counts and other engagements. For example, Microsoft is rolling in some Power BI functionality with enterprise licenses. We are a partner with all three vendors and would be happy to help quote what your organization might need across the platforms. Please contact us at [email protected]
Q: What is core data services?
A: Core data services are part of the SAP framework. It allows you to embed metadata information in the data layer to be used by the semantic layer. Learn more.
Q: Are data modules in Cognos a replacement for using Transformer? What is the conversion from Transformer to data modules?
A: Cognos data modules are not a replacement for Cognos Transformer. Data modules can be compared to Cognos Framework Manager. It is a lighter web-based modeling tool targeted for data analysts. Transformer is a full-fledged MOLAP modeling and creation tool.
Q: How do we migrate Cognos data modules?
A: You migrate using the same migration method as reports, with a deployment package.
Q: Are there any companies using data modules currently?
A: We know companies that use data modules. Many are targeting data analysts in functional groups who want to augment data from existing corporate Framework packages.
Q: Is Cognos looking to expose the package as one of the data sources?
A: A package can already be used as a source for a data module.
Q: Can packages be created out of Framework Manager?
A: Yes, that is the main function of Framework Manager, to model the back-end data source tables then publish a package for data use.
Q: Are there server-side tools for integrating a Cognos package with Power BI or Tableau data sources?
A: Senturus has an Analytics Connector between Cognos Framework Manager packages and Tableau and Power BI.
Q: Does Cognos data modules have additional memory/CPU requirements on the servers to create the data democratization type workflow in Power BI and Tableau?
A: It is not currently possible, read about the alternatives.
Q: How do the transformation capabilities in Cognos data modules compare to those in Power BI Desktop dataset?
A: Power BI is incredibly flexible in terms of transformations. You can change datatypes and do all sorts of changes, split, concatenate, etc. You do this with a language called M and a GUI to do it, but it’s all under the covers.
Q: Can Power BI use data sources which are not necessarily MS SQL Server or flat files? Such as graph data or long strings available as JSON files?
A: Power BI has a ton of connectors, JSON is one of them. The great part about the connector experience is that, if no one has built a connector that works with your particular proprietary technology or format, you can create your own! Senturus, for example, has created an Analytics Connector to allow Power BI to connect to Cognos.
Q: Cognos gives end users the ability to create their own reports. Does Power BI also allow similar flexibility?
A: With Power BI this is definitely possible with two options. You can either download and install Power BI Desktop on your computer to create your own reports against centrally-managed datasets. The other option is to create an app workspace in the Power BI service and create reports against those datasets within the browser.
Q: Can we connect Power BI on the top of core data services (CDS) view?
A: No, it is not possible to directly import data from CDS view using the built-in SAP HANA connector. However, CDS views can be easily exposed as OData Service (API) which can then be used to fetch data from SAP S/4HANA cloud. You can use the OData feed connector in Power BI Desktop to connect to import data from CDS view.
Q: Can I run analytics in a linear regression, such as a sales as a function of other attributes in Power BI?
A: You can absolutely create measures in Power BI that are functions of other things, then display them on a scatterplot and display a correlation.
Q: Does Power BI real time have similar functions as Cognos real time?
A: Power BI refers to this as DirectQuery. You can connect to a wide variety of data sources directly and live query those data sources while interacting with the visuals. See a list of Microsoft DirectQuery data sources. If the data source is on-premise and you’ve published to Power BI service, the Power BI gateway is required and supports a similar set of DirectQuery data sources.
Q: Is machine learning or AI included with or a module for any of the three platforms demo’d?
A: Power BI has AI functionality branded as what is called Q&A. It’s still a maturing product, though.
Q: With Power BI, how do I join two tables with more than one data element?
A: You normally need to create a calculated column of the two columns in each table and then join the two tables on those calculated columns. Power BI doesn’t currently support multi-column joins.
Q: Is Tableau Desktop required or can content be created exclusively in a browser?
A: Web-based authoring can do some of the things that Tableau Desktop can do. Tableau provides a comparison reference and a feature breakdown viz. The good thing is that licensing for web authoring (Creator or Explorer) includes the same features on the associated desktop application.
Q: Can tools connect to live real-time streaming in Tableau?
Q: Does Tableau have dependencies on any resources external to the on-premises installation?
A: In general, no. Tableau Desktop is installed locally and can be used with local data sources without any network connection. Tableau Server can be installed on a Windows or Linux machine which could be offline or only on a local network (no internet). Tableau Online is the vendor hosted service that would require an internet connection to use.
Q: What capabilities are available on the Tableau mobile view?
A: Mobile view can be used to view any views published on Tableau Server (or tableau online) and provides the same interactive functionality as the desktop browser views (filters, click actions, tooltips), but displays are not always rendered in formats that are easy to work with. IT’s best practice to create different views that are optimized for each platform. Desktop browser has a lot more realestate so more information can be displayed on a page. Mobile views are better for compact metrics and charts. Tableau has handy templates in the dashboard developer pane which allows a window to what would be visible on a Mobile or Tablet view based on what’s currently included in a dashboard.
Three Tools, One Dataset, One Dashboard
This demo is unique in the industry: a comparison of the three market leading tools, Cognos Power BI and Tableau, being demo’d head-to-head across a single dataset replicating a single dashboard.
We’ll show how some features can be similar and point out some of the areas of differentiation. This webinar is a snapshot of how we tracked over 150 different attributes across Cognos, Power BI and Tableau. Today, we’ll give you a partial snapshot of what that list looks like. We provide ratings, notes, links and caveats to give you a deeper understanding of the attributes of Cognos, Tableau and Power BI, where the vendors stand, as well as how we came to the conclusions for those rankings.
Why Compare Cognos, Power BI and Tableau
The reason we track and compare Cognos Power BI and Tableau is fairly simple:
- None of the information about the rapid proliferation of these tools based on the increasing demands of users
- The aging systems aren’t meeting the needs of end users and organizations
- The rise of user self-service
- The need for scalability
- Consulting firms are aligned more closely with vendors versus clients
What we believe is unique, is our approach. It’s come out of a response to clients who were finding themselves just getting started looking at the tools or in the midst of an implementation.
Rapid Software Release and Changing Capabilities
It’s hard to keep up with the rapid release schedules and ever-changing capabilities of these myriad tools. Tableau releases a product every three months or so. Microsoft releases a new release every month. Even Cognos has gotten into the game with releases roughly every quarter to a third of the year.
It’s fascinating to watch the competition ping pong and see features ripple out to different product releases across Cognos, Power BI and Tableau. Features like artificial intelligence and natural language processing with support for local files and local web-based metadata modeling and Python and R integration.
Senturus does offer a full tool comparison between Cognos Power BI and Tableau if you want to go deeper. We can walk you through the entire ratings grid and provide guidance and focus on the capabilities that are most important based on your company’s priorities. But there’s no way we can do that in an hour; today’s webinar is just snippet.
Cognos Transformer: Predecessor to Tableau?
Cognos has been around for quite a while. As many veteran Cognos users know, the product has evolved significantly throughout the years. If you’re a real veteran Cognos user, you could even say that maybe the Cognos Transformer desktop product was an early predecessor to the modern-day Tableau.
Cognos generally has followed a fairly strict paradigm of centralized, governed metadata. IT generally would develop the FM model based on some specifications and databases and they would create packages. Consumers would have to then access the packages and write reports and dashboards. But there was this long wait because they would have to wait before the data was modeled and packages were published out to their portal.
Overview Self-Service Data Prep in Cognos Analytics
Now Tableau and Power BI have really pushed the new paradigm of data democratization and the self-service aspects of user-driven data prep and modeling. With Cognos Analytics 11 and now 11.1 and subsequent releases, IBM has responded and has given Cognos self-service data prep features that are almost on par with those of Tableau and Power BI today.
In today’s Cognos demo, I’ll do a quick overview of how we model data in a data module and create a quick dashboard. Then I’ll show the final dashboard that will also be shown in both Tableau and Power BI.
Demo of Cognos dashboard
Data Modeling, Prep and Data Extracts
We are using Cognos 11.1, specifically Cognos 11.1R3. We no longer use Framework Manager to model the data. For you veteran Cognos users, in version 10 and before, you had to use a desktop client tool to model the data and then Framework Manager to publish the packages to the portal.
We’re going to use the newer data modeling features of Cognos Analytics 11.1. We’ll model the data and data modules, show you some of the data prep features, load files and then we’ll create data extracts. These are the things that we use to create the final data module that the dashboard is going to be using.
Let’s go through a flow of how that’s happening: for the demo data, it’s showing where the sources came from. On the left are the sources and on the upper left corner you see SQL server on Azure. Both the Power BI and the Tableau dashboards are using the same database source: the Wide World Importers common data set that Microsoft uses for a lot of its demo purposes.
In Cognos 11.1 I’m going to create a data module off of that database instead of Framework Manager. With Cognos 11, I can take external files like Excel and drop them into Cognos 11.1. I can then upload a file and create a further data module, which will then integrate that data into a data model that the dashboard is going to use.
Let’s create a brand new data module. I already have my data server connection set up, so this is that SQL server database on Azure. We’ve got two schemas up there, I’m going to select them.
I’m going to click OK and then I’m going to select some tables, selecting customer and the date. These are dimensional tables because they’re in the dimension schema.
COGNOS: DATA MODULES, VISUALIZATION STYLES, DASHBOARD, TOOLTIP
Data Module Painter Replaces Framework Manager
We are now brought into the Data Module Painter or designer, which takes place of Framework Manager. Here are the tables I brought in. I’m in a grid mode right now. I could click on the customer and I could see sample pieces of the data over here.
Over here on the Relationships painter, I can now see the entity that I brought in. The sales table is over here as part of the star. I had to create relationships in these tables, in the data module Painter. Here’s my stock item table. I’m going to create a relationship and then join my stock item key to stock item key over here, then match the selected items. Now I’ll refresh the data and there we have the join over here and here’s a sample of view of what that join data looks like.
I would do the same thing for all these tables here. Establish the relationships. I’m basically building out this data module, or package if you’re referring to the older version. This is what’s going to be presented to the user as they build out reports and dashboards.
The other thing from the data module perspective, I could organize the data model so it’s easy for people to work with. I could create folders, I call it for example, The keys. I want to put all my key attributes over here. I could drag that in and quickly organize the table structure here for a user friendly presentation. I can create calculations here and maybe I want to create a percentage of the profit calculation. I’m going to rename that Profit. That’s just a quick view of how the data module is created.
Let’s go back now to my Cognos homepage and create a dashboard now off the finished data module. We’re going to go into the data module and back to my diagram over here. After I do all this integration, I’m going to go create a dashboard off of this: the final data module which has all the integrated sales files and tables joins here altogether.
Cognos Visualization Styles
I’m going to select Create Dashboard, then choose a default template I’ve brought into the painter. This is my new dashboard and if I want to start creating eight KPIs for my dashboard, I could simply drag the metric over here. It’s going to select the correct visualization.
Cognos offers a variety of visualizations in the dashboard. I’m going to choose a line visualization. I will need to give it some information to draw this particular graph, so we’re going to do a line graph for sales by month, by year. So, I’ll show it by month on the x-axis and we’re going to show sales in the y-axis. See, it’s already starting to visualize over here. And I want to show multiple years. I’m going to put that in the color slot. And now I’ve rendered it on my dashboard painter.
This number looks pretty large. I could also go in here again and format that number within the visualization. Again, I could do a quick abbreviate so that Cognos would choose the correct abbreviation for that, and now it looks a lot tighter. I’ve got basically three visualizations on my dashboard over here. That’s a quick create. I’m going to now show you the actual dashboard, one that we’ll also show you later on within Power BI and Tableau.
The Cognos Dashboard, Rendered
This is how it looks in Cognos rendered out. I’ll push the maximize button so you get a full view of it. Now you don’t see all the Cognos application bars and now it looks like a regular type of dashboard. You won’t know it’s in Cognos.
What I did in my dashboard creation was to create a couple of KPI metrics which are up here, plus this line graph over here. Over here is a map that shows you by state the sales and profitability of the state for this dataset.
Over here we have menus that we can interact with and change the numbers based on what we want to show. The numbers will change to reflect that particular choice. I could also change the sales territory over here to New England. The map will zoom in on the New England area so I can get a better look of what’s happening.
Dropdown List Box Tableau, Power BI Better Than Cognos
One common feature in all three tools is these dropdown lists boxes. But in Power BI and the Tableau the dropdown list boxes are over here on the side. That’s because they can still stay as a dropdown list box. If I actually dragged this over here onto my painter, Cognos will render a full list box, which I don’t like. We’re hoping for a new version so eventually when you put this list box over here, you have the option to put a dropdown list box.
Tooltip in Cognos vs. Tooltip Reports Power BI and Tableau
On this map, hovering over this dot, I could get a tooltip that shows me the specific detail of that particular spot. That’s the state of Maine and it had sales of about 196,000 and a profitability of 52.6%.
Another comparison point: Power BI and Tableau have a feature called Tooltip Reports. Below this tooltip area, you’ll see graphs and charts that give you more detail in a graphical manner of what makes up that particular dot on the map.
We have a similar feature in Cognos, but we have to drill through to a different report. I could use a reporting tool in Cognos. For those of you who’ve done reporting in Cognos, this is basically where this is coming from. I created a Report Studio report and it’s going to render a chart based on the parameters I chose from that widget, a visualization on the dashboard.
If you remember, I chose 2016 Q1 sales by employee for the state of Maine and here are the employee lists over here specific to that particular choice. I’m going to go back to my dashboard.
If I wanted to choose a different report, I could drill through again and say I wanted to see the list of employees who sold into the state of Maine, there are specific invoices here with the state of Maine. Remember the choice was 2016 Q1 and I have invoice sales, sales profitability and I’m using the Report Studio conditional formatting to get some red and greens or grays depending on what the ranges you choose.
In Cognos, you have the ability to go back and forth between a more detailed report and going back up to the higher level of visualization of the dashboard. You’re not just limited by high-level metrics; you can get to very detailed reporting from this particular interface.
COGNOS: BULLET CHARTS, SUMMARY; POWER BI: DASHBOARD DEMO, CORE CONCEPTS, SERVICE, PIVOT TABLES, REVIEW
Bullet Charts in Cognos
One other thing I want to point out from a visualization perspective, they integrated a bullet chart in Cognos 11.1, where this is showing sales by employee as relative to goal. There’s your goal target, sales goal and here’s the actuals in the blue bar and you can scroll up and down here.
One thing which the other tools do a little bit better, and that I’d like to see Cognos do, is to be able to have this scale only appear either on the top or the bottom. Then I could have the bullet pieces here shrink down and remove some of the white space. Just as a comparison point when you see the other tool demos.
Cognos Is Web-Based: No Publishing Downtime
Finally, I want to make one other point. Cognos is a fully web-based tool. You saw there was no publishing here at all. I did all my data module designing and joining and creation and bringing in data on the web interface. There was no desktop product. I went over to the dashboard painter and I quickly created visualizations and I saved it. Once that’s done, it’s on the web and other people can consume it, so there’s no lag in terms of publishing or putting things in a package and bringing it up into the web or a different server. Once you’re on Cognos, you can create and publish and save it on a web interface.
Summary: Cognos Comparison Demo to Power BI, Tableau
What I’ve done, quickly is just show you at a very high level how we actually brought data in. We modeled the data really quickly. We created a dashboard off the resulting data module and showed you how those visualizations were broadened fairly easy. I showed you the final product of what could happen off of that data with integrated data from Excel and databases.
Now to the demo of the same dashboard and the flow in Power BI.
Power BI Dashboard Demo: Power BI Comparison to Cognos and Tableau
I’ll present an overview of Power BI in comparison to Cognos and to Tableau building on some of the basic concepts that Pedro presented previously. I’ll try to filter that through the lens of this particular tool.
First, I want to talk you through the core concepts in Power BI and a basic workflow. This isn’t going to cover everything, but this is going to cover how we take the data that we saw in Pedro’s previous presentation and get it to a report that you can use in this product.
Power BI Core Concepts and Workflow vs. Cognos
First of all, we start with the data sources. As with Cognos, we’re going to use SQL Server and Excel for goals. We’re going to bring that in via queries into a model, and we’ll take some time to bring in the tables and create relationships, much like Cognos.
Together, those things form what we call a data set and we’re going to develop it using queries into the model and then use that data set to build multiple pages of a single report. With those two entities together, we are going to develop those in Power BI Desktop. It’s a client we install on our machine.
Power BI Recap: Data Set, Queries, Model Report
First of all, the data set consists of two objects. It’s the queries, which capture how we’re going to define our extract and transform of data sources that are accessed via connectors. The model itself is going to be our metadata definition, so tables, relationships and columns. And then our report is going to be how we visualize that data set.
Publishing to the Cloud: Power BI Service
Once we’ve done all that, we’re going to then publish it up to the cloud. That’s what’s called the Power BI service. This is powerbi.com.
Imagine we’ve taken our data set report and we publish those up into what we call an App Workspace. In this development space we’re going to curate the artifacts we create before we publish them for users to use.
We have another data set to publish, then we have more reports we publish and then we may take particular visuals from those reports and do what’s called pinning into a dashboard. This is not just pages from the reports but the actual visuals, the bar charts. We are then creating those in the app workspace. That’s another artifact alongside the dataset and report, but they only exist out in the service. From there, we can then curate a subset of these and publish them into an app.
This is our production space where we’re going to expose these to users. In this case it’s just a portion of the things that we put in our App Workspace. Users are going to interact with the app in a few different ways. They can use those visuals; they can create their own visuals.
In this example we talked about creating a report in Power BI Desktop. We can also do that in the service and then we can also analyze in Excel. This capability is a true differentiator for the Power BI platform.
Power BI and Pivot Tables
We can then launch Excel connecting out to the cloud and crack open a pivot table and then start consuming the model in the data set we created. This fabulous because analysts know how to use a pivot table in Excel and this is a huge value add for them.
Review Power BI Dashboard, Pinning, App Workspace
To review, the dashboard presents visuals which are pinned from reports. The app workspace is a draft container for those objects. The app is the production space and then the gateway, which we’re not really going to talk about is the thing that lets the service connect back to those data sources.
If we want it to be refreshing our dataset out in the cloud, we need to be able to use a gateway to get all the way back to that…on-premise SQL server resource. In this example, it’s Azure, so a gateway is not necessary, but just know that if we’re using on-premise resources a gateway’s involved.
POWER BI: DASHBOARD DEMO, DAX,VISUALS, CHARTS, MAP, TOOLTIP, THIRD PARTY VISUALS
Power BI Dashboard Demo
Now, let’s jump over to the Power BI dashboard demo. We’re looking at a blank canvas. The way we get data is to go into this list of Get Data on the top left. Power BI offers a huge number of custom and stock connectors and they’re adding more connectors all the time. Here we see Excel, we see CSV, XML, SharePoint access. We’re going to navigate to go to database – this is a subset of those connectors. We’re going to click the SQL Server database.
Now imagine that we put in our credentials, we pull in the tables, we create the relationships, very similar to what Pedro did [in Cognos previously] except we’re doing it and of course in Power BI desktop. The final result of that is this model, which is all the tables that we brought in. And all the relationships. This is the thing we’re going to use to then create measures, maybe rename columns so they’re business friendly, hierarchies.
Creating Measures in DAX, Very Excel-Like
We’re going to go back to our report pane and create a quick measure in DAX. This is really simple stuff. It’s very Excel-like, and it’s function-based formatting. But let’s say we’re going to create sales dollars equals the sum of a column and in this case it’s the total, excluding tax, so we hit that and that’s it. It’s very straight forward and now we have a measure called sales.
I’m going to create a couple more here. One called profit which is going to be profit dollars equals the sum of the profit field. I’ll create one more, which is profit percentage, which is taking one and dividing it by the other. We’re going to now take profit percentage equals divide and we’re using a divide function rather than an actual division, so we can handle gracefully if we happen to divide by zero. I’m going to say sales profit divided, with a comma, by sales and now we’ve created the measure and we can immediately start using that measure so that we just grabbed this and we drag it onto the canvas.
Creating Visuals and Charts in Power BI
It defaults to a line chart rather but we’re going to very easily be able to change it to whatever we want. In this case, let’s change it to a card so we’re just displaying a number. It’s 0.50. We want to make this a percentage. We want to tell Power BI desktop this is a percentage, so I’m going to go up here and hit format and go to percentage and we’re going to see this change in the visual. Now it’s 49.77%, but I only want one decimal, so I’m going to bring it from two to one. Now, it’s going to be 49.8. Great.
Let’s quickly create a line chart. We’re going to create a line chart here and I’m going to bring in sales in the values and then I’m going to go over to the date dimension and I’m going to grab my year and I’m going to put that in the legend and then I’m going to grab my month of year and I’m going to put that in the axis.
You can see once we’ve curated the model, how quickly we can start spinning up visuals. The hard part is putting together the model and I know that we breezed past that, but the relatively easy part is dragging and dropping to create new visuals.
Map and Tooltip Report in Power BI
Now, I’m going to jump over to the final product. It is very similar to what Pedro presented in Cognos. We’ve got our map here. What I’m able to do if I hover over this map is it’s going to create a tooltip report, which as Pedro mentioned, is not possible in Cognos.
We see here the breakdown of California alone and the various locations within California presenting below 50% being red and above being gray, so that’s really cool.
If we float over here, just like in Cognos, we can right click on this thing and we can go to drill through and we can go to invoice details and that brings up another page on the same report but now it’s automatically filtered for just Tailspin Toys.
Back in overview mode, we have our filters here on the left so we can choose an employee. For example, lets look at Amy Trefry and it will automatically filter the entire report to just show things for Amy.
Third Party Visuals in Power BI
I want to talk about the bullet chart on the bottom left. Like we saw here, we have a context menu. I’m right clicking on this thing and nothing is happening. That’s because this is a custom visual. This is not a stock visual; it was developed by a third party and this particular custom visual is about two years old. It’s available in the Power BI marketplace and you can download it and it’s great, but if the developer has not implemented certain functionality that Microsoft has enabled, those things won’t work.
I believe in the last six or nine months Microsoft enabled custom visuals to use context menus. I know that’s very specific, but that’s an example of how this product is still maturing every single month. The particular developer that created this visual has not yet implemented that.
POWER BI: SUMMARY; TABLEAU: DASHBOARD DEMO, DATA SOURCE COMPARISON,PROFIT CALC
Third Party Bullet Chart Better Than Power BI’s
The alternative would be to use the Microsoft stock visual for bullet charts which in my estimation is awful, so that’s why we are choosing the lesser of two evils here. That is just a super quick overview of the report. The next thing I would do is go to hit publish.
Now let’s jump over to Google Chrome and into powerbi.com. We’re looking at the Wide World Importers app workspace that I created. We can look at reports, data sets. We’re not going to talk about the rest of them right now, but I’m going to click Wide World Importer sales and we’re going to bring up the report I theoretically just published and it’s going to look exactly the same. The one difference is that the pages are going to be here on the left whereas in Power BI Desktop, they’re on the bottom.
The visual experience continues to change in Power BI. A lot of this stuff has just changed even in the last couple of months. If you haven’t cracked open Power BI for a while, you may be surprised to a lighter color theme where before it a darker one. Here is a new look on a toggle, just implemented in the last couple of months. I’m sure in the next couple of months they’re going to turn off that functionality and you will default to the new experience.
Anyhow, the report operates exactly the same. I can edit the report here if I want or create a new report. The last thing I want to show you is the analyze in Excel functionality.
I’m going to go back to the workspace, then to the data set and to actions. There’s a lot of different actions we can take, but the action I’m interested in is analyze in Excel. I click that, it’s going to download an ODC file to my computer.
Clicking the ODC file fires up Excel and asks me to enable a security notification. Then it’s going to open up a pivot table which is connecting out to powerbi.com. That same model that I used to create the report is actually here in the pivot table.
I haven’t actually downloaded any data. It’s just a metadata information. Imagine if you know how to use a pivot table how great this is. You can just pull sales into the values. You can just start going hog wild with this thing. Instead of having to do your analysis from a visualization standpoint, you could then use this for building other reports or feeding some other thing within the Excel ecosystem.
I know I’ve breezed through this, there’s so much more I haven’t covered, but I hope that gives you a high-level understanding of how we would go from soup to nuts for this particular set of data sets building these in Power BI.
Tableau Dashboard Demo
Tableau vs. Cognos and Power BI
This is a simple representation of the products that Tableau offers and how it differs from Power BI and Cognos Analytics.
Tableau Desktop, now in version 3, is the primary product that’s been around since the beginning. It’s the primary tool that analysts use to connect to data and to build visualizations and those visualizations can be published on Server. There is an on-premise version of Tableau Server that you can manage on-prem or there’s also a cloud-based offering called Tableau Online.
Tableau Prep is the newest product in the Tableau universe. It’s basically your own ETL tool, allows you to do data modeling and cleansing and cleaning and combining of datasets. This is how those tools all work together.
Data Source Similarities Tableau to Cognos and Power BI
A BI professional can use Tableau Desktop to connect directly to different data sources. Or you can use Tableau Prep to connect to those and build custom data sets and then visualize those in Desktop and then share that on Server. Then those dashboards or charts or reports can be shared with your organization via a browser or mobile app.
This is similar to what you saw earlier in Cognos. You saw how we are combining two data sources for the specific Wide World Importers dashboard. We’re using connecting data from SQL Server on Azure, and we’re also bringing in an Excel spreadsheet in Tableau.
There’s a number of ways to do this. The way I’ll use the data is by blending these two data sources. So, we aren’t combining them into one data set, they’re actually independent data sources in my workbook. Then I blend those together to create just the one chart that we’ve shown in this bullet chart. That’s the only one that’s actually using the target data. Everything else we’re demoing is using the Wide World Importers data.
With that, I’m going to jump over to Tableau Desktop. I’ve already got some data sources connected here but this is sort of like the blank canvas in Tableau. Just to show you how the data looks. This is the Wide World Importers dataset. There’s not a lot of modeling required. Here’s the database and these are all the tables that are available to me. I’ve already pulled in those tables that I’m going to be using them to build my dashboard.
The joins in this instance have been predefined by the database but you can also create your own custom joins to pull it together however you want. You can do inner outer just basic database SQL joins.
Once I’ve got this here I can update and look at the data, kind of do a quick inspection of the data to see what the fields look like, what the data looks like inside my fields or I can look at this as a field layout view.
Back in my Tableau workspace, we see those tables. Tableau organizes these tables by taking your tables and splitting them. It splits the fields in those tables into dimensions and measures. Dimensions are those types of fields that you can group by.
Creating a Profit Calc in Tableau
Like you saw in the Power BI and Cognos demos, let’s create a profit calc in Tableau. The way Tableau does that is similar to the functional in Power BI. It looks a lot like Excel or SQL but I’m going to create a calculation based on the profit value in my database.
These are all the functions that come pre-baked in Tableau. When you mouse over you can see the different types of parameters that might be used. It’s handy, it steps you through what you need when you’re creating calcs.
Another handy feature is…this calculation is valid. If I didn’t complete the calculation properly, an arrow would appear and give me some sort of hint about what I’m missing. That’s nice for troubleshooting more complicated calculations.
KPI CHART: TABLEAU VS. COGNOS; TABLEAU: TREND CHART, MAPS; SALES DASHBOARD COMPARISON; FILTER DIFFERENCES
KPI Chart: Tableau vs. Cognos
I’ve got my profit percentage, so I want to use that. I want to create this profit percentage KPI chart. You’ll notice it’s already different from the way Cognos does it. I’m not building charts in a dashboard. I’m building in Tableau. You basically build your charts or reports one at a time and then pull those into a dashboard, so right now I’m working on a specific sheet. I’m going to craft this KPI sheet and then I can combine that or pull that into a dashboard at a later point.
When I double click on profit percentage, Tableau takes a guess as to how it thinks I want that data to be displayed. In this instance it thinks I want to call up a bar chart. There is a nice little show me window on the right that you can use. This shows the available chart types in Tableau and highlights those that are actually available or makes sense based on the data that’s currently in your visualization.
Now, I’ve got this tiny little number like you saw in the Power BI and Cognos demo. We can easily set the formatting of this number when you set the default format. Every time I use it, it comes back to the same format.
Now, it’s going to change to a percent. You’re going to want this to be a little bit bigger since it’s a KPI. I’m going to bump this up to say 28. Okay, so now I’ve got a nice big KPI. Alright, so that’s my first chart. Let’s now build trend charts similar to what you saw in the Cognos and Power BI demos.
Building a Trend Chart in Tableau
In this example I’m going to use the total sales, the sales amount total, excluding tax. You’ll notice that it’s swapped from a bar chart to a line chart. This is something that Tableau has had in its DNA from the beginning. Tableau is constantly trying to help you work through the best practices and how your data should be presented.
Now, Tableau knows you’re creating a trend chart, but you’ll also notice that my show me window has expanded. There’s a number of different visualization types that I could use based on the data that’s in my element.
You’ll see it defaulted. I double clicked on date, but it brought in as year of date. This is something that Tableau has always done as well. There’s a higher goal. There’s an embedded hierarchy that Tableau establishes on date fields. It starts with the year and this + sign allows me to expand that.
Now, I want to be able to show those years stacked on top of each other. The way we do that in Tableau is to use pill. These are called pills and put it on the color shelf. Now it’s going to dimension my data to create one line for every year. Now I’ve just got the months up here in the trend. I’m going to call this my trend.
Creating a Map in Tableau
I’m going to show how easy it is to create a map in Tableau. When you see these little globe icons, that means that Tableau recognizes those values as geographical values that can be used to actually plot on a map.
I’m going to create one that’s done by state. When I double click on state, it draws in longitude latitude. It draws a map and it puts a point, puts a mark on every state in my data set and then I can quickly add whatever I want to show on this map.
If I also want to show the profit percentage on each of those states. I can drag that to the color shelf. It’s going to default to this blue pallet, but I can change it to be red black like you saw in the Power BI and Cognos demos previously. Okay, so now I have a map showing sales and profit by state and call this map.
Now, let’s go, we’re going to pull all these together into dashboards. You’ll see in my dashboard window that the three charts that I created are available to me here and I can just double click them, and it’ll pop them into the sheets. Double click on KPI, double click on trends and drag the map to the bottom.
I have a dashboard with three charts on it. You can use this as a filter. I want to filter the rest of my dashboard based on a click action on this chart, on this sheet.
I want the rest of the dashboard to filter for New Mexico, I can click on New Mexico, I can click on Colorado. I can do the same thing up here if I just want to see what the sales in August of 2013 were. The KPI was a profit percentage. This is the sales by state.
Difference: Tableau Sales Dashboard Filtered by Quarter, Cognos and Power BI by Year
Let’s jump over to the Tableau sales dashboard and how it differs from the Power BI and Cognos dashboards. You’ll notice those two have the color and coding in their years, so this dashboard should mention that. Instead, the Tableau dashboard is organized by quarter. The main driving filter is the quarter. When I change the quarter over here, the whole dashboard would filter to just this quarter. All the sales, all the different bar charts reflecting what happened in that quarter with the exception of the trend charts. I always want to show what’s going on over time, so I’m not filtering this by quarter but you’ll also notice that when I change my quarters, the colors of the bars are changing to reflect the year of that quarter, so we have four distinct years with four distinct colors.
I want to make sure that that’s encoded in my data, so when I make a change the end user knows that, oh, I’m looking at a different year now, so it’s very obvious.
Filter Difference: Tableau vs. Cognos Dashboard
In the Cognos dashboard, you can’t really do a lot with the filters or where you place them. In Tableau, you can put them wherever you want. There’s a number of different options for how you want to format these filters. If there are too many categorical values in the list that you can’t display them easily, you can use this as a multi-value dropdown. That’s a nice compact way to show the same data because you can place them anywhere you want on the dashboard and then you can tell them exactly what you want them to filter.
All these filters are working as global filters., so anytime I click it it’s going to filter the whole dashboard but if I wanted to just filter one sheet, I could. You’ll notice that when I hover over that point, I’ve got the chart and tooltip you saw in the Power BI demo.
Tableau Tooltips: Overview Then Filter and Drill Down
The tooltip I put in here is to answer the next question I think the user’s going to want to answer. That tooltip will change as I mouse over the different products. The same tooltip is available here. Same question.
Next question being answered who are my top salespeople? But maybe I want to see how they have performed relative to target over time, so now my tooltip is going to be that salesperson’s entire sales history by quarter for the last four years.
TABLEAU: TOOLTIPS, GEO REGION ORGANIZATION; PUBLISHING TO SERVER
Tableau: Tooltips Provide Details On-Demand
The Power BI and Cognos demos both showed this sort of drill down to actual fact level detail. This is a really important part of Tableau’s whole DNA. Tableau based its product on the idea of overviewing first and then allowing people to filter and drill and provide details on-demand. Tableau tooltips provide details on-demand. You can put tooltips on any of your points to give additional context and detail.
Tableau Differentiator: GEO Region Organization
Something else that’s different about Tableau is you can organize the geographical region by state and tell it that these states roll into a region. After I assign it as a geographical element, Tableau will build a map by region. Now I can show you the blow up by state, but I can also add an additional click action to show the invoices for just this specific region.
Now, I can go over to that same chart that looks at our table view. It looks similar to what you saw in the Power BI and Cognos demos, providing those details on the map.
Publishing to Tableau Server
This is all done in Desktop, but then I can publish this workbook to my Tableau Server. This is what that window looks like. It’s the same dashboard published on Server. You’ll see the same click actions, the same filtering works on Server, similar to the Desktop version but I have additional options to create custom views that I can share with colleagues. I can set up alerts, I can schedule myself to be emailed this report on a regular basis.
That’s the end of the Tableau demo.Read more