Aggregate Function - None

Hello,

I am having problems getting the Aggregate Function None to work on my cube. I'm running AS2005 SP1 developer edition, both 32-bit and 64-bit, on two different machines (both of which exhibit this problem).

My cube has 7 dimensions. It has one fact table and 7 dimension tables. The dimension tables each contain a member_id column and a parent_member_id column. The fact table has one column per dimension, which are foreign keys of the member_id columns of the dimension tables.

I have set up a data source view with all 8 tables, constraints on each dimension table between member_id and parent_member_id, and one constraint linking each dimension table to the fact table.

I've set up 7 parent-child dimensions, with two attributes each. The first is called Flat and has member_id as its key column, and AttributeHierarchyVisible is set to False. The second attribute is called Hierarchy and has parent_member_id as its key column, Usage set to Parent and IsAggregatable set to False. Hierarchy is related to Flat.

The cube itself uses the 7 dimensions. It has one measure group called FACTS, which contains a single measure called Data Value. Data Value is linked to a Double source column in the fact table.

The problem I have is with the AggregationFunction option. By default it is set to Sum, and it calculates for each non-leaf member the sum of all its children plus itself. As my fact table contains pre-calculated values for every member, I don't want to do this. I want the behaviour that is described in the help text for the None option:

"No aggregation is performed, and all values for leaf and nonleaf members in a dimension are supplied directly from the fact table for the measure group that contains the measure. If no value can be read from the fact table for a member, the value for that member is set to null."

However, when I set the AggregateFunction to None, it still behaves the same way as with Sum. I can set the option to Count, and the value returned corresponds perfectly to the count of corresponding rows in the fact table. Min, Max and DistinctCount also all work perfectly.

I have also tried using a unary operator column for each of the dimensions, as this was how I accomplished the same thing under AS2000, but it had absolutely no effect.

Any advice or hints will be appreciated!

Thanks,

I've found the problem... or at least the first one.

My cube also had a calculated member (a calculated measure, more specifically). While trying to find out why values were being rolled up into parent members, I naturally trimmed down the cube, removing things one at a time to see if it would work correctly with the most basic cube. I did remove the calculated member, but Visual Studio left a small remnant of it in the code for the cube. After creating a calculated member and deleting it, the following is still left in the code:

<MdxScripts>

<MdxScript dwd:design-time-name="7caf66f4-1187-4bb3-8a46-160e81bac05b">

<ID>MdxScript</ID>

<Name>MdxScript</Name>

<CreatedTimestamp>0001-01-01T00:00:00Z</CreatedTimestamp>

<LastSchemaUpdate>0001-01-01T00:00:00Z</LastSchemaUpdate>

</MdxScript>

</MdxScripts>

When I remove this and deploy and process the cube, the rollups work correctly when I define unary operators for each dimension with the operator '~' for each member.

This is still an issue for me, since I really do need to be able to add that calculated member back at some point. To me, the fact that even when the calculated member itself is removed, the mere fact that the above "empty" MDX script still remains, sounds like a bug. Can anyone confirm this?

Thanks,

|||

When there is no MDX script explicitly associated with a cube, a default MDX script is still used:

http://msdn2.microsoft.com/en-us/library/ms145492.aspx

>>

SQL Server 2005 Books Online

The Basic MDX Script (MDX)

...

If there is no MDX script associated with a cube, the cube assumes the default MDX script. A cube needs to be associated with at least one MDX script because a cube relies on the MDX script to determine calculation behavior. In other words, a cube that was not associated with an MDX script or was associated with an empty MDX script could not and would not be able to calculate any cells. If you programmatically create cubes, either by using Analysis Services Scripting Language (ASSL) commands or by using Analysis Management Objects (AMO), it is recommended that you create a default MDX script containing a single CALCULATE statement for the cube.

...

>>

In order for roll-up to work, there should be a Calculate command in the cube MDX script , like:

<Commands>
<Command>
<Text>/*-- Aggregate leaf data --*/
Calculate;

http://msdn2.microsoft.com/en-us/library/ms144939.aspx

>>

SQL Server 2005 Books Online

CALCULATE Statement (MDX)

Updated: 17 July 2006

Populates each cell in a cube with an aggregate value.

...

The CALCULATE statement is automatically included as the first statement in a cube's MDX script when you create a cube by using Business Intelligence Development Studio. The CALCULATE statement tells each cell in the cube to aggregate from lower granularity cells. After a cell is aggregated, if you subsequently populate lower granularity cells by using expressions, it impacts the aggregated value of higher granularity cells. You almost always want this aggregation to happen, but you can remove it or cause other statements to execute before this statement.

...

>>

|||

It worked! Thanks. You're my hero.

Daniel