Guest interview in Power BI Developer Blog and a new Send To Power BI app for Excel

Be sure not to miss the latest blog on the official Power BI Developer Blog :) , the amazing Power BI team asked us to share some thoughts on our latest work & research working with the new Power BI APIs and capabilities. And we were deeply honored to contribute. How cool is that? :)

Read the full post here.

And celebrating the day :) also announcing that even if you can already use SmartCharts to push & sync data to the new Power BI Preview directly from Excel, you now have a dedicated app just to do that :) a brand new Send To Power BI Office App for Excel.

Give it a test drive and leave us your feedback or review. :) It will be greatly appreciated!

Download the new app from the App store.

clip_image002

For more info:

DevScope GitHub repository
Send To Power BI Excel App
SmartCharts Excel App
DevScope Office Apps
Rui Quintino Blog
Rui Romano Blog
www.devscope.net

Awesome day! :)

Rui

Advertisements

AzureML Web Service Scoring with Excel and Power Query

While drafting some samples & labs for this saturday SQLPort AzureML workshop decided to dig a little bit trying to use some regular AzureML scoring web services (request/response) directly in Excel, using Power Query. :)

So… can it be done? Sure! Advisable? Far,far from it…. but for a quick sample/test scenario it might be handy. :)

Saying again, what I’ll show here is far from a recommended web service usage for AzureML in Excel. It will be slow, probably insecure if you need to distribute your file, and there will be I’m sure better ways to do it with the batch web service mode, soon I hope (without the need to pass through the Azure marketplace),

Side notes:

    How it works

    To the point, you can use the Power Query Web.Contents function to invoke the AzureML web service api. As long as you are careful on: 1) using post and not get, 2)setting the authorization header expected by azureml, 3)setting the content type to json  4) building the json request like shown in the api help page.
    PowerQuery will handle the json response automatically..We encapsulate the web request in a function, we can then use it to build our scored table passing the score input column values and getting the score in return.

Step by Step-From AzureML to Excel Power Query Scoring

You’ll need an AzureML  scoring web service, I used a simple two class log regression model to classify the well known Fisher Iris Dataset (with only two classes, as the original as three):

image

The AzureML Experiment & Web Service Experiment, can’t get much simpler than this.

Training Experiment

image

Web Service Experiment

image

Publishing the trained model as a Web Service

AzureML-Iris Classifier Web Service API Help page

image

this JSON request will be needed for Power Query, same thing as the service key:

image

In Power Query you’ll then create a function like this (text version below):

image

image

image

Invoking the Power Query scoring function

Then you just import the original table to Power Query and add a custom column invoking the new scoring  function

image

Note: to test this it is advisable to filter the top 1/2 rows first, it is not lightning fast…. Smile

image

The result should now be visible, a little bit more Power Query expanding and renaming and that’s it!

image

image

To check that I’m getting the expected score, just used another small tool (more on this later)

image

Power Query results

image

Scoring the whole 100 rows iris dataset

The problem using the regular Power Query web.contents function  is that you will be receiving api usage threshold errors from the AzureML API very, very soon. So using another trick inspired (again) on this post by Chris Webb (amazing tip by Alejandro Lopez-Lago), to add some delay between requests.

Just remove the top filter and refresh the query. Wait between 30 secs-60 secs (this will depend on the kind of endpoint you are using, AzureML is changing this whole “stag/production web service model “ right now from what I’m seeing)

image

note: think the latest Power Query update now shows the row count progress, neat! perfect for this Smile

image

image

image

Some misses there Smile guess the model needs some additional training!

image

Download the sample here, (note: will need your own AzureML endpoint & key, you do know that AzureML has a free tier right? :) ) the full Power Query score function for this model is below.

Take care!

Rui

let

     Score= (sepal_length as any,sepal_width,petal_length,petal_width) =>

let

serviceUri="*yourserviceuri_note:ends with score*",
serviceKey="yourservicekey”,

PostContents= "

{
  ""Id"": ""score00001"",
  ""Instance"": {
    ""FeatureVector"": {
      ""sepal_length"": """&sepal_length&""",
      ""sepal_width"": """&sepal_width&""",
      ""petal_length"": """&petal_length&""",
      ""petal_width"": """&petal_width&"""
    },
    ""GlobalParameters"": {}
  }
}
        
                   
",
      //Define Wait function
    Wait = (seconds as number, action as function) =>
            if (List.Count(
             List.Generate(
              () => DateTimeZone.LocalNow() + #duration(0,0,0,seconds),
              (x) => DateTimeZone.LocalNow() < x,
              (x) => x)
              ) = 0)
               then null else action()
    //Call Wait function to wait 5 seconds
    //then return the current time again
   
,
    Source=Wait ( 0.05, () => Web.Contents(serviceUri,
    [Content=Text.ToBinary(PostContents),
    Headers=[Authorization="Bearer "&serviceKey,#"Content-Type"="application/json; charset=utf-8"]])),
    #"Imported JSON" = Json.Document(Source),
    #"Table from List" = Table.FromList(#"Imported JSON", Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Transposed Table" = Table.Transpose(#"Table from List")
   in
     #"Transposed Table"
    in Score

SmartCharts, SmartPivot & Gantt Chart for #Excel -Latest features & releases @DevScope #Apps

We updated some of our apps and add-ins last week. Brief post introducing some of the new features:

SmartPivot – now with Measure Group/Table Filter in search pane, also Table Report & Filter Tools improvements

A much requested feature now available (should have been in the initial Search release honestly…): SmartSearch pane now allows for measure group/table filtering, much like the regular PivotTable fields browser in Excel.

Download the new SmartPivot release here.

image

Fixed some issues also with Table Report & Filter Tools (both Saved Filters & Filter by list)

image

 

SmartCharts-now with real-time Share feature & Free download during SharePoint Conference 2014

You can now share your charts session, opening it in a browser or tablet/mobile window (disclaimer:some safari/OS issues still to fix). You can then collaborate real time with anyone connected to that SmartCharts session.

Also, you can freely download SmartCharts Task Pane in Office Store during #SPC2014. :) feedback will be appreciated!

Download SmartCharts Task Pane in Office Store

image

 

Gantt Chart & SmartCharts for SharePoint

Soon to be published SmartCharts for SharePoint ;) stay tuned.

image

Gantt Chart for Excel on the other hand is already available in Office Store, and free! :)

Download Gantt Chart for Excel

Gantt Chart for Excel

 

Take care,

 

Rui

To Microsoft Office Apps/Developer Teams: Some requests to improve #Office #Apps Experience–a developer’s view :)

SharePoint conference 2014 is around the corner. The office team & office developer team have been posting some new content on their blog, useful tips for anyone building Office apps for office store. Be sure to check these recent posts for guidance on office apps development:

(also, Office 2013 SP1 was released this week, sp1 new features for sharepoint & office apps api/sdk expected to be announced during the SharePoint conference next week)

Anyway, this post is not for developers, but for Office Apps developer/seller dashboard teams :)

It’s my personal view, after publishing, supporting & improving a few apps (SmartCharts, GanttChart, -a few others coming soon ;) )on how the experience could be improved for end users and developers, hoping to drive a much higher acceptance for Office Apps & the App model in Office365/SharePoint.

So, what have we learned and what would be great to see improved in the Office Apps experience?

1-Apps in the office ribbon are difficult to find (available only through insert/app.. :( )

We frequently get people asking where they can find the app after installing! We need an individual Apps tab, where we could add our favorite apps, our full list of installed apps, quick access to the store & recent apps. And task panes apps  aren’t particularly “insertable” anyway. ~

Sorry, this need an urgent fix! An new Apps ribbon tab!

image

2-No app icons? Except for local catalogs… needs fixing also, why is that? we get the icon in about, but not listed in our apps…

image

Developer/Local Catalogs Apps with Icon

image

Office Apps-no icon :(

(SP1 for Office 2013 seems to bring some minor improvements but far from what I describe above… we get app icons for some apps but you can’t configure the apps that show in the ribbon… currently you will get bing maps and people graph  from msft)

image

Office 2013 SP1 tab for Apps

3-Users can’t get details/screenshots about an app using the Excel built in app browser?

How are they supposed to pay, install & trust an app if they don’t know what its supposed to do?

image

we click try/but/install and the only available info is…

image

come on!? doesn’t make sense when we have rich description and screenshots available in the store.

Also this should be en easy fix by the office team because all we are seeing is a web page from the store. (you can see that page here)

(also note that in this screen there is no way, that I know of ,  of clicking to open the app details in the browser…? you need to click the publisher link and then see all apps…)

4-App analytics in seller dashboard is….well…not great :(

Trust me, the one thing you should do from day one is to configure google analytics, your own custom analytics framework or both (yes, be sure to reflect that in your privacy page).

Office Seller dashboard analytics is not very useful to gain insights on how to tune your app and how it’s being used. Main pain points for analytics in the app seller dashboard (office apps):

  • we get historic data only for the latest 4 weeks :(
  • we only have week totals, not detailed data, not even daily data (no demographics, time, date, browser versions, nothing…)
  • The CSV you can download also has only week total aggregates, not very useful
  • data usually lags 2-3 days behind, and happened recently due to some issues on the office store, even weeks behind current date
  • if you are actually selling apps, there’s no metric whatsoever regarding revenue (you’ll have to wait for the end of month and use very limited tax/payout reports, and even then you’ll have to convert from international currencies to local currency…)
  • paid downloads & free download are bundled together in downloads metric

image

image

Hope all these could be fixed soon, in the world of data, the available dashboards and data are really lacking (being kind here honestly…).

I would gladly skip the built-in Seller Dashboard “dashboards” and exchange all that for the detailed “fact” data on downloads, browser hits. I’ll do my “dashboarding” on that! :)

5-Developer support could be a little better

While developing and getting SmartCharts approved we had several issues with the api, supporting IE9 in Excel web app, strange redirects issues breaking the office API context model, undocumented issues with permissions and data read API calls. I must say that having been in touch with some office apps support teams in this process, we were on your own almost all the time :(. Expect delay for advanced issues, these are the ones difficult to reproduce (ex: excel web app in IE9/chrome).

There a few locations to send feedback (foruns, office sp dev user voice site), but getting through and actually get real help has been a struggle for us. My initial post on addFromPromptAsync /read permission question is still to see an answer, (we just went ahead and requested full read/write permissions instead of read, problem “solved”… but seems a bug/very undocumented to me…)

6-Some bug/issues remain

Some reported issues, that seem to be related to core office app js API  remain,

  • for example excel web app & dates have issues:

clip_image001

  • very annoying issue-some users buy the apps but Excel keeps showing the app as trial, I have seen that happen, there are app reviews from users pointing this issue, and we’ve also received reports from some users with this problem (yet to find a proper workaround… been using  an undocumented one to fix this… ) –
    (note:received two user reports today with this specific issue!…not good…)

That’s it…

Hope this can reach someone on the office teams and help a little bit with some of these issues.  Do you know someone there? can you please pass this along? thanks!  ;)

btw-be sure to check the SharePoint Conference App Awards – People’s Choice! contest. now running into final voting period until February 28, 4:00PM PST. (little disappointed to know that SmartCharts didn’t make it to the final in people awards, wasn’t aware of this voting until very recently :( but hoping we still get a chance on other categories :) )

Take care!

 

Rui

Introducing SmartCharts Data Discovery App for Excel

Finally! :) time to briefly introduce our SmartCharts app for Excel now available at Office Apps Store (download here).

image  image  image image

So, yet another “chart” app/tool for Excel…what’s the point? :) what’s it for?

Well, beyond being a DevScope research project and a place where we will be test driving lots of #dataviz features using the latest technologies, there were some other drivers to build the app:

  • There’s so much data available these days, but still most people can’t even acknowledge that there’s lot of hidden value in data, being it small, medium or big data… so they don’t even start exploring it :(
  • Provide a data discovery tool that keeps user focus on the data, not modeling, not chart designing, just slice, dice & visual data mining
  • There are a lot of patterns for dataviz, but there seem to be a lack of reusable pre-built analytic models for the most simple data intelligence tasks, time series, pattern recognition, drill down views
  • We are building an excessive number of dashboards (really! we are), repeating the same patterns over and over again, same for data mining… can’t we get reusable analytic modules that quickly adapt to different datasets? Data+Services inter-connectable through already available metadata?
  • An Excel app  seemed an awesome way to continue the research work we started with our Data Insights addin few months ago (same core concepts really, we will be back to that eventually ;) )
  • A tool that we can now use to quickly & easily explore small datasets instantly in Excel (under 100k,150k rows) all kind of log tables, log streams, etl logs, usage logs. 
  • (yes, do not expect much value if you’re using it with small tables, or tables without time/date fields) it’s a data discovery tool, not a chart designing tool

All these are of course “experimental hypothesis” that we can now test and research (and already collecting lots of useful feedback :) ).

And stay tuned, we will be releasing some new features this week ;).

Download here & get us some feedback.

http://www.devscope.net/products/SmartCharts

Finishing this post with a few typical usage scenarios…

… these are from real data , loaded the app with different tables I regularly check (clickstream logs, usage logs, etl logs, ssas query logs,…)

(some values masked, sorry!)

Integration load resource usage (~80k rows dataset)

(side note that the first time I loaded this data on the app I immediately found that I was completely unaware on where integration load resources were being spent, and how that patterns were evolving over time)

image

Integration loads vs extracted rows

image

Drill down view of integration time over time

image

Time of day patterns (integration times)

image

Time of day patterns (rows extracted)

image

Now a completely different dataset(120k rows), obtained from an analysis services query trace table log, instant insights: (again note that no modeling or chart designing was necessary…just click, load & explore)

image

 

image

image

 

http://www.devscope.net/products/SmartCharts

Take care!

 

Rui

SmartPivot new build – now with Saved/Quick Pivot filters and Filter by List improvements

Fresh new build for SmartPivot users, download here as usual. This version features:

  •  Improvements on Filter by List feature (interestingly one of the features mostly mentioned by users)
  • And (finally!) a new Saved/Quick filters feature. :)

New Quick/Saved Filters feature

This feature allows saving filter selections on the pivot for later use on other pivots targeting identical hierarchies. The saved/quick filters are persisted locally in user profile configuration, so that you can always refer to them very quickly.

image

Ex: filter your pivot as usual, or through SmartPivot fast search

 

image

 

Now, pick the Quick/Saved Filters option to save your filter:

 

image

 

image

Select your Pivot field and Save Current Filters option:

image

Now just create any other connection to the same cube, and use the quick/saved filters to apply the exact same selection as before.

(as most features in SmartPivot you can fast search saved filters )

image

double click and your previous selection is now filtering the new pivot:

image

Improvements in Filter By List

Filter by list feature now works as a task pane (expect most of SmartPivot dialogs to be migrated to task panes as they allow for better usability).

Improvements in this release:

  • Available as an Excel Task Pane
  • Pick values from range
  • Filter by member names or member keys

image

Other improvements

Fixed an issue with table reports and orphaned calculated measures (undefined measure groups)

Wrapping up

Download SmartPivot through the product page here.

 

Take care,

Rui

An alternative model for faster Excel Self Service BI–Excel Data Insights Addon– Part I

Finally some minutes to share my thoughts & some light into what I presented this last saturday at SqlSat Portugal . I had a great time doing this session (as expected, public speaking can turn into an addiction really, particularly for shy folks and introverts like myself :)… thrilling experience…  ).

Feedback was great and some people were even a little doubtful :) of what was really going on under the hood… for such a fast/single click to insights experience/self service BI with Excel 2013. :)

The presentation slides are available here, you can browse through them online. They are not very extensive but they should guide through  the main topics:

the why – in the age of data, big data, and vast diversity of key performance improvement indicators –or better, datasets-, the need for more agility, productivity, an instant/single click self service BI with Excel 2013, PowerPivot and PowerView is needed (IMO)

and the “what”a somewhat “twisted” self service model/concept that loads your data into existing reusable powerpivot/powerview templates, supported by a new Excel addin (concept sample/preview) that for now goes with the name of Data Insights

Have to say that all this is currently at proof of concept/sample level. Although I am very happy, even surprised, with the results, and yet to discover a big showstopper. Being the reason to fully share this soon, to allow for anyone to contribute and we can move the idea further. Some help from the PowerPivot/PowerView product teams would be particularly helpful as I think this kind of models could be near perfect with a few adjustments in powerpivot/excel/powerview core modules. (also could enable a much wider adoption of data usage & analytics with Excel 2013… just saying…)

The concept is very simple to grasp from an end user perspective, just grab your data (database connections, data explorer, whatever), put it in a regular table:

image

and click Explore data in the Data Insights Addon Excel ribbon:

image

Wait a few seconds (currently I’m not totally happy with performance, part due to the sample addon data handling code-that we can tune a little bit-, part for the excel data model/powerview loading/refreshing times-that will be hard for us to tune if not impossible ;) )

image

Either way, a click and 10 secs to 40 secs latter (depending on the data), we get a full PowerView/PowerPivot model to explore, prefilled with dashboards, commonly used dimensions & visualization patterns. We can easily change what measure to focus on, reusing the same visual & interactive patterns.

image

 

(as a side bonus you don’t even need PowerPivot enabled for this, although PoweView is a must honestly – note to the product team: startup times could benefit a little tuning!)

The original table is added to the model, so you can use all your fields and further customize the model & dashboards, (ex: search). You’ll have both conformed/prebuilt dimensions like date, time, categories, pictures,  together with your original data.

image

image

This was for sales data, the same single click experience can be used to get the same data insights experience for any other dataset as longs as it shares some common concepts like date/measures/categories/images.

Take tweet data for example, a table with tweets in a few msbi hashtags, with users and pictures:

image 

Another click on Explore Data, and:

image

image

Finally can get some insights when’s the best weekdays and time of day to tweet and blog :) : (although the dataset is very small to jump to clear conclusions…)image

image

image

 

This is self service that makes sense to me. Will it target all self service scenarios? Of course not! Are there shortcomings? Yes, they are, more on that later. But for a lot of end user scenarios, simple and small datasets, this would be just perfect! Finally, not more date tables, time dimensions, wasted effort doing the same models over and over again.

All this works from a template based model, so I can edit once directly in Excel, and reuse many times with all kinds of datasets:

image

So here it is, I think there’s something to explore here… what do you think? Trimming the sample & code, few tweaks missing, to share soon.

Final thanks to DevScope BI Team, Rui Romano for the excel addin draft code, Jorge Esteves for the dynamic PowerPivot model, Sónia Gomes for the AddIn icon and help with layout, and Jason Thomas, even if he’s not aware :), for his  work on dynamic measure & group selection. (we were very confortable with this on regular SSAS cubes, the posts by Jason were helpful bringing the concept to powerpivot/dax) . The work by Tom Gleeson at his blog (Gobán Saor) is also of particular relevance to this concept. This one for example :) will probably be very helpful taking this concept further…

Take care,

Rui