SQL Server Performance Forum – Threads Archive

custom rollup formula in AS 2005

Hi all, I’m new to the Forum. Does anyone knows how to realize the "custom rollup formula" in Analysis Services 2005? It seems different from how in Analysis Services 2000… Thank you very much. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Vania

Custom rollup formulas are stored in a column of the dimension table. You can enable custom rollup formulas by setting the CustomRollupColumn property on an attribute. You can create the attribute using the Dimension Designer. Check out "Adding Attributes to a Dimension" In BOL SQL 2005 Beta. You can also create custom member formulas using the "Add Business Intelligence" wizard. Raulie
@hp All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thank you very much for your kind reply.
Actually I don’t understand why should I fill in a whole column to set up the rollup of an attribute.
In AS 2000 I simply had to write a formula in the hierarchy level properties, now I have to fill in an entire column. Any idea?
Thanks,
Vania

I aggree, MS gave Analysis Services 2005 a face lift, implants, and liposuction. Keep in mind that this is only a beta version we are working with so maybe they will include a fimiliar way of doing that. Read on creating custom rollup formulas. "To apply a single MDX expression to all members of an attribute, create a named calculation on the dimension table that returns an MDX expression as a literal string. Then, specify the named calculation with the CustomRollupColumn property setting on the attribute that you want to configure. A named calculation is a column in a data source view table that returns row values defined by a SQL expression. For more information on constructing named calculations, see Creating Named Calculations in Data Sources" Raulie
@hp All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks again.
You say: "To apply a single MDX expression to all members of an attribute, create a named calculation on the dimension table that returns an MDX expression as a literal string. Then, specify the named calculation with the CustomRollupColumn property setting on the attribute that you want to configure. A named calculation is a column in a data source view table that returns row values defined by a SQL expression. For more information on constructing named calculations, see Creating Named Calculations in Data Sources" I tried also this way, but realized that, in the Data Source View, it’s not possible to create a "Named calculation" in a View. Further, If I create the "Named calculation" in a Table, I cannot add the "Named Calculation" column in a View. Do you agree?
In this case, if I put attributes in a view, it seems that I have to use a column in the very Database for the rollup…
Thank you again.

Your welcome, and I must admit that I have not spent as much time as I would like to on Analysis Services 2005. Yes, Custom rollup formulas were as simple as just inserting an MDX expression in the level properties. Now the above applies to column level custom rollups, I am not convinced that there is not a way to implement MDX level custom rollups in 2005. I’ll look into it more put if you find something please do share. Raulie
@hp All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I’ve just done a lot of tests, by comparing 2000 and 2005 MSAS behaviour. Actually, it seems that the "customRollupColumn" in 2005, is the correspondant of the "Custom members" in 2000, that is to personalize the aggregations on specific members. I could not find in MSAS2005 anything similar to the "CustomRollupFormula" of MSAS2000, that is to personalize the aggregation of a specific level of a hierarchy. Further, I could not make the 2005 "customRollupColumn" work! Could you? I think the problem could be that the formula is not applied to a level of a hierarchy no more, but to an attribute! Thus, this attribute can be a "level m" in a hierarchy and a "level n" in another hierarchy, having different levels under it. So it could have different values depending on the hierarchy it is applied to. IN fact, let’s have for instance such a dimension: Key attribute | 1st attribute: Quarter | 2nd Attribute<img src=’/community/emoticons/emotion-7.gif’ alt=’:S’ />emester(half an year) january | 1Â°Quarter | 1st Semester May | 2Â°Quarter | 1st Semester …. If i say that "1st Semester" member of "Semester" attribute must aggregate with the max value of its children, it would have a different value in a hierarchy having "Semester" as 1st and unique level from the one achieved in a hierarchy having "Semester" as "1st level" and "Quarter" as "2nd level". If so, the same problem should happen on the CustomRollupFormula for the entire attribute… What about this? I wonder how Yukon will face (or has faced) this issue… Thanks in advance for your opinion…

The documentation in booksonline 2005 does not give too much detail on this, either that or I havn’t digged deep enough. When you created the customRollupColumn what error did you get. Funny when you use the Business Intelligence wizard and create a custom member formula it creates the customRollupColumn for you corresponding to what you said about the correspondant custom member in AS 2000. Here is a excerpt of the new attribute structure in 2005 Attribute-based dimensions
Analysis Services 2005 structures a cube around dimensional attributes rather than dimensional hierarchies. In Analysis Services 2000, dimensional designs are dominated by hierarchies such as {Year, Month, Day} or {Country, Region, City}. These hierarchies demanded strong data relationships between levels. Attributes, which were exposed as member properties and virtual dimensions, were second-class citizens. While it was possible to make attributes into physical dimensions, performance considerations discouraged widespread use of this technique. Users familiar with relational structures have been perplexed by the strong focus on hierarchies in OLAP databases. The Analysis Services 2005 structure is more akin to a relational dimensional structure. A dimension contains many attributes, each of which can be used for slicing and filtering queries, and each of which can be combined into hierarchies regardless of data relationships. Users who come from an OLAP background understand the value of strong hierarchies, wherein you can be certain that Cities roll up cleanly to Regions and Countries. These natural hierarchies still exist, and should be defined where appropriate: query performance benefits obtain from their use. http://www.microsoft.com/technet/prodtechnol/sql/2005/evaluate/dwsqlsy.mspx Raulie
@hp All postings are provided â€œAS ISâ€ with no warranties for accuracy.

I agree with you, there isn’t much information on the calculations of AS2005 anywhere.. When I create the customRollupColumn, sometimes there are not errors declared, but I cannot obtain my totals, in fact have empty cells. Other times, when I open the cube browser, have the error: "Error in the OLE DB provider. Error 3238658213 (the CurrentMember function can be called only on a Named Set)"
I can’t understand the problem, since I am using the same way I used in AS2000 to apply the "custom member formula": a column with mdx expressions, involving the .currentmember.children. In AS2000 it worked…
If I use a not variable expression, like 2*3, achieve the right value: 6.
Any idea? Thanks you very much again.

Well, finally I have discovered from the help of MSAS2005 Feb05 CTP that the custom level formulas (custom rollup formulas) don’t exist no more, but have been replaced by MDX scripts. Gonna try it…

]]>