Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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

like image 309
Phil Gan Avatar asked Apr 22 '10 10:04

Phil Gan


People also ask

How do I change data source in SSAS?

Expand the database folder, open the Data Source folder under the database name, right-click a data source in Object Explorer and select Properties. Optionally, modify the name, description, or impersonation option. For more information, see Set Impersonation Options (SSAS - Multidimensional).

Is it possible to build a cube without a data source in SSAS?

If you have existing relational data source(s) you can create a Data Source View (DSV) then proceed to build the cube using the DSV. If you do not have a relational data source, you can build a cube with BIDS by choosing to build a cube without a data source.

How do you change the datatype in analysis service?

In the model designer, select the column for which you want to change the data type or display format. In the column Properties window, do one of the following: In the Data Format property, select a different data format. In the Data Type property, select a different data type.


1 Answers

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.

like image 80
Ed Harper Avatar answered Sep 28 '22 10:09

Ed Harper