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!
10 replies on “T-SQL Tuesday #75: Jump into Power BI”
[…] Jorge Segarra jumps into Power BI: […]
Looks great! One thing- in the original codeplex release, drilling into the evaluated policy displayed the explanation text, which often included a link and explanation of the policy being evaluated. I didnt like how cumbersome if felt when it popped up in the report details- is there a slick way to display the policy details from Power BI that would feel more intuitive?
Much cleaner presentation of the data overall and I look forward to leveraging this type of interface!
Thanks, glad you like it! I’ll see what I can do about detail level information you’re asking for.
[…] ← T-SQL Tuesday #75: Jump into Power BI […]
Using EPMF to keep an eye across a dozen or so servers. Looking forward to seeing the Power BI slice and dice.
Two things that would be nice
1) When evaluating the data of the last database backup, can we get an actual date/time instead of some crazy number that seems to have no meaning?
2) I’ve put a couple of policies in place to check business processing of data in the database. Once such check is for the Service Broker queue length. This checking runs every 10-15mins. It’d be good to be able separate these results from the default reporting as given the frequency, any failures tend to skew the charting scales.
Cheers
Phil
You could separate those out by creating a custom category and then modifying report to not select that category by default. Remember its a framework so you can modify anything any way you like.
Great article and run down. Is there a link to this PowerBI solution? Im curious about what the data model for it looks like.
Thanks! Im going to clean it up a bit as current one keys off existing model and then I’ll post. If you’re really wanting to dig into it asap i can post now.
I’ll patiently wait Jorge, thank you for this and all the “virtual” mentoring
I’ll wait impatiently, great effort. I use the EPMF reports but the Power BI version looks really good.