A Fine Slice Of SQL Server
Gilbert Quevauvilliers teases a new series:
I have been doing a lot of evaluation and investigations for organizations who currently are using Azure Analysis Services (AAS) and looking to see if they can leverage Power BI Premium Per User (PPU)
In this series I am going to cover the following details below, which I completed to see if the migration was not only feasible but should be the new normal.
Looks like it will be an 11-parter, so we have some reading to look forward to.
Comments closedSlava Murygin has two questions and two answers:
This post is just answering two simple questions:
1. Can Quest Software’s Spotlight successfully monitor SQL Server Analysis Server?
2. If it can, what SSAS parameters, databases’ and cubes’ details it monitors and provides information about?
First, it’s good to see Slava back in the saddle again. Second, click through for those answers. Slava also promises to check out some other SSAS monitoring tools, so stay tuned.
Comments closedMartin Schoombee takes a look at using calculation groups with role-playing dimensions:
The 2019 release of Analysis Services (compatibility level 1500) brought about a new feature called Calculation Groups, which makes it easier to apply the same logic to multiple measures without the need to duplicate code. Each calculation group represents an entity (table) with attributes (columns) and attribute values (calculation items), and because of this implementation it can be used to deal with role-playing dimensions as well.
Click through to see how it works, as well as some gotchas to keep in mind.
Comments closedNigel Foulkes-Nock explains why SSAS might not be available even if the service is running:
When starting SQL Server Analysis Services (SSAS) Tabular, the Service is quick to report that it has started. In my opinion, this Status is not entirely accurate – SSAS may be running but you cannot access data until it has loaded all associated SSAS Databases into memory and performed its consistency checks. This can take a long time.
After starting SSAS, if you try to browse the Databases using SQL Server Management Studio (SSMS) then SSMS becomes unresponsive. You will receive errors if you try to query a SSAS Database. It’s busy but it doesn’t report as such and doesn’t give any clue of how long it’ll take.
Read on for the explanation.
Comments closedMore details about it, and how it can be used, are in the samples here:
https://github.com/microsoft/Analysis-Services/tree/master/ASJobGraphEvents
The data returned by the Job Graph event isn’t intelligible if you look at the text it returns in Profiler. However if you save a .trc file with Job Graph event data to XML you can use the Python scripts in the GitHub repo to generate DGML diagrams that can be viewed in Visual Studio, plus Gantt charts embedded in HTML. Of course to do this you’ll need to have Python installed; you’ll also need to have Visual Studio and its DGML editor installed (see here for details).
Read on to see how it looks and Chris’s thoughts on the matter.
Comments closedThat said, I wanted to provide some updates with more of a focus on Tabular mode, as it has clearly become the new standard for how most organizations deploy SSAS. I recommend referring to that original series for a focus on Multidimensional mode, but much of the information provided in this blog post will be applicable to both modes. I recommend referencing Allen White’s blog post on SQL Server counters to learn more about many of the Windows-level counters that apply across the board, as I won’t speak to them here.
Let’s dive into the 15 SSAS performance counters you should be monitoring.
Click through for the set, as well as explanations for why.
Comments closedI know that some of this might be able to be done with other calculation groups. I find I have more flexibility when combining Calculation Groups with a disconnected table.
Below are some of my previous calculation group blog posts that might also be of interest:
Click through for the demo.
Comments closedShabnam Watson shows us how to kill idle SQL Server Analysis Services sessions:
Think of this method as an emergency procedure only. As always, have database backups and try this on a development server first. Always take a backup of msmdsrv.ini before you modify any server properties. The default location of the file is this: C:\Program Files\Microsoft SQL Server\MSAS15.MSSQLSERVER\OLAP\Config
If you set the timeout values too low on server that is under resource pressure, you may not be able to get to the server properties using SSMS and change them quickly within the time you set for the timeout. For this reason, I prefer the user of XMLA in this case which makes the process faster.
Read on to see how to do this.
Comments closedKasper de Jonge takes us through parallel processing of partitions and tables in Analysis Services:
One thing that has come up several times in the last few weeks is a misconception that you cannot process multiple partitions on the same table or tables in parallel as it would cause a lock. This could be true if you try to do the parallelism yourself. Like in SQL Server you need to think about transactions, the AS engine is a transactional system too.
So, the AS engine is definitely capable of loading data in parallel but only if you let him do the puzzling on concurrency. This means you must send processing commands to the AS engine in one transaction so the AS engine can manage the locks itself. There are other benefits of letting AS doing the work like recalculating the calculated items (tables, columns etc) once instead of multiple times which improves processing performance.
Read on for an example.
Comments closedWhen I first started looking at the calculation groups and changing the currency formats, I thought that my existing currency format was correct. Boy was I wrong and once I found that out and corrected it, my Currency Format Strings started working.
As per the Microsoft documentation found here Dynamic format strings for currency conversion I had to make sure that my Currency format followed the following pattern.
Read on for an example and demonstration.
Comments closed