Thoughts On the Microsoft/Pyramid Power BI Deal

Ever since Power BI first appeared, the number one request from customers has been the ability to publish reports and dashboards to an on-premises server rather than the cloud. There were two standard responses from Microsoft to this request:

  • la-la-la-I’m-not-listening-la-la-la-cloud-la-la-la-future-la-la-la
  • Maybe we’ll have something in SharePoint v.next

…neither of which were particularly satisfying. A lot of businesses just don’t feel comfortable with the cloud yet, and more importantly a lot of businesses can’t put their most valuable data in the cloud for a variety of legal and regulatory reasons.

Today’s announcement of a deal between Microsoft and Pyramid Analytics is big news because it means Microsoft have got a credible answer to the Power-BI-on-premises question at last. For details, read the blog posts here and here, if you haven’t already, plus the Pyramid press release and this page on the Pyramid site. It’s not a perfect solution – I had been hoping that Microsoft would unveil an on-prem Power BI server that they had been working on in secret – but it’s a lot better than what we had before. It also ties up nicely with existing on-premises SQL BI investments that customers may have, and does so without a cumbersome SharePoint dependency.

What has been announced, exactly? From the Pyramid press release:

Pyramid Analytics … today announced a strategic collaboration with Microsoft consisting of development collaboration and technology integration.

The output of this new collaboration is a range of new features in the Power BI Desktop at expected General Availability on July 24. Among those features will be an option to publish a Power BI Desktop file to Pyramid Analytics Server. This feature will enable an ease of integration between the Power BI Desktop and the Pyramid Analytics Server.

From the Pyramid blog post:

This was the result of a strategic collaboration agreement that included:

  • Pyramid Analytics help in the Power BI Desktop development
  • Technology integration to publish Power BI content to Pyramid Analytics Server
  • Go-to-market coordination so mutual customers and partners get the best of our technologies

Here are some screenshots that Pyramid gave me showing what the ‘Publish to Pyramid’ feature will look like in Power BI Desktop:

2015-07-21_18-43-53

2015-07-21_18-44-51

Obviously this is great news for Pyramid and I’m sure it will be a big boost for their business. They are certainly one of the leading third party client tools for SSAS and, I guess, the one with the biggest presence worldwide. Many of my customers are also customers of theirs, and I’ve always been impressed with their products. It’s interesting that this is a partnership rather than an acquisition… maybe, given the large number of ex-Proclarity guys at Pyramid, they had no desire to see history repeat itself?

For Microsoft, I think it’s the best possible solution in the circumstances. Ever since the Power BI reboot last year, Microsoft’s BI strategy has become a lot more pragmatic – something that I, and pretty much the whole of the Microsoft BI community, have welcomed. Rather than pursue a grandiose strategy that fails on the details, the new focus is now on [shock!] building a product that people not only want to buy, but can buy easily. Some compromises have had to be made based on the position that Microsoft has found itself in, though. I guess with all the resources that are being thrown at Power BI V2, plus the development effort that is going into on-premises BI features in SQL Server 2016, it proved easier to partner with a third party vendor that already has a mature product and spare development resources, than build something from scratch.

There are some downsides to all this, though. First of all, I feel sorry for the third-party client tool vendors that aren’t Pyramid, who must be feeling hard done by right now. That’s business, I suppose. Second, Pyramid’s on-premises solution is yet another dashboarding/reporting component that must be understood and fitted in to the Microsoft BI story along with Power BI Desktop, Excel, Reporting Services, Datazen, PerformancePoint (RIP), Excel Services and so on, making the Microsoft BI pro’s life even harder still. Similarly, the Pyramid brand is likely to confuse customers who really just want to buy a single, Microsoft-branded solution (and don’t get me started on the whole Power BI/Cortana Analytics Suite branding overlap thing either).

Overall, I’m very happy as a result of this news. What with this, and the RTM of Power BI v2 tomorrow, Microsoft is back as a serious contender in BI both on-premises and in the cloud, and is catching up with the competition incredibly quickly.

One-To-One Relationships In Power BI

My blog post from earlier this year about bidirectional relationships and many-to-many in Power BI sparked a lot of interest. What I didn’t realise at the time is that there’s another new feature (albeit rather less exciting) concerning relationships: you can now create one-to-one relationships between tables.

For example, consider the following two tables:

image

Both contain a column called Fruit containing the same, distinct set of values. If you load both these tables into Power BI Desktop, create a relationship between them and make sure the Cardinality is set to 1:1 and Cross Filter Direction to Both, like so:

onetoone

…then not only do you get bi-directional cross-filtering (ie if I select something from Fruit1 it will filter the Fruit2 table, and if I select something from Fruit2 it will filter Fruit1) but the Related() and RelatedTable() functions can be used in a DAX calculated column on either table to look up values in the other. With one-to-many relationships, you can only use Related() in a calculated column on the ‘many’ side of the relationship and RelatedTable() on the ‘one’ side of the relationship.

Thanks to Marius Dumitru for pointing this out to me!

Power BI Desktop As A Client Tool For SSAS Tabular

There has been another flurry of Power BI announcements in the last few days in preparation for RTM on July 24th; you can read about them here if you haven’t already. There’s no point me repeating them all, but in amongst the major features announced there was one thing that I thought was worth highlighting and which could easily get overlooked. It is that by RTM the Power BI Desktop app will be able to connect direct to SSAS Tabular – that’s to say, you will be able to use it as a client tool for SSAS Tabular in the same way you can use Excel and any number of third party products.

The Power BI Desktop app was previously known as the Power BI Designer – the name change was a wise move, because it is in fact a full featured desktop BI tool in itself, and not just a ‘designer’ for the cloud based Power BI service. It is a free download and you can use it without any kind of Power BI subscription at all. Therefore even if you are a traditional corporate BI shop that uses SSAS Tabular and you aren’t interested in any kind of self-service BI at all, you could use it just as a client for SSAS and forget about its other capabilities.

Why would you want to do this though? More specifically, why use Power BI Desktop rather than Excel, which is of course the default client tool for SSAS? I’m a big fan of using Excel in combinations with SSAS (pretty much everything Rob Collie says here about Excel and Power Pivot also applies to Excel and SSAS – for the vast majority of users, for real work, Excel will always be the tool of choice for anything data related), but its data visualisation capabilities fall well short of the competition. While you can do some impressive things in Excel, it generally requires a lot of effort on the part of the user to build a dashboard or report that looks good. On the other hand, with Power BI Desktop it’s much easier to create something visually arresting quickly, and with the new open-source data visualisation strategy it seems like we’ll be able to use lots of really cool charts and visualisations in the future. Therefore:

  • Showing off the capabilities of Power BI Desktop will make selling a SSAS Tabular-based solution much easier, because those visualisations will make a much better first impression on users, even if they do end up using Excel for most of their work.
  • Less capable users, or those without existing Excel skills, will appreciate the simplicity of Power BI Desktop compared to Excel as a client tool.
  • Some users will need those advanced data visualisation capabilities if they are building reports and dashboards for other people – especially if those people expect to see something flashy and beautiful rather than a typically unexciting, practical Excel report.
  • If your users are stuck on Excel 2007 (or an earlier version) and aren’t likely to upgrade soon, giving them the Power BI Desktop app instead will give them access to a modern BI tool. Excel 2007 is an OK client for SSAS but is missing some features, notably slicers, that Excel 2010 and 2013 have and that are also present in Power BI Desktop.
  • Similarly, if your users are expecting to do a mixture of corporate BI using SSAS Tabular as a data source, and self-service BI, but face the usual problems with Excel versions, editions and bitness that prevent them from using the power-add-ins in Excel, then standardising on Power BI Desktop instead could make sense.
  • If you do have a Power BI subscription and can work with the requirements for setting up direct connection from PowerBI.com to an on-prem SSAS Tabular instance, then publishing from Power BI Desktop to PowerBI.com will be very easy. If you need to see reports and dashboards in a browser or on a mobile device, it could be a more attractive option than going down the Excel->SharePoint/Excel Services or Excel->OneDrive->PowerBI.com route.

In short, I don’t see Power BI Desktop as a replacement for Excel as a SSAS Tabular client tool but as a useful companion to it.

The last question that needs to be asked here is: what does this mean for third party SSAS client tool vendors like Pyramid Analytics and XLCubed? I don’t think these companies have too much to worry about, to be honest. These vendors have been competing with a less feature-rich, but effectively free, Microsoft option for a long time now. While Power BI Desktop erodes their advantage to a certain extent, they have a lot of other features besides visualisations that Microsoft will never probably provide and which justify their price. Besides that, the fact that Power BI doesn’t support direct connections to SSAS Multidimensional (yet…? ever…?) excludes at least 80% of the SSAS installations out there.

Checking Columns Are Present In Power Query

Something I was meaning to mention in my previous post (but forgot about…) was that in a lot of cases you don’t really care if your output contains all the required columns – it’s enough just to check that your input contains all the required columns. Luckily M has a function called Table.HasColumns() to help you do this. For example, using the csv source file from my previous post, which should have three columns called Product, Month and Sales, the following query will return true if the source file has these columns and false if it doesn’t:

let
    Source = Csv.Document(File.Contents("C:\MissingColumnDemo.csv"),[Delimiter=",",Encoding=1252]),
    PromotedHeaders = Table.PromoteHeaders(Source),
    CheckColumns = Table.HasColumns(PromotedHeaders, {"Product", "Month", "Sales"})
in
    CheckColumns

Using DateDiff() To Calculate Time Intervals In DAX

One of the most useful new additions to DAX in Excel 2016 and the Power BI Designer is the DateDiff() function. It does exactly what you would expect: calculate the amount of time in between two dates, and express that value as either seconds, minutes, hours, days, weeks, months, quarters or years.

Here’s a very simple table of dates:

image

With this table loaded into the Power BI Designer, you can add new calculated columns to the table by clicking the New Column button on the ribbon. Here are two calculated column definitions that give the number of days and the number of years between the Start Date and the End Date on each row:

DayDurationColumn = 
DATEDIFF(MyTable[Start Date], MyTable[End Date], DAY)

YearDurationColumn = 
DATEDIFF(MyTable[Start Date], MyTable[End Date], YEAR) 

The output is pretty much what you’d expect:

image

It is of course also possible to create measures that use the DateDiff() function to, for example:

YearDurationMeasure = 
DATEDIFF(
FIRSTDATE(MyTable[Start Date]), 
LASTDATE(MyTable[End Date]), 
YEAR)

image

All very straightforward, then, and much easier than having to calculate these values yourself.

Using SelectColumns() To Alias Columns In DAX

A few years ago I wrote this post on how to alias columns in a table in DAX, using a combination of AddColumns() and Summarize(). The good news is that in Excel 2016/the Power BI Designer/SSAS Tabular 2016 there’s a new DAX function specifically for this purpose: SelectColumns(). Here’s an example of how it can be used:

Imagine you have the following source table, called Products:

image

You can write a DAX query to get all the rows and columns from this table like so:

EVALUATE Products

Here’s the output of that query in DAX Studio (and remember, DAX Studio can connect to data loaded into the Power BI Designer, which is what I’m doing here):

image

You can alias the columns in this table using SelectColumns() very easily, like so:

EVALUATE
SELECTCOLUMNS (
    Products,
    "Column One", Products[Product],
    "Column Two", Products[Colour]
)

Here’s the output:

image

The syntax for SelectColumns() is straightforward: the first parameter is a table expression, and after that there are pairs of parameters consisting of:

  • A new column name
  • An expression returning a column from the table given in the first parameter

As you can see in the output of the query above, I’ve renamed the Product column “Column One” and the Colour column “Column Two”.

This means I can now crossjoin a table with itself without needing to worry about conflicting column names, like so:

EVALUATE
CROSSJOIN (
    Products,
    SELECTCOLUMNS (
        Products,
        "Column One", Products[Product],
        "Column Two", Products[Colour]
    )
)

image

One other interesting thing to note about SelectColumns() is that it allows you to do projection in a DAX query easily – as Marco notes here, it was possible before but it wasn’t pleasant. For example, the query:

EVALUATE
SELECTCOLUMNS (
    Products,
    "Just Colour", Products[Colour]
)

Returns:

image

Notice how there are three rows in the output here and that the value Green occurs twice. If you’re a true DAX afficionado, you might get excited about that.

Thoughts On All The Recent Power BI/SQL Server 2016 BI/Excel 2016 News

The last few weeks have seen more Microsoft BI-related announcements in a short time than I can ever remember before. Some of them I’ve blogged about; most I’ve at least tweeted. For good summaries of what’s coming for Power BI, on-premises SQL Server BI and Excel 2016 I can recommend the following posts by other people, all of which are worth reading:

http://www.jenunderwood.com/2015/05/14/sql-server-bi-2016/

http://www.jenunderwood.com/2015/04/23/april-microsoft-bi-world-news/

http://byobi.com/blog/2015/05/ssas-related-enhancements-in-sql-server-2016/

https://gqbi.wordpress.com/2015/05/14/bi-nsight-excel-2016-power-bi-updates-including-new-data-sources-azure-sql-data-warehouse/

https://gqbi.wordpress.com/2015/05/07/bi-nsight-sql-server-2016-power-bi-updates-microsoft-azure-stack/

Even then I’m not sure everything has been covered, and because new stuff is coming thick and fast (custom regions in Power Map! DirectQuery/ROLAP in the cloud with Power BI connecting to Azure SQL Database!) it’s hardly worth trying. However, I do think this is as good a point as any to work out what I think about all this activity and where Microsoft is heading.

SSAS Multidimensional Improvements

I’m well past the stage of feeling angry about the neglect of SSAS Multidimensional over the past few years, and I’m genuinely grateful that it’s getting some investment rather than nothing at all. That said, I’m not sure which customers asked for Netezza support or DBCC – they aren’t things I’ve ever needed. The promised performance improvements are where I expect the real value to be, and on their own they will probably give existing customers reason enough to upgrade to 2016. It would have been nice to get even one new feature from this list though.

SSAS Tabular Improvements

As expected, the Tabular engine in SSAS 2016 gets a lot of new stuff for free because of its shared heritage with other Power BI tools. My feeling is that uptake of Tabular has been slower than it should have been because 2012 was, frankly, a bit v1.0 with all the immaturity that implies, and there haven’t been any substantial improvements since then. With 2016, though, it looks like Tabular will take a great leap forward and as a result be seen as a much more capable platform. There will certainly be fewer reasons to choose Multidimensional over Tabular, although for applications that require complex calculations (such as financial applications) Multidimensional will still have the upper hand. The more reasons I have to love Tabular, the less I’ll worry about the lack of new features in Multidimensional.

Power Query And The Corporate/Self-Service BI Crossover

As regular readers of this blog may have noticed, I like Power Query a lot and I’m pleased to see that it has extended its reach into corporate BI. Power Query as a data source for SSAS will be important for scenarios where Power Pivot models are upgraded to server-side solutions; I don’t think it will be a good idea to use Power Query if you’re building an SSAS solution from scratch though. Power Query in SSIS was another predictable development and one which should make it easier to work with certain data sources (such as Excel files); the existing ability to publish the output of an SSIS package as an OData feed using the Data Streaming Destination, which can then be consumed by Power Query, could open up some interesting scenarios where a user builds a data set in Power Query and publishes it via SSIS for consumption by other Power Query users.

It’s the promised integration of Power Query and SSRS that excites me most though. I asked for it here and it looks like my wish has been granted! As well as providing access to a wider range of data sources and a common ‘get data’ experience with other tools, I think it will be the key to making SSRS and in particular Report Builder the self-service BI tool that so many customers want it to be. Report Builder has struggled with two problems since it first appeared: first, make it easier for users to lay out a nice-looking report on a canvas, something that the current version does a reasonable job of I think; and second, make it easy for non-technical users (who, for example, might have little or no SQL knowledge) to get data from data sources for their reports – this is where it has not succeeded in the past, and where Power Query could make all the difference. Power Query, among other things, is a solid, user friendly, SQL generation tool. This, plus the fact that SSRS will be updated for all modern browsers and get new visualisations and report themes etc, means that the vast number of existing SSRS customers will have a lot of good reasons to upgrade to 2016, and when they do they’ll also find it easy to integrate with the rest of Power BI.

Power BI: Will Anyone Buy It?

It’s very easy for Microsoft BI fanboys like me to get all worked up by the constant drip feed of tweets about new Power BI features. An impartial observer will point out that some of these features, like the ability to change the colours of your charts in Power View, are actually things we should be embarrassed at not having already. Nonetheless I think it’s fair to say that Microsoft are doing a good job of getting its core customers excited about Power BI and there’s also a lot of evidence that people outside this core at, at least, curious, so from a marketing perspective everything’s going well.

Even if the marketing is good, that will only get Power BI evaluated. Those evaluations will only turn into purchases if the product itself is up to the task. Microsoft set itself an extremely difficult task when it decided to change the direction of Power BI and deliver a respectable version 1.0 this year; the impressive speed that new features are arriving at suggests that they will manage it. When this product is put side-by-side with competing tools it will have some advantages – Power Query is excellent, the Power Pivot engine is fast and can handle all kinds of complex calculations – but will inevitably appear immature in other respects such as visualisation. I think the limit on the amount of data that can be held in a single data model, either on the desktop or in the cloud, is also something that will be a problem for those of us who are used to building server-side SSAS solutions that can hold all the data the user ever needs to see. Maybe DirectQuery/ROLAP on SQL Azure and perhaps Azure SQL Data Warehouse will make this irrelevant? Overall though in my opinion the version of ‘new’ Power BI that will RTM later this year will be seen as more than good enough from a technical standpoint, and if this rate of change is maintained for version 2.0 then it will be something special.

I also think that the focus on building APIs and connectors to other web services is a really clever move. There are a lot of other vendors out there who don’t want to build their own BI functionality, and if Microsoft can convince them to use Power BI that will bring a lot of customers on board. Even at this early stage it looks like Microsoft is doing a good job of recruiting these vendors (SQL Sentry for example, but there are many others) as well as getting other teams inside Microsoft (like Visual Studio Online) to do the same. Close integration with new Microsoft services like Azure Stream Analytics and Azure SQL Data Warehouse should have a similar effect, although less pronounced given that these new services will have few users initially.

While I admit the divorce from Excel was the right thing to do in the circumstances, I still find that I prefer working in Excel over the Power BI Dashboard Designer. Maybe that’s partly due to habit, but Power View still has a long way to go before it has the flexibility of Excel PivotTables and especially cube formulas. That’s why I think Marco Russo’s campaign to create an API for the Dashboard Designer and to support external connections from Excel and other tools is so important. If you haven’t voted already, please do so now! This would be a killer feature in that it would allow you to continue to build reports in Excel (maybe 32-bit) while still making use of new features in the engine. It would give use all the good things we have today with the Excel Power add-ins and more. It would also, as Marco points out, be another reason for third party vendors to use the Power BI platform.

The final factor to consider is price. Making the Dashboard Designer free is important, because it’s not just a Dashboard Designer but a complete, standalone desktop self-service BI solution in itself. Many customers will use it as such without buying a Power BI subscription – that is, if they know that is an option. The free/$9.99 cloud subscription model is also very attractive, and all in all the new pricing model is a refreshing change from the nightmare that ‘old’ Power BI licensing was. I wonder if there will be any particular incentives (financial or otherwise) for partners to sell or recommend Power BI to their customers? If not,there probably should be.

Conclusion

Overall, I’m happier with the direction that Microsoft BI is going in than I have been for a long time. Power BI now seems like it has some momentum behind it, and that it is a coherent product rather than a collection of (individually impressive) tools bound into Excel that, for one reason or another, customers couldn’t use to their full potential. We’ll have to see whether it does become a commercial success or not but I think it has a good chance of doing so now. Excel 2016 also has some welcome improvements, even if it is now the ‘slow track’ for self-service BI; the more users discover Power Pivot and Power Query via Excel 2013 and soon 2016, the more likely it is that they’ll start using the rest of the Power BI stack.

Meanwhile it seems like at last there is at last a serious commitment to improve the on-premises SQL Server BI stack on the part of Microsoft. Some time ago I wrote a post on why corporate BI and self-service BI are both necessary and I still stand by what I said there; it’s also clear that a lot of customers, especially enterprise customers and especially in Europe, are not yet ready to put their most valuable data in the cloud. Microsoft has the chance to be one of the few vendors with great self-service and corporate BI stories, and great on-premises and cloud BI stories. Also, given that today’s SQL Server BI customers are the most likely to become tomorrow’s Power BI customers, keeping them happy in the medium term while Power BI matures should be a priority.

Let’s see where we are this time next year…?