Update Dimension Member

• magwitch

  Valued Member

  Points: 74

  More actions

  September 24, 2013 at 3:53 am

  #148309

  I've been tasked with populating the description of all dimensions and measures in our analysis services cubes to then be able to pull this out as a report for the end user to have a data dictionary. The following code will be the basis of the report. Is it possible to run something similar to update the descriptions rather than manually doing this in BIDS and reprocessing? I thought using UPDATE DIMENSION MEMBER might do it, but can't get it to work.

  SELECT CUBE_NAME,

  CATALOG_NAME,

  REPLACE(REPLACE(CAST(DIMENSION_UNIQUE_NAME AS VARCHAR(100)),'[',''),']','') AS Dim_Name,

  LEVEL_NAME AS Attribute_Name,

  LEVEL_UNIQUE_NAME AS Dimension_Attribute_Level,

  LEVEL_CAPTION,

  DESCRIPTION AS Description

  FROM OPENQUERY("<server>\<db>",

  'SELECT * FROM $SYSTEM.MDSCHEMA_LEVELS

  WHERE [LEVEL_NUMBER]>0

  AND [LEVEL_IS_VISIBLE]')

  WHERE CAST(CUBE_NAME AS VARCHAR(255))= 'X'

  AND CAST([CATALOG_NAME] AS VARCHAR(255)) = 'X'

  Thanks

• Raunak Jhawar

  SSCoach

  Points: 15701

  More actions

  September 30, 2013 at 12:59 am

  #1654436

  CAST & REPLACE are SQL functions and the keyword AS not understood by Analysis Engine. This DMV will not work.

  Raunak J