More Power BI API awesomeness, now on PowerShell & SmartCharts

Amazing week for Data lovers & Business Intelligence minds, Power BI Preview is now *globally* available! Be sure to check out the public announcement  for details. And Chris Webb’s BI blog for additional insights.

By now you know that there’s a new & strong developer story for the new Power BI,  and even if at the moment we have a simple API to test drive, it already allows to think of interesting scenarios. Particularly for real-time dashboards or  the click to insights pattern  I happen to obsess so much ….

Really, seems there will be no place for data to hide from Power BI :). When Power BI can’t get there… we will bring it to Power BI in a blink of an eye, in real time if needed, all using the brand new APIs.

Don’t have Power BI Designer? or even Excel? and need to quick load  some data to explore? Real time? Check out DevScope github  & use the awesome Power BI API Powershell modules to load your Data into PowerBI directly from the command line (for more info & a cool real time sample don’t miss  Rui Romano blog post).

Not forgetting we already have NuGet Power BI api modules for .Net, Xamarin,  iOS & Android by Sylvain Pontoreau .  Or more recently new sample web apps for Power BI api scenarios.

Adding that, as of… now :) you can also use SmartCharts to quick send & live sync to Power BI directly from Excel. :)

Start with your data as usual or the sample dataset and just click Send To Power BI.

image

Authenticate & authorize the app for Power BI access,  choose a Dataset and a table name :

image

and your data will be available in your Power BI account ready to more Power BI awesomeness

(note: if using date/time columns we also add some extra time related columns we happen to use in the app).

image

Your data ready to explore in Power BI:

image

image

image

Noting that you should be able to do all this on trial mode without any restrictions.  Got feedback and requests? let us know (there’s a feedback form in the app :) we will get back to you)

Get SmartCharts here, or checkout or other DevScope Labs  Office Apps here.

If you want to know more about the new APIs, some additional recommended reading:

More to come :) stay tuned

Rui

#SQLSaturday Portugal is coming to Porto! tomorrow!

SQL Saturday is coming to Porto for the first time and it’s already tomorrow! Smile  One entire day of free sessions for Data Enthusiasts and Professionals!

SqlSatPortugal team has put everything they got (as usual!), not a doubt that it will be an awesome event. Great speakers & great sessions like you would expect . Topics going from the regular suspects SQL Server, Analytics, Business Intelligence to Mobile Development and PowerShell Tips,  not forgetting the most recent Data technologies from Msft like AzureML!

Some close friends and team mates will be sharing their knowledge also,  Sandro Pereira (DevScope Integration Architect & MVP for Biztalk), José António Silva (R&D Director at DevScope) and Rui Marinho (former team mate at DevScope now pursuing his mobile passion at Xamarin). Inspiring to work every day among such group of amazing minds.

Check out the schedule . Not registered yet? Hurry up!

Celebrating SqlSaturday we are also finishing some updates to our Office Apps and will be covering these and other Data/DataViz related tips on our DevScope sponsored session. Excel will never be the same!

(Expect got some goodies at the end, including invitations to upcoming workshops on Azure Machine Learning I’ll be doing soon )

Just a teaser Winking smile … see you all tomorrow!

Take Care,

Rui

image

image

image

New samples at @devscope #msbi codeplex- II–#PowerShell Dynamic Outliers with #SSAS mining models

Finally… ! Updated our codeproject project with an improved sample for this. :) btw-See the first post on this thread for other Powershell MSBI data driven/dynamic samples & utilities

II-#PowerShell Dynamic Outliers Monitoring with #SSAS mining models

The idea for this sample goes right back to the initial releases of the Excel Data Mining Addin. Now that’s a cool Excel Addin, in fact it’s way more valuable today and it’s a little sad that data mining is even a little bit forgotten in the MSBI stack :( (yes, it is…). The issue with SSAS Data Mining, particularly this addin I think… it  really was ahead of its time! because now, in our always changing data driven world, it’s the time for such agile & self service data mining/data intelligence scenarios.

Anyway, that Addin showed that is was possible to do 100% dynamic outlier detection based only on the given data/schema. Models would be generated in runtime for each dataset.

And so, finally, we had the time to take this concept and turn to PowerShell to make a lot of other data driven scenarios  scriptable & able to run completely automated (ex: scheduled/intelligent alerts/trends/anomalies monitoring).

So, what if we could use this to monitor several data streams we have available in our solutions?  We already have & use several very agile & data driven kpis/reports/datasets… but that still usually involves fixed alert thresholds, what if we would have something looking at these data streams every day and tell us, “hey, here’s something that’s not usual, are you aware?”? (ie something that would do what I usually do, so that I can go on doing several other things… like blogging and reading :) ).

And that’s precisely what the sample scripts does:

  • iterate all the views in a predefined schema (love this pattern for dba/bi monitoring)
  • execute & get the datasets, we used SQL server but you can use any datatable
  • pass the datatables to the data mining module (it will, like the excel data mining addin, create a temporary model dynamically for each table, do the outlier stuff, some nasty hacks of our own, get an outlier probability value for each row
  • then filtering (in this scenario) for today outliers, using a convention styled approach – choosing the first date column available (btw-a better approach would be save the last execution time for each view and use that date value as a date filter)
  • if there are outliers, send the report by mail (using the cmdlets I talked about in my last post)
  • if not, keep quiet!

And anytime I want to monitor another dataset, just create another view in that schema, the script will adapt accordingly… hence the data driven mantra applies:) (data+services mindset to explore in a future post…)

Like this:

A schema (mon) where I can put the data streams I want to monitor for outliers/anomalies.

image

As a sample I use a dataset of SQL Analysis Services processing logs/elapsed times, by object (measure group, partition, dimension,…).

Ensure that I have a date column to allow for proper date/time filtering-after the outlier detection-, (1 month old outliers aren’t that interesting anyway).

 

image

Now imagine that I force something strange in that data stream, changing the started date as so to increase the processing time for that SSAS object (creating an anomaly)…

image

And I run the provided outlier sample script…. :)

image

and…amazingly (remembering that I did not configure any kind of warning thresholds…), my two “simulated” outliers are now highlighted in my mailbox

image

How cool is that? :) The script doesn’t know have to know the data in anyway, anything goes, that’s the beauty of it.

(defending myself :) from possible comments by MSBI team regarding this screenshot… I can only say that I promise to reassess my mobile stack after the “new” release of the MSBI stack for mobile… :)  )

Be advised though that the script is just a sample, there are a –few- known “issues” at this moment:

  • Do not expect perfect results, we didn’t spend much time with SSAS mining model tuning for the moment, and we are limited to the accuracy we can expect to get from analysis services mining models (would be great to get some data mining experts help/feedback!)
  • Some column types can cause the mining model/structure not to be created
  • We had to use a hack… and introduce a outlier probability of 2 when we were getting an outlier probability of 0… (cof, I know, will have to dig deeper as for the reason for this… sure we messed up anywhere)
  • Still missing is the column highlight where the outlier is most probable, that’s possible, the data mining addin does this but we hadn’t time for that yet, sorry
  • several others issues will appear I’m sure…( I did say that’s a sample right? :) )

That’s it for today, browse/get the sample at http://devscopebisamples.codeplex.com !

Note that there’s a bundle download available with Excel Data Insights AddIn, Mail DataTable Reports & this outliers sample.

The “tiny” main script:

# for each view found
$viewName = $reportViewsSchema+”.”+$_.Name

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

$firstDateCol= $data[0].Table.Columns | ? {$_.DataType -eq [datetime]} | select -index 1 | select $_.ColumnName

$dmTable = Set-Outliers -dataTable $data[0].Table -connectionString $ssasconn

write-host “Evaluating OutlierProbability threshold…”
$x=$dmTable | sort-object OutlierProbability -descending | select -index 5| select OutlierProbability

write-host “Highlighting…”
$dmTable=$dmTable | ? {($_.$firstDateCol -ge [System.DateTime]::Now.Date )} | select *,@{name=”SysRowCssClass”;expression={if ($_.OutlierProbability -ge $x.OutlierProbability ){ “highlight” } else { “” }}} | sort-object $firstDateCol.ColumnName -descending

$alertCount=($dmTable | ? {$_.SysRowCssClass -like “highlight”}).COunt
write-host “Today outliers: $alertCount”

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

ps-and don’t tell my boss that PowerShell & Data Mining is doing our work now! ;) We’ll figure out something else to do eventually….

Take care,

 

Rui

2012 Perceptual Edge/Stephen Few Dashboard Design Competition-submission by @DevScope #dashboard #dataviz

So… we didn’t win this one…(we will be back next year!) but we had a great time at DevScope building our very own dashboard version  for this years Stephen Few dashboard competition! Pure fun! :)

The results were published today in Stephen’s blog (congrats Jason & Shamik!) and the post is a must read! And be sure to check the forum were several of the other 91 entries are being posted and discussed, including ours.

I’ll leave you with the amazing DevScope dashboard for the 2012 Perceptual Edge Competition! :) 

(click to zoom)

 

 

As Stephen would say, take care!

 

Rui

[DevScope] There’s a new MVP in town, Sandro Pereira, DevScope Biztalk Specialist

My friend & coworker Sandro Pereira was (finally! Smile ) awarded as Biztalk Microsoft® Most Valuable Professional (MVP). Awesome work by Sandro, check this out:

Sandro  MDSN Foruns Profile :

clip_image001

 2010 Biztalk Blog Review:

In 2010, there were 64 new posts, growing the total archive of this blog to 108 posts. There were 244 pictures uploaded, taking up a total of 19mb. That’s about 5 pictures per week…

BizTalk Mapper Extensions UtilityPack 

BizTalk Mapper Extensions UtilityPack is a set of libraries with several useful functoids to include and use it in a map, which will provide an extension of BizTalk Mapper capabilities.

And all this without ever scratching his leading role on our long list of  complex integration and biztalk based projects.

 

Congratulations Sandro!