March 26, 2012 at 10:45 am
I am trying to get a list of measures and dimensions that I can copy and paste.
I am looking to get something similar to
select name from sys.columns order by name desc
I am unable to find a way to either query this directly, scrape from my solution for SSAS, or other means in searching. Any help would be appreciated. I have about 200-300 measures plus dimensions in test and 100-200 in production and I need to compare differences.
March 26, 2012 at 12:26 pm
Something like this: http://bennyaustin.wordpress.com/2011/03/01/ssas-dmv-queries-cube-metadata/
Only for 2005. $system cube does not exist. When trying to manipulate to put information about my cube in I am running into failure after failure.
March 26, 2012 at 2:03 pm
Beh, got what I needed - tried to avoid using this method but seems like only thing could find reference to work. Opened SSMS, opened object explorer, connected to SSAS, opened cube, went to dimensions, scripted to XML. Modified xml a bit and pushed into a variable. Queried variable.
SELECT nref.value('ID[1]', 'nvarchar(50)') ID,
nref.value('Name[1]', 'nvarchar(50)') Name
FROM @XML.nodes('//Attribute') AS R(nref)
or...
For Member information:
/* right click on cube in SSAS object explorer and script to create. Grab chunk of stuff above where members are created */
select left(replace(ltrim(vcChunk),'CREATE MEMBER CURRENTCUBE.[MEASURES].[',''),charindex(']',replace(ltrim(vcChunk),'CREATE MEMBER CURRENTCUBE.[MEASURES].[',''))-1), ltrim(vcChunk)
from [splitstringtotablefunctionneededhere](@text,';')
where vcChunk like '%CREATE%';
For measure and dimension information:
/* right click on measures groups and script as create - need to remove xmlns tag in create tag */
/*select dimensions*/
SELECT nref.value('ID[1]', 'nvarchar(50)') ID,
nref.value('Name[1]', 'nvarchar(50)') Name,
nref.value('AttributeID[1]', 'nvarchar(50)') AttName
FROM @XML.nodes('//Create/ObjectDefinition/MeasureGroup/Dimensions/Dimension/Attributes/Attribute') AS R(nref)
Order by 3;
/*select measures*/
SELECT nref.value('ID[1]', 'nvarchar(50)') ID,
nref.value('Name[1]', 'nvarchar(50)') Name,
nref.value('AttributeID[1]', 'nvarchar(50)') AttName
FROM @XML.nodes('//Create/ObjectDefinition/MeasureGroup/Measures/Measure') AS R(nref)
Order by 1;
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply