“Understanding Power BI Premium” Events

I’ll present “Understanding Power BI Premium” at three public events in June.

  • June 8th, Carolinas Power BI Group in Greensville, SC at 6 PM
  • June 24th, SQL Saturday Chattanooga, TN at 1:15 PM
  • June 26th, Atlanta MS BI and Power BI Group, Atlanta, GA at 6:30 PM

My calendar has the details.

Microsoft Power BI has enjoyed a lot of attention and success since it became generally available in July 2015.  But its licensing model and cloud-hosted limitations barred wide adoption, especially with larger organizations. The recently introduced Power BI Premium will change all of this. Join this session to learn how Power BI Premium will allow your organization to achieve:
• Flexibility to license by capacity
• Greater scale and performance
• Extending on-premises reporting with Power BI Report Server
• Embedded analytics

Understanding Writeback Target Allocation

I’m working on architecting a financial planning solution powered by Analysis Services Multidimensional. One thing that might not be obvious is how Multidimensional selects the target of writeback allocation. In this case, planning will be done at Customer and Product level. With the default equal allocation when writing at the customer level, it might appear that writeback doesn’t work correctly. You’d expect that only the cells that contribute to the aggregated value (10 in the screenshot below) will be affected by writeback. However, if the Customer and Product entities are in different dimensions, writeback will affect all products!

The reason behind this becomes obvious if you right-click the pivot table, and from its options enable “Shows rows with no data”. Then, you’ll see all products appearing with each customer (customers are crossjoned with products). Recall that by default, the pivot table uses NON EMPTY in MDX query to exclude combinations that don’t exist in the cube. But writeback makes no such assumptions. The reason for this is that the writeback cell is empty, then there is nowhere the writeback value will be allocated to. If the Customer and Product entities are in the same dimension, then the default equal allocation will write to all children of the affected parent, irrespective if their values contribute to its aggregated cell.

So, writeback is not the same as drilling through a cell. Now that you know how it works you can use different allocation settings to achieve the behavior you want. For example, you can choose a weighted allocation with the following expression to avoid writing back to empty values:

iif(Measures.CurrentMember = 0, null, Measures.CurrentMember)

Atlanta MS BI Group Meeting on May 22th

MS BI fans, join me for the next must-attend Atlanta MS BI and Power BI Group meeting on May 22th at 6:30 PM. My esteemed friend, Stacey Jones from Microsoft will explain the predictive analytics capability of Cortana Analytics Suite. He’ll demo a predictive analytics solution that was developed in a single day! It leverages Azure Machine Learning, Azure Data Factory, HDInsight, Spark, Power BI, and Intelligent Apps to deliver a solution that predicts the likelihood of flight delays for a customer. I will do a quick demo of the latest Power BI cool features. Our premium sponsor, TEKSystems, will sponsor the meeting.

Presentation: Cortana Intelligence Suite End-to-End
Level: Intermediate
Date: May 22nd, 2017
Time 6:30 – 8:30 PM ET
Place: South Terraces Building (Auditorium Room)
115 Perimeter Center Place
Atlanta, GA 30346
The Cortana Intelligence Suite provides tools to cover all types of business intelligence needs, from compelling dashboards to cutting edge predictive analytics! In this session we will look at a predictive analytics solution that was developed in a single day. It leverages Azure Machine Learning, Azure Data Factory, HDInsight, Spark, Power BI, and Intelligent Apps to deliver a solution that predicts the likelihood of flight delays for a customer.
Speaker: Stacey Jones has enjoyed his 27 year specializing in Business Intelligence and all things data. He currently serves as the Data Solutions Architect for Microsoft at the Atlanta Microsoft Technology Center (MTC).
Sponsor: People are at the heart of every successful business initiative. At TEKsystems, a leading provider of IT staffing, IT talent management and IT services, we understand people. Every year we deploy over 80,000 IT professionals at 6,000 client sites across North America, Europe and Asia. Our deep insights into the IT labor market enable us to help clients achieve their business goals—while optimizing their IT workforce strategies.

What Does Power BI Premium Mean for You?

I’m sure we’ve heard the announcements today about Power BI Premium. In fact, Power BI Premium is so important that Microsoft has positioned it as a new product under the Power BI marketing umbrella name instead of a new licensing model. Microsoft and industry experts covered the announcements well so I won’t reiterate the obvious. You may wonder what these changes mean for you. Let’s summarize.

Power BI Portfolio

In a nutshell, Power BI Premium targets larger organizations which have faced two issues with the current Power BI licensing model:

  • No “reader” license. If a report has a Power BI Pro features, all users accessing reports would need Power BI Pro license. So, if you a report that used Power BI Pro features, such as gateways or live connections and you won’t this report to be available to 1,000 users, you had to foot $10,000/month bill because everyone required Power BI Pro.
  • Per user license. A case in point – one year after a successful Power BI hybrid pilot, a Fortune 100 organization has purchased whopping 5 Power BI Pro licenses. There are several reasons for the slow adoption by large companies but one of them is the per-user license.

Large organizations who are seeking a mass Power BI deployment to potentially thousands of users could save big with Power BI Premium (use the nice calculator to find how much). On the downside, I’m not happy about Microsoft requiring Power BI Pro licenses for contributors on top of Power BI Premium.

I don’t see smaller organizations being very much interested in Power BI Premium. For them, a welcome change would be that Power BI Free adds Power BI Pro features. On the downside, Power BI Free loses simple dashboard sharing. This reflects the Microsoft vision about Power BI Free: it is for individual users who are evaluating Power BI. To mitigate the impact of the Power BI Free changes, Microsoft offers one year Power BI Pro trial offer to all Power BI Free users as of May 2nd.

Today Starting in June 2017th
Power BI Desktop Connect to 70+ data sources

Data transformations

Report creation and exploration

No changes
Power BI Free No live connections, No gateway connectivity

Smaller capacity limits and data refresh rates

Only simple dashboard sharing

Access to all data sources
Performance equivalent to Power BI Pro
No sharing (not even simple dashboard sharing)
Power BI Pro Access to all data sources

Larger capacity limits and data refresh rates

All sharing options (simple, workspaces, org content packs)

No changes
Power BI Premium Increased capacity limits
Dedicated environment
Content distribution (reader license)
Power BI Report Server
More features in future, such as in-memory caching, incremental refresh (read the whitepaper)

Personally, I’d like to see more Power BI pricing tiers added, e.g. Standard tiers. Currently, the lowest Power BI Premium tier (P1) is $5,000 per month which would be probably out of reach for smaller organizations. But fear not, you can stay within the old Power BI Pro licensing model.

Power BI Report Server

Microsoft has decoupled SSRS from SQL Server so it gets more frequent updates. SSRS becomes actually two products:

  • SSRS – This is the SSRS we know it but with no Power BI integration. It will get new RDL features but no Power BI features. See the Microsoft blogs here and here for more details.
  • Power BI Report Server – Distributed as a standalone installer, Power BI Report Server is a superset SSRS as it gets both existing report types and Power BI reports. As far as the reason for the name change, the Power BI name is a strong brand while SSRS has been associated with the old style paginated reports.

You can get Power BI Report Server in two ways:

  • As a part of the Power BI Premium bundle. You get the same number as licensed EE cores as the number of v-cores you purchased with Power BI Premium.
  • Standalone and covered by a SQL Server Enterprise Edition with Software Assurance license, plus Power BI Pro licenses for report authors (as with Power BI Premium).

So, although Power BI Report Server has divorced SQL Server, it’s still covered by its license (kind of when you send your kid to college but she still lives with you). Currently, SQL Server doesn’t check for Software Assurance in any way (there isn’t such SKU). So, it looks like Power BI Premium licensing would be an honor system for customers who want to get it standalone covered by a SQL Server Enterprise Edition license.

Power BI Embedded

Power BI Embedded has been gaining a lot of traction but the problem was that it’s separate from Power BI Service. Consequently, it had to catch up with Power BI Service. For example, it still doesn’t have connectivity to on-premises data sources. The good news is that Power BI Embedded marries Power BI Service so there will be a feature parity and a common set of APIs. The part that I’m not excited about is that its new licensing model requires Power BI Premium (good bye per-session licensing). This might be a showstopper for small ISVs. I hope that Microsoft introduces less expensive pricing tiers to better cater for needs of smaller companies. [Update 6/15/2017:  Microsoft announced low-cost EM* plans for Power BI Embedded starting at $625/mo]


Power BI Report Measures Over Tabular Models

The May release of Power BI Desktop adds the ability to define DAX calculated measures when Power BI Desktop is connected live to a Tabular model or Power BI datasets. This is conceptually similar to defining MDX calculated members in Excel connected to a cube. The measure definitions are local to the Power BI Desktop model (the Tabular model is not modified). You can do all measure-related tasks as when you define measures in the data model, such as changing the data type and formatting the measure or changing the home table. In the screenshot below, I’ve defined a YTD report measure over the Adventure Works Tabular model.


Behind the scenes, the DAX query generated by Power BI Desktop adds the measures as query-scoped measures in the /* USER DAX BEGIN/END */ section:

DEFINE MEASURE ‘Reseller Sales'[Reseller Sales YTD] =


TOTALYTD(SUM(‘Reseller Sales'[Sales Amount]), ‘Date'[Date])




“Reseller_Total_Sales”, ‘Reseller Sales'[Reseller Total Sales],

“Reseller Sales YTD”, ‘Reseller Sales'[Reseller Sales YTD]


Report-level measures are a welcome enhancement. Bringing this further, I’d like to see the ability to define report-level measures using the Quick Measure feature. Another feature that I’m waiting for is the ability to use custom measures (both defined in the model and report-level) in the new numeric range slicer (currently in preview).