The effect of Summarize by Default on a Tabular Model

Another one of my experiences recently was this strange looking query generated off my Power BI reports. These reports were running off live a live connection to a tabular model on Analysis Services. And of course these queries at up my CPU, leaving it at 100% for quite a long while, with the reports not responding. One thing about the reports were that they had quite a number of parameters (which of course were a client requirement which we couldn’t do much about).

Here’s just a tip of the ice berg sample of the huge query that is generated:

Big Query

The data model was quite a large one with 30+ measures, and 10+ dimensions, and one thing that we had missed out was that all numeric fields that were not used as measures such as the Year, MonthNumber and WeekNumber fields of the Calendar dimension, and EmployeeAge field of the Employee dimension had their Summarize By property set to Default. But this was not indicated in any way in Power BI especially in the Fields section, where usually fields that are summarized show up with a Σ to indicate that the field is summarized when the data model is a Power BI data model.

The phenomenon is explained in detail in Pinal’s SQLAuthority. Make sure you take a look. Happy New Year!


Report and Dashboard Usage Metrics in Power BI

One thing we tend to overlook as technical folk working in the business intelligence arena is the importance of analytics about analytics. However, to business folk, it is quite important that they know how valuable their reports and dashboards are to others, if not what’s the purpose in them doing what they do if nothing is being used. The same applies to reports and dashboards in Power BI.

Power BI Usage Metrics Location

Power BI offers something known as Usage Metrics for each report when you access reports or dashboards off the Power BI portal. You can find it above the top left of each report, which when clicked on gives you a summary of how your report or dashboard has been used.

Power BI Usage Metrics

It includes quite a few useful insights as to the main report’s usage. However, as humans can never be satisfied, the business user naturally will ask for more analysis on top of what they see on the summary. For instance, “I need to see at-a-glance which user looks at which page of the report more often”. That’s when you wished you had a way of providing users with customization options of report usage metrics. But there is actually a way.

Each app workspace gets its own report usage metrics data set, it’s just that you don’t see it when you are in the portal. In order to access it (at least for now) you need to use Power BI Desktop. When you open Power BI Desktop, you need to sign-in with the appropriate login, and then choose Power BI service from Get Data menu item. You then get listed with a set of app workspaces; within each you would find a list of all the datasets that were every published to each of the workspaces. Additionally, Power BI will also give you two more datasets: Report Usage Metrics Model and Dashboard Usage Metrics Model. However, these data models will only show up if you had attempted to view usage metrics at least once on one of the reports of the app workspace. The moment you click on the Usage Metrics link and the following happens; that is when the model is created:

Usage Metrics Generating

Usage Metrics Generated

And now, it’s your playground, a whole new data model that gives you analytics on the produce of another data model.

Power BI service


Keeping in touch with Technology

This is not going to be a post about technology nor about a technical solution to a problem, rather something equally important that I feel has helped my career quite a lot. Inspiration for this post comes from a question that I received from an attendee at yesterday’s SLDC meetup asking how I keep myself up to date with technology. My expertise lies in business intelligence and databases especially on the Microsoft side of things, and so was this particular attendee’s. However, he had been, or rather his job had taken him on various stints on various technologies for a period of time, and he was now getting back into Microsoft technologies, but was concerned about the amount of learning that has to be caught up with. So back to the question, “how do you keep yourself updated?” and I know that this is a question that lingers in the minds of many. My answer to him was: “Nights”. But I think the right answer should have been: “Passion”.

Passion (

In technology, I feel, one should have passion. Not the type of “passion” you see on every other CV describing the CV’s owner; but real, undying love for the type of technology you work with. “But I do have passion” some retort when I ask for demonstrable passion from interviewees, “I came up with a great idea for a dashboard for my client, and built this really awesome dashboard that won my organization a lot of praise” — That’s not passion, that’s just doing your job. That’s what you are supposed to do. “But I really really love to work with SQL Server” they’d say; then show me something that showcases it, I ask, something beyond just doing your job. Mostly I don’t get an answer. Well, if you have the passion you need to go beyond.

Let’s bring in an analogy. Marriage. You could marry either because you decide you need to settle down, have a husband/wife to take care of you, and start a family with; or because you are really, madly, deeply in love with that person you are in love with, and you want to spend your life with that person doing every crazy, stupid thing you ever wanted to. Passion for technology, for me, is like the latter option. You need to go beyond the great job you do at work, and explore the technology more; experiment → try new things → fail → fail again → read → learn → succeed → experiment more → teach → write → evangelize. But all this in your own time: A couple of hours in the night, an evening on a weekend, on game night, on a Friday night when you should be drinking the wee hours away, one day of your vacation… It takes a little bit of sacrifice. But it does pay off.

Once you go through a process such as this, you will have a revelation; people start calling you an expert, you see opportunities in your career that you never saw before, you get opportunities that you never dreamed of before. You would see things paying off — It has for me. Several times.

All those people who I know, who are passionate about technology, do the things they do because it comes from within, either because they are hardwired like that, or they have come to realize how much it is important and cultivate the habits. If you were wondering, what these people do; well, here are a few things…

Read. That’s the starting point. Read documentation, blogs, white papers to understand more. Then you can start experimenting; trying out tutorials, or scenarios of your own. And don’t just stop there; put your code into a repository such as Github. You can easily pull these out to showcase at an interview. Write about your experiments, take notes and start a blog to share your learning with the world (and also to showcase at interviews). Talk about your research and experiments; Organize a tech talk for your team, or your organization, or be a little bold and speak at a local community event — you will eventually become known in the technology community, and the technology will automatically get associated with you. The more you write about something and the more you speak about something; the more you understand it and the more that others associate you to it.

So, go ahead and work on it. Do it now. It’s hard at first. Persevere and it will pay off. I promise.

Unexpected Exception in Power BI connected to SQL Server 2016 Analysis Services

One interesting issue that showed its ugly head in my recent project was an unexpected exception that popped up on all visuals on a Power BI report. How it happened was quite strange; one moment all the reports were working fine. The next moment when a report visual was modified and deployed to Power BI, all the visuals start turning into a grey box with a cross, with a “See details” link. The details weren’t at all helpful. Just, “An unexpected exception occurred.”:

Clicking on the “Copy Details” link and pasting on a text editor, and quickly scanning through showed this:

And to make matters worse, all subsequent report loads, including that of other reports, puts the reports into hang, until Analysis Services is restarted. The cycle starts again when the offending report is opened again.

A little digging around pointed at a bug on SQL Server 2016 SP1 Analysis Services. I’m not sure if the problem occurs on a version prior to SP1, however there is a fix: SP1 CU5 (KB Article: KB4037354). Once installed the problem disappears. I tested the issue out on SQL Server 2017 Analysis Services, and this problem does not occur on SQL Server 2017.

Speeding up ETL data cleansing

While I was at the PASS Summit this year, I was sitting in Paul Turleys’s (b|t) session on creating integrated BI solutions. Paul was talking about how ETL takes up most of a BI solution’s development lifetime, regardless of how you planned for it. That’s when someone from the audience asked the question: “…methodology or tool in the MS stack to shorten the time taken for ETL?”. Paul’s answer was: “There’s no silver bullet, But I’m going to say it’s Power Query”. This got me thinking, since for a few weeks, my team had been struggling to get data cleansed on the project that they were working on.

Despite having a data profiling document, which documented how source data mapped to the data model, along with the rules that needed to be applied to these data, such as nullability, constraints, valid domain values and so forth, bad data continued to come in. Apart from bad data, there were scenarios that were not identified nor thought of before that were coming in with the data. Though we do usually expect some amount of bad data or anomalies, which can be reported on, this was pretty bad. We needed to have a data set that would not fail the ETL so that it can be at least tested for all business logic that we had identified as requirements.

The issue was that the team had to build the Integration Services packages first, before running it to finally find out the various data problems that kept spitting out. So, it took a while for the developer to identify the problem, realize the data issue, report it to the client, have it fixed by the client, (sometimes do a fix on the package) and repeat. There were times when I had to jump in to help figure out what the data problems were. But because getting onto a tool (specially non-Microsoft), connecting to the source, and then writing SQL queries to retrieve and check the data was quite a slow process, I preferred using Power Query.

Power BI is a self-service tool; it can connect to many different types of data source, the best thing about it is the ease of pulling in data, building joins, filtering, sorting and perform all the data manipulations that you want do is made super easy. And you could just save it all in one just file, and use it the next time you want it. So I get it (or at least formed an impression in my head), and related to it when Paul said “Power Query” as the tool to speed up the life time of ETL development.

Explaining how you could go about it is another post for the future, but I am presenting it at the Sri Lankan Data Community Meetup on Nov 29th in Colombo,if you are interested 😉