Press "Enter" to skip to content

Category: Analysis Services

Monitoring SSAS Using Profiler

Published 2016-03-30 by Kevin Feasel

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?”

Comments closed

SSAS Auditing

Published 2016-03-28 by Kevin Feasel

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.

Comments closed

SSAS Tabular In DirectQuery Mode

Published 2016-03-21 by Kevin Feasel

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.

Comments closed

Nested Display Folders

Published 2016-03-18 by Kevin Feasel

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.

Comments closed

Get Diretory Information For SSAS

Published 2016-03-16 by Kevin Feasel

Jens Vestergaard shows us how to get the Data, Log, Temp, and Backup directories for Analysis Services using Powershell:

Just recently a reply was made to the Connect item, highlighting the fact, that the current values of the Data/Log/Temp and Backup Directories – meaning the currently configured values – is exposed through the Server.ServerProperties collection. According to the answer, only public property values are exposed.

Using PowerShell, we can now retrieve the desired information from any given instance of Analysis Services. Doing so would look something like this:

It’s good to know that this information is available via Powershell.

Comments closed

MDX For Beginners

Published 2016-03-01 by Kevin Feasel

Dan King continues his series on MDX:

So obviously we brought back internet Sales for all years captured into the cube.  So now we need to understand the WHERE clause and how to use it properly in MDX.

The “WHERE” clause in MDX is used to define another slicer and limit the attributes from the defined slicer. The slicer used in the “WHERE” clause cannot be the same slicer used when defining your rows hence why I use the date dimension.  The MDX query below depicts the WHERE clause and syntax:

I liked Dan’s introductory-level presentation on learning MDX, and this blog series is following along those same lines.

Comments closed

Power BI With SSAS

Published 2016-02-19 by Kevin Feasel

Jens Vestergaard’s T-SQL Tuesday entry involves Power BI feeding from Analysis Services:

My story with this half-baked product (the Dashboard you are about to see), is that I needed some way of tracking performance on a couple of Analysis Services (SSAS) query servers. There are a lot of good posts and talks about how to collect and store performance counters and SSAS logs out there, and I suggest you look into this, this or that, if you need inspiration.

The current data set is about 200K rows, as I am sampling each server every 5th minute.

Both of these are valuable tools in a Microsoft BI environment.

Comments closed

Analysis Services Performance Monitoring

Published 2016-02-18 by Kevin Feasel

Bill Anton has a cheat sheet for SSAS performance monitoring extended events:

In this post we ran through the list of Extended Events that you’ll want to collect along with an explanation on the type of performance-related information you can derive from them. Depending on the type of instance you’re monitoring, we’re only really only talking about 5 (multidimensional) or 6 (tabular) events that need to be captured.

If you are in charge of administering Analysis Services cubes, Bill’s series is an excellent way of making sure you’re keeping up on good monitoring practices.

Comments closed

Custom SSAS Assemblies

Published 2016-02-11 by Kevin Feasel

Jens Vestergaard shows us custom assemblies in SSAS:

The example in this post will be the well known Hello World example in the context of SSAS, and I trust this will illustrate the possibilities with this technique well enough, for you to apply your own solution, to your challenges.

If you’re at all familiar with CLR in the database engine, this looks to be the Analysis Services equivalent.  Hopefully it doesn’t have the same “We can’t possibly use this!” taboo that CLR seems to have in the database engine world.

Comments closed

Analysis Services Permissions

Published 2016-02-01 by Kevin Feasel

Jens Vestergaard walks through permission scope in SQL Server Analysis Services:

What this post will not be about: The how to setup basic dimension security in SSAS nor How do you manage Security.

In this post, I will highlight the difference between standard NTFS permission scope and the way SSAS handles Allowed and Denied sets when dealing with multiple roles. So if you define multiple roles on your solution, you should be on the lookout, because SSAS has some surprises.

It’s interesting that allowed permissions take precedent over denied permissions, as that’s not the norm for either NTFS or the SQL Server database engine.

Comments closed