AMO: Enumerating DSV Tables and Columns

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();