MDX – Select a List of All Measures in Your Cube

To get a full list of measures in your cube, open a MDX query against your Analysis Services multi dimensional database and run the following query.   The output is pretty cool, you get the measure name, measure unique name as referenced in MDX queries, measure group name and format string.    Very useful for […]

MDX – Getting Jiggy with Top 10 Somethings in a SSRS Parameter

You want your SSRS parameter serve up only the Top 10 somethings you have a Analysis  Services Multidimensional cube.  The following sample MDX in your Parameter’s dataset should get you started along the right track.  Simply substitute the dimension attribute you need, your actual measure and the various parameters you might like to feed into […]

Command Line for Snipping Tool

Windows 8 brings a few new ways of accessing programs.   One of my earlier frustrations was where to locate the Snipping Tool.  To access the Snipping Tool from the command go to Start, Run and enter “SnippingTool.exe”.  Of course if you are looking for a better experience you could try something like Coolbarz that caters […]

SSIS Package Error “proxy is not allowed for subsystem”

You attempt to run or create an agent job with a proxy defined and get the error “proxy is not allowed for subsystem”.  Proxies are useful when you want to run an agent job as a lower privileged account.  If you encounter the error above try adapting the following script and execute to resolve :

TSQL to Select Only Databases that have tables sp_MSforeachdb

This piece of code is handy for generating a list of databases that actually have tables, thereby ignoring any databases that have been created, but do not yet have table structures created.  The code also searches for databases of a specific partial search string.  The undocumented stored procedure sp_MSforeachdb is used here and a nice […]

Schedule Backup and Restore of Analysis Services Databases

Backup your Analysis Services databases with xmla as per the following example … together with a SQL Server agent job and optionally a SSIS package, this allows you to perform unattended backups and restores of your Analysis Services databases.  Use either an Analysis Services job step in the SQL Server agent job or a SSIS […]

SSRS – Add a Detail Row to a Matrix

You have a matrix and you want to add a detail row.   One of the reasons could be to display extra detail columns but this is not so obvious in a matrix that works like a pivot table on a budget. For a Tablix, this is fairly straight forward, if for example, you have […]

Insert an Analysis Services Result Set into a SQL Server Table

Connect to Analysis Services database, and insert a result set into a SQL Server relational table using a linked server and a query.  An example is outlined below.  Pay attention to the placeholders you require in your linked server, as well as the data types you define for your table.  If your table’s data types […]