Tag Archives: power pivot

Watch the Power Pivot 101 Webinar Recording

Thank you to everyone that attended my webinar titled Power Pivot 101: An Introduction! Also, thank you to Thomas Leblanc (blog|twitter) for making it possible. I had a great time presenting to the PASS Excel BI Virtual Chapter and I’d love to be able to do it again any time.

If you weren’t able to make the webinar, you can easily view the entire recording right here!

If you’d like to play along with the webinar and follow through with my examples, you can download the data sources here.

If you want to download the Power Pivot model I created during the webinar and play around with it, that can be download here, as well.

Power Pivot Learning Resources

Read about options for upgrading a Power Pivot model.

Interested in hands on training with the experts from Pragmatic Works? Consider taking their Power Pivot modeling class.

Here is part 1 of 10 DAX calculations for your Power Pivot model.

Feedback?

We had a lot of questions at the end of the webinar and I didn’t have time to answer all the questions. If you had a question that I didn’t get to, please just leave a comment down below with your question.

If you had any other feedback, you can leave that comment down below, as well. Thanks for reading and I hope you enjoy the webinar.

Taking #PowerPivot to the Next Level

Power Pivot is an amazing, flexible and powerful business intelligence tool (among other things) and there is no doubt about that fact. As a feature included with Excel 2013 and 2016 (and an add-on for Excel 2010), Power Pivot allows user with a little technical expertise to integrate disparate data source together within a flexible data model. Once the data is loaded into Power Pivot, we easily have the ability to create powerful calculated measures, key performance indicators Continue reading Taking #PowerPivot to the Next Level

Importing Power Pivot & Power View into Power BI

Import Power Pivot into Power BI

The Power BI August update just rolled out today (8/20/2015) and in the latest update there’s a lot of cool, new enhancements such as writing custom MDX or DAX queries to access your SSAS data sources, connectors for Azure HDInsight Spark and Azure SQL Data Warehouse (so awesome!), some various UI improvements and a bunch more. But one of the coolest features (and much needed IMHO) is that we now have the ability to import Excel artifacts, such as Power Pivot models and Power View reports straight into Power BI Desktop!

New to Power BI Desktop? Read this first!

Import your Power Pivot Model into Power BI

To begin importing a Continue reading Importing Power Pivot & Power View into Power BI

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

Introduction to Power BI Desktop Video Walkthrough

Last night I finished editing and posting my video walkthrough of Microsoft’s new Power BI Desktop tool. This tool is awesome! If you’re looking for an end to end analytics tool that will allow you to consume all types of data sources, mash it up, and then report on it in one single place, this tool can do that.

So give my video walkthrough a watch to get ramped up on Power BI Desktop and leave a comment down below if you enjoyed and learned something from the video!

Choose Your Weapon: Power BI Edition

With an estimated 500 million Excel users in the world, it’s no wonder that Excel is the #1 business intelligence too in many organizations around the globe. And with the release of Excel 2013, the collection of powerful and flexible data analysis tools built into Excel has only continued to grow. Microsoft is constantly adding new features and functionality to Power BI at pretty fast rate, so now is a great time to start learning about everything that MS Power BI can offer your organization.

Because Excel is just full of a slew of incredible tools, its important for us to understand the difference between the tools, when you should choose each tool, and the Continue reading Choose Your Weapon: Power BI Edition

PowerPivot 101 Training Webinar with Q&A

To watch the free recording of my webinar called PowerPivot 101, just head over to PragmaticWorks.com, create a free login and you’re all set! Please let me know if you have any questions.

PowerPivot 101 free webinar training

Once again, thanks to everyone that attended my session on PowerPivot. Here are my responses to some of the questions I received during my session but did not have time to answer until now.

1. Does the current version of PP support drill through?

The current version of PowerPivot does support drill through. All you have to do is right-click a measure in your spreadsheet and select Show More Details or alternatively you can double-click the measure.

image

2. Is PowerPivot add-in required for SharePoint?

PowerPivot for SharePoint is required if you wish you share Excel workbooks that contain PowerPivot data via SharePoint. For more information on installing and setting up PowerPivot for SharePoint, check this out.

3. Where will a replay of the PowerPivot 101 webinar be available?

Head over to the Pragmatic Works Learning Center to view the recording of this webinar as well as the recordings of all the previous webinars!

4. Book/website recommendations?

Definitely check out PowerPivotPro.com run by Rob Collie. It’s a great website with tons of cool PowerPivot-y stuff, plus Rob is a cool dude.

Check out these books!

image4

PowerPivot for Excel 2010: Give Your Data Meaning
Marco Russo & Alberto Ferrari

image10

Practical PowerPivot & DAX Formulas for Excel 2010
Art Tennick

5. What is the name of the component/function in SharePoint that would make the “live” data connections possible in published PowerPivot?

I assume the question is directed at utilizing a live connection to a data source, such as SQL Server. To my knowledge, this is not natively supported with PowerPivot. The data in your PowerPivot workbook must be refreshed.

There are two ways to refresh data in a PowerPivot workbook. You can refresh it manually anytime you want or you can schedule a refresh through SharePoint like I showed during the webinar. For more information on refreshing data in a PowerPivot workbook, check out MSDN.

6. I thought DAX was only supported using the Tabular mode of SSAS. My understanding is that PowerPivot is built on the Multidimensional model. I wouldn’t think DAX would be supported. Can you comment?

DAX (Data Analysis Expression language) is the formula language for PowerPivot and Tabular modeling and is used to specify calculations and to create new columns. It’s basically an extension of the Excel formula language. For more information related to DAX in regards to PowerPivot and Tabular modeling, read this and this.

7. Can you blog about having a calculated field that I can put on a slicer to filter by number of records (i.e. products with more than 100 records).

To filter records where a measure is greater than or less than a certain amount is pretty easy. In your workbook, just click the drop down arrow above the attribute you wish to filter, go to Value Filters, and select Greater Than, Less Than, or whichever option you desire.

image

Then you can select which measure you want to use and what quantity to use.

image

Thanks for all the great questions everyone. Don’t forget to check the recording of the webinar in case you missed anything!