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 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.

Dimension Attribute Question

In my relational model, I have a dimension table called ProductRelease with two foreign keys to a Time table. The Time table looks something like this:

DayDate (PK, datetime, not null)

YearId (int, not null)

MonthId (int not null)

The ProductRelease table looks something like this:

ReleaseId (PK, int, not null)

ScheduledDate (FK, datetime, not null)

ActualDate (FK, datetime, not null)

I'm simplifying things for this question, but for the Product Release dimension in Analysis Services, I want to have two attributes: one called Scheduled Month Id; and the other called Actual Month Id.

In the Visual Studio dimension editor, I drag the ActualDate column from the ProductRelease table in the Data Source View pane to the Attributes pane, and then do the same with the ScheduledDate column. This creates two attributes called Actual Date and Scheduled Date. I then process the dimension, go to the Browser tab, click Member Properties, and check the Actual Date and Scheduled Date attributes, and the correct dates are displayed in the browser. For the dimension member I am looking at, the values are:

Scheduled Date = 11/29/2006

Actual Date = 12/8/2006

So far so good -- this is what I expected.

Next, I drag the MonthId column from the Time table in the Data Source View pane to the Attributes pane. This creates a new attribute called Month Id, which I then rename to Actual Month Id. Then I create another attribute by dragging Time.MonthId again and rename it to Scheduled Month Id.

After processing the dimension, I go back to the Browser tab, reconnect, and check Actual Month Id and Scheduled Month Id in Member Properties. The result is that both columns have the same value:

Actual Month Id = 11

Scheduled Month Id = 11

For the Actual Month Id and Scheduled Month Id dimension attributes, they both have their KeyColumns property set to Time.MonthId (Integer), and NameColumn and ValueColumn are both set to (none). These were the defaults when I dragged the columns to the Attribute pane.

My question is, what do I need to change so Actual Month Id will be 12? I cannot seem to figure out the correct values that need to go into KeyColumns, NameColumn, and ValueColumn to get this to work.

Thanks for any help!

-Larry

You might need to create a database view or DSV named query which explicity joins [ProductRelease] twice to the [Time].

So that you have a [ProductRelease] table that looks like

ReleaseId (PK, int, not null)

ScheduledDate (FK, datetime, not null)

ScheduledMonthID (int, not null)

ActualDate (FK, datetime, not null)

ActualMonthID (int, not null)

The dimension designer won't recognize the multiple "roles" of the [Time] table and treat them accordingly like the cube designer will, which brings be to my next suggestion, that the [ProductRelease] might be used as a fact table. The measures might be

ReleaseCount = 1,

OnTimeCount = CASE WHEN SceduledDate <= ActualDate THEN 1 ELSE 0 END

etc.

Either that or move the the scheduled/actual dates to the relevant fact tables which reference the ProductRelease dimension.

My short story here is that Fact tables are for resolving many-to-many relationships. It can be done in dimensions but very seldom with one table.

Hope this helps (or even just makes sense).

|||

Hi John,

Thanks for the response. ProductRelease is actually a fact-dimension table, and I do have some measures like ReleaseDays and DeliverySlipDays. Furthermore, I am using Scheduled Date as a cube dimension and plan on using Actual Date as a cube dimension as well. So the Time dimension will be used as a role-playing dimension.

But I also want to use Scheduled Date and Actual Date in a dimension hierarchy so users can navigate through the releases by those two dates (e.g. all releases scheduled for Q1, all releases actually released in Q2, etc.).

Anyway, you answered my question -- thank you very much; i.e. the dimension designer won't recognize the multiple roles. So I guess I'll create a DSV named query as you suggest for the purpose of the dimension navigation.

As an aside, I'm curious if what I'm trying to do is achievable via AMO. In other words, could I write a C# app using the class library to define these multiple roles for the dimension attributes. Probably not what I want to do since that will get my db out of synch with my Visual Studio project, but just a curiosity.

-Larry

Different results for MDX queries when using Attribute Hierarchies

We receive different results for the follow 2 MDX expressions. The only difference is that the second parameter in the Where clause uses a separate dimension called Asset Class in the first query, whereas in the first it uses an Attribute Hierarchy dimension on the Asset dimension.

The first provides the expected results which is the top 10 Equity assets, whereas the second returns just 3 Equity assets which belong to the top 10 assets overall.

Can anyone explain this? Using a cross join in the Topcount function works, but unfortunately ProClarity which we are using does not deal with this properly.

Query 1

SELECT NON EMPTY { [Measures].[Value Base] } ON COLUMNS ,

NON EMPTY { TOPCOUNT( { [Asset].[Asset].[All].CHILDREN }, 10, ( [Measures].[Value Base] ) ) } ON ROWS

FROM [MIQB Daily]

WHERE ( [Period].[Month].&[2005-11-01T00:00:00], [Asset Class].[Asset Class Category].&[Equity])

Query 2

SELECT NON EMPTY { [Measures].[Value Base] } ON COLUMNS ,

NON EMPTY { TOPCOUNT( { [Asset].[Asset].[All].CHILDREN }, 10, ( [Measures].[Value Base] ) ) } ON ROWS

FROM [MIQB Daily]

WHERE ( [Period].[Year Month Hierarchy].[Month].&[2005-11-01T00:00:00], [Asset].[Asset Class Hierarchy].[Asset Class Category].&[Equity )

At first sight this might be an issue with your attribute relationships. Have you looked into that?|||

Yes we believe the relations have been set up correctly and the indicator on the hierarchy has turned green.

We think it is because the hierarchy in the Where clause is in the same dimension as the hierarchy in the Topcount function in the second case - possibly something to do with the auto exists?

Interestingly if you use the browser in the Dev Studio and filter on the [Asset].[Asset Class Hierarchy] in the sepate Filter pane, then doing a top 10 query works fine, but if you put the filter on the page section of the browser, it does not produce the correct results.

|||

Can you "translate" this to an Adventure Works cube? Do you get the same results if you run the two queries in management studio?

Regards

/Thomas

|||This is a known bug that Microsoft is fixing (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=549706&SiteID=1). Should get it by the end of August. It is a major fix that will be backported to SP1 (as it required some fixes from the SP2 branch).|||

Many thanks for your post - we were worried that it might have been a 'feature' rather than a bug

Paul

|||We are testing the fix now and the results look promising.

Different results for MDX queries when using Attribute Hierarchies

We receive different results for the follow 2 MDX expressions. The only difference is that the second parameter in the Where clause uses a separate dimension called Asset Class in the first query, whereas in the first it uses an Attribute Hierarchy dimension on the Asset dimension.

The first provides the expected results which is the top 10 Equity assets, whereas the second returns just 3 Equity assets which belong to the top 10 assets overall.

Can anyone explain this? Using a cross join in the Topcount function works, but unfortunately ProClarity which we are using does not deal with this properly.

Query 1

SELECT NON EMPTY { [Measures].[Value Base] } ON COLUMNS ,

NON EMPTY { TOPCOUNT( { [Asset].[Asset].[All].CHILDREN }, 10, ( [Measures].[Value Base] ) ) } ON ROWS

FROM [MIQB Daily]

WHERE ( [Period].[Month].&[2005-11-01T00:00:00], [Asset Class].[Asset Class Category].&[Equity])

Query 2

SELECT NON EMPTY { [Measures].[Value Base] } ON COLUMNS ,

NON EMPTY { TOPCOUNT( { [Asset].[Asset].[All].CHILDREN }, 10, ( [Measures].[Value Base] ) ) } ON ROWS

FROM [MIQB Daily]

WHERE ( [Period].[Year Month Hierarchy].[Month].&[2005-11-01T00:00:00], [Asset].[Asset Class Hierarchy].[Asset Class Category].&[Equity )

At first sight this might be an issue with your attribute relationships. Have you looked into that?|||

Yes we believe the relations have been set up correctly and the indicator on the hierarchy has turned green.

We think it is because the hierarchy in the Where clause is in the same dimension as the hierarchy in the Topcount function in the second case - possibly something to do with the auto exists?

Interestingly if you use the browser in the Dev Studio and filter on the [Asset].[Asset Class Hierarchy] in the sepate Filter pane, then doing a top 10 query works fine, but if you put the filter on the page section of the browser, it does not produce the correct results.

|||

Can you "translate" this to an Adventure Works cube? Do you get the same results if you run the two queries in management studio?

Regards

/Thomas

|||This is a known bug that Microsoft is fixing (see http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=549706&SiteID=1). Should get it by the end of August. It is a major fix that will be backported to SP1 (as it required some fixes from the SP2 branch).|||

Many thanks for your post - we were worried that it might have been a 'feature' rather than a bug

Paul

|||We are testing the fix now and the results look promising.

Different Default Members for Role Playing Dimension

I have a dimension named "Unit of Measure" that has an attribute hier named "UOM Abbrev". This dimension is used twice in my cube, named "Base UOM" and "Reporting UOM". I am trying to set the default member for the "UOM Abbrev" but it complains if I tried to set it to a value from the dimension like [Unit of Measure].[UOM Abbrev].&[Something] because [Unit of Measure] isn't found in the cube.

Yet if I try to set it to one of the specific role playing names I get an error complaining about the other role playing name.

Is it possible to have the default member for an attrib hier be different based on which role-playing dimension is being used?

I would really like to have something like this:

iif(Dimension is [Base UOM], [Base UOM].[UOM Abbrev].&[Abbrev1], [Reporting UOM].[UOM Abbrev].&[Abbrev2])

Is this possible and if so can someone provide me with syntax to handle it properly?

Thanks,

Ashton Hobbs

Hi Ashton,

You could try updating the default member for each role with cube MDX script statements instead, like:

Code Snippet

ALTER CUBE CurrentCube UPDATE DIMENSION [Base UOM].[UOM Abbrev],

DEFAULT_MEMBER = [Base UOM].[UOM Abbrev].&[Abbrev1];

ALTER CUBE CurrentCube UPDATE DIMENSION [Reporting UOM].[UOM Abbrev],

DEFAULT_MEMBER = [Reporting UOM].[UOM Abbrev].&[Abbrev2];

Different calculation based on dimension attribute?

Hello

I would like to do this in pseudo code for a calculated member:

if(dim.value == 1) then measure.val

else -measure.val

Anyone have a suggestion?

For AS2005, you should include the following in the MDX script for your cube (your will have to adjust for naming):

CREATE MEMBER CURRENTCUBE.[Measures].[MyCalculatedMeasure] AS

[Measures].[MyMeasure] * -1,

NON_EMPTY_BEHAVIOR = [Measures].[MyMeasure];

SCOPE ([MyDimension].[MyAttribute].[1], [Measures].[MyCalculatedMeasure];

This = [Measures].[MyMeasure];

END SCOPE;

... and if it's a calculated member defined at run-time.

WITH MEMBER [Measures].[MyCalculatedMeasure] AS

IIF([MyDimension].[MyAttribute].currentmember IS [MyDimension].[MyAttribute].[1], [Measures].[MyMeasure], -1 * [Measures].[MyMeasure]), NON_EMPTY_BEHAVIOR = [Measures].[MyMeasure]

SELECT .....

|||

Thanks for the quick response, I did solve my first problem.. sorta, with SQL in the integration layer. Now I have almost the same problem though. Now I am trying to do this instead:

If([Dim Account].[Dim Account Type] = 1) then 0 else [Measures].[Fact Result AMOUNT]

I modified the query like so, but it only returns 0, never the Fact Result AMOUNT measure.

CREATE MEMBER CURRENTCUBE.[Measures].[Cost] AS

0,

NON_EMPTY_BEHAVIOR = [Measures].[Fact Result AMOUNT];

SCOPE ([Dim Account].[Dim Account Type].[1], [Measures].[Cost]);

This = [Measures].[Fact Result AMOUNT];

END SCOPE;

|||

Please remove the NON_EMPTY_BEHAVIOR = [Measures].[Fact Result AMOUNT] part.

If you don't remove it - you are risking to get wrong results.

|||

It looks like your logic is reversed. If you want to achieve the following

If([Dim Account].[Dim Account Type] = 1) then 0 else [Measures].[Fact Result AMOUNT]

Then the script should be.

CREATE MEMBER CURRENTCUBE.[Measures].[Cost] AS

[Measures].[Fact Result AMOUNT],

NON_EMPTY_BEHAVIOR = [Measures].[Fact Result AMOUNT];

SCOPE ([Dim Account].[Dim Account Type].[1], [Measures].[Cost]);

This = 0;

END SCOPE;

Mosha - does this address your issue about the NON_EMPTY_BEHAVIOR returning incorrect results? If not would you be able to explain where the issue is?

|||

Mosha - does this address your issue about the NON_EMPTY_BEHAVIOR returning incorrect results? If not would you be able to explain where the issue is?

No, it is still wrong in this example, because when Fact Result AMOUNT can be NULL, the Cost won't be NULL (it will be 0). Note that in your first example, it was correct, since MyCalculatedMeasure and MyMeasure are always either together NULLs or together not NULLs (assuming that there are no other calculations in the cube).