Add YTD for number of cubes?

Hello,

We have number of cubes sharing one Time dimension; we need add YTD, QTD, and MTD to each cube, is there a easy way to add YTD, QTD to Time dimension one time instead of add them to the cube one by one?

Thanks in advance for the advice.

Yes - you can add the member holders for YTD, MTD, QTD etc to the corresponding attribute of the Time dimension, and add all the MDX formulas to the dedicated column in the dimension table, and the use CustomFormulaColumn property on the attribute to point to that column. After that, every cube which includes this dimension will have all these formulas automatically.|||Thank you very much Mosha for the help.

1. add YTD column to the Time dimension
2, add all the MDX formulas to the YTD column
(
[All Periods].[YTD].[Year to Date],
[All Periods].[Year].[Year].Members,
[All Periods].[PK Date].Members
) =

Aggregate(
{ [All Periods].[YTD].DefaultMember } *
PeriodsToDate(
[All Periods].[Year - Quarter - Month].[Year],
[All Periods].[Year - Quarter - Month].CurrentMember
)
);

You mean all these code add into column?
3, use CustomFormulaColumn property on the attribute.
where can I find 'CustomFormulaColumn' property?
I checked BI Studio, and could not find that.

Thanks.
|||

You can only put the MDX expression, not the MDX statement into this column So in your case it would be the right hand side of the assignment.

The real name of the attribute's property is CustomRollupColumn.

|||I put MDX expression in YTD column, and point CustomRollupColumn property to All_Periods.YTD. After processing, YTD just show the text which are the 'MDX expression' I put in and measure.order became '#VALUE'.

What else I am missing?

Thanks.
|||I did Google search for CustomRollupColumn Property, some people said that it's work in SSAS 2000, but not in SSAS 2005. Is it true?

|||CustomRollupColumn property works fine in SSAS 2005. Probably it is the best for you to look at example to understand how it should be set up. In the Adventure Works sample, take a look at Accounts dimension, Accounts attribute - it has custom rollups defined.

Add YTD for number of cubes?

Hello,

We have number of cubes sharing one Time dimension; we need add YTD, QTD, and MTD to each cube, is there a easy way to add YTD, QTD to Time dimension one time instead of add them to the cube one by one?

Thanks in advance for the advice.

Yes - you can add the member holders for YTD, MTD, QTD etc to the corresponding attribute of the Time dimension, and add all the MDX formulas to the dedicated column in the dimension table, and the use CustomFormulaColumn property on the attribute to point to that column. After that, every cube which includes this dimension will have all these formulas automatically.|||Thank you very much Mosha for the help.

1. add YTD column to the Time dimension
2, add all the MDX formulas to the YTD column
(
[All Periods].[YTD].[Year to Date],
[All Periods].[Year].[Year].Members,
[All Periods].[PK Date].Members
) =

Aggregate(
{ [All Periods].[YTD].DefaultMember } *
PeriodsToDate(
[All Periods].[Year - Quarter - Month].[Year],
[All Periods].[Year - Quarter - Month].CurrentMember
)
);

You mean all these code add into column?
3, use CustomFormulaColumn property on the attribute.
where can I find 'CustomFormulaColumn' property?
I checked BI Studio, and could not find that.

Thanks.
|||

You can only put the MDX expression, not the MDX statement into this column So in your case it would be the right hand side of the assignment.

The real name of the attribute's property is CustomRollupColumn.

|||I put MDX expression in YTD column, and point CustomRollupColumn property to All_Periods.YTD. After processing, YTD just show the text which are the 'MDX expression' I put in and measure.order became '#VALUE'.

What else I am missing?

Thanks.
|||I did Google search for CustomRollupColumn Property, some people said that it's work in SSAS 2000, but not in SSAS 2005. Is it true?

|||CustomRollupColumn property works fine in SSAS 2005. Probably it is the best for you to look at example to understand how it should be set up. In the Adventure Works sample, take a look at Accounts dimension, Accounts attribute - it has custom rollups defined.sql

add two time hierarchies to a dimension?

I got problem when trying to add two exactly same time hierachies to a
dimension.
There are two fields StartTimeID, and EndTimeID, which point to Time
dimension table by TimeID. I would like to use these two time hierachies to
restrict my query.
Something like SQL: Select ... from ... where StartTimeID > 1000 and
EndTimeID < 2000 for a time range specified.
I am testing this. Is this possible in MDX after I have the hierachies?
Guangming
I found the solution: add one more named set (like View) of Time table and
join these two time ID fields to these two Time dimension tables. Everything
should be ok.
"Word 2003 memory Leakage" wrote:

> I got problem when trying to add two exactly same time hierachies to a
> dimension.
> There are two fields StartTimeID, and EndTimeID, which point to Time
> dimension table by TimeID. I would like to use these two time hierachies to
> restrict my query.
> Something like SQL: Select ... from ... where StartTimeID > 1000 and
> EndTimeID < 2000 for a time range specified.
> I am testing this. Is this possible in MDX after I have the hierachies?
>
> Guangming
|||I think you will still have issues, the two MDX ranges will be evaluated
independantly, effectively resulting the equivalent of a logical OR
between the two conditions.
eg: Select ... from ... where StartTimeID > 1000 OR
EndTimeID < 2000 for a time range specified.
Because multiple rows in the fact table typically rollup into a single
cell in the cube these sort of queries are difficult to implement in a
cube.
I don't know if anyone else out there has any experience with this?
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

add two time hierarchies to a dimension?

I got problem when trying to add two exactly same time hierachies to a
dimension.
There are two fields StartTimeID, and EndTimeID, which point to Time
dimension table by TimeID. I would like to use these two time hierachies to
restrict my query.
Something like SQL: Select ... from ... where StartTimeID > 1000 and
EndTimeID < 2000 for a time range specified.
I am testing this. Is this possible in MDX after I have the hierachies?
GuangmingI found the solution: add one more named set (like View) of Time table and
join these two time ID fields to these two Time dimension tables. Everything
should be ok.
"Word 2003 memory Leakage" wrote:

> I got problem when trying to add two exactly same time hierachies to a
> dimension.
> There are two fields StartTimeID, and EndTimeID, which point to Time
> dimension table by TimeID. I would like to use these two time hierachies t
o
> restrict my query.
> Something like SQL: Select ... from ... where StartTimeID > 1000 and
> EndTimeID < 2000 for a time range specified.
> I am testing this. Is this possible in MDX after I have the hierachies?
>
> Guangming|||I think you will still have issues, the two MDX ranges will be evaluated
independantly, effectively resulting the equivalent of a logical OR
between the two conditions.
eg: Select ... from ... where StartTimeID > 1000 OR
EndTimeID < 2000 for a time range specified.
Because multiple rows in the fact table typically rollup into a single
cell in the cube these sort of queries are difficult to implement in a
cube.
I don't know if anyone else out there has any experience with this?
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

add an attribute to an existing dimension

Hello experts,

Is there a possibility to add an attribute to an existing dimension? (AS2005)

Right now I solve this problem with delete and create this dimension, but it’s a bad solution because a delete all properties too.

Best regards

Alex

If the dimension is based off a table or view reference in the DSV, right-click the background of the DSV and select Refresh. If you are using a named query, you will need to add the field to the query.

Then, go to the dimension editor. The subset of the DSV displayed in the editor should now show the field. Drag that field to the attribute hierarchy list on the left. You now have a new attribute hierarchy.

Make sure its relationships to other attribute hierarchies are appropriately set.

B.

|||

Hello Bryan

Thank you very much. It’s very easy if you know where you have to click ^^

Have a nice day