Professional Microsoft® SQL Server® Analysis Services 2008 with MDX

12.1. Dimension Writeback

Dimension data requires an update under several circumstances. 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 promotions 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 2008 allows you to change the dimension data directly through the Dimension Browser in the BIDS as well as SSMS. You can manually create dimension members for small dimensions within the Dimension Browser. It is very helpful to users during the development phase of your UDM so that you can make appropriate modifications to your dimension data. To update the dimension data in your UDM, Analysis Services updates the data in the underlying relational table and then does an incremental process to update the data in the UDM. You should be aware that using BIDS for updating dimension data can reduce performance. This is because Analysis Services sends relational queries for each member update separately to update the dimension data in the relational database followed by the incremental ...