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 producing business documentation for the kind people using the cube… Though if you haven’t specified custom captions for your measures, you may want to export the output to Excel then add some metadata to the measure names.

select * from $system.mdschema_measures

This blog is awesome http://dwbi1.wordpress.com/2010/01/01/ssas-dmv-dynamic-management-view/.

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 the dataset.  Remember to enable multi select on the report parameter and set a default to the dataset’s parameter value if you want the default to be all Top 10 members.

WITH
MEMBER [Measures].[ParameterValue] AS [Dimension].[Attribute].CURRENTMEMBER.UNIQUENAME
MEMBER [Measures].[ParameterCaption] AS [Dimension].[Attribute].CURRENTMEMBER.MEMBER_CAPTION
 MEMBER [Measures].[ParameterLevel] AS [Dimension].[Attribute].CURRENTMEMBER.LEVEL.ORDINAL
SELECT {Measures.[Amount],
Measures.ParameterValue,

Measures.ParameterCaption,
Measures.ParameterLevel} ON 0,
ORDER(
 NONEMPTY(

TOPCOUNT(
 [Dimension].[Attribute].Children,
 10,
 [Measures].[Amount]
 ),[Measures].[Amount]
 )
,[Measures].[Amount],BDESC) ON 1

FROM ( SELECT ( STRTOSET(@Parameter1, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( STRTOSET(@Parameter2, CONSTRAINED) ) ON COLUMNS
FROM ( SELECT ( STRTOSET(@Parameter3, CONSTRAINED) ) ON COLUMNS

FROM [YourCube])))

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 for quite configurable toolbars http://www.bryntyounce.com/coolbarz.htm.

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 :

USE msdb ;
GO

EXEC dbo.sp_grant_login_to_proxy
    @login_name = N'DOMAIN\ACCOUNT',
    @proxy_name = N'PROXYNAME' ;
GO

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 little bonus is the script will ignore offline database states.

Declare @vTable Table (name NVARCHAR(256))

Insert into @vTable

EXEC sp_MSforeachdb N'

IF EXISTS

(

SELECT 1 from [?].sys.tables

WHERE ''?'' like ''%partialsearchstring%''

)

BEGIN

SELECT ''?''

END;'

SELECT * FROM @vTable ORDER BY name

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 package Analysis Services DDL task to complete the mission.  In the examples below, overwrite of the backup file is enabled.

 

<Backup xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine“>

<File>d:\sqldata\YourSSASDatabaseBackupFile.abf</File>

<AllowOverwrite>true</AllowOverwrite>

</Backup>

 

Likewise restore your Analysis Services database with xmla as per the following example

<Restore xmlns=”http://schemas.microsoft.com/analysisservices/2003/engine“>

<File>d:\sqldata\YourSSASDatabaseBackupFile.abf</File>

<DatabaseName>YourSSASDatabase</DatabaseName>

<AllowOverwrite>true</AllowOverwrite>

</Restore>