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 Security

Hi,
is it possible to secure specific dimension? I'd like to deny selected roles using certain dim. For example, pricing role shouldn't use Customer dim, Basic role should use just Date and Location dim. Could perspectives be used for that (specify dims/measures and grant access just to that perspective) or is there other way? Obviously, I must be overlooking something, this is really basic requirement, isn't it...

TIA,
Radim

Hi! Have a look at roles in the solution explorer. Right click on roles and explore the features.

Perspectives are not a security feature. They are more about easier browsing/navigation.

Regards

Thomas Ivarsson

|||Hi! Thanks, but of course I have tried that :) If you go there, you'll be able to see just two options (at least I do) allowed for dimension security: Read and Read/Write (or to inherit settings). I know that (unfortunatelly) perspectives are more for convenient browsing then for anything else.
Has someone really tried that with success?

Radim|||

On the following tab you have dimension data where you can give access to or deny members in dimensions.

Regards

Thomas Ivarsson

|||That's right. We've implemented dynamic member security system (clr callback function) already, but what I'm asking is how to deny the whole dimension for certain role. I don't want go down to members.

Why this cannot be implemented, is there any internal limitation?

Radim|||

OK. I understand. Can this link be of any help: http://sqljunkies.com/WebLog/mosha/archive/2005/04/10/10599.aspx ?

Regards

Thomas Ivarsson

|||Thomas, I really appreciate your effort :) But yes, I've read all post from Mosha (it's a must for everyone who wants to get deeper into AS :) ). I know that I could solve it by granting access just to Allmember in some dim, but that's not good. My thinking is - why should I unveil dimensions structure if I don't want to? If Customer dim (for example) is not interesting for certain role, why I just can't hide it. Or should I create new cube for that... ridiculous. I'm really missing this functionality and it seems strange to me, that nobody else is asking same question...

Thanks,
Radim|||

Sorry about consumed information. If you are interested in hiding a dimension by code I have not done that. I have implemented dynamic security with a fact table and a virtual cube in AS2000.

What I do not understand is why you cannot use the tab where you allow/deny access to dimensions and point that combinations to separate roles.

I have not tried this by code.

I do not know how big your cubes are but sometimes building a new cube can be the easiest solution, even if it not a good solution. We have done that depending on that we need to deny access to some measures and dependent calculations. Without separate cubes this will mean a large administrative burden. As long as they are in the same project they share dimensions and it is easy to copy scripts.

If you have large cubes I understand that this is impossible.

We have also the problem that ProClarity do not recommend dynamic security with their Analytics Platform.

Regards

Thomas Ivarsson

|||Thomas, in ideal world I'd like to have three choices on Dimensions tab: None, Read and Read/Write. I can see only Read and Read/Write. So that's useless. On Dimension Data tab I can deny all members from certain dim and that does the trick, but dimension is still visible (even though it cannot be used for analysis). Imagine that you have 20 dimensions in a cube (just as an example) and you deny all members for 18 of them. Now, in a browser (Proclarity or anyhing) you would still see all 20 dims, where 18 is completely useless. And that's what I'd like to overcome.

Could you please publish that recommendation about Proclarity? Dynamic security is heart of our cubes and Proclarity is the default browser.

And our solution is still growing, dwh is over 100GB and major cube around 10GB. So don't fancy idea about bulding and processing multiple cubes..

Radim|||

Here is a link to a ProClarity Blog: http://blogs.proclarity.com/blogs/dgustafson/default.aspx, regarding dynamic security.

Edit: I will have to test a little more time but I think you are correct. The dimensions will show but you cannot use them. If you construct perspectives after applying security that can help filtering denied dimensions.

Regards

Thomas Ivarsson

sql

Dimension Name Column Format Property SSAS 2005

I have tried entering different forms of syntax into the 'Format' property underneath the 'Name Column' property for a specific attribute within a dimension and it never seems to change the output when I view the attribute within the 'Browser' tab of the dimension. I tested this on the Adventure Works DW and I am unable to change the attribute format. Here is an example:

1. With the Adventure Works DW, open the 'Employee' dimension and modify the 'Birth Date' attribute's format property underneath name column. I have entered "d", format("DimEmployee"."BirthDate", 'mm/dd/yyyy'), and convert(varchar, "DimEmployee"."BirthDate", 101).

2. Process the dimension and click on the browser tab and view the 'Birth Date' hierarchy.

Has anyone had any luck using this 'Format' property for the 'Name Column' of an attribute? I believe you could easily do this in AS 2000, so I am wondering what the trick is in SSAS 2005. I would think that you could use this property, but I guess I need to know what the proper syntax is. I know that I could easily modify the data source view, but I want to know how to be able to do this in the future if needed.

I have checked on the web and in BOL and haven't found any reference information for this property and how to use it. If anyone knows of any documentation please let me know. I will take a look at the SQL 2008 BOL and see if that has anything new.

Thanks.

I just got a response back from Microsoft and this is what I had kind of figured because no matter what you type in this property it never would produce an error or change the results of the text.

The "Format" string for Attribute names is a stub for a later addon and is not implemented. Attribute names will only accept WChar types. Any formatting should be done either in the data source view as a "Named Calculation" or in the source table/view on the relational source.

Dimension Name Column Format Property SSAS 2005

I have tried entering different forms of syntax into the 'Format' property underneath the 'Name Column' property for a specific attribute within a dimension and it never seems to change the output when I view the attribute within the 'Browser' tab of the dimension. I tested this on the Adventure Works DW and I am unable to change the attribute format. Here is an example:

1. With the Adventure Works DW, open the 'Employee' dimension and modify the 'Birth Date' attribute's format property underneath name column. I have entered "d", format("DimEmployee"."BirthDate", 'mm/dd/yyyy'), and convert(varchar, "DimEmployee"."BirthDate", 101).

2. Process the dimension and click on the browser tab and view the 'Birth Date' hierarchy.

Has anyone had any luck using this 'Format' property for the 'Name Column' of an attribute? I believe you could easily do this in AS 2000, so I am wondering what the trick is in SSAS 2005. I would think that you could use this property, but I guess I need to know what the proper syntax is. I know that I could easily modify the data source view, but I want to know how to be able to do this in the future if needed.

I have checked on the web and in BOL and haven't found any reference information for this property and how to use it. If anyone knows of any documentation please let me know. I will take a look at the SQL 2008 BOL and see if that has anything new.

Thanks.

I just got a response back from Microsoft and this is what I had kind of figured because no matter what you type in this property it never would produce an error or change the results of the text.

The "Format" string for Attribute names is a stub for a later addon and is not implemented. Attribute names will only accept WChar types. Any formatting should be done either in the data source view as a "Named Calculation" or in the source table/view on the relational source.

Difficulty outputting in right format. -Tricky one

Hi can anyone help me with this?
I have would like to be able to output some data in a very specific way
and unfortunately my SQL isn't quite up to it. The database is not
SQL Server so in this case only SQL can be used.
I have 2 Practically Identical tables that record test results from 2
separate test points in a process. They appear like this (this example
is cut down):
Table 1(T1)
STDATE PASSFA
20051101 P
20051101 P
20051101 F
20051102 F
20051102 P
20051102 F
...and so on.
Table 2(T2)
Identical to table 1(T1) in format, although results could differ.
I would like the data to output like:
STDATE PASS(T1) FAIL(T1) PASS(T2) FAIL(T
2)
20051101 2 1 1 1
20051102 1 2 1 2
The database is not SQL Server so in this case only SQL can be used.
This is driving me crazy, please help.Try:
select
coalesce (x.STDDATE, y.STDDATE)
, x.PASS as 'PASS (T1)'
, x.FAIL as 'FAIL (T1)'
, y.PASS as 'PASS (T2)'
, y.FAIL as 'FAIL (T2)'
from
(
select
STDDATE
, sum (case when PASSFA = 'P' then 1 else 0 end) PASS
, sum (case when PASSFA = 'F' then 1 else 0 end) FAIL
from
T1
group by
STDDATE
) x
full join
(
select
STDDATE
, sum (case when PASSFA = 'P' then 1 else 0 end) PASS
, sum (case when PASSFA = 'F' then 1 else 0 end) FAIL
from
T2
group by
STDDATE
) y on y.STDDATE = x.STDDATE
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"philipbennett25" <pbennett@.xyratex.com> wrote in message
news:1132696702.642862.158690@.g43g2000cwa.googlegroups.com...
> Hi can anyone help me with this?
> I have would like to be able to output some data in a very specific way
> and unfortunately my SQL isn't quite up to it. The database is not
> SQL Server so in this case only SQL can be used.
>
> I have 2 Practically Identical tables that record test results from 2
> separate test points in a process. They appear like this (this example
> is cut down):
> Table 1(T1)
> STDATE PASSFA
> 20051101 P
> 20051101 P
> 20051101 F
> 20051102 F
> 20051102 P
> 20051102 F
> ...and so on.
> Table 2(T2)
> Identical to table 1(T1) in format, although results could differ.
>
> I would like the data to output like:
> STDATE PASS(T1) FAIL(T1) PASS(T2) FAIL(T2)
> 20051101 2 1 1 1
> 20051102 1 2 1 2
> The database is not SQL Server so in this case only SQL can be used.
> This is driving me crazy, please help.
>|||Hi Tom,
Thanks for replying to my question. I have tried this out but I cannot
get it to work (more than likely to be my own lack of skill). To give a
bit of background the data is coming from an AS/400 DB which doesnt
support as many functions as SQL server.
Table 1 is actually called TESTER and Table 2 is called BURNIN.
I had been able the data for a single table in the fromat that I wanted
but not both tables. I had also managed to UNION the tables so that the
outputs appeared in the format but had a criteria that showed which
table the output had come from, but I had really wanted to be able to
have the 'inline' output from both tables by date (STDATE)
Thanks again for your help.
*** Sent via Developersdex http://www.examnotes.net ***|||SQL has different dialects on different boxes. You may be able to create a
linked server in SQL Server to point to the AS/400 and then use T-SQL to do
the query. Failing that, you could to a DTS data pump to haul the data from
AS/400 to SQL Server and to it that way.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"Philip Bennett" <pbennett@.xyratex.com> wrote in message
news:u1CUUKB8FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Hi Tom,
> Thanks for replying to my question. I have tried this out but I cannot
> get it to work (more than likely to be my own lack of skill). To give a
> bit of background the data is coming from an AS/400 DB which doesnt
> support as many functions as SQL server.
> Table 1 is actually called TESTER and Table 2 is called BURNIN.
> I had been able the data for a single table in the fromat that I wanted
> but not both tables. I had also managed to UNION the tables so that the
> outputs appeared in the format but had a criteria that showed which
> table the output had come from, but I had really wanted to be able to
> have the 'inline' output from both tables by date (STDATE)
> Thanks again for your help.
> *** Sent via Developersdex http://www.examnotes.net ***|||Tom,
I am sure that I am being stupid, That SQL you kindly wrote for me
seems so close. It keeps stopping and saying 'Table Y Undefined'. Being
able to do this in the code, without any hardware changes/improvements
would make my life much better.
Thanks|||Could you please post your DDL please the exact code you're using?
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"philipbennett25" <pbennett@.xyratex.com> wrote in message
news:1132768693.786210.98530@.g14g2000cwa.googlegroups.com...
> Tom,
> I am sure that I am being stupid, That SQL you kindly wrote for me
> seems so close. It keeps stopping and saying 'Table Y Undefined'. Being
> able to do this in the code, without any hardware changes/improvements
> would make my life much better.
> Thanks
>|||Tom, I dont know much about DDL's (Data Definition Library). Simple SQL
seems to be what I can use.
select
coalesce (x.STDATE, y.STDATE)
, x.PASS as PASSa
, x.FAIL as FAILa
, y.PASS as PASSb *Alias dont need inverted commas for me*
, y.FAIL as FAILb
from
(
select
STDATE
, sum (case when PASSFA = 'P' then 1 else 0 end) PASS
, sum (case when PASSFA = 'F' then 1 else 0 end) FAIL
from
TESTER
group by
STDATE
) x
full join
(
select
STDATE
, sum (case when PASSFA = 'P' then 1 else 0 end) PASS
, sum (case when PASSFA = 'F' then 1 else 0 end) FAIL
from
BURNIN
group by
STDATE
) y on y.STDATE = x.STDDATE|||Tom, I just got it, It worked, So thank you so much. Final question if
you can bear it. STDATE appears as 20051122 format and is a string, so
how would I mod this query to only take in the first 6 characters and
therefore group it by month. Also, where do I add criteria?
Really, Thanks so much. -I owe you a Pint.|||On 24 Nov 2005 09:25:20 -0800, philipbennett25 wrote:

>Tom, I just got it, It worked, So thank you so much. Final question if
>you can bear it. STDATE appears as 20051122 format and is a string, so
>how would I mod this query to only take in the first 6 characters and
>therefore group it by month. Also, where do I add criteria?
>Really, Thanks so much. -I owe you a Pint.
Hi philipbennett25,
I guess the real question is why you use a string to store a date value.
But since that's what you're currently facing, here's how you could
change the query you posted in your previous post:
select
coalesce (x.YYYYMM, y.YYYYMM)
, x.PASS as PASSa
, x.FAIL as FAILa
, y.PASS as PASSb *Alias dont need inverted commas for me*
, y.FAIL as FAILb
from
(
select
LEFT(STDATE, 6) AS YYYYMM
, sum (case when PASSFA = 'P' then 1 else 0 end) PASS
, sum (case when PASSFA = 'F' then 1 else 0 end) FAIL
from
TESTER
group by
LEFT(STDATE, 6)
) x
full join
(
select
LEFT(STDATE, 6) AS YYYYMM
, sum (case when PASSFA = 'P' then 1 else 0 end) PASS
, sum (case when PASSFA = 'F' then 1 else 0 end) FAIL
from
BURNIN
group by
LEFT(STDATE, 6)
) y on y.YYYYMM = x.YYYYMM
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)sql