Chapter 11: Updating Your UDM Data

In previous chapters you saw how metadata constituting OLAP databases (the UDM) can be manipulated using the Analysis Services 2005 development environment. You learned that the metadata changes are propagated to Analysis Services instance through Create or Alter statements. Similarly, dimension and fact data within your UDM might have to be updated. For example, change of marital status for an employee, where marital status is an attribute within the employee dimension. Or perhaps an update for next fiscal year's budget based on the current year's revenue where budget is a measure within your UDM. Analysis Services 2005 provides you the ability to update dimension as well as cube data. Data updates within your UDM are referred to as a writeback because you are writing data back into an existing UDM. Updating members within your dimension is referred to as dimension writeback and updating the measure values within your cube is called cell writeback. By the way, don't let the three letter acronym throw you off; a UDM is another way of referring to a cube.

Dimension writeback enables a user to add, update, or delete dimension members; such as when a new employee joins the company, the user can update the employee dimension by adding a new member. Likewise, when an employee changes departments or leaves the company altogether, the user can update or delete relevant dimension members. When you update dimension members, Analysis Services automatically updates the corresponding tables in the data source, processes the dimension, and then cascades changes to affected cubes. In Analysis Services 2005, the operations Delete, Add, Move, and Update on dimension members are supported through BIDS, and through XML/A statements. Cell writeback supports user writeback values referred to by tuples in a cube. In a typical user scenario, a company's budget for next year is allocated based on market conditions and other factors. Analysis Services supports in-session writeback (what-if), which enables the user to change cell values directly in memory, and check resulting effects such as aggregate results and calculated values. The user can then choose to commit or discard (abort transaction) the changes. If the changes are committed, other users will be able to see those changes. You learn to writeback to dimension and cube data in this chapter through examples with the help of the sample Adventure Works DW relational data.

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 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 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 writeback 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 cannot 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 Services 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.

4. Analysis Services should have write permissions for the tables while impersonating the account specified in the data source and the dimension table cannot have auto increment key column while adding new rows.

Now that you know the limitations of the dimension writeback in Analysis Services, you can try dimension writeback on a database to understand the behavior better. Follow these steps to perform dimension writeback operations:

1. Create a UDM from a subset of the relational AdventureWorks DW data. Execute the following SQL script against the relational database to create the subset of data needed for this scenario. The following SQL script (CreateWriteBackExampleTables.sql in the Chapter 11 folder that can be downloaded from this book's accompanying web site) creates three tables, WB_Employee, WB_Period, and WB_Fact, and retrieves a subset of the data from the DimEmployee, DimTime, and FactSalesQuota tables of the Adventure works relational sample database. You are now ready to create an UDM on top of these three tables. Note that you should ignore any "cannot drop the table" messages if you get them.

        USE [AdventureWorksDW]     GO     DROP TABLE [WB_Employee]     GO     DROP TABLE [WB_Period]     GO     DROP TABLE [WB_Fact]     GO     CREATE TABLE [dbo].[WB_Employee](       [EmployeeKey] [int] NOT NULL,       [ParentEmployeeKey] [int] NULL,       [FullName] [nvarchar](101) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,       [DepartmentName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL     ) ON [PRIMARY]     GO     SELECT DISTINCT [CalendarQuarter]+[CalendarYear]*10 as QuarterKey,         CAST ([CalendarYear] AS VARCHAR (10) )+' Q'+         CAST (CalendarQuarter AS VARCHAR (10)) AS QuarterName         ,[CalendarYear]     INTO WB_Period     FROM [AdventureWorksDW].[dbo].[DimTime]     GO     INSERT INTO [WB_Employee]     SELECT [EmployeeKey]         ,[ParentEmployeeKey]         ,[LastName]+','+ [FirstName] AS FullName         ,[DepartmentName]     FROM [AdventureWorksDW].[dbo].[DimEmployee]     GO     SELECT [EmployeeKey]         ,[CalendarYear]*10 +[CalendarQuarter] AS Quarterkey         ,[SalesAmountQuota] AS BudgetExpenseAmount INTO WB_Fact     FROM [AdventureWorksDW].[dbo].[FactSalesQuota]     GO     INSERT INTO WB_Fact     SELECT 275, quarterkey, budgetexpenseamount     FROM dbo.WB_Fact     GO 

2. Create a new Analysis Services project using BIDS and name it WriteBackExample.

3. Create a data source to the Adventure Works DW sample database. Make sure you set the Impersonation mode for the data source to default in the data source wizard.

4. Create a DSV and include the tables WB_Fact, WB_Period, and WB_Employee from the Adventure Works DW data source. Make sure the primary keys for all the tables are marked appropriately and establish the relationships between the tables in the DSV as shown in Figure 11-1. Note that to get the parent-child relationship, just drag and drop ParentEmployeeKey on to EmployeeKey.

5. Launch the Cube Wizard, select the defaults on various pages to create the cube. The wizard will create two dimensions: WB_Employee and WB_Period. Rename the dimensions to Employee and Period in Solution Explorer and then again in the Dimensions pane of the Cube Designer.

6. Open the Employee dimension. Rename the key and the parent attributes as Employee and Manager, respectively, for better readability. Set the NameColumn for the key attribute (Employee) in the Employee dimension to point to the Full Name column so that you can see the employee names while browsing the employee dimension's parent-child hierarchy.

   
   Figure 11-1

7. Delete the FullName attribute from the dimension because it is used only as the named column for the key attribute in this example. Do this by right clicking on "FullName" in the Attributes pane and selecting Delete.

8. To write-enable the WB Employee dimension, click the Employee dimension in the attribute pane of the Dimension Designer and change the dimension property WriteEnabled to True, as shown in Figure 11-2.

   
   Figure 11-2

9. Deploy the Analysis Services project to your Analysis Services instance.

Adding a Member to a Dimension

In this scenario, a new employee named "Smith, James" who just joined the Adventure Works company needs to be added as a member to the Employee dimension. James will report to "Bradley, David" in the Marketing department. You can add a member to the Employee dimension through the key attribute or the parent attribute of the employee dimension because the employee dimension contains a parent-child hierarchy. You need to enter the values for all the properties for the dimension member to be added. Because you need to add a member under "Bradley, David," it is most convenient to use the Parent attribute of the Employee dimension, which is the parent-child hierarchy. Follow the steps below to add "Smith, James" to the Employee dimension:

1. Open the Employee dimension and switch to the Browser tab.

2. In the Hierarchy drop-down box, choose Manager hierarchy.

3. Click the member properties icon in the dimension Browser as shown in Figure 11-3. Select the Show All checkbox to select all the member properties and click OK.

   
   Figure 11-3

4. Click the Writeback icon (shown in Figure 11-4) to add a new member to the dimension. You will now see a new column called Key added to the Browser view, as shown in Figure 11-4. This column shows the Id of the manager. The id of the manager is the value in the key attribute hierarchy (Employee) in the dimension. If you browse the Employee attribute you will see these values for the members of the attribute.

   
   Figure 11-4

5. In the dimension browser you can see two David Bradleys (Figure 11-4), which indicates David Bradley is a manager as well as an employee. In the tree view showing the employees, rightclick the first "Bradley, David" and choose Create Child from the pop-up menu as shown in Figure 11-5. Notice that you can also create James Smith as a sibling member by selecting one of the employees reporting to David Bradley.

   
   Figure 11-5

6. You will now see a new row being created under "Bradley, David" with the ParentEmployee already pre-populated with "Bradley, David." The cursor is located in the column where all the members are shown so that you can enter the name of the new employee. Enter the name "Smith, James." Enter the value 300 for the member key and Marketing as the Department Name as shown in Figure 11-6.

Figure 11-6

This parent-child hierarchy has a member property called Manager which is a column in the dimension browser. That column is read only because when you input a new member under a certain manager, the existing parent is populated into the Manager column automatically. Note that if you want to undo changes to a new member that was created, hit the "ESC" key. Otherwise, after you have entered all necessary values, move the cursor to a different member to add the new member to the dimension. BIDS sends the values for the new member "Smith, James" to the Analysis Services instance. The DDL sent by BIDS to Analysis Services for the dimension writeback is shown below. The Insert command is new in Analysis Services 2005 and does the writeback of the new member's values in the dimension.

     <Insert xsi:type="Insert" xmlns:xsd="http://www.w3.org/2001/XMLSchema"     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">      <Object>       <Database>WriteBackExample</Database>       <Cube>$WB Employee</Cube>       <Dimension>WB Employee</Dimension>      </Object>      <Attributes>       <Attribute>        <AttributeName>Employee</AttributeName>        <Name>Smith, James</Name>        <Keys>         <Key xsi:type="xsd:int">300</Key>        </Keys>       </Attribute>        <Attribute>        <AttributeName>Manager</AttributeName>        <Keys>          <Key xsi:type="xsd:int">7</Key>        </Keys>       </Attribute>       <Attribute>        <AttributeName>Department Name</AttributeName>        <Keys>          <Key xsi:type="xsd:string">Marketing</Key>        </Keys>       </Attribute>      </Attributes>     </Insert> 

The DDL has two sections, Object and Attribute. The Object section is where the object is defined and where the cube and dimension names are specified. You already learned that each database dimension is considered a cube of single dimension within that Analysis Services database. Hence the new member is being created in the database dimension rather than the cube dimension. Therefore, you see the name $WB Employee being used to refer to the WB Employee database dimension. The newly added dimension member and its properties are specified as part of the Attributes section. Whenever dimension data is being updated, the corresponding data gets updated in the relational database and then Analysis Services does an incremental update of that dimension. Analysis Services, after receiving the command to add the new members, creates a corresponding SQL statement to update the data in the relational table WB_Employee. If there are some constraints set on this table, such as FullName cannot be null, the corresponding SQL update will fail and this will get propagated back to the dimension browser and shown to the user. If the relational update statement succeeds, the Analysis Services instance automatically does an incremental update of the dimension so that the newly added dimension gets added to the database dimension, and thereby can be accessed in all the cubes that use the dimension. Once the incremental update is successful, Analysis Services sends a response back to the dimension browser and you can proceed with additional operations.

If you query the relational table WB_Employee, you will see that a new row has been added to the relational table with the values you had entered as shown in the following table:

Key	Parentkey	FullName	Department
300	7	Smith, James	Marketing

You have now successfully added a new member to the employee dimension using the dimension writeback functionality supported through the BIDS. Next, you learn about modifying member properties of existing members.

Modifying Data of Members in a Dimension

Assume that James Smith worked for a year in the Marketing department and wanted to move to a different department for his career growth. He interviewed with the Engineering group and secured a new position. This data needs to be reflected in the Employee dimension. Assume James' new manager is Gail Erickson. Updating James' information in the employee dimension so that he has moved to a different organization needs to be done as a two-step process. You need to update the reporting structure for James so that Erickson becomes his manager and then you need to update his new department information. Now let's go ahead and update James' information.

As mentioned in the previous section, the dimension browser does not allow you to edit the parent attribute (Manager column in this example) when you add a new member. Similarly, you cannot update or edit the Manager column directly. Therefore the BIDS provides the functionality of dragging and dropping James' information under Erickson so that his manager's name gets updated. Select the record for James in the dimension browser view with writeback enabled and then drag and drop James under "Erickson, Gail." The drag-and-drop operation can also be accomplished by cut-and-paste options available in the dimension browser. To do so, select James' record, right-click, and select Cut to remove the link from James' current manager. Then select "Erickson Gail," right-click, and select Paste so that James is moved under "Erickson, Gail."

You will see that James has now moved under "Erickson, Gail" and his manager information has been automatically updated as shown in Figure 11-7. However, other attributes of James do not get updated automatically. There are several reasons why this is true; Analysis Services does not know what attributes of a child are to be inherited from the parent. For example, you might have an attribute join date instead of department. James' join date and Erickson's join date need not be the same and hence join date for James should not be updated automatically. In this example, you see that the Department Name is one of the attributes that can be updated. It would have been nice if the Department Name updated had been done automatically. It is not, because you can have a business scenario where your manager might be a person from a different department but the work you are doing might still be the same. For example, you might have two departments coming under a general manager. A person from one of the departments might get promoted and might have to report directly to the general manager due to new responsibilities, even though he or she is working for his/her original department. Hence BIDS does not automatically update the member properties of a member when the member is moved from one parent to another. Appropriate values need to be updated by the end user.

Figure 11-7

BIDS sends the following update DDL to the Analysis Services instance server:

     <Update xsi:type="Update" xmlns:xsd="http://www.w3.org/2001/XMLSchema"     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">      <Object>       <Database>WriteBackExample</Database>       <Cube>$WB Employee</Cube>       <Dimension>WB Employee</Dimension>      </Object>      <Attributes>     <Attribute>        <AttributeName>Manager</AttributeName>        <Keys>          <Key xsi:type="xsd:int">11</Key>        </Keys>     </Attribute>     </Attributes>     <Where>      <Attribute>        <AttributeName>Employee</AttributeName>        <Keys>          <Key xsi:type="xsd:int">300</Key>        </Keys>      </Attribute>     </Where>    <MoveWithDescendants>true</MoveWithDescendants>   </Update> 

Similar to adding a new member, Analysis Services updates James' information in the relational database through appropriate relational update queries as shown in the following SQL query followed by an incremental process of the dimension. The update queries sent to the relational database can be seen if you monitor the trace (with progress events enabled) coming back from Analysis Services. You will learn to trace events on Analysis Services instances using SQL Profiler in Chapter 13. Analysis Services creates parameterized relational queries (the ? symbol indicates a parameter) to avoid SQL injection (a type of security vulnerability). The queries generated will depend on the relational backend and Analysis Services use of an appropriate cartridge (information that tells Analysis Services how to form the relational queries for this database provider) for that specific relational database.

     UPDATE [dbo].[WB_Employee]     SET [dbo].[WB_Employee].[ParentEmployeeKey]= ?     WHERE     (     (         [dbo].[WB_Employee].[EmployeeKey] = ?     ) 

In order to correctly update James' department to Engineering, you need to right click James' record and select Rename. James' record is now enabled for updates. Then click the Department Name column and change the department value from Marketing to Engineering. To complete the writeback, move the cursor to a different member. The record in the dimension table will be changed as shown in the following table:

Key ParentKey	key	FullName	Department
300	11	Smith, James	Engineering

In the DDL that was sent to the Analysis Services instance when you moved Smith, you can see a tag MoveWithDescendants set to true as shown in the following code. This tag informs Analysis Services to move all the descendants of the current member to the new parent. This is an example of a business scenario where an entire organization or division moves under a new manager.

     <MoveWithDescendants>true</MoveWithDescendants> 

Another common scenario is when a manager moves to a different department and all his or her direct reports automatically report to the second line manager until a new manager is identified. In such a circumstance, the MoveWithDescendants tag should be set appropriately to achieve the behavior. The dimension browser does not allow this functionality; however, you can create your own DDL and send it directly to the Analysis Services instance to achieve this behavior.

The BIDS dimension browser also facilitates moving members from one parent to another parent. All you have to do is to select multiple members by holding the Ctrl key down, and move the members to the new parent by using drag-and-drop or cut-and-paste operations. This would be helpful in circumstances where you have a re-org in your organizations.

Deleting Dimension Data

One scenario that eventually happens in all companies is an employee leaves the company. The human resources department often deletes the employee record from the main database so that payroll and benefits are terminated for the ex-employee. Analysis Services provides you support to delete members in a dimension from the dimension browser. Assume John Wood leaves the company and you need to delete the member corresponding to him in Employee dimension. To delete the member from the dimension, select the record for "Wood, John" and hit the delete button. The Delete Members dialog is launched, as shown in Figure 11-8. This dialog prompts to either delete all the descendants reporting to John Wood or make them report to John Wood's manager. If an entire group of employees under John Wood are leaving the company to start a new business or getting laid off, you would choose the Delete their descendants option. A more common scenerio is the second choice, which is Promote their descendants. Click OK after making the selection.

Figure 11-8

The BIDS dimension browser sends the drop statement to the Analysis Services instance as shown in the following code. Analysis Services sends a relational command to delete the member from the relational table and then does an incremental process of the dimension.

     <Drop xsi:type="Drop" xmlns:xsd="http://www.w3.org/2001/XMLSchema"     xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"     xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">      <Object>       <Database>WriteBackExample</Database>        <Cube>$WB Employee</Cube>        <Dimension>WB Employee</Dimension>      </Object>      <Where>       <Attributes>        <AttributeName>Employee</AttributeName>        <Keys>          <Key xsi:type="xsd:int">275</Key>        </Keys>       </Attribute>      </Where>     </Drop> 

If the dimension member had fact data in a fact table associated with him or her, the fact data will not be available for querying. Even though the data is processed inside the cube while querying, Analysis Services will not be able to identify the associated member and hence not return results for this member. You can send the following query to the Analysis Services instance before and after deletion of the member John Wood to see the difference between the returned result sets.

     select measures.members on columns,     {[WB Employee].[Manager].&[7],     [WB Employee].[Manager].&[7].children } on rows     from [Adventure Works DW] 

Be aware that Analysis Services automatically deletes dimension entries from a dimension table, but does not automatically delete the corresponding fact table entry from the fact table. The fact table entries for the deleted "Wood, John" (key 275) is still in the fact table. Before you remove the dimension member, you need to make sure that the dimension member doesn't have any data in the fact table. The fact data does exist within the UDM; it is just being restricted by Analysis Services. It is being restricted because the corresponding dimension member was deleted. Such fact data is referred to as orphan fact data. Because the dimension data corresponding to John Wood has been deleted and the fact data is still available, if you do a full process of the database you will see processing errors because Analysis Services by default checks for referential integrity. You can change the error configuration settings on the cube to handle the rows (delete or associate it with Unknown member of the dimension) associated with John Wood or you can delete the entries corresponding to John Wood using the following SQL statement and then reprocess the entire database:

     delete from WB_Fact     where employeekey = 275 

Analysis Services 2005 provides you BIDS and SSMS for you to directly add, edit, and remove dimension members. You can manually create dimension members for small dimensions within 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. In order 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, because Analysis Services sends relational queries for each member update separately to update the dimension data in the relational database followed by the incremental process. If your scenario is to do a bulk operations such as moving an entire organization from one geographical location to another and you need to update all the dimension members, you are better off updating the relational database through bulk update and then processing the dimension.

We consider the dimension writeback supported by the dimension browser in BIDS or SSMS to be helpful under specific circumstances. This would be helpful during modeling and adding new members to see the dimension structure. Most often the relational database that stores the dimension data might not be accessible to the person who is involved in maintenance of your cubes and dimensions. The Analysis Services administrator might have granted permissions to a specific account that can access the relational backend that is part of the server role of Analysis Services or specified as part of the database data source impersonation, as seen in Chapter 2. Under such circumstances, as an administrator of the Analysis Services database you can update the dimension data through the dimension writeback supported by Analysis Services tools. We recommend the use of dimension data update through the dimension browser whenever you have limited data to update or the frequency of updates is quite low, once a month, for example. Everyone accessing the UDM cannot perform updates on dimensions. Users who are part of a role that has explicit write permissions and process permissions specified for dimensions can only do dimension writeback.

Now that you have learned how to update dimension data using the dimension browser, you should also be aware of the prerequisites which you learned earlier and the limitations in the Analysis Services tools. The developer designing the dimension needs to take into account the limitations. Two limitations we are aware of that you can potentially encounter while using dimension browser for writeback are:

1. If attributes within the dimension have different name and key columns defined, you cannot writeback to the dimension. The only exception is for the attributes that have Usage set to Key and Parent.

2. Dimension attributes need to have Discretization Method specified to None. This is because if an attribute has been discretized into a specific bucket, you cannot write the discretized value back to the relational data. It would have been better if Analysis Services would have disabled writeback on those specific attributes rather than not allowing writeback on the entire dimension.

These limitations are only due to the design of the dimension writeback through the dimension browser. If you write your own tool to update dimension data, you can send appropriate DDLs to Insert, Update, or Delete members to overcome the limitations.

Now that you have successfully learned to update dimension data, the following section discusses the need to update cell values in your cube. This is a common scenario for a lot of business organizations and we hope you will find the next section extremely useful.