Voice of the DBA

Tag: Power BI

  • Solar Production After 5 Months in Power BI

    I’ve been tracking my usage and comparing that with the estimates for my solar power system. I wrote about the database design and tracking the usage and some of the estimates.

    In this post, I want to look at the reporting in Power BI. Here is my refreshed comparison report as of Jul 31.

    2022-08-01 10_17_38-SolarProduction - Power BI Desktop

    I have 3 sections here. At the bottom right is a table with a range setting that shows raw data. I need to write about the import process for this data, but that’s for another day.

    At the top, I have the running total of production (light blue) and estimated production (dark blue). The first month or so we were close to the estimate, but we started to outperform the estimate regularly, which is good news. The Tesla wasn’t in the model or estimate, and it uses some power, so it’s good the system is ahead.

    The bottom left is the month by month comparison, which you can see is a fairly steady overproduction each month.

    The configuration is fairly simple for this report. For the line chart, I’ve set the x-axis as the date. My Y-Axis has the sum of both of the raw production numbers. This gives me a running total across time.

    2022-08-01 10_32_02-SolarProduction - Power BI Desktop

    The lower left month by month bar chart is similar, but I’ve added the month, which separate this into buckets.  This lets me see if a particular month’s comparison is hidden in the line chart by the long term running total.

    2022-08-01 10_36_16-SolarProduction - Power BI Desktop

    I can’t change much, but this does help me to look for anomalies and possible production issues. Perhaps a panel isn’t performing or there is another issue. By glancing at this every few days, I can decide if I need to look at more detail on the system.

    Hopefully the company doing monitoring would detect issues, but this is a good double check for me and it also gives me long term data backup in case they have issues.

  • Power BI Dashboards

    This is part of a series on my preparation for the DP-900 exam. This is the Microsoft Azure Data Fundamentals, part of a number of certification paths. You can read various posts I’ve created as part of this learning experience.

    I don’t know a lot about Power BI. I’ve lightly hacked and played with it, but it has evolved and changed so quickly that I needed to dig into some concepts.

    Power BI Dashboards are a part of what you need to know for this exam. Over the years, I’ve lightly made a few reports, but not dashboards, so this was an area I needed to study up on a bit, especially these concepts.

    Dashboard Basics

    A dashboard is different from a report. A dashboard is

    • a single page
    • available in the Power BI Service only (not Power BI Desktop)
    • composed of tiles
    • can use data from one or more reports, and more than one dataset
    • one dashboard can be featured
    • supports natural language queries
    • can’t see the underlying data, but can export data

    Tile Sources

    A tile can be from:

    • a report (a visualization)
    • another dashboard
    • an Excel workbook in OneDrive for business
    • Quick Insights
    • An on-premises paginated report from Power BI Server or SSRS

    There can be standalone tiles for  images, text boxes, video, streaming data and web content.

    These are a series of facts I think are important to understand about Power BI Dashboards

  • The PowerBI Volleyball Report – Organizing Data To Start

    One of my goals this year was to build a report that I can present to kids and parents showing the skills progression of their kids. I have attempted a few times to put something together in Excel, but it wasn’t easy for parents to visualize, and I wanted something better to let them focus on a specific kid, without making the data hard to consume. Power BI makes this easy.

    This post looks at my data capture process, and how I evolved it a bit to make this easy to handle in Power BI.

    Data Capture

    I’ve tried a few ways to gather data during matches, but paper turns out to be the easiest way to ensure this happens quickly and fairly accurately. While there are a number of apps, I find them problematic as one wrong press means the data capture stops, and I can then miss the next item.

    I used to calculate all totals by hand and then put them in a spreadsheet for parents after each competition, which worked well, but this format isn’t easy for Power BI to deal with.

    2021-07-28 14_12_31-Stats2022_16Select.xlsx - Excel

    Easy for humans, but bad for reporting.

    As a result, I stopped to think what would be good and easy for Power BI. A table is best, and while I don’t want to bother with a database, I can modify my Excel formula easily enough to handle this.

    Since I will report on different areas, I decided to keep a master sheet for each report area. This means I have a “serve” worksheet, as well as others for Serve Receive, Attack, Digs, Blocks, and Assists. I can also add in new sheets as needed.

    Making a tabular format means that I added a few columns to this list. These columns are the slicers that atheletes and parents might want to use when they are reporting. In my case, these are:

    • date of event
    • event name
    • opponent
    • player

    With these columns, I can take my paper sheets, type in the raw data, and let Excel do a few calculations. This also means my main report is just a few sums from these raw sheets to get the totals above. From last season, I had data like this:

    2021-07-28 14_19_37-Stats2021_15Select.xlsx - Excel

    I also decided to enter data in the same order each time so that once I have a sum to copy data from this sheet for one player, I can copy/paste those formulas for the rest. This keeps the burden low for post game work.

    This also means that when I “Get Data” in Power BI, I just load data from each worksheet into a separate table in Power BI. This allows separate reports that are simpler to produce, as much of this data doesn’t make sense when combined together. This also means that I don’t have one huge table where I’m trying to manage data and potentially scrolling around a lot from left to right. This also means I can load this into SQL Server easily if I want to.

    This also means I need to set up incremental refresh in Power BI.

  • DAX and Power BI Stairway Live

    As part of the Redgate Community Circle, I’m going to be running a live class on DAX and Power BI, every Thursday, from 12MDT for about 30-45 minutes. We’ll see how things go.

    I’ve been wanting to learn DAX and I keep putting it off. I can google with the best of them and fumble through things, but that’s not ideal. And it might not be correct, as I’m not sure I’m always writing the code that I need, or the best code. So I want to walk through Bill Pearon’s Stairway to DAX and Power BI live.

    Join me and we’ll work through the series, learning some DAX and applying it to Power BI, which will be interesting. I will read the articles ahead of time, and download anything I need, but for the most part, I’ll be fumbling along with you.

    If  you’ve got time, join me live. The webinar link is here, and you’re welcome to join, for the whole time or just a part. I anticipate things going for 30+ minutes, but I’ll cut off at an hour. Feel free to participate and as long as we don’t have too many people, we can all discuss the article and try things out live.

    For security, I’m not directly posting details, but here is some of it.