Tabular Caching

Bill Anton discusses using the Formula Engine cache with Analysis Services tabular models:

If tabular data is already in memory, what’s the point of having a cache at all? Memory is memory, right? Both are in main memory and access speed is the same, right?

Good question! Yes, access speed is the same. However, there are other benefits to a cache store.

For example, even though the data is already in memory, queries against the tabular model can still be slow… very slow even… usually in cases where the execution of the query is bound to the single threaded formula engine. To be sure, this is not a tabular specific problem… formula engine bound queries can be (and are commonly) found in both technologies and the issue (usually) speaks more to the deign of the model and/or the way the query is written (be that DAX for tabular or MDX in multidimensional). That said, performance problems related to FE-bound queries can be terribly difficult to resolve as it usually requires a redesign of the data model and rewrite of the query or measure(s) involved.

Bill points out the limitations of this solution, but within those limitations this looks like it could be a huge time-saver for end users.

New Features In SSAS 2016 Multidimensional

Chris Webb takes a look at SQL Server 2016, specifically around Multidimensional Analysis Services:

In fact the drillthrough/multi-select improvements (which I blogged about here) already shipped as part of SSAS 2014 and are reliant on improvements in Excel 2016 as much as in SSAS; similarly the Excel 2016 query optimisations are not reliant on any changes in SSAS 2016 and will benefit users of all versions of SSAS.

So what has actually changed with SSAS 2016 Multidimensional? I don’t know all the details on every change, but here’s what I know right now:

It sounds like the answer is “not much.”  Tabular has been getting more love in Analysis Services.

Excel MDX Performance Improvements

Chris Webb notes performance improvements in Excel 2016 for PivotTables which connect to Analysis Services:

In that post, Microsoft point out that how much of a performance increase you get will depend on a number of factors. I guess they have to do this to manage expectations. However I’m going to come right out and say that these changes are probably the most important thing that has happened in the last five years for Analysis Services or Power Pivot query performance and if you are using Excel PivotTables with SSAS or Power Pivot for reporting, you should upgrade to Excel 2016 (you’ll need the Office 365 click-to-run version) just for these changes.

The key improvement that Microsoft have made here is to stop Excel bringing back unnecessary subtotal values in the MDX queries that it generates. This has been a gigantic problem for years and several people have blogged about it: I did here, and among other people Rui Quintino and Richard Lees have posts on the subject that are worth reading. A lot of my consultancy work involves SSAS and MDX performance tuning and I think I see around five to ten customers every year who are seriously affected by this – and I’m sure this is just the tip of the iceberg. Even if you don’t think you are affected, you may have users who are putting up with slow refresh times for Excel-based reports without your knowledge.

That’s a pretty big statement in bold.  Read the whole thing.

SSAS And The Monthly Release Cycle

Chris Webb shares some thoughts on what the monthly SSDT/SSMS release cycle could mean for Analysis Services:

While getting bugs fixed quickly is great, I really hope that this new focus on tooling means that we get new BI-related features in SSMS and SSDT. SSMS has, in particular, been shockingly bad at supporting BI developers: for example, I find it unbelievable that we still don’t have a DAX query window in SSMS when the language has been in SSAS Tabular since SQL Server 2012 (I know the community have filled the gap with DAX Studio, but that’s not the point). Aaron Nelson recently announced a Trello board where ideas for new features in SSMS can be debated but there’s very little BI-related stuff there at the time of writing.

One can hope.  The big problem is getting enough support to bump items up on the backlog, and for that, there’s a Trello board.

Tabular Image For Azure VMs

Mark Vaillancourt has a new Connect item:

Currently, when utilizing the SQL Server images in the VM Gallery in Azure, any installations of SQL Server Analysis Services default to Multidimensional. Thus, if you want SSAS Tabular, you have additional work to perform.

I was just chatting with a Senior Program Manager on the SQL Server Analysis Services product team. They currently don’t have anything in their plans for providing SQL Server Gallery Images with SSAS Tabular instead of Multidimensional. We agreed that it is a good idea for that to happen. We also agreed that a Connect suggestion would be a great way to gauge broader community support/appetite for providing Gallery images with Tabular installed.

Here’s the Connect item.

MDX Keyword Colors In SSDT

Shabnam Watson notes that MDX keyword coloring is available in SQL Server Data Tools for Visual Studio 2015:

The MDX keywords in a cube script were not properly changing colors in the SQL Serve Data Tools (SSDT) Preview version for Visual Studio 2015. (14.0.60316.0). (See my previous post on this problem here.)

The different keyword colors make it easier to write, organize and read an MDX script inside a cube.

Looks like they squashed that bug.

SSAS Environment Results

Bill Anton is sharing data from a March survey:

Instead of digging into the data and spoon feeding you the results, I’ve created a Power Pivot model (download link) that you can use to explore for yourself. I’d also like to invite you to share any interesting insights you uncover in the comments section and/or provide feedback on this little survey experiment (missing questions, phrasing, etc).

Happy digging.

Adding Perspective Descriptions

Bill Anton shows how to add descriptions to perspectives in SSAS Tabular:

Unfortunately, in Tabular projects, there’s no direct way to add a description for perspectives – not even withBIDS Helper.

In Tabular projects, there is a (modal) popup window for managing perspectives…

The answer is not a great one, so hopefully the SSAS team picks up on this and improves the Tabular experience.

Is Power BI SSAS In The Cloud?

Koos van Strien hits us with an interesting thought about SSAS versus Power BI:

As I’m currently planning to migrate the entire BI architecture of one of my customers to the cloud, this made me think: can we ditch SSAS as we know it already in favor of Power BI? What are the alternatives?

To study that, I’ve put some diagrams together to show the possibilities of moving BI to the cloud. First, I’ll discuss the possible architectures, then the impossible architecture (but maybe the situation I was looking for).

One man’s opinion:  there will be SSAS for Azure.  I have no proof of this, and the nice part about having no proof is that I can throw out wild speculation without fear of violating NDA….  But to me, Power BI solves a different problem and acts in conjunction with SSAS rather than as its replacement.  I also don’t see any technical reasons why SSAS couldn’t live in the cloud, and so that leads me to believe that it will be there eventually.  But hey, it turns out wild speculation is occasionally wrong…


Bill Anton walks through perspectives in Analysis Services:

In an enterprise solution, you might have 10+ perspectives – some of which might have similar names – and without a clear description it will be confusing for a user (especially new users) to know which perspective is the correct one.

A better idea is to add a description/annotation property for each perspective where a more helpful text description can be provided indicating the business process, common types of analysis, etc. This would provide a metadata hook for self-service reporting tools (e.g. Excel, Power BI) as well as enterprise data cataloging solutions such as Azure Data Catalog.

Another helpful feature would be the ability to set the visibility of a perspective – or if you’re more familiar with the Tabular vernacular: “hide it from client tools”!

While you’re reading about perspectives, fill out Bill’s SSAS survey.


September 2017
« Aug