March 4, 2011 at 4:48 am
Hi all
I have to enumerate all related tables for cube using AnalysisServices or AdomdClient.
Object DataSourceView and his Schema property, provide access to all table names, relations, parent tabels, child tables, columns etc. in entire database. But I have to find table names, relations, parent-child tables for specified cube and her dimensions, not entire db.
Read tables in same way as double click on cube in solution explorer in BI development studio
Does anybody have some hint how to do that?
Thanx
Srdjan
March 4, 2011 at 9:59 am
Think of it in pieces... 🙂
The dimensions in a cube, are Database dimensions. So, (and there are different approaches for what you loop through first) if you loop through the DB dimensions and determine their source table/s, then loop through the dimensions that exist in the specific cube, you'll have the dim tables for that cube.
You do a similar thing with the Facts, except you need to interrogate the Partitions for each Measure Group. Assusming a very standard setup, your partitions won't be based on queries and possibly will hold 'all' of the data (ie you haven't partitioned on Time). If you have partitioned your data and/or you've used queries, you mayb be happy simply to returnthe query string, alternatively, you'll be required to then parse that string to extract the table name/s.
HTH,
Steve.
March 4, 2011 at 2:03 pm
Yes, pieces 🙂
The Name Column is of type DataItem and KeyColumn is of DataItemCollection.
So, solution is (c# code):
Microsoft.AnalysisServices.Database db = srv.Databases.FindByName(Database);
if (db != null)
{
foreach(Microsoft.AnalysisServices.Cube cb in db.Cubes)
{
foreach (CubeDimension cDim in cb.Dimensions)
{
foreach (DataItem di in cDim.Dimension.KeyAttribute.KeyColumns)
{
string tName = di.Source.ToString(); // parse this variable
}
}
}
}
March 4, 2011 at 2:34 pm
That's it for the table/s that are used in your dimensions, like I said, you'll have to interrogate the Partitions for each Measure Group too, to get your Fact references.
Steve.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply