Cross reference dimensions and cubes SSAS 2008

Question

As I find it necessary to change measure names in existing fact tables - as a result of my increased understanding with each new cube - I find I would like to do this by changing the name in the underlying fact table.

But I would like a where-used (or cross-reference) so that I knew which cubes included which dimensions.

Can anyone help?

Thanks in advance

Answer 1

Frank,

A couple ideas for you:

1. You could use the ADOMD classes from C# or VB.NET to iterate through the databases, cubes and dimensions in your database and detect any usages of the old column name.  This involves some programming but would be effective. Here's a link into the document tree to get you started: http://msdn.microsoft.com/en-us/library/microsoft.analysisservices.adomdclient.cubecollection.aspx

2. If you scripted out your databases to XMLA files (in SSMS right click on DB and choose script database to...), then you could use a text editor, grep, etc., to search for the occurrences within each XMLA.

---

Rob Kerr SQL Server MVP CTO, BlueGranite

Answer 2

One approach is to use DMVs. Open up a DMX query window in SSMS and you get get a list of all database and cube dimensions in your SSAS database. The query below gives you a list of all cubes that reference the Date dimension in AdventureWorks. Won't give you info on role-playing dimensions but a straightforward and simple approach to querying SSAS metadata.

SELECT	*
FROM	$system.MDSchema_Dimensions
WHERE	DIMENSION_NAME		= 'Date'

HTH, Martin

---

http://martinmason.wordpress.com

Answer 3

Thanks Martin. My solution, slightly adapted from your proposal is to the run the following in SSMS having first selected the required SSAS database.

SELECT    DIMENSION_MASTER_NAME, CUBE_NAME, DIMENSION_NAME
FROM    $system.MDSchema_Dimensions
WHERE    DIMENSION_MASTER_NAME    = 'dimension name'

Rob, thanks for your input. I have kept the link. It may prove useful, but I am going for the quick solution to my specific problem for now.

Regards, Frank