Analysis Services

I saw the question below on the MSDN forum about processAdd not working in AMO 2016 and I thought it sounded strange so I did some investigation:https://socia... When I ran Redgate Reflector over the Microsoft.AnalysisServices.... I came across this little gem: Where it checks if the object is of a type IQueryBinding from the Miocrosoft.AnalysisServices... ......

A few weeks ago I posted a way to manually find and kill long running SSAS queries. In this post I’ll take it a step further and show you an automated solution. The idea behind this technique is inspired by a blog post Chris Webb did some years ago. Chris implemented his solution using SSIS while this version uses PowerShell. You might ask - why create a Powershell version? Well it does a little bit more in that it logs the cancelled queries and sends the user an email. It also uses membership in ......

Recently one of our production Tabular servers was being hit with a number of extremely large queries that were causing excessive load and impacting the performance for other users. These queries were pretty much attempts at extracting detail level information. Some of these were due to the fact that some of the users had their own alternate hierarchy which we had not been informed of, so we were able to extend the cube design to rectify this. Other users were trying to build 10+ page reports in ......

You have designed Aggregations for your cube, but how do you know that they are currently processed? Hopefully you have your processing routines setup in production so that your indexes are always kept processed. But maybe you are working in a development environment or you are performance tuning that you want to double check that your aggregations are currently processed. It is not immediately obvious how you can figure if the indexes for a partition or a set of partitions are processed as this ......

There was an interesting question on the Analysis Services newsgroup tonight which involved a requirement to produce a product of a set of values. ie. value1 * value2 * value3 * .... valueN You could do something like this with a unary operator, but that would change the aggregation for all measures. In order to do this for just a single measure I proposed exploiting a technique I saw Itzik Ben-Gan use in T-SQL. The basic mathematical proof for this technique was the following: logN (val1*val2*...*valn) ......

I have been experimenting a bit more recently to see what I can do with Powershell and Analysis Services. The following small script executes an MDX query using an XMLA connection. I have borrowed the xsl files from one of Chris Harrington's excellent ThinOlap samples. 1 [System.Reflection.Assembly... 2 [Microsoft.AnalysisServices... = new-Object Microsoft.AnalysisServices.... 3 $xmlac.Connect("localhost\s... ......

The following DSO Script lists the size for either all the cubes in a database, or for a single cube. Copy the script out and save it to a file called ListCubeSizes.vbs To run the script issue the following command from a command shell. You will need to be logged on as a user with OLAP Administrator priviledges to get this script to run. cscript ListCubeSizes.vbs <Server> <Database> [CubeName] If your server, database or cube names have spaces in them, surround them in quotes. eg. “Foodmart ......

There have recently been a couple of questions in microsoft.public.sqlserver.... on how to implement a dimension where members can have multiple parents. I did this for a client a number of years ago and thought I would share the technique here. Before I start, I need to stress that this technique does have down sides, it will take longer to process your cubes and having the same members in multiple positions in a dimension can be confusing to some users. If you can implement your design using multiple ......

The DSO script below will list the number of aggregations for either a single cube or all the cubes in the specified database. Copy the script out and save it to a file called ListOlapAggregations.vbs To run the script issue the following command from a command shell. You will need to be logged on as a user with OLAP Administrator priviledges to get this script to run. cscript ListOLAPAggregations.vbs [] If your server, database or cube names have spaces in them, surround them in quotes. eg. “Foodmart ......

This script will backup and Analysis Services 2000 database and timestamp the .cab file with the date of the backup. Copy and Paste the following script into a file called OlapBackup.vbs and execute it be running the following from a command line. cscript OlapBackup.vbs Before running this script in your environment, be sure to update the assignments section. '--------------------------... Name : OlapBackup.vbs' Author : Darren Gosbell ......

News

Legal
Any and all code, software, examples, suggestions and anything else on this web site is available for you to use at your own risk. No warranty is expressed or implied.
Views and Opinions
The views and opinions expressed on this web site are not necessarily the views or opinions of my employer.