Monthly Archives: March 2019

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

Should you get certified?

There was a long discussion on Twitter yesterday about whether you should get certifications or not. While the answers were all over the place, there were a number of common refrains. The general consensus was that experience is always better when possible, but that a certification is better than nothing.

This being a complex topic, I thought I’d lay out the various factors to give a more comprehensive answer than you can easily fit in a tweet.

So the first two questions we need to answer are “Why do certs exist?” and “Why do people take them?”. Without these, we can’t give a good answer to whether you should take them. Certifications often exist for reasons that have nothing to do with your personal best interest. It is necessary to understand that fact.

Why do certs exist?

A vendor like Microsoft does not create a certification as an act of charity. Certifications are an expensive thing to create. I wrote all of the questions for the Pluralsight Power BI skill assessment and it was grueling process. I was asked to write at different level of understanding and to try to have plausible distractors as wrong answers.

While they do charge money to take a certification exam, I suspect Pearson takes most of that money and Microsoft likely breaks even, if anything. Oracle, on the other hand, charges quite a bit for their certifications. So we have to ask, why would Microsoft or another vendor create a certification? These driving factors will shape the content inside a certification, so it is important. A few reasons come to mind:

  1. Marketing
  2. Business/partner relations
  3. Technician adoption
  4. Market driver

Now it’s worth saying that these reasons apply specifically to a third party vendor. Platform neutral companies like CompTIA are trying to act as an accreditation body and have different motivations.

Marketing

Certifications are a marketing tool. They are a way to highlight new features in a a new version of SQL Server, for example. That highlighting is also done out of necessity, so that people can’t auto-pass the latest version of a certification.

Additionally, having certifications looks good on a company and is an indicator that the technology is fully-baked. I remember years ago looking into Vertica, a niche columnar database engine way before the time of Power Pivot. I remember looking into getting certified in the technology and thinking “Okay, they are pretty niche, but they have a certification path, so there must be something here.”

Same thing could apply to Microsoft and newer technologies like Power BI. It took a number of years for Microsoft to come out with a certification for that technology, in part because it changes so quickly. I could easily see an IT manager that is considering adopting Power BI using the existence of certifications as a sign that a) there is a path forward and b) Microsoft has made an investment and is unlikely to dump the technology.

Business/partner relations

Businesses need a way to assess the skill level of job applicants as well as growing employees. Certifications, along with college accreditations and years of experience are ways to measure someone’s skill level. Now, certifications aren’t necessarily a good way of measuring skill level. Often they measure memorization skills, certifications can be cheated, and sometimes certifications are out of date with the real world. But they are quick and easy from a business perspective.

At my last job, if I recall correctly, to get to level 2 on the help desk you had to pass the CompTIA A+ exam. This served as a clear bar of entry, and because turnover was so high on the helpdesk, reduced the amount of work assessing the skill of people who were likely to be gone in a year anyway.

Microsoft has a similar problem with Microsoft partners. Microsoft wants as many partners as possible, as long as they are competent and credible. So, how does Microsoft give a partner their stamp of approval without going through and an expensive auditing and assessment process. They use 3 criteria:

  1. Social proof. To become a Microsoft partner, you need 3 customers that will vouch for you.
  2. Certifications. You are expected to have 1-2 people with certain Microsoft certifications.
  3. Capital. You need to pay a certain fee to become a Microsoft partner.

Technician adoption

It is in Microsoft’s best interest for there to be a clear path forward for people to learn their technologies in order to increase technician adoption. If they want technicians to start using Azure for example, there needs to be a smooth path from remembering to understanding to application.

Certifications represent a small piece of this, along with training materials, Microsoft conferences, evangelists and so on. In theory, certifications represent a stepping stone to becoming an expert in a new technology.

Market Driver

Did you that Microsoft desperately wants you to learn PowerShell? They likely see it as a key differentiator and a way for them to stay relevant in the age of devops and infrastructure-as-code. So, let’s say that you are an executive at Microsoft and you want more people to use PowerShell, how do you accomplish this?

Well, one option is to add it as a requirement to many of your IT Ops certifications. And that’s what Microsoft has done. If a vendor has a large enough base of people taking exams, they can drive what people have to learn via the certification requirements.

Why do people take certification exams?

There are two reasons people take certifications:

  1. Accreditation
  2. Learning a technology

The important question is are they good for either of those?

Accreditation

In terms of accreditation, certifications are a mixed bag and can even be a negative indicator. By definition, the things that are easiest to write for standardized tests for fall near the bottom of Blooms Taxonomy. And so despite a decent variety in the types of questions Microsoft uses, test are naturally going to cater more toward people who are good at book learning and memorization.

image

Another issue is that is often easy to cheat on a certification. Testing centers do a good job of watching your conduct and verifying your identity. So in-person fraud isn’t an issue. However, it’s pretty easy to find dumps of the exact questions used on an exam. I once had a co-worker that had accidently used a dump to study and was asking the team about the right answer on a question. I pointed out to him that that was a verbatim question from the exam I had just taken.

Microsoft is making strides to address these two issues by introducing labs into their new role-based certifications. This will address the roteness and the cheating.

Compared to what?

An important piece of this is compared to what. The general consensus was that real, hands-on experience is almost always better than certifications. But for many new to the field, especially if you don’t have a bachelor’s degree it can be a catch-22. You need experience to get a job and you need a job to get experience. Certifications can be a way to break this paradox, along with internships, bootcamps, MOOCs, home labs and side projects.

Another issue is if you are settled in a job and want to pivot in another area. For example, let’s say you are a DBA that wants to pivot into Machine Learning. Part of the challenge is you are likely not gaining direct experience in your current position. Getting a certification in machine learning could help show that you have enough knowledge to make that transistion.

If you have the option to do an internship or a real project, I would recommend that over getting a certification. But lacking that, a certification is a decent option and much better than nothing. Just be aware that the content can be skewed and not always in line with the latest best practices.

Who is looking at them?

Another thing to consider is who is going to be looking at the fact that you have a certification? Like I said, they can be a bit of a mixed bag and I believe that IT managers understand that fact.  However, in many organizations, it isn’t IT who is the first pass but HR. HR, by not being domain experts, are more likely to lean on easy metrics and more likely to value certifications. In a pile of resumes, a certification could be what gets you past the first filter.

Learning Path

The other reason people get certifications is as a way of learning. The general opinion on this is decidedly negative.  Much of this is because of the skew we talked about towards new features and memorization. An ideal certification exam would give you a real problem and force you to solve it with the tooling. The second half of the Microsoft Certified Master was like this and was very well respected. It was also expensive and cost thousands and thousands of dollars to take.

Additionally, if you are just looking to learn, there is a vast set of free and cheap resources to learn. Often times you would be much better off with a technical book and a home lab, just banging away at real-world tasks.

But that being said, I have a much more positive opinion of certification exams. I think a lot about a quote by Donald Rumsefeld:

Reports that say that something hasn’t happened are always interesting to me, because as we know, there are known knowns; there are things we know we know. We also know there are known unknowns; that is to say we know there are some things we do not know.
But there are also unknown unknowns—the ones we don’t know we don’t know. And if one looks throughout the history of our country and other free countries, it is the latter category that tend to be the difficult ones.

When you are just first starting with a technology, it is utterly overwhelming how many moving pieces there are. I find certification invaluable in getting a lay of the land and addressing those unknown unknowns. Certifications can be a way of getting past impostor syndrome and feeling like you understand a technology.

Are certifications skewed and sometimes wrong? Yes, absolutely. But they are also generally comprehensive and touch upon a wide swatch of subjects. I think a lot of when I got my second certification, specifically on SQL Administration. I remember reading about high availability and thinking “I don’t need to know this, we have like 2 SQL servers.” Which was true, until I accidently became a consultant and was configuring mirroring for customers.

Summary

Certifications are a flawed tool, often skewed toward certain subjects, outcomes and types of learning. But despite all of their flaws, they can be a way to get your foot in the door somewhere, or get a broader understanding of a technology. They shouldn’t be your first choice, but they shouldn’t be ignored either.