CODEWITHSUNDEEP
ssasolap
Scott Weinstein
Scott Weinstein

Reputation: 19117

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

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?

Upvotes: 2

Views: 9933

Answers (3)

saso
saso

Reputation: 802

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/

Upvotes: 1

Kyle Hale
Kyle Hale

Reputation: 8120

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

Upvotes: 2

Scott Weinstein
Scott Weinstein

Reputation: 19117

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
            }
        }
    }
}

Upvotes: 3

Related Questions