13. prosince 2018

Monitoring BI Solution using Power BI (presented on SQL Saturday Prague)

Tento článek píši v angličtině. Téma jsem prezentoval na SQL Saturday Praha též v anglickém jazyce, kde byla značná část publika mluvící jiným jazykem (a nemám na mysli jen bratry a sestry ze Slovenska :) ) Dále již tedy v angličtině.

I will write this article in English and this is because I had a session on this topic on SQL Saturday Prague also covered in English. Significant part of audience was not speaking Czech (and I'm not talking about brothers and sisters from Slovakia only). Therefore rest of blog post in English

Blog post:
When doing anything it is good to have feedback if you are doing right. How to know if you are doing BI right? If your end users are using your outputs they are either happy with them (good for you) or they are using them because they have to (and if not happy with content and/or performance they will usually tell you).
If they have access to report and not using it? There is something wrong about it. Especially if you spent several weeks building output for particular user and now see zero usage. And that is it. Monitoring will tell you if your effort was successful or if you did all for nothing (paycheck will maybe ease your pain, but not completely).
So how do you get feedback about usage of your BI stuff?
By monitoring usage.
If your end user complains about slow SSRS report, what can you do about it? Improve it if you know the reason. How do you get to know reason?
By monitoring report executions. And analyzing performance related data.

In this blog post I will focus just on analyzing SSRS reports usage and OLAP usage (as on SQL Saturday)

To analyze performance and usage you will need 3 tables in ReportServer database.
First of all
dbo.ConfigurationInfo - property ExecutionLogDyasKept needs to be changed from default 60 if you want to analyze data over longer period then 60 days
then you will need dbo.Catalog - list of reports, folders
ExecutionLogStorage - main table containing interesting stuff, can be joined to Catalog by connection Catalog.ItemId = ExecutionLogStorage.ReportId
At the end of this blog post will follow link to sample file created during presentation.
Tables described in data model:
Executions = ExecutionLogStorage
Reports = Catalog
OlapQueryLog = OlapQueryLog

DAX generated tables
Users = DISTINCT(UNION(DISTINCT('Executions'[UserName]),DISTINCT('OlapQueryLog'[MSOLAP_User])))
Measures of interest including DAX formulas to calculate it in blue
I'm interested in number of executions. Could be calculated in DAX as
Total Executions = COUNTROWS('Executions')
Also interested in Distinct Users of reports
Distinct Users = DISTINCTCOUNT('Executions'[UserName])

For performance troubleshooting we can break execution of SSRS report into
Time to return dataset (ExecutionLogStorage[TimeDataRetrieval])
Intermediate (format independent) report format creation contains data and layout, report level formulas ExecutionLogStorage[TimeProcessing]
Rendering (to specific format) - mhtml, Excel, Pdf, etc. ExecutionLogStorage[TimeRendering]

I would be also interested in BytesCount. If this number is high, execution on report server can be already finished on server, but it will take some time to render it on client (BytesCount then sent over network).

DAX Calculated column
Execution Time = (Executions[TimeRendering]+ 'Executions'[TimeDataRetrieval] + 'Executions'[TimeProcessing])/1000
Average Execution Time = AVERAGE('Executions'[Execution Time])
Average Data Retrieval = AVERAGE(Executions[TimeDataRetrieval])/1000
Average Data Rendering = AVERAGE(Executions[TimeRendering])/1000
Average Data Processing = AVERAGE(Executions[TimeProcessing])/1000

RequestTypeID 0 is adhoc execution, 1 is subscription.

OLAP usage analysis
For analysing OLAP usage you can enable loging on SSAS instance for both multidimensional and tabular. To avoid too many queries stored there is default sampling 10. Every tenth query will be stored. But to get general idea it is sufficient.
OLAP Executions = COUNTROWS('OlapQueryLog')

Then I can just create 2 common dimensions for calendar and distinct users (see DAX above). Create relationships between tables.

And create a report

Sample report is available here:

It can be downloaded here

This blog post was about providing resources to attendees of my session on SQL Saturday Prague. To other readers it should give idea how to monitor your BI landscape and get some information about usage and performance related metrics. File shared here is far from enterprise ready, but can be used straight ahead. Just change connections from localhost to your servers and remove last steps in Power Query transformations (I have to fake data, so I used first, last 4 for user names and report names).
If you want something finer tuned you can check out tool by my friends from Joyful Craftsmen (who participated heavily on SQL Saturday Prague). You can check out their tool here
Enjoy and if you have any feedback, let me know. 

Žádné komentáře: