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.
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.
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.
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.
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.
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.
Chris Webb digs into MDX query serialization.
The Serialize Results Begin event marks the point where SSAS starts to construct the cellset returned. The Serialize Results Current events that immediately follow it, with EventSubclass “1 – Serialize Axes” show SSAS serialising the tuples that are present on the Columns axis (listed as Axis 0 in the TextData column), the Rows axis (Axis 1) and the Where clause (Slicer Axis). The numeric values in the ProgressTotal column for the Serialize Results Current events shows the number of tuples on each axis: the two tuples on columns are the two measures, the six tuples on rows are the six years, and there’s one tuple on the slicer. After that SSAS gets the data for each of the cell values (as shown by the Query Subcube Verbose event – note that this query is running on a warm cache) and there is then a Serialize Results Current event with EventSubclass “2 – Serialize Cells”; the ProgressTotal column shows that twelve cells (2 columns * 6 rows) in total were returned. The Serialize Results End event shows that SSAS has finished constructing the cellset and not surprisingly it’s followed immediately by the Query Cube End and Query End events.
There are a couple of interesting things to note about this query. First, SQL Server Management Studio on my laptop says that it takes nine seconds to run, even on a warm cache; the Duration column for the Query End event in Profiler, however, shows a value of around six seconds. The three second difference must be the time it takes for SSAS to return the cellset to SQL Server Management Studio, and for SQL Server Management Studio to render the results (my guess is that it’s the second operation that takes the majority of this time – other client tools may be more efficient at rendering large resultsets).
I have so little experience with MDX that this is a wide world of amazement to me. So it’s very nice that Chris is digging into this and peeling away some of the mystery.
During a recent engagement involving an Analysis Services health assessment, one of the things I noted was that the Flight Recorder was enabled. During the post-assessment review with the client, where we walked through the report and recommendations, I quickly explained what the Flight Recorder was, why it should be disabled (answer: never used, wasted resources, etc), and that disabling it would not produce a noticeable performance gain – the recommendation was mostly out of habit.
Bill’s answer is to build custom performance monitoring.
The only thing missing was SSAS. After watching Chris Webb’s video tutorial –Cube Deployment, Processing and Admin on Project Botticelli, I decided it had to use Microsoft.AnalysisServices.Deployment.exe. After a bit of scripting and testing, I managed to write a PowerShell that updates the xml config files for the deployment – it sets the ProcessingOption to ‘DoNotProcess’. It updates the Data source – where the cube will refresh the data from. The script isn’t perfect. For starters, what if you have more then one data source? Also what if your not using SQL Server 2014? Still the great thing about open source is that other can update it. Anyone can improve it, its not reliant on me having free time. So hopefully by the time we move to SQL 2016 someone will have already updated it to work with SQL 2016.
A big part of product maturation is automated deployment. Good on Matt for introducing that to the community.
For query workloads, we can see important information about every single query that hits the system including details such as the total duration of the query, query text (MDX/DAX), start and end times, as well as the associated user account. We can also determine details as to how the query was executed such as the number of partitions scanned, aggregation hits/misses, cache hits/misses, other queries running at the same time, etc…all of which have an effect on the performance of any one particular query. A secondary benefit is that we’ll be able to identify the usage pattern(s) of folks using the cube. For example, is usage low/moderate throughout the week with a heavy spike on Friday mornings?
Bonus note: it looks like there will be an xEvents for Analysis Services GUI in SQL Server 2016.