Add Power BI Dashboards For Data

[jacobgexigo]

Create Power BI reports from the data repository to allow for modern and customizable visualizations that could be shared via the .pbix file. This file could then be customized and published by anyone to their own PowerBI.com environments.

[jacobgexigo]

I am happy to get started on some mockups to see how this could be addressed

[DavidWiseman]

Hi that sounds like a good idea. I know a couple of people asked about Power BI for DBA Dash at SQLBits. I don't have any experience in this area so I wouldn't be able to take on the work myself. If you are able to take a look at it that would be great.

The GUI uses stored procedures for everything so you might be able to reuse some of these for Power BI. Here's a few examples: dbo.CPU_Get dbo.IOStats_Get dbo.PerformanceCounter_Get dbo.PerformanceCounterSummary_Get dbo.Waits_Get dbo.ObjectExecutionStatsSummary_Get dbo.ObjectExecutionStats_Get

If you have any questions about anything, let me know.

Some additional background:

The user interface for DBA Dash started off with some SSRS reports. I later created a Windows GUI app to improve the user experience and add some functionality that wasn't possible with SSRS. There are definitely some trade-offs with a Windows GUI though - one of those been accessibility of the data. I'm guessing Power BI might help with that and is probably a better experience than SSRS. The old SSRS project is still in the repository if anyone wanted to use it or help maintain it. I've stopped working on it in favour of the Windows app though.

[PascoBOCC-JasonBoswell]

I am happy to get started on some mockups to see how this could be addressed

Have you been able to make any headway with Power BI dashboards?

[Jacob-Krueckl]

Just going to chime in and say that this is a great idea and would be extremely helpful in our use-case of DBADash.

We have many clients that we help monitor their systems and being able to provide them a report on request has been a challenge.

Theoretically we could create a SQL account that has access to only their statistics and expose our server to their network but then they would have set up the GUI. If I could view a dashboard and forward it to them then that would be VERY helpful!

[DavidWiseman]

Just going to chime in and say that this is a great idea and would be extremely helpful in our use-case of DBADash.

We have many clients that we help monitor their systems and being able to provide them a report on request has been a challenge.

Theoretically we could create a SQL account that has access to only their statistics and expose our server to their network but then they would have set up the GUI. If I could view a dashboard and forward it to them then that would be VERY helpful!

Thanks for the additional data point. This is a popular request. At the moment I'm not using Power BI for anything and I have zero experience with it. That could change in the future, but if anyone is interested in contributing PBI reports for DBA Dash I would consider pull requests.

[hassan-sabirin]

I gave this one a go. One of the challenges I'm facing is that the timestamp of collected data is in UTC. Understandably DBADash users would be in different timezones (some with daylight saving requirement). This is not easy to get right for every TZ and DST variations. I'll explore if the TZ conversion can be handled on the Power BI side instead of customising the DBADash views.

[Jacob-Krueckl]

It appears that Power BI has limited support for managing time zones (TZ) and daylight saving time (DST) adjustments. Despite its capabilities, Power BI doesn't correctly handle the DST offset as demonstrated in the simple SQL query:

SELECT GETUTCDATE() AS UTCDateTime

Although I am located in London, and it correctly identifies the time zone, it fails to apply the current +1 hour DST adjustment. Here’s an example demonstrating this issue: One could manually adjust this in the data source; however, this approach lacks robustness and may not be sustainable for dynamic time zone changes.

[jacobgexigo]

From a design and use perspective, I think it's acceptable to have a slicer or report filter to allow the user to select a timezone or offset to apply to all dates. It's either that or do a parameter that the user can change to select the offset and have it all done in Power Query on the way in.

[Jacob-Krueckl]

The DBADash client handles it through a drop-down in the top right however I'm not sure its possible to have a slicer in PBI that actually alters the data itself.

Parameter is all fine and dandy for local pbix file usage, however once you publish the file to the PBI service you lose all control and you are at the mercy of whoever published it. Not to mention that DST goes in and out of season leaving you the requirement of re-publishing the report every x months.

To be honest, I'm not sure that the DBADash client itself even handles DST now that I am looking at it.

[DavidWiseman]

I can't comment on Power BI but usually, it's better to do time zone conversions in the app IMO. The DBA Dash GUI allows you to switch the timezone and most of the conversions are done on the app side. This is typically done using TimeZoneInfo.ConvertTimeFromUtc which should handle DST correctly. Most of this is done here

That said, there are other places where I've opted to do the conversion in SQL. For example, if I'm aggregating data into days etc - if I do the aggregation in SQL and the timezone conversion in the app - the day boundaries align to UTC instead of the local timezone. If I do the timezone conversion in SQL along with the aggregation then the day boundaries align with the selected timezone. This isn't a big deal when looking at long-term trends and for the newer query store reports I opted to do it the same way as Microsoft (Aggregates in UTC then converts to local timezone). Sometimes, timezone conversion is done based on UTC Offset - so DST might not be correctly handled in all cases. I'm open to PRs if anyone finds any timezone/DST bugs and wants to fix them.