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.

Uh oh, things are getting worse

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 columnshard 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?

Dashboard in a DayOn 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.

Previous Post
[Video] Office Hours 2018/8/29 (With Transcriptions)
Next Post
Single-Column-Key Missing Index Recommendations are Usually Wrong.

7 Comments. Leave new

  • Dylan Fullerton
    September 3, 2018 12:05 pm

    Happy Labor Day
    You all are amazing for taking the time to share your SQL talents and knowledge to the online learning community like myself. I have been searching a long time for the best resource in learning SQL Database and it’s counterparts…..looks like I finally found it.
    Thank you very much!
    Dylan Fullerton
    dfdylbin389@gmail.com
    dylanrayfullerton@gmail.com

    Reply
  • Hi Brent, I have started using Power Bi Desktop instead of Excel and find it really good. Is this the replacement for SSRS? In the long term I would say yes. One question, from Power BI desktop do you know how to automate a refresh, save the updated pbix and then publish this to Power BI Web? It is the only manual steps in my process and I want to automate as much as possible. Thanks in advance Adam

    Reply
    • Adam – nah, I’m not using the web part so I can’t help you there. Sorry about that!

      Reply
    • It is not the replacement for SSRS. It depends on the use case: Power BI is strong in one page interactive reports/dashboards. SSRS is good in pixel-perfect, multi-page reports. The ones that you print out and leave on the desk of your manager. SSRS excels in financial reporting for example. It’s kinda hard to build a profit/loss or balance sheet in Power BI Desktop. For the moment, SSRS is also much better in subscriptions and delivery of reports.

      Regarding the automation: you upload the .pbix file to the Power BI service. The service will separate the reports from the data set. For the data set, you can configure an automated refresh. If the data is in the cloud, you don’t have to do anything else. If your data is on-premises, you have to install a gateway so the Power BI service can reach your data. More info: https://docs.microsoft.com/en-us/power-bi/refresh-data

      Reply
    • emanuele.meazzo1
      September 4, 2018 2:19 am

      Hey Adam,

      the dataset refresh can be scheduled only in the cloud part of the application; You’re supposed to publish the report to the cloud and then schedule the dataset refresh there (PBI Desktop is “techically” only for report developement, not consultation). If your data is on-premise, you’ll need the PowerBI gateway installed in your network

      Check this out: https://docs.microsoft.com/en-us/power-bi/refresh-scheduled-refresh

      Reply
  • Power BI Desktop prompts you to log in with your Microsoft account. You can close/skip the prompt, but it will prompt again the next time it starts.

    Is there anyway to permanently disable the prompt?

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}