locked
Multi-Dimenshional OLAP PivotTable for web browser. RRS feed

  • Question

  • What is the best approach for delivering an OLAP sourced PivotTable to the web browser? Excel on-line can not handle this task. You can see the pivot but as soon as the data needs to be refreshed it dies. I've been told Power BI via SSAS Enterprise Gateway will deliver OLAP data to a browser but Power BI doesn't have a Pivot Table.

    With the availablity of msmdpump.dll and the whole HTTP pipeline to execute MDX/MDX queries and get cube data back in XML, it's mind blowing Microsoft doesn't offer a solution for this. Perhaps I'm missing something? Any good third-party tools?

    Crtieria is must be entirely browser based (no client side plug-ins required), must use HTML/HTML 5 rendering. 

    Wednesday, March 9, 2016 12:12 AM

Answers

All replies

  • In SharePoint we use ExcelServices for example for Adhoc Analysis. We also use XlCubed Webedition for this stuff. BUt there are a lot of Tools. We are also using msmdpump for Access.
    Wednesday, March 9, 2016 10:54 AM
  • +1 for Excel Services.
    Wednesday, March 9, 2016 2:12 PM
  • Hi rmz,

    You can also use SSRS SharePoint integrated mode to open connection to multidimensional model. You can use a matrix in SSRS or POWER BI to render your data. This visualization works same way as Pivot Table. Please see:

    Visualization types in Power BI

    Regards,


    Simon Hou
    TechNet Community Support


    Thursday, March 10, 2016 1:43 PM
  • I found this article.

    https://support.office.com/en-us/article/Use-a-PivotTable-report-to-make-external-table-data-available-in-Excel-Services-158b812a-79d2-4c2e-95a6-069cf2588fce

    The workflow it describes is a power user designing a PivotTable report, "flattening" the report to a two-dimensional table and publishing that report to Excel Services.. I have had a look at Power BI's matrix visualization widget as someone told me on another forum "it's just like a pivottable" well, it's really not. As with the workflow for Excel services. The process involves a power user manipulating the pivottable to get to a "finalized" two-dimensional subset or a fixed multi-dimensional view. I'm not seeing a way the "published" report to SharePoint via Excel Services or SQL Reporting Services or to Power BI, regardless how or where it's published exploring all possible workflows it seems Microsoft has absolutely no answer for a scenario to deliver to the end-user (END USER not a power user responsible for building and publishing reports) the full OLAP pivot table manipulation capabilities found in the desktop Excel client.

    To be perfectly clear- For the scenario I'm looking at, I do not need to derive a report from a Excel OLAP PivotTable, or perform a similar operation though PowerBI with a Matrix visualization. I need to take 100% of the interactive functionality of the Excel OLAP PivotTable and make it available to end users through a browser. The UI for this scenario needs to be a solution that treats both Windows and Mac OS X users like first class citizens. Ideally it would be pure HTML 5, but I suppose a Silverlight based implementation would work.

    OLAP was designed to provide an enormous amount of ad hoc power on demand to the end-user. Not only that, Microsoft has built the gateway needed to achieve this through msmdpump.dll/IIS configuration. The back-end plumbing to accommodate this with not just SSAS Tabular but also SSAS Multidimenshional data is in place, but it is apparent as a company Microsoft has no solution for the front-end. The solutions all involve a middle man and providing a reduced, two-dimensional table set (or perhaps a pivot table set with greatly reduced interactive functionality).

    It appears Microsoft cares very little about OLAP and is perhaps strategically moving away from it for BI. I'd love to know what a good replacement is because we've tried using Power Query it's just too slow for the amount of data we're crunching and we need the ability to perform pre-processed calculations.

    Speaking only for myself now, this scenario spotlights why I have become hesitant recommending Microsoft solutions. There's a lot of evidence something you'd expect would exist does (e.g. fully functioning web based OLAP Pivot) so you infer that it does and when you ask Microsoft support if there is a way the answer is always "yes" then when you dig in the details are often quite horrible and not at all what you wanted. That is a pattern with Microsoft Enterprise technology I have encountered over and over again.

    As a professional that recommends solutions I see a great deal of personal risk and liability when considering a Microsoft recommended path because of these sort of problems. I also can't help but wonder if features like this are purposely withheld by Microsoft so that a third-party can deliver a product to offer the functionality (business partner deals) that would not surprise me at all

    Given all of this information it looks like I have to explore open source solutions. Possibly Hadoop-Hive-Kylin, the Apache stack actually delivers this (with web based pivot) 100% license free and Microsoft with all of it's moving parts can't even seem to get their enterprise teams on the same page to provide something as basic as an web OLAP PowerPivot.I see Microsoft seems to know this is a great option since Microsoft has built that stack into a VM product called Azure Data Warehouse. Azure Data Warehouse essentially takes the Apache open source "big data" stack and puts it on a Linux VM which is then billed at a significant premium... Of course you could stand up your own Ubuntu instance in the cloud and install the Apache big data stack youself but then you'd loose compatibility with tools like Power BI. Its crafty and desperate, but it won't work long term. It only make me want to cut the chord from Microsoft all together.





    • Edited by rmz Friday, March 18, 2016 3:53 AM
    Friday, March 18, 2016 3:43 AM
  • Hi Rmz,

    I agree with you that it would be natural for Microsoft to offer an HTML 5 client. (Maybe they don't want to cannibalize Office sales.) However, there are a myriad of good third party solutions that do offer thin client solutions. It is not difficult, I wrote one (ThinSlicer) back in 1998. There are more sophisticated commercial offerings now such as ReportPortal, Cognos, Panorama, to name just a few. If you don't think any of these are good enough, then you might consider developing one and commercialising it.

    Hope that helps,


    Richard

    Wednesday, March 23, 2016 3:34 AM