Updating Your UDM Data - Professional SQL Server Analysis Services 2005 with MDX - page 511

Updating Dimension Data in UDM
There are several circumstances where dimension data requires an update. If
an organization is selling products, then adding new products to the catalog
may end up on the "to do" list and hence the changes need to be reflected in
the product dimension. You might come up with new promotions for a holiday
season line to increase your sales and hence need to update data in your pro-
motions dimension. One of the most common scenarios is changing data for
employees. As new employees join the organization their information needs to
be added to the appropriate dimension. Or existing employees' information
might have to be updated due to a change in properties of the employees.
Analysis Services 2005 allows you to change the dimension data through the
BIDS as well as SSMS. In order to update the dimension data, the dimension
first needs to be write-enabled, which means the users with write permissions
on the dimension can update data. You learned the basics of dimension write-
back in Chapter 8 , and in this chapter you learn dimension writeback through a
user scenerio.
In this section you learn how to add, delete, and update dimension members
using the dimension writeback technique. Before you start implementing the
scenario, you should understand that certain prerequisites must be addressed
to write data to a dimension. These prerequisites are as follows:
1. The dimension property WriteEnable needs to be set to True. This can
be done in BIDS.
2. The dimension to be write-enabled must be derived from a single
table, which means all dimension attributes' key and name columns
have to come from a single table; that is, a snowflake dimension can-
not be write-enabled.
3. If a dimension has been created from a named query, that dimension
cannot be write-enabled. When dimension data is being updated, the
data is updated in the backend relational database and Analysis Ser-
vices needs to know the table to which the update needs to be done.
In sum, Analysis Services does not support write-enabling dimensions
that have been created from named queries or views in a relational
database.