Category Archives: Power BI

DAX error: A function ‘XXXX’ has been used in a True/False expression that is used as a table filter expression. This is not allowed.

Whenever you start trying to use more complicated filters in the CALCULATE or CALCULATETABLE functions in DAX, you may start to get the following error:

A function 'MAX' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

image

The function in single quotes may vary. Instead of MAX, it could be SUM, MIN, AVERAGE or nearly anything. Sometimes, you may not even be using a function and the error will just say CALCULATE is the problem:

A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression. This is not allowed.

image

What causes this error?

The error is caused by using a TRUE/FALSE expression, something that evaluates to TRUE or FALSE, to filter the table in a way that CALCULATE or CALCULATETABLE doesn’t support.  So the error is saying you can’t use a boolean comparison to filter your table except in very specific circumstances.

The following comparisons are not supported:

    1. Comparing to a column to a measure. SalesHeader[TerritoryID] = [LargestTerritory]
    2. Comparing a column to a an aggregate value. SalesHeader[TerritoryID] = MAX(TerritoryID[TerritoryID]])
    3. Comparing a column to a What-If parameter. SalesHeader[TerritoryID] =

TerritoryParameter[TerritoryParameter Value]

In fact, you only have three options if you want to filter a column in a CALCULATE/CALCULATETABLE function:

  1. Compare the column to a static value. SalesHeader[TerritoryID] = 6
  2. Use variables to create a static value. VAR LargestTerritory = MAX(SalesHeader[TerritoryID])
  3. Use a FILTER function instead of a true/false expression. FILTER(SalesHeader, SalesHeader[TerritoryID] = [LargestTerritory])

This is because CALCULATE was designed for safety and performance. Complex row based comparisons can dramatically affect performance. So, in order to do more complex comparisons, you have to take the safety feature off and use the FILTER function.

How do I fix it?

In order to fix the issue, wrap your expression in the FILTER function. To use the FILTER function, you need to pass in the table you want to filter, and then a TRUE/FALSE expression to determine which rows get return. So, let’s say we had the following code:

CALCULATE (
    SUM ( SalesHeader[TotalDue] ),
    SalesHeader[TerritoryID] = [LargestTerritory]
)

to use the FILTER function, we would use this:

CALCULATE (
    SUM ( SalesHeader[TotalDue] ),
    FILTER ( ALL ( SalesHeader[TerritoryID] ), SalesHeader[TerritoryID] =    [LargestTerritory] )
)

The ALL function isn’t strictly necessary, but normally when we filter a single column in a CALCULATE function, it will undo any existing filters on that column. We use ALL here to replicate that behavior. In order to understand the specifics better, check out this article at sqlbi.com

Want to learn more about DAX? Check out my free learning path, or my paid Pluralsight course where I cover CALCULATE, FILTER, ALL and more in how to use DAX.

Parameters not yet supported in Power BI Aggregations

At the time of this writing, Power BI Aggregations are still in preview and actively being worked on.  Once they leave preview, I expect this issue will either be fixed, or the limitations will be specified in the documentation, just like with DirectQuery in general.

Currently whenever I try to use a what-if parameter or a disconnected parameter table, Power BI Aggregations don’t work as intended, instead it reverts to Direct Query. Which means if I need to use a parameter of some sort, I can’t get the benefit of using aggregations.

UPDATE: This issue seems to depend on where they are being used. Reza Rad identified that the issue does not occur in an if statement.

UPDATE 2: According to Microsoft, this is intended behavior because the parameters aren’t in the pre-aggregations or the mappings. I’ve created a uservoice ticket for this.

Setup

To reproduce this issue, I’ve made an extremely simple data model based AdventureWorks2014 data. There are 4 tables involved with no direct relationships:

  1. SalesHeader, which is my fact table, stored in directquery mode.
  2. SalesHeaderAgg, which is my aggregation table, stored in import mode.
  3. TerritoryParameter, which is a What If Parameter, generated with DAX
  4. Territory, which is a disconnected table, stored in dual mode.

image

I’ve mapped all the columns from my aggregations table to my detail table. In theory, all DAX queries that don’t require a count on CustomerID or TerritoryID, should hit the aggregation table.

image

To start with, I have a table summing TotalDue by Customer.

image

I’ve connected profiler to the SSAS instance that Power BI Desktop runs in the background. This allows us to see what is bring run behind the scenes and if it is hitting the aggregation table.

In this case, Power BI Desktop is doing a TOPN:

EVALUATE
TOPN (
502,
SUMMARIZECOLUMNS (
ROLLUPADDISSUBTOTAL ( ‘SalesHeader'[CustomerID], “IsGrandTotalRowTotal” ),
“SumTotalDue”, CALCULATE ( SUM ( ‘SalesHeader'[TotalDue] ) )
),
[IsGrandTotalRowTotal], 0,
‘SalesHeader'[CustomerID], 1
)
ORDER BY
[IsGrandTotalRowTotal] DESC,
‘SalesHeader'[CustomerID]

And looking at the events, we can see a successful query rewrite, with no DirectQuery events. everything looks good.

image

The problem

Instead of using an implicit measure, let’s use a explicit measure, with a filter based on a parameter field:

Param Total =
CALCULATE (
SUM ( SalesHeader[TotalDue] ),
FILTER (
SalesHeader,
SalesHeader[TerritoryID] = TerritoryParameter[TerritoryParameter Value]
)
)

And at first, everything looks fine. No DirectQuery calls.

image

But, if I select one of the parameter values using a slicer, now it switches to using DirectQuery.

image

So what’s the difference? Well in the second DAX query, it’s applying the filter via TREATAS

image

What if I use an actual table in dual storage mode and just take the MAX instead?

Param Total =
CALCULATE (
SUM ( SalesHeader[TotalDue] ),
FILTER (
SalesHeader,
SalesHeader[TerritoryID] = MAX ( Territory[TerritoryID] )
)
)

Well, I get the same exact DAX pattern and the same result.

Conclusion

Ultimately, this is one of the tradeoffs of using preview functionality. I’m working with the customer to get a ticket escalated with Microsoft. Ultimately, it may just be an intended limitation of the technology. I hope not, though, because aggregations provide for huge performance improvements with minimal effort.

That being said, if anyone has any ideas, I’m all ears! Below is my proof of concept.

ParametersDemo

Create a Power Query custom data connector in 10 minutes

Getting set up

When I heard about custom data connectors for Power Query, I had assumed there would be a lot of work involved. While there is definitely quite a bit of work in implementing advanced features like query folding,  creating your very first connector is simple.

So, first you need Visual studio installed and the Power Query SDK installed as well. Once you do that, you will see Power Query as an option when creating a new project. Visual studio will also have support for .pq or Power Query files.

image

Once you create a new data connector project, you are presented with two main Power Query files. The first one, is simply a test query you can run on demand to test your connector.

image

The other file is your data connector. It has a bit of boilerplate to specify the types of credentials it accepts and publishing details such as beta status. Otherwise there is just a little bit of code defining the actual functionality. In this case we are defining the Contents function, which acts as a hello world:

image

If we run it as is, our test query will be run and we’ll see the results in a testing program.

image

Adding a function

So now, what if we want to add some more functionality? Say maybe a function to square numbers. First, we’ll add a SquareNumbers.Squared function to the main file:

shared SquareNumbers.Squared = (x as number) =>
let
y = x * x
in
y;

Then we update the sample query to call out function:

let
result = SquareNumbers.Square(7)
in
result

And it works as expected:

image

Exporting the connector

Once you have the connector working the way that you want,  run a release build in visual studio. This will create a .mez in the bin/Release folder of your solution. Copy that file to the [Documents]\Power BI Desktop\Custom Connectors folder. You will likely have to create that folder.

Whenever you open Power BI Desktop, it will recognize the connector but won’t let you use it because of security settings.

image

To get around this, go into the options for Power BI Desktop and then security. Under security, select “Allow any extension to load without validation or warning.” Then Restart Power BI Desktop.

image

Now we can see it is available in our list of connectors.

image

By default it will call the Contents function:

image

But we can easily modify the M code to call our squared function as well.

image

Which will give us the output we expect.

image

What next?

If you are interested in going deeper with Custom Data Connectors, such as adding a navigation view or  query folding, check out the TripPin tutorials.

New Power BI skill assessment on Pluralsight

I’m quite excited to announce the new Power BI skills assessment on Pluralsight is in beta. This assessment is the result of me spending dozens of hours writing questions and some wonderful peer review by Gilbert Quevauvillie. Please do me a favor and take it. It will take 15 minutes and we need your help to calibrate it.

I’ve received feedback that some of the questions are a bit weird and not really core Power BI skills. For example, there are questions about R syntax, deploying to SharePoint, etc. That’s by design. The assessment is self-calibrating as people take it. Once it goes live, those harder, oddball questions will only show up when you’ve correctly answered the easier core questions.

The reason we need your help is we need to know which questions are easy, which questions are hard, which questions are correlated, which questions are too guessable. I appreciate everyone’s help in getting the assessment to be ready to go live.

Why is Power BI Free?

Something that some people search for is the question “Why is Power BI Free?”. Power BI is free because it benefits Microsoft to have an easy on-ramp to Power BI and to attract as large an audience as possible. It is in their financial interest.

If you are wondering what the catch is, the catch is that the free version of Power BI has very limited sharing capabilities, among other features.

In this post, I’m going to cover some reasons why Microsoft would make Power BI free. But before we can elaborate on all of that, we need to clarify what we mean by “free”. Read my post from last week for more details.

So why is it free?

So why would it behoove Microsoft to provide a limited free version of Power BI? Some ideas come to mind:

  • People can learn for free. This is important since Microsoft is aiming for a broader audience instead of a deeper one. The main target audience for Power BI is the everyday business user, not BI developers.
  • People are skeptical. It can be hard to convince a business to make a large investment in a BI product. By having a free version, a small group of people can do a pilot project without spending any money.
  • Razors-and-blades sales model. Companies will often sell products at a loss or give them away for free, if there is a paid compliment needed to take advantage of that product. Think about how cheap printers are, but how expensive ink is, for example.
  • SaaS is where they make their money. Related to the previous item, Microsoft makes a lot of their money these days from subscriptions. It used to be that they primarily sold software as standalone packages. But in the last few years, they are making more and more money from  Saas like Office 365, or cloud computing like Azure. Power BI fits neatly into that space.
  • Free dashboards are good marketing. Some people will make really cool and innovative dashboards and then share them publicly. This doesn’t detract from Microsoft’s business model at all. Free users are free marketing.

Overall, Power BI is free because so much of the real value comes from the enterprise collaboration and sharing. You can make beautiful visuals with a lot of tools, but few compare to the IT Governance story that Microsoft has.

Is Power BI free?

Power BI Desktop, the authoring tool, is completely free to use. Users can also create free accounts on the Power BI service, with a number of restrictions. In short, Power BI  is free to get started, but if you want to do any serious professional work you are going to have to pay for a license.

Some pieces of Power BI are free and some aren’t. Parts that are available for free:

  • Power BI Desktop
  • Power BI Service (with limitations)
  • Power BI Mobile

Parts that are not available for free:

  • Enterprise sharing and collaboration
  • Power BI Report Server
  • Power BI Premium

Which parts are actually free?

Power BI Desktop

First there is the report authoring tool, known as Power BI Desktop. This tooling is completely free to use.

image_thumb1

(PBIX file credit of Microsoft. Available here.)

You will have to either create an account or deal with some mild nagging about signing up for a mailing list. You can disable that nagging with a registry change.

image

While Power BI Desktop is a great authoring tool, it is a terrible collaboration tool. If you were to live entirely in PBI Desktop, you’d have to pass around PBIX files which is incredibly clunky.

In my opinion, if you are going to look at using an on-premises, self-service tool you are better off using Excel. You still get a lot of the same capabilities with Power Query and Power Pivot, but inside of a tool people understand, and a tool Office 365 can render online.

Power BI Service

The Power BI Service, think powerbi.com, allows for free users. These free users can create reports and upload them, but with a significant number of limitations. The biggest is you only have one way of sharing content to others. Specifically with Publish to Web, which essentially makes your entire report free to the public.

You also only have one way of privately consuming other people’s reports, and that’s if someone places content in Power BI Premium. Otherwise, other users can’t share their reports directly with you. Power BI Free users are truly and island to themselves.

One other thing worth nothing is that you can’t sign up with a personal email. David Eldersveld has a good blog post on the issue. As of this writing, the uservoice request to change this has 2,800 votes.

See here for some more limitations of the free version of Power BI.

Power BI Mobile

Power BI Mobile is a way to consume Power BI Reports on Apple, Android and Windows mobile devices. Here is a picture of Power BI Mobile on my phone.

Screenshot_20180917-111943

Which parts aren’t free?

Enterprise sharing and collaboration

Power BI is, by design, a collaboration tool. It is designed for people to publish and share their reports. If you want to take advantage of content curation using app workspaces, you’ll need to pony up and pay for a Power BI pro license.

If you are doing any real work with Power BI, you are going need to pay for a license for yourself as well as any report consumers.

Power BI Report Server

In addition to Power BI Pro, there is Power BI Report Server, which is the on-premises solution for hosting Power BI Reports. If you decide to go with Power BI Report server instead of making use of the Power BI Service, then you are going to need to pay for SQL Server Enterprise as well as Software Assurance. Alternatively you could pay for Power BI Premium.

Power BI Premium

Power BI premium is an alternate licensing model where you are licensing the content instead of the users. Once you have 500 or more users, it starts to make sense. Until then, the $5,000 per month is pretty pricey.  It has other benefits as well, such as paginated reports and incremental refresh.

Summary

Some parts of Power BI is Free, but once you want to share with others, use more advanced features, or alternate deployment options, you are going to have to start paying.

Power BI Learning Path – Free and Paid Resources

how to keep up with technology. When you are starting out with a technology, it’s just plain hard to get a lay of the land.

So, I thought I’d put together a learning path for Power BI, a technology that changes literally every month. This is a bit of challenge because there are so many moving parts when it comes to Power BI. Accordingly, let’s break down those moving parts into different categories.

image

So, when I think about Power BI, I like to think about the flow of data. First we have the Data prep piece with Power Query, where we clean up dirty data. Next we model the data with DAX. I’ve written before about the difference between Power Query and DAX. They are like peanut butter and jelly and compliment each other well.

Now, if you are a SQL expert, you may not need to worry about Power Query or DAX much. Maybe you do a lot of the work in SQL. But either way, once your data is modeled, you need to visualize it in some way. You need to learn how to create your reports with Power BI Desktop. Once your report is created, you then need to publish it.

Finally, there is what I would call the IT Ops side of Power BI. You have to install an on-premises data Gateway to access local data. You need to license your users. You need to lock down security. All of these things might be outside of what a normal BI developer has to deal with, but are still important pieces. However, unlike the data flow model we talked about, the ops pieces happens at all of the stages of development and deployment.

With that overview in place, let’s get on to the individual sections and the learning paths as a whole.

Getting started with Power BI

When it comes to getting started with Power BI, I have two recommendations. First get your hands dirty, and secondly buy a book. Power BI is in many ways an amalgamation of disparate technologies. It took me a long time to to understand it and it didn’t really click until I took the edX course and did actual labs.

The reason I say to buy a book is this is a technology that is hard to learn piecemeal. When you are starting out you are much better off having a curated tour of things.

Free resources

  • Check out Adam Saxton’s getting started video.
  • Search Youtube for Dashboard in an Hour. This is a standardized presentation that will show you the basics in under an hour.
  • Follow the guided learning. This will walk you through bite sized tasks with Power BI.
  • Take the edX course. It has actual labs where you have to work with data inside of Power BI.
  • Check out the Introducing Microsoft Power BI book from Microsoft Press. It’s a bit dated at this point, but it’s free and is a great start.
  • Check out the Power BI: Rookie to Rockstar book from Reza Rad (b|t). The last update was July 2017, but it’s also very comprehensive and good.

Paid resources

  • Stacia Misner Varga (b|t) has a solid course on Pluralsight. It’s worth a watch.
  • Consider reading the Applied Power BI by Teo Lachev (b|t). It’s a real deep dive which is great, but can be a lot to take in if you are just getting started. A neat feature is that it’s organized by job role.

Learning Power Query and M

When it comes to self-service data preparation, Power Query is THE tool. The way I describe it is as a macro language for manual data manipulations. If you can pay someone minimum wage to do it in Excel, you can automate it in Power Query. Again, check out this post for the differences between Power Query and DAX.

Free Resources

  • Start with the guided learning. This quickly covers the basics
  • Reza Rad has a solid getting started post on Power Query that you can follow along with.
  • Matt Masson has a phenomenal deep dive video on the Power Query formula language, a.k.a M, from a year ago. It really helps elucidate the guiding principals of Power Query and M.
  • Blogs to check out:
    • Imke Feldmann (b|t) regularly has complex functions and interesting transformations on her blog.
    • Ken Puls (b|t) focuses on Excel and along with that, Power Query.
    • Gil Raviv (b|t) often has neat examples of things you can do with Power BI and Power Query.
    • Chris Webb (b|t) regularly dives into the innards of Power Query and what you can do with it.

Paid Resources

  • Ben Howard (b|t) has a Pluralsight course on Power Query. It’s a bit introductory, but great if you are just getting started.
  • Gil Raviv recently (October 2018) released a book on Power Query. What I really like about this book is it has more of a progression style instead of a cookbook kind of feel.
  • Ken Puls and Miguel Escobar (b|t) also have a book on Power query that has a cookbook feel. I found it helpful in learning Power Query, but it’s heavily aimed at excel users.
  • Finally, Chris Webb also has a book on Power Query. He goes into a lot of detail with it. However, the 2014 publish date means it’s starting to get a bit old.

Learning DAX

I always say that DAX is good at two things: aggregating and filtering. You aren’t doing those two things, then DAX is the wrong tool for you. DAX provides a way for you to encapsulate quirky business logic into your data model, so that end users doing have to worry about edge cases and such.

Free Resources

  • Read the DAX Basics article from Microsoft
  • Check out the guided learning on DAX
  • Learn the difference between Calculated columns and Measures in DAX. They can be confusing.
  • Make sure you understand the basics with SUM, CALCULATE and FILTER
  • Understand Row and Filter contexts. They are critical for advanced work in DAX
  • Blogs to check out
    • Matt Allington (b|t) has a blog with Excel right in the name but also writes about all the different parts of Power BI Desktop.
    • Rob Collie (b|t) has a voice all his own. read his blog to learn about DAX and PowerPivot without taking yourself too seriously.
    • Alberto Ferrari (b|t) and Marco Russo (b|t) are THE experts on DAX. Read their blog. Also see their site DAX.guide.
    • Avi Singh (b|t) regularly posts videos on Power BI and will often take live questions.

Paid Resources

Power BI Visuals

The piece of Power BI that is most prominent are they visuals. While it’s incredibly easy to get started, I find this area to be the most difficult. If you are heavily experience in reporting this shouldn’t be too difficult to learn.

Free resources

Paid resources

  • A really interesting book is The Big Book of Dashboards. While it doesn’t mention Power BI, it covers all the ways to highlight data and what really makes a dashboard.

Administering Power BI

Power BI is much more than a reporting tool. It is a reporting infrastructure. This means at some point you may have to learn how to administer it as well.

Free resources

Paid resources

Keeping up with Power BI

One of the big challenges with Power BI is just keeping up. They release to new features each and every month. Here are a few resources to stay on top of things:

Going Deeper

Finally, you may want to go even deeper with things. Here are some final recommendations:

I’m starting a BI newsletter. 5 BI links every week.

I’ve written before about how to keep up with technology. In the post, I describe 3 currencies we can spend to extend out learning: time, focus and actual money. As you get older, you start to get less time and even less focus, but your pay rate goes up. So, every year it becomes more and more important to learn on curation to find just the good stuff.

As part of that I’m starting my own curated mailing list for BI links. Power BI changes on a monthly basis and it’s such a pain to keep up with it. This week is the 3rd week so far.

So what’s the catch? Well, I’ll also be including whatever things I’m up to at the bottom of each email. So if you don’t like me, maybe don’t sign up, hah. Here is this week’s weekly BI 5:

  1. David Eldersveld talks a bit about #MakeoverMonday. This sounds like a great community program and I always find making things pretty to be the hardest part.
  2. Wolfgang Strasser is keeping track of all the November updates for Power BI. I keep seeing memes about this from Microsoft employees, so I’m expecting something big to drop at Pass Summit.
  3. Ginger Grant continues her series on SSAS best practices. I love seeing posts about how to do things right instead of just how to do the basics. Great stuff.
  4. Chris Webb also my conspiracy theories about where Power BI is going. Also keep an eye out for announcements about data flows.
  5. Finally, If you are going to PASS Summit, check out the BI Power Hour. All learning will be accidental.

Sign up to the list today!

 

The many tentacles of Power BI; or, Microsoft’s not-so-secret plan to take over the world.

Three years ago, I started learning about Power BI and I thought, “Man, this is a really crappy replacement for SSRS.” Three years later, that’s still true, it’s a crappy replacement for SSRS. But that’s missing the point. Microsoft’s ambitions are MUCH, MUCH bigger than replacing SSRS.

Let’s review the many tentacles of Power BI; or, Microsoft’s not-so-secret plan to take over the world.

Tentacle 1 – SQL Server

It is unsurprising that Power BI is often compared to SSRS. If you come from a SQL background, there is a good chance that you need to report on the data in your database in some way. While there are a multitude of tools available to do so, often it’s easiest to go with whatever Microsoft recommends. You already paid for it, right?

For many years, that was SSRS. And for half a decade, between 2010 and 2016, not much changed with SSRS. Much like some of the plants at the Phipps conservatory, it wasn’t dead…just dormant.

“I’m Not Dead, I’m Dormant” T-Shirt

So what changed around 2016? A couple of things. First, Microsoft acquired Datazen which would later turn into SSRS mobile reports. Second, Microsoft rebuilt the SSRS rendering engine to take advantage of HTML 5. Third, there was a new kid on the block: Power BI.

If you come from the SQL world, this is all old hat by now. Microsoft is investing a lot of time and money into Power BI, so it’s tempting to think that Power BI is replacing SSRS, but it isn’t. If we look at the traditional BI pyramid, Power BI and SSRS occupy two totally different strata.

image

SSRS is designed for things that need a page number on them. This is generally detail-heavy, pixel perfect kind of work. This kind of operational reporting is descriptive, it tells you facts, but doesn’t add a lot of spin. This is at the base of the pyramid

Once we have the business up and running, we can move from “is” to “ought”. What should we do? Where should we go? This kind of reporting is higher-level, less detail oriented. You are looking at the company as a whole or at multiple years of history. You may drill down deeper, but generally speaking someone else is sweating the small stuff. This kind of reporting is prescriptive and where we find Power BI.

Finally, once we are moving and we are moving in the right direction, we can be even more forward looking and make use of predictive analytics. What’s going to happen in the future? What can the data tell us? This is where tools like Azure Machine learning and R can start to help us.

Power BI and SSRS are aligning

Power BI and SSRS are aligning and getting closer. If Power BI was a drop-in replacement, I wouldn’t expect to see this. I would expect to see Microsoft heavily pushing one over the other and that’s it.

By contrast, look at SSAS tabular and multidimensional modes. Tabular is available in Azure Analysis Services and multidimensional mode isn’t. Yes, I know it’s on the roadmap but it’s definitely not a number one priority. Every time I hear about SSAS, people are talking about tabular mode. In economic terms, DAX and MDX are subsitute goods. Sales of one reduces sales of the other. Think Coke versus Pepsi.

Power BI and SSRS, on the other hand, are complimentary goods. Think peanut butter and jelly. Yes, they are both things you can put on your bread, but they go better together. Yes, I know that seems strange, but look at what’s going on.

Last year, they announced Power BI Report Server, which is basically SSRS server with Power BI rendering on top. This year they announced SSRS rendering in Power BI Premium. Now, whether you are on-premises or in the cloud you use Power BI and SSRS in the same place.

Give it another year, and I bet the alignment is going to get even closer. I bet they will add Power Query as a data input for SSRS, just you wait and see.

Tentacle 2 – CRM, ERP, and business data

Microsoft wants your business data and it wants it badly. Last week I spoke at the Phoenix Power Summit, and I was really out of my element because it was all about business. It was basically 5 conferences in one, focusing mainly on all of the dynamics products (AX, NAV, Great Plains and Dynamics 365).

It was strange walking around the 250 vendors and 4,000 other attendees. Out of everyone there I only knew 2 people. And what those 4,000 people represented was something that I had never fully considered: standing there was Power BI’s core audience. It’s not me and it’s probably not you. It’s Chris in accounting. Let’s meet Chris.

Say hi to Chris

When I first learned about Power BI I was confused. Why did I need Power Query? Why did I need DAX? I already had all this stuff. I had T-SQL and SSIS. And the truth was, those tools weren’t for me, they were for Chris.

So much of this stuff clicked when I came up with the persona of Chris in accounting. This is the kind of person who is great at vlookups, okay at SQL and is stuff in the land of spreadmarts, flat files and Access databases. This person understands the business intricately and just wants to get things done. And many time, she has almost no IT support to get her job done, so she has to make do with the tools she has. For her, Power BI is a life saver.

And walking around me last week was 4000 Chris-es from accounting. This is going to be big.

Microsoft shoots across the bow of Salesforce

It doesn’t take a genius to realize that Microsoft and Salesforce are direct competitors. Both were interested in buying Linkedin, and more recently Microsoft fired a warning shot to Salesforce.

At Ignite, Satya announced the open data initiative along with Adobe and SAP. Guess who isn’t in the picture? That’s right, it’s Salesforce.

Image result for ignite open data initiative

Part of that announcement was them talking about the Common Data Service. When I first heard about CDS months ago, I was again confused. It sounded like some weird semantic layer for the data in Dynamics CRM. Maybe useful if your data lives in Dynamics 365, otherwise who the heck cares.

Oooooh boy was I wrong. Microsoft is aiming for something much, much more ambitious than an awkward pseudo-database layer for people who don’t like SQL. They are aiming for a common shape for all of your business data. They want to want to create a lingua franca for all of your business data, no matter where it is. Especially if it’s hiding in Salesforce.

Now, do I expect them to succeed? I’m not sure. I’ve learned the hard way that every business is a unique snowflake, even two business in exact same industry. But if anyone can do it, Microsoft has a good shot. They’ve been buying up CRM / ERP solutions for decades.

Tentacle 3 – Data Science, Machine learning and Azure

Last week I had the pleasure of attending the Phoenix SQL user group and listen to Matthew Roche talk about Power BI Dataflows. When I first heard about dataflows, like everything else in this space, I didn’t quite get it. It sound like Power Query, but for the Power BI Service directly? Weird.

The way Matthew described it made a lot of sense. Dataflows represent an intermediate layer for your data, much like a traditional data warehouse. This is after all of the boring ETL bits, but before all of the sexy BI semantic layer bits. This is, metaphorically, chopping all your broccoli and putting it into plastic bags. The end user still has to decide what the final product looks like.

And guess what shape they let you put your data into? That’s right, the common data service. Microsoft wants to act like as a clearing house for your data. They want to act as a middle man for all of your data sources and all of your data consumption.

Bigger ambitions

My favorite slide from Matt’s presentation is this one. (Courtesy of James Serra).

One weird thing about dataflows is that it’s saving the results to flat files in a data lake. Now, it was about this point that the DBA in me started freaking out. Why are we storing things in flat files? Flat file databases are older than I am!

(Sidenote: SQL Server 1.0 is about 6 months younger than I am.)

Well apparently, that’s standard fare for data lakes and how they scale. But the part that excites me is all the avenues that opens up. Because you can Bring Your Own Storage Account and gain access to all of that delicious data.

This is some seriously cool stuff.

Summary

Power BI remains a poor replacement for SSRS. But it’s a fine compliment to SSRS and is a growing way to report on the data living in you SQL Server.

For business users, especially Dynamics 365 users, it represents a way to get at and visualize their data. Power BI, PowerApps and Flow represent a huge step forward for business users, especially those stuck living in spreadsheets and access databases.

Finally, Microsoft wants to Power BI and the Common Data Service to be a central hub for all of your business data and analytics. This is going to expand into providing easy ways to take your business data and pipe it into Azure Machine Learning or Azure Databricks.

New Course: Deploying and Publishing Power BI Reports

My third Pluralsight course is out now, and it covers all the myriad ways of deploying Power BI:

  1. Manual sharing
  2. App workspaces
  3. Content packs
  4. Publish to web
  5. Office 365 embedding
  6. Power BI Premium
  7. Power BI Embedded

It can be overwhelming all the different ways of deploying Power BI, but in this course I walk you through the smallest, self-service options all the way to the large, scalable options.

image

I recently quit my job so that I could make more courses, so if you like what I do, please go watch it. You are supporting my family by doing so!