Okay so I already posted a round up post for this month. You can see it here. Tonight one of the participants, Rob Farley (Blog | Twitter) made me aware of the fact that I missed a HUGE opportunity in making the round up in a Power BI report! *face palm* With that I proudly give you the interactive version of this month’s TSQL Tuesday round up!
T-SQL Tuesday #75: Round Up
This month I challenged the blogging community to share their own creations in Power BI. We got a ton of great entries this month, thank you everyone who participated! My overarching goal for this month’s topic was to get folks who may not normally play in the BI space to use this fantastic solution and maybe get some ideas flowing on how they may be able to apply it in their everyday work.
UPDATE: Thanks to Rob Farley for the idea, I’ve made this round up into Power BI report! (Direct link)
- My contribution this month was to do an overhaul on the enterprise policy management framework reports from codeplex. If you use policy based management in your enterprise you should check it out.
- Marlon Ribunal (Blog | Twitter) shared with us a really interesting report. He shows us comparisons of hospital charges for different services/cases for hospitals across the US compared to the US average and other cities around the world. Not going to lie, spent a lot of time playing with this report and seeing the cost differences between services in my hometown of Tampa, FL.
- Rob Farley (Blog | Twitter) stepped it up by showing us his step count. He exported his Fitbit data walk us through (yes, all these puns are definitely on purpose) how he set this all up. Run over to his blog and check it out! Okay I’m done with puns now…walk it off. Okay really done.
- Steve Jones (Blog | Twitter) fueled by Super Bowl fever, no doubt, brings us a report comparing the career of future Hall of Fame quarterback Peyton Manning’s career to another HOF quarterback from Denver, John Elway. What’s really interesting about this entry is not so much the content (which is fun to look at too) but the process in which he gathered the data. Steve shows us how Power BI can be used as a lightweight ETL-like tool. The data that drives his report is actually pulled directly from a webpage, which is a really cool trick to do.
- Also be sure to check out Steve’s own editorial/summary post. There’s also SQLServerCentral’s Weekly newsletter you should check out. I’d also like to point you to his Voice of the DBA podcast on iTunes, if you don’t know about it. Great stuff!
- Jon Morisi (Blog ) is trying to show us…the Truth is Out There! Inspired from a TED Talk, he decided to showcase Power BI’s mapping and timeline visualizations to show us UFO sightings across the US for the last two years. What’s cool is this is another post where the dataset is pulled from a website. All I know is California and Florida are dueling banjos of strangeness…
- I was excited to see Chris Yates’ (Blog | Twitter) entry. On Twitter he was one of the most enthused about this topic and was a perfect example of what I wanted to see this month. Ultimately this experience lit up some ideas for Chris on how to use this going forward, which was really exciting to see.
- Niel Hambly (Blog | Twitter) was another really exciting entry as it perfectly showed how DBAs can leverage Power BI right away. Niel’s report visualized data from the MAP Toolkit (note: One of my favorite free tools, btw) to review your SQL Server environment. Not only does he do a fantastic job stepping through everything, you can download the code/report and apply it to your environment.This one is a must-read for DBAs everywhere!
- Tim Peters (Blog | Twitter) post is another runner in the group. He shares with us a report visualizing his very impressive running data. In addition to breaking down his mileage, Tim also breaks down an important lesson. Remember what the B in BI stands for.
- Malathi Mahadevan (Blog | Twitter) is another DBA learning the BI side of things. In her post she analyzes the data from SQLSaturday events she helps organize held in Louisville, Kentucky. Power BI helped her come up with some interesting insights! Go check out what she learned about attendees from using Power BI.
- Jens Vestergaard (Blog | Twitter) missed the call but the topic lined up with something he was actively working on and I wanted to make sure his post made the roundup as its really cool, especially for BI pros. Jens analyzed SQL Server Analysis Services (SSAS) performance using Power BI. Definitely worth a look if you work with SSAS at all.
- BONUS: John White (Blog | Twitter) was gracious enough to let us include his post in this roundup. In a funny circumstance, he posted an entry that was basically the exact topic of the month and he didn’t even know about T-SQL Tuesday! John is an avid diver and in his post he analyzes worldwide shark attack data!
Thanks again to everyone that participated this month. Even if you weren’t able to participate in the official event, please feel free to share your Power BI experiences in comments!
T-SQL Tuesday #75: Jump into Power BI
So this month I’ve decided to bring every participant in this blog series kicking and screaming into the BI world. Power BI, if you haven’t heard or seen it yet, is Microsoft’s analytics and visualization solution. This month I challenged everyone to create their own Power BI report and share it with the world via our new Publish to Web feature. So far I’ve seen some great contributions from fellow bloggers, and those will shared in the upcoming round-up post. You can also check out the winners of the recent Power BI Report Contest (Congrats Jason and James!).
For my contribution to this contest I’ve decided to share with you a work in progress. If you know me, I’m a huge lover of Policy-Based Management. In fact, I’m actually part of the Enterprise Policy Management Framework (EPMF) project on Codeplex. T-SQL Tuesday event is normally a DBA-centric event so I figured I’d help the DBA crowd wrap their heads around how a BI solution can help them in their day to day.
What I did to kick start this effort was to create this Power BI report that allows you to explore the database repository that contains the EPMF policy evaluation results. The current EPMF project uses Reporting Services to deliver its reports. This won’t change. If anything I’ll be exploring new capabilities with SQL Server 2016 and R-integration. Here’s a screenshot of what the SSRS dashboard report looks like:
While this report is nice, it can be a little cumbersome doing interactive analytics against this data set. The reports are somewhat “interactive” in that you can click on a visualization to drill down in to a detail level report behind it, but it’s not really interactive. Enter Power BI! Below is my first swag (read also: none of this is set in stone for final EPMF release, any/all feedback is appreciated) at creating a Power BI version of this dashboard. And yes, I’ll be doing away with pie chart. Dataviz people rejoice!!!
I’ve included a lot of the existing elements from existing report including seeing detail level information of policy results breakdown by server and policies. You also get interactive elements such as choosing policy results, server names, month. On the trending and details page (second tab), I’ve used some custom visuals from Power BI’s Custom Visuals Gallery such as the tadpole spark grid plus to show trending over time.
The best part of using Power BI to visualize on your operational data? You can access your reports and insights anywhere, anytime through our mobile apps! We’ve got apps on all major platforms (iOS/Android/Windows Mobile) as well as a universal Windows 10 app. Think about this. Many large organizations pay thousands upon thousands of dollars for monitoring software that includes capabilities to push to mobile, and even then they’re usually shrunken mobile websites. This is a native application functionality in the palm of your hands at a fraction of the price, so cool!
What else is in the future for EPMF? I’ll be rebuilding the data model from the ground up to make it more efficient for reporting. The current model is very simplistic, and works, but as enterprises adopt EPMF and monitor more and more, the current model has certain scale issues. What do you think? Are you excited for new developments in EPMF? Are you using EPMF today? Let me hear it in the comments!