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/.
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.
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
Measures.ParameterLevel} ON 0,
,[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
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.
Handy link to download the Skydrive Desktop App for Windows plus downloads for all other devices supported …
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 ;
@login_name = N'DOMAIN\ACCOUNT',
@proxy_name = N'PROXYNAME' ;
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'
SELECT 1 from [?].sys.tables
WHERE ''?'' like ''%partialsearchstring%''
SELECT * FROM @vTable ORDER BY name
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.
Likewise restore your Analysis Services database with xmla as per the following example