Tag Archives: Best Practices

Dashboard Design, dataviz, Power BI, Power BI Desktop Designer

5 Tips for #PowerBI

16 Comments

After a couple months of fun with Power BI, I’ve picked up a few little tricks along the way that have helped me to be able to create some pretty cool data visualizations and dashboard reports. Here are five Power BI tips and tricks that you may find useful as you begin creating dashboards for your organization.

New to Power BI? Start here to get acquainted!

1) Use a pie chart or donut chart as a KPI

One of the ways we can create a KPI visualization is to use a pie or donut chart visualization, which you can see here.

image

In the chart above, I create a KPI to quickly display which tight ends score more than, less than or equal to the average number of touchdowns all tight ends scored last year allowing me to quickly identify tight ends that score more TDs than average.

Here is my calculated column to create the KPI value for you to have as an example:

TD KPI = if(int('TE Stats'[TD])>INT('TE Stats'[Avg TE TDs]),"1",IF(int('TE Stats'[TD])<int('TE Stats'[Avg TE TDs]),"-1","0"))

 

Then configure a pie chart as follows.

First, I place my KPI calculated column as the Legend and as the Values.

Power BI KPI pie chart

Then I hid all the labels and configured the colors to display red (-1), yellow(0) or green(1) depending on the value of the KPI.

Power BI KPI pie chart

Now when I use a slicer to select a player, my pie chart acts as a stoplight KPI. Cool!

Power BI KPI

2) Use a chart as a slicer

I’ve previously blogged this tip before, but this one is too nifty to not share again, in my humble opinion. One of the advantages to using a visualization like a funnel chart as a slicer is you gain the ability to single-select a filter, which is something the current slicer lacks. Check out this post to learn more about leveraging Power BI’s natural cross filtering to create some pretty cool slicers.

image

3) Create a spark line with a line chart

A nice way to create a small trend line, also called a spark line, is to use a line chart visualization which you can see below.

Power BI sparkline line chart

This trick is pretty easy. Just create a normal line chart visualization, hide all the labels and shrink the chart down to the desired size.

4) Use a scatter graph and matrix to create a calendar chart with day labels

Last week you may have seen my blog post on how to use a scatter graph to create a calendar chart. One of the ways you can improve the calendar scatter graph is to create the visualization along side a matrix visualization, as seen below.

image

You can use the matrix to display the names of the week below each day in the calendar and then also optionally display the totals by day.

5) Right align the y-axis on a bar chart to prevent the labels from hiding

The bar chart is a great visualization type to use in your Power BI dashboard because its so easy to differentiate the differences between the categories. But one of the problems with the visualization in Power BI is that sometimes its hard to see the categories on the y-axis if the chart is too small. See the image below to see an example the issue I’m talking about.

image

One way you can work around this is to right-align the y-axis. This will cause the full value of the y-axis categories to always be displayed in all their glory albeit on the right side.

image

You just have to live with the category labels on the right side of the bar chart.

Resources

Need more Power BI tips? Check out these tips:

Here’s three Power BI best practices to follow.

Here are the new visualization types in Power BI.

Converting Power Pivot models to Power BI is now a thing!

Dashboard Design, Power BI, Power BI Desktop Designer, PowerPivot

Three Best Practices for #PowerBI

2 Comments

Since the release of Power BI Desktop this past week, I’ve been really spending my extra time digging into the application focusing on learning and experimenting as much as I can. When my wife has been watching Law and Order: SVU reruns at night after the rug rats are in bed, I’ve been right there next to her designing Power BI dashboards like the total data nerd that I am. When my kids have been taking their naps during the weekend, I’ve been writing calculations in the model for my test dashboards. Or when I’ve been riding in the car back and forth to work I’ve been thinking of new things to do with Power BI Desktop.

Since I’ve been spending a decent amount of time with Power BI Desktop, I thought I’d take a moment to share three things to know and remember when designing your Power BI models and dashboards that I think will help you make the most of this tool and be effective at providing the data your business needs to succeed.

1. Optimize your Power BI Semantic Model

It probably hasn’t taken you long to figure this one out if you’ve built Power Pivot/Tabular models or at least it won’t when you do start developing Power BI dashboards. The visualizations in Power BI and Power View are heavily meta-data driven which means that column names, table or query names, formatting and more are surfaced to the user in the dashboard. So if you using a really whacky naming convention in your data warehouse for your tables like “dim_Product_scd2_v2” and the column names aren’t much better, these naming conventions are going to be shown to the users in the report visualizations and field list.

For example, take a look at the following report.

Power BI Dashboard without formatting

Notice anything wonky about it? Check the field names, report titles and number formatting. Not very pretty, is it? Now take a look at this report.

Power BI Dashboard with formatting

See the difference a little cleaned up metadata makes? All I did was spend a few minutes giving the fields user-friendly name and formatting the data types. This obviously makes a huge difference in the way the dashboard appears to the users. By the way, I should get into the movie production business. 😉

My point is that the names of columns, formatting, data types, data categories and relationships are all super important to creating clean, meaningful and user friendly dashboards. The importance of a well-defined semantic model cannot be understated in my opinion. A good rule of thumb is to spend 80% to 90% of your time on the data model (besides, designing the reports is the easy part).

I’d also like the mention the importance of the relationships between the objects in the semantic model. Chance are you will have a small group of power users that will want to design their own dashboards to meet their job’s requirements and that’s one of the beauties of Power BI. But when users began developing reports, they may query your model in unexpected ways that will generate unexpected behaviors and results. I only want to mention this because the relationships between the objects in the model will impact the results your users will see in their reports. Double check your relationships and ensure that they are correct, especially after you add new objects to the model since the Power BI Desktop will sometimes make an incorrect guess at creating the relationship.

2. Choose the Right Visualizations

The best dashboards are those that tell a clear story within seconds. Your data should tell a story that is easy to read and can communicate the tale of the data to the users without a lot of extra work on their part. If your users have to look at the report for a long time in an attempt to decipher the visualizations plastered across their screen, chances are they won’t want to use your dashboard.

Let’s look at two different charts that I think will illustrate my point on the importance of choosing the right visualization for the story. The chart below shows a comparison of Domestic Sales and International Sales for different movie genres. If the purpose of this chart is to determine from which market most of the money comes from for the various film genres, then this chart isn’t doing that great of a job because we can’t clearly see the difference between the markets for Westerns.

Power BI line chart

Is there a better way to tell the data’s story? What about the pie/donut chart?

Power BI donut chart

Goodness, no. Stay away from pie and donut charts. The problem with pie/donut charts is that even with only a few categories it can be very difficult to compare the slices in the pie. And if the purpose of our dashboard is for the users to quickly gain insights into the successes and failures of the business, I recommend you stay away from the pie/donut charts.

Power BI clustered bar chart

Now that’s what I’m talking about! With a clustered bar chart, we can clearly see from which markets most of the money comes from the different genres. This is a much better visualization choice for the data. We don’t have to stare and squint in order to determine the differences between the bars.

Visualization choice is critical with designing an effective and useful dashboard, so always make sure you choose the best visualization for the job.

3. Remember the User!

We as developers can oftentimes find ourselves lost in the minutia of data processing times, ETL performance, writing code, documenting the solution and all the other things that go along with designing and building a business intelligence solution. In the midst of all that awesome and glorious development work, it can be easy to forget that the whole purpose of this solution is to make the user’s job easier, faster, better, etc.

I only mention this because too many times I’ve encountered solutions that did not make the user’s job easier. Users are crafty and resourceful people. They’re (mostly) good at their job and will find a way to do their job without having to use your crappy dashboards and reports that are confusing and difficult to use. And once you start down the path of having your users work around your solution instead of with your solution, your solution has failed because at that point its not a solution; It’s an impediment.

Meet with the users as frequently as necessary to constantly gather feedback. During the requirements gathering phase its important to ask lots of questions especially if you’re unfamiliar with the data. And once its time to start designing reports, you may meet with the users even as frequently as daily since this will be the user’s primary way to interact with your solution. I’ve been on projects where my team and I worked in a conference room with a few power users. This was excellent as we were able to get immediate feedback on any reports developed and make the required changes as desired.

Wrap Up

So in a nutshell, here are my three best practices for designing and building a killer Power BI reporting solution:

  1. Optimize the data-model by doing the following:
    1. Set data types correctly
    2. Apply user-friendly formatting to the data including explicit measures.
    3. Rename fields, measures, and tables with user-friendly naming conventions.
    4. Validate relationships between tables are created correctly.
  2. Use the right visualization that communicates the story of the data as clearly as possible.
  3. Remember the user and their experience with your solution! If the user likes to use your solution then its a success!

 

More Resources

Here’s a few more Power BI related resources you may find useful:

Check out the new visualization types in the latest release of Power BI

Learn about Power BI Desktop in this video walkthrough
Learn Power BI Desktop with Dustin Ryan

Feedback?

So what do you think? What best practices did I leave out that you thought I should have included in this list? Leave a comment down below and let me know! And as always, thanks for reading. 🙂

Performance Tuning, SSAS

Three SSAS Best Practices to Follow

Image 1 Comment

There is a lot of work that goes into performance tuning a SQL Server Analysis Services solution for a client. And even though there may be many steps involved in identifying performance and management issues with a large SSAS solution, there are a few things that we can quickly check and implement to give us quick wins for improving the performance of our cube. In this post, I’d like to quickly point out three best practices that we can follow to improve performance and create a more positive experience for our users. These are not meant to be the top three best practices to follow, but rather three (among many) very important best practices you should follow. I believe following these three best practices will make a difference in your solution.

Create Hierarchies with Attribute Relationships

In my opinion, creating natural hierarchies are the single most beneficial thing an SSAS developer can do to improve the performance and usability of a cube. There are several reasons correctly defined user hierarchies are beneficial, but here are a couple of the top reasons.

Increased Query Performance

Calendar HierarchyCreating attribute relationships between attributes that are included in a user defined hierarchy improve the performance of queries using these attributes for a couple different reasons. In this case, we’ll look at the Calendar hierarchy in the Adventure Works Date dimension. The Calendar hierarchy has five levels with the Calendar Year attribute at the top level and the Date attribute at the bottom level of the hierarchy. We can also see the following attribute relationships created to give SSAS an understanding of how the members of these attributes relate to one another. Once created, these relationships give SSAS the ability to understand that a given date member is related to a single month member, a given month member relates to a single quarter, and so on and so forth.

imageThis also means that during processing, special indexes are created that map the relationships between each member of each level in the hierarchy. This means that before a query is written, SSAS also knows to which month, quarter, semester, and year each date aggregates. These indexes are only created if you correctly define the attribute relationships between the attributes in the hierarchy.

Improved User Experience

imageUser defined hierarchies also improve the user’s experience with the dimension because the hierarchy provides a very natural way for the user to navigate through the data in the dimension. A user defined hierarchy does two things for a user: 1) Using the hierarchy obviously presents the data to the user in an understandable and clear way. And 2) the hierarchy organizes the data based on your business requirements. This means that the user doesn’t have to think about how should these attributes be organized or labeled because you’ve done that for them. The user can focus on reading their reports, understanding the data, and making business decisions.

Partition Measure Groups to Separate Volatile and Static Data

imageMeasure groups that are larger than (about) one million records should be separated into multiple partitions. There are several advantages to separating larger measure groups into multiple partitions.

Increased Processing Performance

Partitions in a measure group are processed in parallel. This means that a measure group containing three years worth of data separated into one partition for each month will process faster than a measure group with all three years worth of data in a single partition. Your processing strategy for each measure group may vary depending on the amount of data. For instance, a large telecommunication company may collect hundreds of millions of records per day, dictating a more complex and granular partitioning strategy.

Also, we should consider which partitions contain data that is changing vs. data that is now static. If our businesses will continually log transaction in the current month, we can partition our data by month effectively separating our volatile data from the static data. This means we only need to process one months worth of data in our cube to pick up the latest changes instead of having to processing the whole measure group.

Increased Query Performance

By partitioning our data at the month level, certain queries will also perform better. For instance, imagine a user queries our measure group searching for last month’s sales figures. Because we have partitioned our measure group with each month being loaded into a separate partition, this means that the entire measure group does not have to be scanned. Only the partition containing the data for the month in question needs to be queried.

Create Aggregations for Large Measure Groups

You can think of Aggregations as indexes for SSAS. Aggregations are used to help SSAS find the answer to a user’s query faster. Aggregations are typically used for measure groups that are large and take a considerable amount to query.

Improved Query Performance

Aggregations contain the data of a measure group at a summary level typically higher than the lowest level of data included in the measure group. Aggregations are populated during the processing phase of the partition. You can think of Aggregations as exactly the same as the lowest level of the measure group just at a summary level. This means that when SSAS receives a query that can be answered using an Aggregation, SSAS does not have to spend the additional time required to retrieve the measure group data from the lowest level and roll up the data to the requested level because the Aggregation design already contains the data at the desired level.

Beware Too Many Aggregations

Because Aggregations are built during the processing phase, this means that for every aggregation you define more time is required to build the aggregations. This also means that it is especially important to only build useful aggregations that are necessary to improve performance. By creating aggregations that are not often used, you can degrade query performance and increase processing time with little to no benefit. Like indexes on a SQL Server table, too many aggregations or the wrong aggregations can actually hurt performance, so make sure you test, test, and then test to ensure your aggregations are helping your query performance.

I hope you have found this information useful. By following these three best practices, I really believe you’ll see a generous improvement in the performance of your SSAS cubes.

If you found this information helpful, I’d love to hear from you! Please leave me a comment and let me know what you think. Do you have any ideas on something I left out or should have included? Please let me know! Thank you!

Pragmatic Works, SSIS

Are You Using SSIS Best Practices?

image

Earlier this month, Pragmatic Works released a new tool for their Pragmatic Works Workbench toolbox: The Best Practice Analyzer. The guys and gals over at Pragmatic Works have come up with a tool that analyzes your existing SSIS packages and compares them with a standardized set of best practice guidelines produced by the expert team of consultants and developers you’ve come to know and love over the past several years.

The Best Practice Analyzer combs through the SSIS packages you have selected and produces a report outlining all the best practice violations including violations of varying severities, such as “Warning”, “Error”, “Performance”, and “Informational”.

First, select the packages you’d like to investigate for adherence to best practices.

Next, select the best practices to be included in the report.

The end result is an easy-to-read report identifying any violations of best practices.

Violations could include everything between leaving a component description blank, using a fully block transform in a Data Flow Task, not enabling error logging, plus many more. Head over to PragmaticWorks.com to get more information on the SSIS Best Practice Analyzer.