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

“Multi-threading” the Sql Server Analysis Services Formula Engine II-a parallel query msmdpump proxy

Following a previous post (“Multi-threading” the Sql Server Analysis Services Formula Engine – I #ssas #mdx ), we returned recently to the issue of multi threading and SSAS formula engine. See that last post or this post by James Serra for reference.

Honestly it’s kind of amazing that something like the SSAS engine can run incredibly  well using a single threaded model for each query. It’s damn smart, and as long it runs smart you usually don’t need brute force. :)

Until… you find yourself with a very (very) complex cube, together with a rather complex scorecard model also completely built in SSAS/mdx, and…. PerformancePoint with its “with clauses” (and  like Chris Webb pointed before, with clause disables FE cache)

Sample query generated by PerformancePoint (a scorecard model, kpis on rows), running in 50-60 secs.

image

.

To return a complex and FE intensive scorecard with several different KPIs (completely different measure groups), with values/metrics like value, ytd value, prior year, end of year projection, target, ytd target, score and so on… well, requires a not so usual amount of computation by SSAS. Worst, due to the, let’s call it a “reasonable”, :) amount of mdx scopes involved it triggered some internal thresholds i, and it stops being smart… maybe switching to cell by cell mode? Query 3 kpis individually, none exceeds 2-3 secs, get the 3 together in the same query, –> 30-40 secs….

After exhausting all the tuning we could possibly remember… reducing down the query from 1m20 secs, to under 40-50 secs, but still annoying, why all the CPU power if we are waiting for damn 50 secs?

Another thing to note was that every row was a different KPI, completely isolated from all the other rows returned, really a very good candidate for partitioning and multi threading. But SSAS doesn’t do that (query hint would be great SSAS team ;) ),

so….

(and this where I have to say the usual disclaimer, please do this at home, never at work! it’s not supported… aside from tolerating that 50 secs what follows is the worst possible thing you could do…)

We had previously built some SSAS http msmdpump proxies before (it allows you to query SSAS server over http) adding some “extra” features we needed…

So why not trying intercepting that query in a “fake” msmdpump (proxy) built in asp.net, partitioning it by member rows requested, run a bunch of parallel MDX queries against the SSAS DB, get the results, join the cell set together, and return as a SSAS XMLA pump reply….? (kinda…nuts….yes)

And well, far, far, far away from being a reusable module for this scenarios we built it for only this specific one, changed the connections in PerformancePoint pointing to the http endpoint (only for this kpis/scorecards), and gave it a go:

From here, query running to 50 secs, almost no cpu activiy:

image

To here, query now running under 14 secs (multi-threading is very noticeable, obviously you should have spare cpu available, not for a already heavily loaded server ):

image

Short story, how it works?

1-a web app gets the XMLA http requests targeted to the isapi SSAS msmdpump, inspects them for a very specific pattern. everything else passthrough to the regular msmdpump (debug/testing) or blocked (production server, we only need to answer a very specific pattern from performance point)

2-it splits the member list requested in the mdx rows (on rows), generates an identical MDX query for each group of N members

image

3-using .net parallel apis then sends x simultaneous queries/threads to the local SSAS db:

 

image

image

image

4-and the tricky part, getting the result cell set xml for each query, and aggregating that in a unique cellset xmla that then is returned to the original http request made by performance point (you can also fire a man studio mdx query and passing it through the proxy)

Closing Notes

Honestly I don’t even know if it’s a good snippet to share , but well we confirmed that it can work in very, very specific scenarios, as a last resource when you can’t  influence queries being made (tried that also… didn’t work out  so good…) …. If you risk being fired for not tuning that query that the CEO runs every day,  you may want to test something like this… otherwise forget what you’ve read. :)

But if this post happens to generate some requests where it can be put to good use I consider to share it privately or even on our codeplex sample site.

Please be warned that in most cases you won’t need anything like this, SSAS FE engine should work just fine  because it’s very smart (and we are not! :) ), only for very complex cubes and specific scenarios something as risky as this can be possibly useful.

ps-this also opens a rather interesting possibility, spreading the inner requests to secondary servers in a scale out farm…  imagine that working automatically for some queries… will we get this for SQL 2016? ;)

As for the code, its goes something like this (lots of complexity removed, just the core steps):

 

image

image

….

image

 

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

Hot July 2013 for #msbi content–Selected posts & news on #ssas #powerbi #powerview

I don’t remember the last post I wrote just to gather/curate content on #msbi, but these last weeks/days have been so full of good #msbi content, I really can’t resist! …and we still have two days left! :)

Amazing content, read for yourself: (anything missing? just say so, comment below !)

MSFT Blogs & all about Power BI

“Pure Power” :)

Don’t miss the amazing Power BI demo by Amir Metz: Power BI Demo

Power BI Blog

Worldwide Demographics & Statistics using Power Query and Power View by Faisal Mohamood [MSFT]

Office Blogs

Announcing Power BI for Office 365

Microsoft Business Intelligence

Q&A Feature of Power BI

MSFT research

DO YOU KNOW WHERE YOU’LL BE 285 DAYS FROM NOW AT 2 P.M.? THESE DATA-MASTERS DO

Kasper de Jonge PowerPivot Blog

Power BI

 

#msbi community

Chris Webb’s BI Blog

New MDX Divide() Function

Point-In-Time Dimension Reporting In DAX

Defining DAX Measures In The With Clause Of An MDX Query

Some Thoughts About Power BI

 

Jason Thomas Blog

Drill-down Behaviour in Power View

Power BI for Office 365

 

Gerhard Brueckl’s BI Blog

DAX vs. MDX: DataMembers in Parent-Child Hierarchies

 

Blue Granite Blog

Is Microsoft Power BI a Game Changer?

 

CSS SQL Server Engineers

Tracking down Power View Performance Problems by Adam W. Saxton

 

LessThanDot

Rants about Connect by Koen Verbeeck

 

From oralys blog:

Round function in MDX by Hilmar Buchta

PDW: Cube processing experience by Michael Mukovskiy

Considerations for the distribution key for distributed tables on PDW (part 2) by Hilmar Buchta

Considerations for the distribution key for distributed tables on PDW (part 1) by Hilmar Buchta

Caution with SQL Server 2012 SP1 CU4, Bug with nested multi-valued parameter by Stefan Grigat

 

Paul Turley’s SQL Server BI Blog

DAX: Returning the Nth Selected Value in a Slicer

We’ve Got The Power: “Power BI”, New Microsoft BI Suite Announced

 

http://redphoenix.me/

Excluding Analysis Services files from Anti Virus scanning

 

Jason’s Technical Topics

Cache Warming on an Analysis Services Tabular server

 

Byobi

SSRS + MDX: Dynamic Dimension Attribute on Row Axis

Why You Need to Routinely ProcessFull

 

Other selected news & content:

Stephen Few

A new edition of Information Dashboard Design

http://www.idgconnect.com/

Portugal: An Unlikely Business Intelligence Hotspot? :)

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

Fastest way to get a #msbi VM with #sharepoint 2013 & #PowerView Multidimensional? use online MS sales demo VMs

Do you need a clean Sharepoint 2013 #msbi VM with PowerView multidimensional for demos/testing purposes? Don’t want to –or can’t- build one from scratch? and have you access to Microsoft partner resources?

Online pre-prepared #msbi vms&demos  at http://fs.mssalesdemos.com/

Well, be sure to check the MS partner site http://fs.mssalesdemos.com/ for demo VMs. You’ll find at least two fully loaded VMs for #msbi with SharePoint 2013 and several prepared demo content & resources (powerview, powerpivot, excel).

These VMs work extremely well for demoing or testing purposes, they start in minutes and you connect using remote desktop. Performance is very acceptable. And you can save them for later use! :)

Unfortunately none has CU4 applied to run PowerView over multidimensional cubes, but we can solve that very quickly! Just download & install the CU4/(or the most recent CU5) :)

Download and install SQL 2012 SP1 CU4 for SSAS multidimensional support in SharePoint PowerView

I started up an instance of the “SQL Server 2012 BI Demo – Breakthrough Insight (March 2013)” VM. Already has SQL 2012 SP1.

image

Wait until the VM starts, connect using the rdp link:

image

and just download the hotfix inside the VM (should be very fast, 770MB took only a few minutes in my case).

image

Apply the SQL 2012 SP1 CU4 (I updated all instances just in case, took a bit longer). (ps-be sure to stop the World Wide Web Publishing Service service to avoid a restart and closed all Man. Studio and similar apps,

restart the www service after CU4 finished)

image

image

And you’re done!

Create a “Semantic Model connection for PowerView” data source for your SSAS cubes

Now, just create a new data source in Sharepoint (ex: http://intranet.contoso.com/Shared%20Documents/) and open it to start PowerView . You even got some multidimensional models installed (ex: contoso retail).

image

image

Click the created data source and PowerView should open connected to the Contoso Retail cube:

Check the official guide to know more: “Understanding Power View for Multidimensional Models

image

& Save the demo VM for later use on mssalesdemos

And for the better part :), you can then save the VM for later use (just don’t trust it will be there forever… they sometimes expire and disappear without warning…).

For this use the “Stop and Save” option (prepare to wait between 5-10 minutes while the VM is saved).

image

image

The new VM should then appear in your catalog. You can come back later and start from were you left off:

image

btw- if you want to check other demo resources in the VM:

 

http://intranet.contoso.com/sites/bicenter

http://intranet.contoso.com/PowerPivot%20Gallery/Forms/Gallery.aspx

imageimage

Take care!

Rui

Follow

Get every new post delivered to your Inbox.

Join 279 other followers