New samples at @devscope #msbi codeplex- I–Quick Dynamic #PowerShell Mail DataTable Reports

Just uploaded a few more BI samples at our codeplex MSBI samples project. This time some purely data driven PowerShell cmdlets & samples for sending data table reports and… :) detecting outliers (dynamically & at runtime) using SSAS data mining temporary models (very similar to what Excel Data Mining addin does in fact).

Usage for this scripts  is mainly for agile monitoring & alerting needs. Be advised that the outliers reports is work in progress though – really just a concept sample.

btw-Powershell scripts/samples credits go to Rui Romano, impressive as always :)

I-Quick, Agile, Data driven, dynamic, mail reports from your queries

image

Just produce a DataTable anywhere with PowerShell (useful Invoke-OLEDBCommand cmdlet also in the sample)

Then send it quickly by mail, no UI binding needed, no reports, your data is the report. Note that table formatting is a little tuned beyond the PowerShell default html tables. You can highlight, there’s also a more data friendly handling of numeric values and some others.

Import-Module .\DevScope.BI.Samples.PowerShell.psm1 –Force

$data = Invoke-OLEDBCommand -connectionString $connStr -sql “select * from $viewName”

    $html = Out-Email -subject “Alerts in $viewName : $alertCount” -inputObject $data -to $mailto -from $mailfrom -smtpServer $mailsmtp

The fun starts when you combine this with a complete agile/data driven mindset. For example, mailing every view in predefined schema as long as it as at least a row with a column alert flagged to 1. That’s precisely what the sample script provided does (and our preferred use):

$reportViewsSchema=”mail”

Invoke-OLEDBCommand -connectionString $connStr `
    -sql “select Name from sys.views where SCHEMA_NAME(schema_id) = ‘$reportViewsSchema'” |%  {
   
   # for each view found
    $viewName = $reportViewsSchema+”.”+$_.Name
   
    $data = Invoke-OLEDBCommand -connectionString $connStr -sql “select * from $viewName”

   
    $alertCount=($data | ? {$_.Alert -eq 1}).COunt
    write-host “Alerts found: $alertCount”

    # send mail only if alert column is present and its 1
    if ($alertCount -gt 0)
        {
        $html = Out-Email -subject “Alerts in $viewName : $alertCount” -inputObject $data -to $mailto -from $mailfrom -smtpServer $mailsmtp
        }

}

 

Mix this with SQL DMVs, SSIS Logs, SSAS Logs & whatever info you gather for monitoring/operations your BI/other solution, result: pure fun & almost effortless insights! :)

1) prepare your data

image

2)configure your scripts, edit the ExecConfig.ps1 for settings, and

image

or even better :)

image

 

That’s BI for the BI team, as we don’t actually have a budget for that really. :) We need the insights though!

Just download the scripts at devscopebisamples.codeplex.com. Would be great to improve and get some feedback!

image

Next post: Dynamic Outlier Detection with PowerShell and SSAS Mining models!

 

Rui

Advertisements