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.

Dimension Solve Order

Hi,

I'm struggling to get a technique that I've used in AS2000 to work with AS2005.

In AS2000, although I'm not aware that its documented, you can alter the solve order of the dimensions by dragging one dimension above the other in the design area.

I have a solve order issue on a calculated measure in AS2005, and I believe that I need to resolve it by altering the solve order of the dimensions. I've tried altering the order of the dimensions within the 'Cube Strucutre' tab, but it doesn't have any effect.

Does anyone know how to alter the solve order of the dimensions in AS2005?

Any help would be appreciated!

Many thanks

Jeremy

Hello! I think that the order of the dimensions in SSAS2005 cubes is by letter from a to z.

I have not find a way of changing that.

Edit: Solve order is for calculations only. I have never heard about a solve order for dimensions.

The only thing that will change by dragging dimensions in different orders is default non-time dimension that will appear in a client.

I can be wrong but I have been working with MS cubes since OLAP services.

HTH

Thomas Ivarsson

|||

Inside the 'Cube Stucture' tab you can alter the order of the dimensions by dragging and dropping. This is the same in AS2000.

However, I would like to know, does this alter the solve order of the dimensions? It did in AS2000. As far as I can tell, it does not in AS2005.

Can anyone else help?

|||

Hi,

Can anyone else help with this?

I would really like to get this resolved. Maybe its possible to alter the solve order of dimensions with MDX Scripts instead?

Thanks

Jeremy

|||

dear Friend,

Open your dimension design in SSAS, in the attributes tab, select the attribute you want to order, and right-click ans select Properties. IN the Advanced Properties of this attribute you have the options OrderBy and OrderByAttribute...

helped you?

regards!!

|||

Hi,

Thanks for the comment.

Unfortnately the OrderBy and OrderByAttribute properties control the ordering of members.

I'm interested in controlling the solve order of dimensions. E.g. the dimension 'Product' should be calculated after the dimension 'Time'. Is anyone aware how to do this?

Thanks

Jeremy

Different kind of connection issue

Was able to solve my connection issues with my local (test) machine. However, now I've got a much bigger issue on my intranet machine (W2K Server that acts as a domain controller).

When I use essentially the same set up for connecting as I did for the other machine (adjusted for machine name, etc), I get the following message:

Login failed for user '[username]'. The user is not associated with a trusted SQL Server connection.

I went into the SQL Server Express Config, and discovered under "SQL Server 2005 Services>SQL Server(SQLEXPRESS)>Properties" that this service is set up to login as the Local System built-in account. When I try to change this to Network Service, I get the following popup error message:

Cannot perform this operation on built-in accounts. [0x8007055b]

1) This happens regardless of wheter I stop the service and try to reset, apply and restart, or simply try to change it while it's running

2) I can find nothing on the error code above anywhere on the Web (Google came up EMPTY).

Anyone have any ideas about this one?

Seems like your SQL Server is setup for WIndows Authentication only. So if you want to use mixed (and therefore also SQL Server authentication) you have to configure the Server for this, in the administion console or with the registry settings:

--
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q285097
--
INF: How to Change the Default Login Authentication Mode to SQL While
Installing SQL Server 2000 Desktop Engine by Using Windows Installer
--
<snip>
Another way to change the security mode after installation is to stop
SQL Server and set the appropriate registry key for your installation:

Default instance:
HKLM\Software\Microsoft\MSSqlserver\MSSqlServer\LoginMode

Named instance:
HKLM\Software\Microsoft\Microsoft SQL Server\Instance
Name\MSSQLServer\LoginMode

to 2 for mixed-mode or 1 for integrated. (Integrated is the default
setup for the SQL Server 2000 Data Engine.)
</snip>

-URL-

If you don′t wanna use mixed authentication and only Windows Auth. you have to provide the "Integrated Authentication=True" in the connectionstring rather the UserId / Pwd.

HTH, Jens Suessmeyer.

|||

Changing the registry key worked.

One minor issue though - I tried the "Integrated Authentication=True" solution before I tried the registry key solution, and I got an error message saying that this was not a valid keyword or some such. Any idea why? Just Curious.

|||

Sotty mixed the two thing up, its "Trusted Connection=true" or Integrated Security=True, try these one, the connection string (just ti keep this in mind for you) can be found here:

http://www.connectionstrings.com

HTH; Jens Suessmeyer.