Posts

Power BI vs. Tableau (Part 3)

pbivstableau

Feeling the heat, Tableau has come up with an updated Power BI vs. Tableau battle card as one of my clients pointed out. This time designed as a video. I guess the previous “10 Ways Power BI Falls Short” slide deck, which I discussed in the part 1 and part 2 of my “Power BI vs. Tableau” blog , wasn’t effective enough. I concur given the large number of customers abandoning them. Tableau is desperately trying to breathe new life into their aging software by a series of acquisitions to stay competitive but they’re fighting now an uphill battle. And their marketing materials should have a timestamp since Power BI improves every month and points get outdated quickly (see the first part 1 of my blog).

But let’s take a look at the latest battle card so we are better positioned to answer the question asked by Tableau “Which one helped me answer my questions faster?”

  1. 2:17 minute – Tableau shows a bar multiple by Year and Region, with the narrative “it’s surprising that other tools can’t do that”. Here is a similar visual with Power BI.
  2. 4:00 minute – Maps. OK, Tableau is very excited about lassoing points on the map like it’s the Holy Grail of geospatial analytics. What about using authoritative spatial data, such as ArcGIS. Because Tableau its proprietary visualization framework that doesn’t natively support plugins, this is what you have to do to get it with Tableau integrated with ArcGIS. With Power BI, it’s built in a custom visual, which by the way supports lassoing. So, which one helped me answer my questions faster?
  3. 5:30 minute — The high-density outlier pitch. Alas, this time isn’t taking the central stage. As explained here, as of 9/30 all Power BI visuals except maps are high density so it’s a mute point.
  4. 7:11 minute – Tableau pitches they connect to lots of, lots of data but shows less connectors than Power BI. Power BI has 80 connectors and growing every month.
  5. 8:00 minute – Tableau discusses collaboration and how easy it should be to share with coworkers by deploying to on-premises Tableau server. Well, I’d argue that it’s easier to deploy to the cloud, or even better, give the user the choice to deploy to a PaaS platform or to on-premises server.
  6. 10:00 minute – I’m losing the pitch as Tableau is demonstrating how dashboards have full fidelity when published. So, do Power BI reports.
  7. 11:00 minute – Recipients personalizing shared reports and dashboards by creating views. Tableau has a point here, the Power BI sharing story needs consistency. Previously, organizational content packs allowed users to create personal read-write copies but apps, which supersede content packs, don’t have this feature (they are read-only to the recipient). About filtering, users can pass filters on the report URL. And users can create reports from scratch if they have access to the dataset, such as members of a workspace.
  8. 13:12 minute – Sandboxing and lifecycle management. The current Power BI Service story is to use workspaces for different environments, but Power BI Premium would most likely improve on this. On premises, you can use DEV and QA report servers which don’t require licensing. Try to get a free server with Tableau for QA/DEV!
  9. 14:00 minute – Auditing. Power BI supports usage metrics on report and dashboard level. Power BI also has comprehensive and integrated auditing with Office 365.
  10. 15:00 minute – More pitch about the Tableau Server governance. Well, SSRS/Power BI Report Server had this for a long time. But it also supports SSRS traditional reports, Excel reports, and Power BI reports. Speaking of a centralized report management, can you deploy anything else than Tableau reports to the Tableau report server, so it becomes an enterprise report portal for different report types?
  11. 16:00 minute – More about auditing. SSRS/Power BI Report Server has an execution log for this.

Then at the bottom of the page, we have a study by someone ex-Gartner, citing lower TCO for Tableau compared to Power BI. I can’t be reading this right. From the Gartner’s 2017 Magic Quadrant for Business Intelligence and Analytics Platforms, “On an annualized basis, Microsoft Power BI is roughly one-third of the license cost of a three-year perpetual BI license, but 80% lower than other cloud BI products. Low total cost of ownership was cited as the second most important reason for reference customers choosing Microsoft Power BI.” Yet, this researcher has found that “Microsoft Power BI’s total cost of ownership (TCO) to be 29% higher than Tableau”? Pick who to trust. And should business users deal with 20 GB datasets to start with? What about the novel idea of putting all of this data into a centralized semantic layer so we don’t have datasets moving around? Wait, isn’t a Tableau model limited to a single dataset with “data blending” capabilities? What’s the TOC then for all these isolated Tableau spreadmarts?

When you talk to Tableau about Power BI, get them to answer also the points I made in Part 2 of “Power BI vs. Tableau” blog.

Hey Cortana, where is Power BI data?

A Power BI presentation is rarely complete if someone doesn’t ask me to demonstrate the Cortana integration. However, unlike everything else in Power BI that gains features in time, in my opinion the Cortana experience has lost some value. Previously, you could ask natural questions in Cortana across datasets, just like you can use the Power BI Service Q&A to gain insights from dashboards. Unfortunately, Microsoft has removed this feature in favor of searching for report pages that are specifically optimized for Cortana (also known as Cortana answer cards). You can also search dashboards and reports by name but that won’t get you the “vow” effect since 1) you need to know the name and 2) when you click the name Cortana opens Power BI in a separate browser window. So much about natural queries and machine learning. I think the change took place after Microsoft decided to move away from Cortana natural queries to Microsoft’s Azure Search Service.

UPDATE 11/7/2017: I’ve heard from Microsoft about the Cortana change. Previously, you could ask natural questions in Cortana across datasets, just like you can use the Power BI Service Q&A to gain insights from dashboards. However, Microsoft noticed there were a large percentage of cases where customers were enabling Q&A in Cortana simply to search for reports, without intending to ask natural questions. This was resulting in unexpected Q&A ad-hoc results showing up in Cortana answer lists, leading to user confusion. So, Microsoft decided it would be better to restrict results to Cortana answer cards until Power BI supports configuration settings to allow model authors to explicitly control whether ad-hoc answers should be enabled as well.

Back to the Cortana answer cards, they appear to work only for datasets created in Power BI Desktop. They don’t work with the Power BI samples, nor they work in reports connected to dataset created directly in Power BI Service (e.g. by importing an Excel file).

Nevertheless, with the rising popularity of voice technologies (Alexa, Siri, Cortana), you’d probably still get the “vow” effect. And, even better, the reports are now interactive!

110617_2220_HeyCortanaw1.png

Can’t get enough from Cortana or get it to work at all? Try the “Troubleshoot Cortana for Power BI” article at https://powerbi.microsoft.com/documentation/powerbi-service-cortana-troubleshoot/?

Power BI Service in Get Data

Scenario: You have published a Power BI Desktop file with a report to Power BI Service. You want to give certain users access to some pages in the report. This is not a security mechanism although it could be, if you want to control security at the report layer.

Solution: When a customer asked about this scenario, my first thought was to create reports directly in Power BI Service. For example, the report that’s included in the Power BI Desktop file could have some visuals that you might want to share with everyone. Then, you create additional reports for each group of users and share them via dashboard sharing. This approach will work but if someone nukes the dataset, your reports go down with it. True, you can export a report connected to a dataset created in Power BI Desktop but who bothers to back up reports on a regular basis?

Then it dawned on me that you can connect Power BI Desktop live to a published dataset, just like you can connect to Analysis Services (recall that datasets published to Power BI Service are hosted in Tabular).

The major benefit of using Get Data->Power BI Service is that you can still back up the Power BI Desktop file and your reports with it.

Another benefit is that you can treat the published dataset as a “semantic layer” and let users create reports from it without importing data. For example, instead of asking users to call a stored procedure passing parameters and importing data, you can export all the required data and publish it to Power BI Service. Then, all users have to do is to connect to the dataset and start creating reports.

110517_2358_PowerBIServ1.png

Power BI DirectQuery with Parameterized Stored Procedure

Issue: You have a parameterized SQL Stored procedure that you want to call in DirectQuery mode. You attempt to use “exec sp parameter” syntax in the Power BI Desktop which works in the Query Editor but it fails to execute due to syntax error when you apply changes.

Workaround: Power BI uses the “select * from exec sp” syntax which doesn’t work. However, if you switch to OpenRowset (you’d need to enable ad hoc distribution queries on the database first), it will work. Other possible workarounds that would probably work is inserting the stored procedure results into a temp table and then select from the table, or wrapping the stored procedure with user-defined table function.

I attach a sample that demonstrates the OpenRowset approach. Download the Stored Procedure Direct Query.zip file (link provided at the end of this post) and rename the from *.zip to *.pbix.

Here is the query text:

= Sql.Database(“elite2”, “AdventureWorks2012″, [Query=”SELECT * FROM #(lf)OPENROWSET(‘SQLNCLI’,’trusted_connection=yes’, ‘exec AdventureWorks2012..uspGetAddress ”” & @City & “”’)”, CreateNavigationProperties=false])

I went one step further and parameterized the stored procedure parameter so that your users can set it conveniently using Edit Queries -> Edit Parameters.

Stored Procedure Direct Query

Understanding Dates in Power BI Quick Measures

As I explained here, Power BI introduced the highly anticipated Quick Measures feature to avoid writing DAX formula for common measures. The feature is still in preview, so make sure you enable it in Power BI Desktop from File, Options and Settings, Options, Preview Features. Many quick measures, such as time intelligence calculations (YTD, QTD, Period Over Period Change, etc.), rely on a date field. The date field could come from a Power BI-provided date hierarchy or a Date table.

Letting Power BI auto-generate date hierarchies is easy but dangerous and limiting. As Chris Webb explains here, Power BI-provided hierarchies could bloat the size of the data model. For example, by disabling them in data in a data model provided by a customer, I was able to reduce the data model size from 300 MB to 30 MB!

Instead, a best practice is to have a Date table. It gives you a control over what date periods you want to include. It also allows you to analyze several fact tables by date in a single visual.

Unfortunately, unlike Power Pivot or Tabular, currently you can’t mark a Date table in Power BI Desktop, even though everything is configured correctly in your model. Attempting to use a date field from a Date table when setting up a quick measure, results in this error.

102317_0006_Understandi1.png

A “primary date column” means a field of a Date type in a Date table marked as such. DAX time intelligence functions only work when several conditions are met by the date column, such as must be at date granularity, must be continuous, cannot have duplicate values, etc. Since PowerBI hasn’t verified that a user supplied date column meets all those conditions, it doesn’t allow users to define Quick Measures to prevent unexpected results if user is unaware that they cannot just pick any date column.

So why the teaser then? A primary date column will work if you import a model with marked Date table from Power Pivot or if you connect to Tabular. While waiting for Power BI to let us mark Date tables, the workaround is to use a Power BI-provided hierarchy (at least to get the formula), and then change the formula manually to reference the Date field in your Date table. For an example of how to do this, refer to my blog that I mentioned at the beginning. Just don’t give up on data tables!

Power BI Bookmarks

So far, Power BI has lacked the ability to navigate to particular report element, such as another report page, and to allow you to show/hide visuals. For example, Tableau allows you to add buttons to pages and navigate to another page on button click. Such navigation capabilities are important for story telling and for emphasizing elements on the screen.

This changes with the October release of Power BI Desktop which brings a set of navigation capabilities, including bookmarks, spotlight, and selection page.

Suppose you have a meeting and walk management through the sales performance of your company. You start with a bar chart. There is a decrease in sales in which you can explain by using the Analyze Increase/Decrease which I discussed here. Now you have two visuals on the same page:

100917_1332_PowerBIBook1.png

But at the beginning of your presentation, you’d want to bring focus on the bar chart, explain the overall trend, and then focus on why sales have decreased (the waterfall chart). You can use bookmarks to achieve this.

  1. Open the new Selection pane (in the View ribbon tab) and hide the Waterfall chart.
  2. While on the View ribbon tab, check the Bookmarks pane. Click Add to bookmark the state of the current page.
  3. Unhide the waterfall chart and create another bookmark.

Now you have two bookmarks that show/hide visuals on the same page. This is conceptually similar to the Power Point animation pane although bookmarks don’t come up with cool entry and exit animations. Now when you present, you can simply use the Bookmarks pane for navigation the way you can use a Table of Contents (TOC) in a Word document.

100917_1332_PowerBIBook2.png

Bookmarks are a welcome new Power BI feature. They are important for data story telling and for improving the navigation experience in a busy report.

Configuring Power Query OData Feeds

Scenario: A customer has implemented a Power BI model that retrieves data by calling the Dynamics CRM OData Feed endpoint. The dataset refresh operation is timing out. They want to increase the timeout setting but unlike connecting to SQL Server, where you can set the timeout duration in the source advanced properties, there is no UI for ODATA.

Solution: If you open the query behind the table, and click the Source step, you’ll see in the Query Editor formula bar the following M code:

= OData.Feed(“https://<tenant>.api.crm.dynamics.com/api/data/v8.1/”)

From the documentation, we realize that OData.Feed can take additional settings and one of them is the Timeout setting that you can pass to the third options argument. For example, you can change the M code to set the timeout to 1 hour:

= OData.Feed(“https://<tenant>.api.crm.dynamics.com/api/data/v8.1/”, null, [Timeout=#duration(0,1,0,0)])

The timeout uses the duration data type which is explained here. Bringing this one step further, you can restrict what records get pulled in the OData.Feed using $filter and also only grab data for the columns you’re using by including the field names in the $select parameters, e.g.

“https://<tenant>.crm.dynamics.com/api/data/v8.1/leads?$filter=<somefield> eq 740110001&$select= <removed list of fields>”

Power BI Premium Reports on Mobile Devices

Can we surface Power BI reports deployed to Power BI Premium Report Server on mobile devices? Yes, and we have three quick options:

  1. Since Power BI reports render in HTML5, they should render in any modern browser.
  2. On iOS and Android devices, consider Power BI Mobile for optimum viewing experience. In Power BI Mobile, go to the Settings and click Connect to Server. Then plug in the report server URL, e.g. http://<servername>/reports. Note that the Power BI Mobile Windows app doesn’t support Power BI Premium Report Server yet. Also, by default your reports will only work if you are on your company intranet or the mobile device connects to your company VPN. To make reports available outside corporate network, you can configure a Web Application Proxy to tunnel in.
  3. For embedding reports on a page, have an HTML page with iframe that request the report by URL passing the embed parameter as explained here.

Power BI can’t reach to your report server? Don’t forget to configure the firewall on the Power BI Premium Report Server to allow inbound connections over port 80, as explained here.

Power BI Group Security

Power BI security is interwoven with Azure and O365 security. Sure, granting individual users access by entering emails works everywhere but it quickly becomes counterproductive with many users. For example, a large insurance company would like grant report access to 250 users and integrate this report with Dynamics 365. If you secure individually, this will require entering 250 emails three times: Dynamics 365, Power BI, and Row-level Security (RLS). As in the old days, we want to use groups to reduce the maintenance effort because all these users are added to a security group, we can just grant access to the group. And, when users come and leave, we make changes to one place only: the group and everything will work magically. However, nothing is simple and security is no exception. In fact, Office 365 has various group types, as you can see below.

o365groups

To make things even more interesting, all groups are not created equal and currently Power BI supports has a varying degree of supporting them. Things will undoubtedly become more unified in time but for now the following table is meant to help you understand what’s supported and where.

Feature O365 Group O365 Distribution List O365 Security Group Exchange Dynamic Distribution List
App workspace content Yes No No No
App distribution No Yes Yes (with email) No
Dashboard sharing No Yes Yes (with email) No
RLS No Yes Yes No

Let’s start with the features. “App workspace content” means adding group members to a shared app workspace. “App distribution” means publishing an app for a wider distribution, e.g. to the entire organization, as you can do previously with organizational content packs. “Dashboard sharing” means sharing a dashboard by email (the first sharing option in Power BI before workspaces and content packs were introduced). RLS means row-level security, i.e. adding groups as members of an RLS role so that they gain different access to data. Moving to group types, “O365 Group” is the group you’d get when you create a workspace. This type of group is supported by many O365 applications, such as Yammer, SharePoint, Teams, etc. “O365 distribution list” is meant for sending email to multiple users in Outlook Web Access. “O365 Security Group” is Azure Active Directory group (if you sync your on-premises AD to Azure, you’d get O365 security groups too). “Exchange Dynamic Distribution List” allows you to create condition-like groups, e.g. for sending email to everyone.

Are you still with me? One more twist. Due to a bug, app distribution and dashboard sharing requires the O365 security group to have an email assigned. A few more notes:

  • Creation of content: App workspaces in Power BI are meant for authors to collaborate and create content. You need a Power BI Pro license for accessing these workspaces and you cannot yet add AD security groups as members/admins to app workspaces
  • Distribution of content: Once content is created in an app workspace, you can distribute it in two ways:
    • Share out individual dashboards. If the original app workspace is in premium, recipients just need a free license. If not, they need Pro licenses. Also, you can share dashboards to security groups but not O365 groups.
    • Publish out an app from the workspace. If the original app workspace is in Premium capacity, recipients just need a free license. If not, they need Pro licenses. Also, you can publish an app to AAD security groups but not O365 groups.

TIP: If you have Power BI Premium, you can share a dashboard from a Premium workspace to other users. You don’t have to publish an app. This is explained in more details here.

Clear as mud? As I said, I hope that table would become obsolete in a few months when Power BI would support all group types everywhere.

Quick Insights Coming to PBI Desktop

The September release of Power BI Desktop brings Quick Insights in the form of the very nice Explain Increase/Decrease feature (in preview). Suppose you have a column chart and you see a sudden increase or decrease in the chart series. You can now right-click on the bar and then click Analyze, Explain the Decrease menu. This runs the Quick Insights machine learning algorithms to predict what caused the anomaly. As in Power BI Service, Quick Insights produces several reports.

091417_1346_QuickInsigh1.png

In this case, one of the Quick Insights visuals informs me that most of the decrease is contributed to the bad performance of one of the sales reps (Stephen Jiang). I could have sliced and diced all day long but I could have found this myself. If I find a particular Quick Insight visual useful, I can add it to the report. From that point, I can use just like any other visual on the report.

The Power BI Desktop Explain Increase/Decrease helps you performing root cause analysis in a blink of an eye.

Currently in the preview, the feature is rough around the edges. For example, I was able to get it to work only if I add a field from a Date table, such as Calendar Year, to the chart. Other limitations include:

You can’t use it if your visual includes:

  • TopN filters
  • Include/exclude filters
  • Measure filters
  • Non-additive measures and aggregates
  • Show value as
  • Filtered measures (it’s the new thing we use for scatter chart in insights)
  • Categorical columns on X-axis unless it defines a sort by column that is scalar. If using a hierarchy, then every column in the active hierarchy has to match this condition
  • Non-numeric measures: Model measure support is limited to sum and count only right now.

There are also limitations in data connectivity:

  • Direct query
  • Live connect
  • On-prem RS
  • Embedding