Analysis Services

How to build your own SSAS Resource Governor with PowerShell
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 ......

Posted On Monday, March 31, 2014 6:04 AM | Comments (0)

SSAS – Listing Active Queries with PowerShell
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 ......

Posted On Monday, March 3, 2014 7:20 AM | Comments (2)

VBScript to backup and timestamp an Analysis Services Database
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 ......

Posted On Sunday, October 2, 2005 6:14 PM | Comments (2)

Running XMLA Queries the Powershell way
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... ......

Posted On Wednesday, June 14, 2006 8:25 PM | Comments (2)

DSO Script to list Cubes Sizes
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 ......

Posted On Friday, February 24, 2006 5:54 PM | Comments (0)

Partial Alternate Hierarchies
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 ......

Posted On Monday, October 24, 2005 8:58 AM | Comments (9)

DSO Script to list the number of aggregations in a cube (or all cubes in a database)
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 ......

Posted On Monday, October 17, 2005 3:06 PM | Comments (0)

DSO Script: Listing all source tables for an Analysis Services 2000 Database
I created the following script in response to a question on the microsoft.public.sqlserver.... news group. Someone had posted a question about how to list all the source tables for an Analysis Services database that they had inherited. Using a one of my other scripts as a shell it was not too hard to pull this together, hopefully other will find it useful also. 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 ......

Posted On Saturday, October 1, 2005 6:50 PM | Comments (1)

Copying OLAP Partition Aggregations using DSO in Analysis Services 2000
As you can see by the dates in the comments below I have had this script kicking around in my tool box since 2002. It was used in the context of the development of a very large cube with lots of partitions. As we tweaked the cube structure during the development stage it would invalidate the existing aggregations, making it necessary to often have to redesign the aggregations. The wizard in Analysis Manager does this on a partition by partition basis and while you can choose to copy the aggregations ......

Posted On Thursday, September 8, 2005 7:22 AM | Comments (1)