How dynamicly retrieve XMLA script of dimension creation?

my task simple explain: need programmatically information user can see if goes in vs cube designer -> dimension usage tab, i.e. relations between measure groups , dimensions. can make user in management studio right-click cube , select "script cube as"->"create to", put result xmla-script t-sql-script, that, using xquery, retrieve information relations. these scripts:

 

select   	nref.value('../../../../../../../id[1]', 'nvarchar(50)') measuregroup,  	nref.value('../../../../../cubedimensionid[1]', 'nvarchar(50)') dimension,  	nref.value('tableid[1]', 'nvarchar(50)') tablename,  	nref.value('columnid[1]', 'nvarchar(50)') columnname  @xvar.nodes('//measuregroups/measuregroup/dimensions/dimension/attributes/attribute/keycolumns/keycolumn/source') r(nref)    -- list of dimension - attempt key fields:  select   	nref.value('dimensionid[1]', 'nvarchar(50)') dimension	  @xvar.nodes('//dimensions/dimension') r(nref)  nref.value('dimensionid[1]', 'nvarchar(50)') not null

the main problem dimensions separate objects cubes , cube creation script doesn't contain information key fields. tested xmla scripts of kind:

<discover xmlns="urn:schemas-microsoft-com:xml-analysis">   <requesttype>mdschema_measuregroup_dimensions</requesttype>   <restrictions>     </restrictions>    <properties/>  </discover>

with these parameters: http://msdn.microsoft.com/en-us/library/ms126079.aspx, but information can list of dimensions attributes.

any ideas how full information relation between measure groups , dimensions?

 

 

problem solved quite easy. there appeared it's possible generate "create to" script not cube, whole database. , contains needed information dimensions. scripts extract metadata (ssas2005):

declare @measures_dimensions_relations table  (pk int identity(1,1), measuregroup varchar(25), dimension varchar(25),   tablename varchar(25), columnname varchar(25), dimtablename varchar(25), dimcolumnname varchar(25))  insert into	@measures_dimensions_relations 	(measuregroup, dimension, tablename, columnname) select  	--nref.value('../../../../../../../../../name[1]', 'varchar(25)') cube, 	nref.value('../../../../../../../id[1]', 'varchar(25)') measuregroup, 	nref.value('../../../../../cubedimensionid[1]', 'varchar(25)') dimension, 	replace(nref.value('tableid[1]', 'varchar(25)'), 'dbo_', '') tablename, 	nref.value('columnid[1]', 'varchar(25)') columnname	 @xvar.nodes('//measuregroups/measuregroup/dimensions/dimension/attributes/attribute/keycolumns/keycolumn/source') r(nref) nref.value('columnid[1]', 'varchar(25)') not null 	and nref.value('../../../../../../../../../name[1]', 'varchar(25)') = @cube_to_analyse order 1,2,3,4  declare @dimensions table  (pk int identity(1,1), dimension varchar(25), attribute varchar(25), tablename varchar(25), keycolumnname varchar(25))  insert into	@dimensions 	(dimension, attribute, tablename, keycolumnname) select  	nref.value('../../../../../name[1]', 'varchar(25)') dimension, 	nref.value('../../../name[1]', 'varchar(25)') attribute, 	replace(nref.value('tableid[1]', 'varchar(25)'), 'dbo_', '') tablename, 	nref.value('columnid[1]', 'varchar(25)') keycolumnname @xvar.nodes('//dimensions/dimension/attributes/attribute/keycolumns/keycolumn/source') r(nref) nref.value('../../../../../name[1]', 'varchar(25)') not null 	and nref.value('../../../../../name[1]', 'varchar(25)') = nref.value('../../../name[1]', 'varchar(25)') -- leaves key attributes  update mdr set dimtablename = isnull(d.tablename,   (select top 1 d1.tablename @dimensions d1 d1.dimension=mdr.dimension)), 	dimcolumnname = isnull(d.keycolumnname, (select top 1 d1.keycolumnname @dimensions d1 d1.dimension=mdr.dimension)) @measures_dimensions_relations mdr left outer join @dimensions d on d.dimension = mdr.dimension , mdr.columnname = d.keycolumnname  select * @measures_dimensions_relations mdr

SQL Server  >  SQL Server Analysis Services