SSAS dimension source table changed - how to propagate changes to analysis server?

Question

Hi,

Sorry if the question isn't phrased very well but I'm new to SSAS and don't know the correct terms.

I have changed the name of a table and its columns. I am using said table as a dimension for my cube, so now the cube won't process. Presumably I need to make updates in the analysis server to reflect changes to the source database?

I have no idea where to start - any help gratefully received.

Thanks

Phil

Answer 1

Before going into the details of how to amend the cube, have you considered creating a view with the same name as the old table which maps the new column names to the old? The cube processing process should pick this up transparently.

EDIT

There are quite a lot of variations on how to amend SSAS - it depends on your local set-up.

If your cube definition is held in source control (which it should ideally be), you need to check the cube definition out and amend it from there.

If your definition exists only on the server you need to open it from the server:

1. Open the Business Intelligence Development Studio (BIDS) - typically on the Windows start menu under Programs > Microsoft SQL Server 2005.

2. Go to File > Open > Analysis Services Database

3. Select your server/database and click OK.

Once you have the project open in BIDS, you can amend the Data Source View to switch to the new table.

These instructions are based on the principle that it's going to be easier to alias the new table to look like the old in the DSV, since this means fewer changes within the cube definition.

1. Open the Data Source View from the Solution Explorer - there should be only one.
2. Locate the table you need to change in the DSV
3. Right-click on the table and select Replace Table > With New Named Query

4. Replace the existing query with a query from the new table with the new columns aliased with the new names:

   SELECT ~new column name~ AS ~old column name~ FROM ~new_table~

Once the new query has been set, deploy the changes:

If you use source control, check in and deploy the project to the target server.

If you opened the cube definition from the server, select File > Save All

Finally, re-process the cube.