Building SQL ConstantCare®: You Should Probably Learn Power BI. I Did.

When I was prepping for the PASS Summit last year, I wanted to unveil something awesome and free that would help you analyze your SQL Server’s performance. I ended up building the Power BI Dashboard for DBAs, an easy way for you to visualize your server’s wait stats.

When we started building SQL ConstantCare®, our mentoring service, I did not want to build a user interface. I know, it sounds crazy, but I wanted to just send you regular emails about what actions you needed to take on your SQL Server. I didn’t want to give you numbers – I just wanted to help you take your next steps towards healthier, faster databases.

I didn’t want to send you numbers.

However, I needed those numbers, so I use Microsoft Power BI.

You might say, “Brent, that’s because you’re a Microsoft shill,” but get a load of this: Power BI Desktop is the only Microsoft component in our entire stack! The data is uploaded to Amazon Web Services, where Richie imports it into AWS Aurora PostgreSQL, and sends you automated emails with AWS Lambda code written in JavaScript.

If you work with data, Power BI Desktop is like a next-level Excel. I bet you know how to use Excel, and I bet you’re not wildly happy about it – you just use it in your development and DBA jobs for 100-level data plumbing tasks.

I firmly believe you will be a better developer and DBA when you learn Power BI Desktop. You won’t be great at it, and you’ll resent it just as much as you resent Excel, but it’ll serve you and your career better. It’s just a better way to render data.

“Okay, I’m in. What the hell is Power BI Desktop?”

Let’s start with Excel. There are two ways to get data into Excel:

1. Manually enter it. This is bad, but let’s be honest, this is how most of the world uses Excel. They create a spreadsheet, put data in, and then it becomes the single source of the truth.
2. Create a data source, like point to a SQL Server. Build your queries in Excel, import the data, and then whenever you want to see it updated, refresh it. This kinda uses Excel as a front end, like Access, giving you Excel’s cool graphing capabilities. This is awesome, but…not a lot of folks use Excel this way.

The free Power BI Desktop is specifically designed for that latter use case. It ain’t the home for your data – it’s just a front end for data that lives somewhere else. Like Excel in the latter use case, all your logic lives in a file, except instead of .xlsx, it’s .pbix. You can hand the .pbix file to somebody else and they can see your reports as-is, or hit Refresh and fetch the latest data from the sources you defined.

I’m a huge fan, but before you go hog wild, I wanna caution you about two signs to look for that’ll tell you when it’s time to bring in a Power BI pro:

1. Watch out for files that go viral. Sooner or later, you’re going to hand that .pbix file to somebody else, and they’ll make their own changes, and your changes and data will get all out of sync. (That’s not a Power BI Desktop problem: that’s a source control and discipline problem.) That’s where Microsoft’s online services for Power BI come in. You can upload/publish your report to PowerBI.Microsoft.com or to your own on-premises report server, define access permissions for who can see it, configure how to connect to the database, and set up regular refresh rates for your data so everybody’s seeing the same stuff at the same time.
2. Watch out for rapidly changing multi-GB data sets. If you build your report in a way that you have to import the entire data set every time you want to get the latest changes, you can have a bad time as your database grows. You’ll hit a point where you’ll want to switch to incremental refreshes of your data, and right now, that is by no means a trivial change. (That’s not a Power BI Desktop problem either – it’s just the same problem that every data warehouse load process faces as it switches from skunkworks to enterprise-grade.)

As long as you stay mindful of those two warning signs, you can just get by with the totally free Power BI Desktop, building reports for your own personal & occasional team use.

“So Brent, what are you doing with Power BI?”

In the last week, 247 customers uploaded 9,913 collections of DMV data for 1,482 servers. Just to pick a random DMV, we’ve got 117,633,525 rows in sys.dm_os_wait_stats right now. When you wanna be the Senior DBA for 1,482 servers, you gotta think at a larger scale.

I can’t import all of that data into Power BI Desktop, so my general workflow goes like this:

1. In Postgres, write DMV queries to gather the right raw data (filtering out things that don’t matter, and creating new calculated columns that I can use for analysis)
2. In Power BI, use those queries as data sources, and then build visualizations to spot problems faster
3. Every day, open Power BI, look for customer servers that need attention. Sometimes, I can get to the root cause just with Power BI. Other times, I use it as a launching point, then go run Postgres queries directly against the customer’s DMV data. If I find that I needed queries to solve a particular problem several times, then I try to take that query back up to step 2, and bring the data into Power BI so I can move faster next time I see that problem.
4. Really long term – if I see a problem happening over and over – turn that thought process and query into an automated rule that Richie can build.

In short, I’m using Power BI Desktop and Postico (like SSMS for Postgres) the same way I’d have used Excel and SSMS ten years ago. Power BI Desktop has gradually replaced Excel in my data work. It’s not perfect by any means – no copy/paste, can’t sort on two columns, hard to reset filters (that item isn’t completed in Power BI Desktop), hard to hyperlink out of it, no Mac client, etc – but for me, the tradeoffs are worth the end result.

Ready to learn Power BI with me this month?

On September 18th, the Dashboard in a Day class will teach you how to:

• Connect to, import, and transform data from a variety of sources
• Define business rules and KPIs
• Explore your data with powerful visualization tools
• Build stunning reports
• Share your dashboards with your team and/or the world

It’s taught by analytics pro and MVP Steph Locke. I’ll be in there myself – I’m invested in this tool, and I need to sharpen my skills.