MDX Solutions: With Microsoft® SQL Server™ Analysis Services 2005 and Hyperion® Essbase, Second Edition

Chapter 11. Changing the Cube and Dimension Environment through MDX

MDX, like SQL, offers a Data Manipulation Language (DML) as well as a Data Description Language (DDL). Using DDL statements, it is possible to significantly alter the given cube environment through a client application. Some of the things that you can do for a user's session regardless of the source of the cubes are

• Alter the default member for a dimension

• Refresh the cell data and dimension members

If the dimension has writeback-enabled, you can

• Create a new member

• Drop a member

• Move a member within the hierarchy

• Update the custom member formula or member properties associated with the member

If a cube has writeback enabled, you can also update data cells within it, either programmatically or with the UPDATE CUBE statement.

These commands enable you to build applications, such as budgeting and forecasting applications, in which end users can alter the database without being granted administrative access to the cubes or using the old Decision Support Objects (DSO) or the new Analysis Management Objects (AMO) code to update the databases.

The result of each command in this last set of four is that the database and all sessions connected to it immediately see the changes to the dimension structure, and the table that underlies the dimension is immediately updated. Thus, a certain amount of collaborative construction and analysis is supported.

Note that creating a new member in a writeback dimension is not the same as creating ...