An alternative model for faster Excel Self Service BI–Excel Data Insights Addon– Part I
March 18, 2013 1 Comment
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:
and click Explore data in the Data Insights Addon Excel ribbon:
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 ;) )
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.
(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.
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:
Another click on Explore Data, and:
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:
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…