Dimension Writeback

I am interested in using the Dimension Writeback feature to solve a specific problem in a forecasting application.

I only need to Update attribute values on existing dimension members, I don't need to insert or delete members.

Looking at various resources on the web, I think I understand the following ...
- I must be using the Enterprise version of SQL Server / SSAS
- I need to write enable the relevant dimension from within my development environment
- My users need to be using an OLAP Client which supports dimension writeback.

Some questions ...
- Is my understanding above correct ?
- Do the following OLAP clients support dimension writeback
Excel 2007 Pivot Tables
Excel Services running within Sharepoint 2007
If not, can someone point me towards a client which does support dimension writeback
- Is there any way to experiment with this feature without having an Enterprise edition SQL Server setup ?

Thanks

Marcus

Hello. I have som experience regarding this on SSAS2000 and I have not seen any information about changes in SSAS2005.

Here is a link to what functionality that is included in different editions of SQL Server 2005: http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Dimension writeback is not a client feature, but cell or cube writeback is. I think that you can only write back to dimensions in BIDS unless you find a way to write code for this with the new object model in SSAS2005. Dimension writeback is normally used to add members to a dimension, restructure a dimension(because it is not correct in the source system) and add some calculation logic to the writeback dimension.

I do not think it is a good idea to permit users to change members in a dimension. You will see a problem with different ideas of how a dimension should be built and clients changing other clients writeback members.

HTH

Thomas Ivarsson

|||

> Dimension writeback is not a client feature, but cell or cube writeback is. I think that you can only write back to dimensions in BIDS unless you find a way to write code for this with the new object model in SSAS2005

Actually, you are wrong. Dimension writeback is as client feature as cell writeback is. There are ALTER CUBE statements which can do it available since AS2000. In AS2005 there is also an XML flavor of these APIs.

|||

Hello Mosha. I am most sure I am wrong because I have only seen dimension writeback in the SSAS2000 dimension editor. I can only remember IntelligentApps as a client that used it, if I am not wrong.

It is not in ProClarity or previous versions of Excel(before 2007)

From my professional point of view we have had a lot of problem with cell writeback before SSAS2005. Dimension writeback is actually good because it have helped with shortcomings in products like Cognos Controller. But this is only as a centralized feature in order to add accounts that are missing in that Cognos product.

What you can do by writing code is a different story.Perhaps I was not clear enough on that point.

Would you recommend client dimension writeback, from the point of having a UDM and a single version of the truth?

Edit: Another question. If a client add a member to a dimension or reorganize it, what will happen to your cube project in BIDS?

Regards

Thomas Ivarsson

|||

Thomas,

Regarding your last question, I don't think the project in BIDS would change based on dimension writeback changes as these types of changes are simply pushed into the dimension table; they don't cause structural changes to the dimension (unless I'm missing something or misunderstand your question and comments).

HTH,

Dave Fackler

|||

Hello Dave. In AS2000 it was possible to move members and groups of members in a dimension that was write enabled. I have not tested this on SSAS2005 because my client, that use this feature, is still on AS2000.

Would not this be a structural change that could cause problems for BIDS? The structure of the dimension in the BI project would not be the same as on the SSAS2005 server?

Another problem will be if several users can add changes on the same members?

I accept Mosha's conclusion that it is technically possible to do it, from a client, but would this not create more problems than it solves?

Regards

/Thomas Ivarsson

|||

Dave is right - writing back to the dimension (or more precisely to the attribute) is not a structural change - it is like incremental process of dimension from all points of view (i.e. indexes and aggregations need to be recomputed after dimension writeback, just like with incremental processing).

Like everything else, dimension writeback is transactional, so multiple users is not a problem by itself either.

But as Thomas says, there is a difference between "technically possible" and "widely used in practice". I haven't seen any client tool which supported dimension writeback except for the one built-in into BIDS, and I don't have a real-life experience with customers using this feature.