Wednesday, April 25, 2012 4:32 PM
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
Sunday, April 29, 2012 7:15 PM
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
Sunday, April 29, 2012 7:55 PM
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
Tuesday, May 01, 2012 9:13 AM
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
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.