AggregateFunction = None

Hi everibody.

I use a AggregateFunction = None for a measure (price for article). When I go in a client Olap, every data is Null.

Where I wrong?

Tank you

Nothing is wrong. AggregationFunction = None means the data is not aggregated and only exists on leaves of measure group. Therefore all aggregated cells are NULL. This is how None is supposed to work.|||

Sorry Mosha. For first tank you, but.....

I read that leaf of a hierarchy take value from Fact table, then the total or subtotal don't exist, but value for leaf must me exist. Instead, I never see data

Bye from Florence

Marco

|||

There is important difference between hierarchy leaves and measure group leaves. The data is loaded at measure group leaves, not at hierarchy leaves. For more details about difference between the two you can read the following: http://www.sqljunkies.com/WebLog/mosha/archive/2006/04/29/leaves.aspx

|||

Tahk you Mosha. I try to understad......

Bye Bye

Marco

AggregateFunction = None

Hi everibody.

I use a AggregateFunction = None for a measure (price for article). When I go in a client Olap, every data is Null.

Where I wrong?

Tank you

Nothing is wrong. AggregationFunction = None means the data is not aggregated and only exists on leaves of measure group. Therefore all aggregated cells are NULL. This is how None is supposed to work.|||

Sorry Mosha. For first tank you, but.....

I read that leaf of a hierarchy take value from Fact table, then the total or subtotal don't exist, but value for leaf must me exist. Instead, I never see data

Bye from Florence

Marco

|||

There is important difference between hierarchy leaves and measure group leaves. The data is loaded at measure group leaves, not at hierarchy leaves. For more details about difference between the two you can read the following: http://www.sqljunkies.com/WebLog/mosha/archive/2006/04/29/leaves.aspx

|||

Tahk you Mosha. I try to understad......

Bye Bye

Marco

Aggregate - Sum with group by

Hi,

I'm trying to use the aggregate transformation to sum my orders table unit price and quantity with a grouping of state but i can't see how to add the sub grouping. My order table has the following fields of interest Unit Price (Money), Quantity (Integer) and State (Varchar)

ID Unit Price Quantity State 1 $2.19 500 AZ 2 $29.99 33 WA 3 $1000.00 1 WA 4 $1.20 7 WA

When i run the aggregate i want the output to be grouped by state

Total Price Quantity Sold State $2.19 500 AZ $1031.19 41 WA

Hope the values are correct

Martin

Add the aggregate to your data flow, and connect an input. Open the properties and check the Unit Price, Quantity, and State on the available input columns. In the grid at the bottom, make sure the State has the Operation set to "Group By", and Operation to "Sum" for the Unit Price and Quantity columns. That should do it.|||In the operation selection for state i can't select "Group By" it only has "Count". Any ideas?|||

Martin Perkins wrote:

In the operation selection for state i can't select "Group By" it only has "Count". Any ideas?

That's strange. What's the data type of the state column? Have you installed service pack 1 at least?|||

The State property is Nvarchar(max) Just installing service pack 2 for SQL Server 2005

|||

It was the length of the state column. If i convert the column from Nvarchar(max) to Nvarchar(20) i can then group by.

Thanks for help

Martin

|||

SSIS treats NVARCHAR(MAX) as BLOB type, and does not support grouping by blobs. You need to convert it to fixed-lenght type, like NVARCHAR(2).

You can do it either

1) at the source - NVARCHAR(MAX) is very inefficient method of storing 2-character data, if you can change the source, I suggest doing it

2) case to fixed-lenght type in the SQL query

3) use SSIS to create another column of fixed lenght (I'm not sure if type convertion transform does it, if not then try derived column transform)

Hope this help,

Michael.