Category Archives: Power BI

Refreshing Excel Power Query & Pivot Tables with SSIS and Task Factory

image With SSIS 2014 and earlier there is currently not native way to refresh an Excel workbook which include Power Query queries. Now that functionality is rumored to be included with SQL Server 2016 but if you’re currently running SQL Server 2014 or 2012 you are out of luck. But that’s why Pragmatic Works put together the Excel Power Refresh component for SSIS.

Configure the Excel Power Refresh Task in SSIS

Configuring the Excel Power Refresh Task is pretty straightforward. There’s not a lot of complexity to this component, which is a good thing.

First create a Connection Continue reading Refreshing Excel Power Query & Pivot Tables with SSIS and Task Factory

Here’s the New #Excel 2016 Chart Types!

The Office 2016 Public Preview is now available for download! Included in the preview of Excel 2016 are a handful of new chart types and since I’m a huge fan of awesome data visualizations, I thought I’d take a few moments to play around with them and share my experience with you so you can have a better idea of what to expect in the next version of Excel. But to be honest, if you’re a data & visualizations nerd like me, you’re probably pretty excited! Continue reading Here’s the New #Excel 2016 Chart Types!

Cleaning Your #PowerBI Power Query Code

image Over the weekend I found this nifty tool called Power Query Management Studio. Someone shared it on Twitter and you’ve probably seen the link to download the tool on technet. Basically this tool is a fancy Excel workbook that allows you to easily clean up your Power Query code and insert it back into your Excel workbook or Power BI semantic model. It’s pretty nifty and easy to use so I figured I’d give you a quick run down on using it to clean up my Power Query code in my Fantasy Football & NFL stats Power BI model, which you can download here. Continue reading Cleaning Your #PowerBI Power Query Code

Twitter Analysis with #PowerBI & Plus One

Earlier this week Christopher Finlan put together this awesome Datazen dashboard using Plus One. Christopher has been doing a lot of cool things with Datazen so I recommend that you do like I did and subscribe to his blog. But Christopher’s cool work with Plus One inspired me to create my own Social Media dashboard using Plus One, as well.

powerbi search completePlus One has created this nifty little desktop application that you can download and install on your computer. Once you’ve set the app up, all you need to do is enter a search query. In my case, I wanted to see what people were doing and saying with Power BI on Twitter. Plus One can only recover the previous seven days of data, so you’ll need to periodically refresh your search or schedule the search, which you can do easily with the Plus One application. Continue reading Twitter Analysis with #PowerBI & Plus One

#PowerBI and #SSAS Tabular: A Natural Fit with the Power BI SSAS Connector

SSAS Tabular and Power BI In late June last month, the Microsoft Power BI team released the Microsoft Power BI Analysis Services Connector. The Power BI SSAS Connector allows your deployed Power BI reports to utilize your on-prem SSAS data sources. It’s super easy to set up and can be downloaded for free! And who doesn’t love “free”? Continue reading #PowerBI and #SSAS Tabular: A Natural Fit with the Power BI SSAS Connector

Importing Excel Power View Dashboards into Power BI

If your organization is now a Power BI customer, congratulations. You’re now ready to create some very cool dashboards, integrate disparate and disconnected data sources and take advantage of Power BI’s ability to modify and transform your data, build interactive and dynamic dashboards and then share them with your team and organization. But until you create your dashboards to take advantage of the new visualization types and other improvements, you can easily import any existing Power View sheets in Excel into your Power BI site.

Power View dashboard in Excel ready for Power BI goodness

Above you’ll see an example of a Power View dashboard that I will import into my Power BI site. Continue reading Importing Excel Power View Dashboards into Power BI

#PowerBI Tip: Use the Treemap, Column or Funnel Chart as a Colorful Slicer

Power BI Desktop has been out for GA for over a week now and some of the pro’s out there have come up with some pretty cool tricks. For instance:

But if you’re looking for a way to spice up you report filtering with a little color, try using the Treemap, Column or Funnel chart as a Slicer for those fields that only contain a few unique values. At this point with Power BI, you don’t have any customization options for the Slicer visualization (although I’m sure that is coming down the pipe in a future release). This option won’t work terribly well if the field you would like to use as a slicer has more than a dozen or so unique members, but you can experiment with it and see what you can come up with. Here’s my custom slicers in action.

column chart as slicer

tree map slice in action

Power BI Funnel slicer in action

To multi-select tiles in the custom slicer, just hold Cntrl as you click.

This little trick relies on the natural cross filtering between data regions in the Power BI dashboards. First I created a measure that calculates the distinct count of the field that I wish to use as my slicer. In this case the field is Genre.

Power BI Distinct Count DAX calculation

Then I added a Treemap/Funnel chart to the report using the field Genre as the Group value and the measure Distinct Count Genre as the Values.

image

Then just resize the visualization so that the squares are about evenly sized. There’s a few ways you can arrange it, but just play around with it and see what you can come up with.

Power BI Dashboard with Treemap Slicer

Power BI Dashboard with Treemap Slicer

image

Funnel slicer

If you are wondering how I made the column chart slicer, here’s a gif image that shows the steps I used. Enjoy!

Power BI column chart as slicer making of

Feedback?

What do you think? Leave me a comment below and let me know. Or if you’ve got a neat Power BI trick you’d like to share, let me know, as well. I love to hear new ideas! Thanks for reading!

#PowerBI Fantasy Football Player Stats Dashboards for Download

Every year at Pragmatic Works some coworkers, including consultants, marketing staff, support team members, software development staff and project management, partake in a company fantasy football league. And with the recent release of the new Power BI Desktop, I thought what better way is there to prepare to completely annihilate my coworkers and friends in an imaginary nonsensical game than by creating some nifty Power BI dashboards based on last years player stats as recorded by Yahoo! Sports. So I thought I’d walk you through some of the steps I followed to leverage the Yahoo! Sports NFL player stats page as a data source and some of the query transformations I applied to prepare the data for reporting.

Power BI dashboard with Power BI Desktop Continue reading #PowerBI Fantasy Football Player Stats Dashboards for Download

Three Best Practices for #PowerBI

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. 🙂

Power BI and Big Data

If you’re worked in the wide and diverse field of information technology for almost any amount of time, it probably hasn’t taken you long to discover that the one thing constant about IT is that the technologies and strategies involved change faster than you can learn them. And if you work in business intelligence like I do, you don’t have to look very far at all to see change. The Microsoft Power BI team rolls out a software update every month! If I want to stay learned up on the technology, I have to really be on top of things.

New to Power BI? Start here!

About ten years ago when Hadoop was first being developed at Yahoo, Continue reading Power BI and Big Data