UPDATE CUBE in subcubes

Introduction

Excerpt from BOL: "subcube is a subset of a cube on representing a filtered view of the underlying data".

Subcubes can be used for different purposes:

• Simplify user navigation by displaying only needed cube items.
• Improve query performance by limiting query evaluation space.

Implementing security

Subcubes can be interesting for implementing application-level security, similar to dimension security (I talked about this in a recent article dedicated to SSAS 2008 personalization extensions).

For example, one user can have different positions in a company:

• As a head of the IT department, he will need to see IT related data for all the branches of the enterprise.
• As a head of a branch, he will need to have only access to his branch’s data.

This requirement could be achieved with SSAS security by defining two roles (where role is specified at connection time with Roles connection property), but would require SSAS administration operations each time a filter needs to be modified, and could lead to a huge amount of roles in SSAS database.

Note: Indeed, application-level security will never replace server-side security. We are more in a situation where cube filters needs to be changed dynamically, as in a Budget and Planning application for instance.

Now that we decided to use subcubes for handling application-level security, what about UPDATE CUBE use in such a context?

Case study

For simplicity, we will use a very simple write-enabled cube called Organization, containing just one dimension. This dimension (also called Organization) represents company structure:

http://cid-9cf41ea10109e385.skydrive.live.com/embedrowdetail.aspx/Public/Blog/UPDATECUBEinSubcubes/Picture01.png

In our case, our user has access to all divisions in USA Operations, but only wants to see east divisions (northeast and southeast).

Creating subcube

The following MDX command will be used:

CREATE SUBCUBE Organization AS SELECT {[Organization].[Northeast Division],[Organization].[Southeast Division]} ON 0 FROM Organization

Now if we query dimension members, we will only see our two divisions (and its ascendants):

http://cid-9cf41ea10109e385.skydrive.live.com/embedrowdetail.aspx/Public/Blog/UPDATECUBEinSubcubes/Picture02.png

Using UPDATE CUBE

Let’s issue the following command to spread a value on our two divisions:

UPDATE CUBE Organization SET ([Organization].[USA Operations])=1000

We expect our value to be evenly distributed on our two divisions (i.e. 500 on each). This is not what we get:

http://cid-9cf41ea10109e385.skydrive.live.com/embedrowdetail.aspx/Public/Blog/UPDATECUBEinSubcubes/Picture03.png

We are the rest of the values gone?

If we temporarily drop our subcube, and query again, here is what we see:

http://cid-9cf41ea10109e385.skydrive.live.com/embedrowdetail.aspx/Public/Blog/UPDATECUBEinSubcubes/Picture04.png

What happened is UPDATE CUBE distributed the amount 1000 on [USA Operations] descendants, including out of subcube members (highlighted in blue).

What have we learned?

UPDATE CUBE can’t be used in a subcube when it’s issued on a cell having filtered descendants.

Using COMMIT TRAN

Regarding what we’ve previously learnt, we will now work on individual divisions.

Let’s issue the following commands to update Northeast division (at this stage, we are back in a subcube context):

BEGIN TRAN

UPDATE CUBE Organization SET ([Organization].[Northeast Division])=500

The command works fine:

http://cid-9cf41ea10109e385.skydrive.live.com/embedrowdetail.aspx/Public/Blog/UPDATECUBEinSubcubes/Picture05.png

At some time, we will want to validate (i.e. persist) our updates. The following command will do so:

COMMIT TRAN

The command seems to work properly, as a success message is returned. For a write-enable cube, COMMIT TRAN statement as the effect of inserting records in a writeback table. Here we should get one record, as our UPDATE CUBE command was applied on a leaf cell.

If we check the content of the writeback table, we see no records were inserted: COMMIT TRAN has been ignored!

We notice the same effect with a ROLLBACK TRAN command: updated values are not discarded!

Does this mean we will never be able to persist our updates?

The solution

If we recall the previous scenario, here is what we did:

1. Create subcube
2. Start transaction
3. Update cube
4. Commit transaction

Now, let’s slightly change our scenario:

1. Start transaction
2. Create subcube
3. Update cube
4. Drop subcube
5. Commit transaction

This time it works, and records are inserted in writeback table.

What have we learned?

UPDATE CUBE commands can be sent in the context of the subcube, but transaction must be handled outside of the subcube.

This means that each time we need to validate or cancel our updates, we will have to temporarily drop our subcube.

Note the behavior is the same with SSAS 2005 and SSAS 2008.

Conclusion

While there are some limitations with UPDATE CUBE in a subcube context (updates only on cells not having filtered descendants, transaction outside subcube), there is still an interest at using it, when business logic such as application-level security needs to be implemented.