How can I obtain a list of a SSAS Cube's dimentions and dimentions' attributes

Question

I have a user asking me to list the dimensions and dimension attributes of our SSAS cube.

I susspect I could generate this via AMO, but I'm wondering if there's an MDX query or SSMS option to show the same info.

Any ideas?

Answer 1

Here's a script via AMO

[Reflection.Assembly]::LoadWithPartialName("Microsoft.AnalysisServices") | Out-Null
$server = new-Object Microsoft.AnalysisServices.Server
$server.Connect($serverName) 
foreach ($db in $server.Databases)
{
    Write-Host $db.Name
    foreach ($cb in $db.Cubes)
    {
        Write-Host "`t" + $cb.Name
        foreach ($dm in $cb.Dimensions)
        {
            Write-Host "`t`t" + $dm.Name
            foreach ($at in $dm.Attributes)
            {
                Write-Host "`t`t`t" + $at.Attribute
            }
        }
    }
}

Answer 2

In SQL Server 2008, you can do the following:

1. Connect to an Analysis Services server.
2. Right-click on a cube, choose New Query -> DMX.

3. Run the following query

   select Dimension_Unique_Name, Property_Name, Property_Caption from $SYSTEM.MDSCHEMA_PROPERTIES where Cube_Name = [YOUR CUBE NAME HERE] And Property_Type = 1

(You can also do select * and get some other information like data_types, cardinality, etc.)

1. The whole list of OLAP Schema rowsets can be found at http://msdn.microsoft.com/en-us/library/ms126079.aspx

Answer 3

I had the same issue and I've created a .NET CLR for SQL server that returns a list of SSAS objects.

You can find it here: http://sasokoren.com/export-list-of-ssas-objects-and-translations-using-amo-in-net/