1

I have a software component (the DevExtreme PivotGrid) that supports being bound to an XML/A HTTP endpoint it does MDX queries against, and that's the only remote binding it supports. A nice thing about this kind of binding is that Excel also can have its pivot grid bound against it.

The XML/A endpoint is implemented by a "data pump" IIS module that comes with and binds against a Sql Server Analysis Services instance.

Recent developments in Sql Server has brought some OLAP capabilities to the Sql Server proper: The Columnstore Index, which supposedly is a similar technology as the one SSAS uses in tabular models.

The advantage to have Sql Server proper do this are:

  • No need for a SSAS instance, which reduces deployment complexity.
  • The query results reflect real time data, rather than some nightly snapshot.

However, Sql Server proper doesn't support XML/A or MDX and it doesn't come with such a data pump.

So my question:

  • What's the best way to have such a XML/A MDX endpoint that runs against a Sql Server in a way that makes use of the Columnstore Index?
  • I know there is something called the "direct query mode" in which SSAS defers MDX queries as SQL against a Sql Server instance, but it's not clear whether that works well with a ColumnStore Index. Does it?
  • If there's no way at all, is there at least some kind of endpoint that Excel works against?
John
  • 6,693
  • 3
  • 51
  • 90
  • 1
    Yes, SSAS Tabular model DirectQuery mode works well against columnstores. It won't be as fast as if you cache data in SSAS though. SSAS 2016 is highly recommended for DirectQuery and is required if your tool sends MDX. – GregGalloway Oct 24 '16 at 01:11
  • You can import the data into Excel and then build PivotTables. If that's what you want and need more details post back. – GregGalloway Oct 24 '16 at 01:13
  • @GregGalloway Too much data for an import to Excel (about 10 million rows). It looks like SSAS 2016 with DirectQuery my most sensible option. – John Oct 24 '16 at 01:17
  • 1
    if you have 64-bit Excel you can definitely import it. Excel 2013 and higher have the Excel Data Model and Power Pivot that uses columnar storage like SSAS InMemory mode and like SQL columnstores. You can easily import 10 million rows. What version of Excel do you have? – GregGalloway Oct 24 '16 at 01:21
  • @GregGalloway You're right, it's an option. But I do need to let Excel connect to the sql instance directly then I think. I tried an odata binding once, but that was slow and Excel/PowerBI can't do incremental updates. I'd prefer an http binding, but it's worth considering. – John Oct 24 '16 at 01:29
  • For what it's worth I would look at Power BI. If you install a Power BI Gateway then you can use visualizations in the cloud connected live or refreshed periodically through the gateway. Just my 2 cents if direct connection to the SQL instance is a networking challenge. – GregGalloway Oct 24 '16 at 03:22
  • @GregGalloway Cloud wouldn't be accepted by my customer. I'm in Germany, people don't sent their BI to the cloud here, lol. – John Oct 24 '16 at 04:07

0 Answers0