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
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).
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.
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.
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:
Open the Business Intelligence Development Studio (BIDS) - typically on the Windows start menu under Programs > Microsoft SQL Server 2005.
Go to File > Open > Analysis Services Database
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With