Data Warehousing and Business Intelligence

6 July 2010

AMO: Enumerating DSV Tables and Columns

Filed under: Analysis Services — Vincent Rainardi @ 11:44 pm
Tags:

To list down the tables in the DSV (and views, and named queries) and their columns using AMO, we use DataSourceView class. First we get the DSV directly from the cube, like this:
DataSourceView DSV1 = Cube1.DataSourceView;

Then we loop around each table on the DSV, like this:
for (int i = 0; i <= DSV1.Schema.Tables.Count – 1; i++)
{
}

And inside that loop we retrieve the table names (or named query names) using “DbTableName” Extended Property, like this:
F.WriteLine(DSV.Schema.Tables[i].ExtendedProperties[“DbTableName”]);

Notes:

  1. Instead of writing to file or screen, we can assign to arrays for later processing.
  2. Extended property: If you browse it on Local variables, the ExtendedProperties are here: DSV > Schema > Table > Non Public members > List > n > ExtendedProperties
  3. DSV.Schema.Tables[i].TableName contains the element name, not the named query name.
  4. FriendlyName extended property is changable in SSAS DSV (select the view/table, right click, properties – see below). Some cube designer use this FriendlyName property to rename a view/table (not named query).

To get the SQL for that named query, we use “QueryDefinition” extended property, like this:
F.WriteLine(DSV.Schema.Tables[i].ExtendedProperties[“QueryDefinition”])

To get the column names, we loop around the columns, like this:
for (int j = 0; j <= DSV1.Schema.Tables[i].Columns.Count – 1; j++)
{ F.WriteLine(DSV1.Schema.Tables[i].Columns[j].ColumnName }

We do have DbColumnName and FriendlyName extended properties (see below left). The values of DbColumnName are the same as ColumnName, but FriendlyName is changable (see column property in SSAS DSV screen, below right). The “design-time-name” ExtendedProperties contains a GUID.

We can also get the data type for each column, as well as max length and ordinal, like this:

DSV1.Schema.Tables[i].Columns[j].DataType
DSV1.Schema.Tables[i].Columns[j].MaxLength
DSV1.Schema.Tables[i].Columns[j].Ordinal

To add a column we use Add method, giving 2 parameters: column name and data type, like this:
DSV.Schema.Tables[i].Columns.Add(“Column9”, System.Type.GetType(“System.Int32”));

To add a table/named query we use Add method, like this:
DSV.Schema.Tables.Add(“Table9”);

Then use the Update method to update the DSV:
DSV.Update();

Advertisements

3 Comments »

  1. Can I do this somehow in Powershell for a whole list of servers? (About 200). I’d like to list all server with their catalogs, cubes, measures etc? Thanks lots.

    Comment by Reiner — 6 September 2011 @ 1:33 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: