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.

Monitoring SSAS Using Profiler

Chris Webb has part 2 of his SSAS multi-dimensional monitoring series:

What’s clear from these examples is that trying to relate what’s going on in the query to what you see in Profiler is quite tricky even for seemingly simple queries; for most real-world queries it would be almost impossible to do so with total confidence. That said, when I’m tuning queries I usually comment out large parts of the code to try to isolate problems, thus creating much simpler queries, and I hope the value of this post will lie in you being able to spot similar patterns in Profiler to the ones I show here when you do the same thing. In part 3 of this series I’ll show you some practical examples of how all this information can help you tune your own queries.

Whenever I read Profiler, my next question is “Is there an extended event which covers this?”

SSAS Auditing

Matt Smith links to Analysis Services auditing details and includes an audit table:

There are various types of Auditing in the Microsoft BI stack. There is auditing in SSRS, SharePoint, SSAS and not forgetting SQL has its own auditing.

Today I am looking at the SSAS auditing – you can find out more about it onTechNet.

Olaf Helper has published some TSQL code for querying the audit data -on theScript Center.

Just because it’s in a cube doesn’t mean we shouldn’t be able to audit it.

SSAS Tabular In DirectQuery Mode

Melissa Coates digs into DirectQuery mode on Tabular models:

When developing an SSAS Tabular model, you can choose one of two options for handling the underlying data:

In-Memory Mode (aka Imported Mode).  Stores the data in the in-memory model, so all queries are satisfied by the data imported into the Tabular model’s storage engine. This requires the model to be processed for updated data to become available for reporting. This mode is conceptually analogous to MOLAP in SSAS multidimensional models (though the SSAS architecture differs significantly).

DirectQuery Mode. Leaves the data in the source and sends the queries to the underlying database. In this case there’s no processing and SSAS serves as a semantic model to improve the user experience. This mode is conceptually analogous to ROLAP in SSAS multidimensional models (though there are architectural / implementation differences between DirectQuery and ROLAP).

It looks like DirectQuery mode doesn’t fit all circumstances, but there are a few cases in which it makes a lot of sense.

Nested Display Folders

Koen Verbeeck shows how to use nested display folders in Analysis Services and get Power BI to use them as well:

On the same day, I also found out it’s possible to nest display folders in SSAS. Which was even better, because I have a project with dozens of measures and this functionality really makes the difference. All you have to do is putting backslashes to indicate where a new child folder begins

This makes intuitive sense, so good on Microsoft for supporting this.


July 2017
« Jun