Performance on parent childe 2000 vs. 2005

We like to use parent childe dimension in our OLAP project. In Analysis Server 2000 we know there is no aggregation stored in intermediate levels. Analysis Server could only store aggregation on leaves and the root level. This could slow down the query performance.

How dose Analysis Server 2005 managing aggregation in parent child relation? And what happened with attributes hierarchies on in parent child dimension?

Hope someone can give me a hint.

Marco

Hi,

you could have one parent child and many othere hierarchies in the dimension. Analysis Services is capable to build aggregations on all othere hierarchies and root/leave of parent child, but is still not capable to build aggregations on intermediate levels of the parent child.

I expect the server to be faster especially if you could limit the parent child by restriction with other hierarchies

HANNES

Performance of aggregation functions

In theory, should the new aggregation functions in AS2005 (LastNonEmpty, LastChild, AverageOfChildren etc.) perform as well as the traditional ones (Count, Sum etc.)?

Just wondering (and because I am using LastNonEmpty for an inventory snapshot scenario)...

In general, the build-in semi-additive measures perform the same way the additive ones do. We have been using LastNonEmpty for Project REAL and we've seen absolutely no differences between measures -- see http://www.microsoft.com/sql/bi/ProjectREAL

_-_-_ Dave|||

We've been struggling with this for several months. Any semi-additive measure or calculation using a semi-additive measure has severe performance issues when we use a date filter in BIDS. OWC and the KPI viewer use a sub-cube approach to this. This can be illustrated in the Project REAL sample data base:

CREATE SUBCUBE [REAL Warehouse] AS ( SELECT ( { [Time].[Calendar].[Calendar Month].&[12]&[2004] } ) ON COLUMNS FROM [REAL Warehouse])

// This query using a semi-additive measure runs in 1 minute & 5 seconds
SELECT
{
[Measures].[Available Qty]
}
ON COLUMNS
FROM [REAL Warehouse]
// This query using an additive measure runs in less than 1 second (31 ms)
SELECT
{
[Measures].[Sales Qty]
}
ON COLUMNS
FROM [REAL Warehouse]

DROP SUBCUBE [REAL Warehouse]

// This query using the same semi-additive measure and a where clause runs
// in less than a second
SELECT
{
[Measures].[Available Qty]
}
ON COLUMNS
FROM [REAL Warehouse]
WHERE [Time].[Calendar].[Calendar Month].&[12]&[2004]

AdventureWorks has a similar scenario using a date filter and the Average Rate measure (lastnonempty). The fact table is very small, so the performance issue doesn't seem as bad.

Any thoughts as to why we see this kind of performance issue? We've applied all the hotfixes, but still have the issue. We've also tested this under the SP2 CTP (using our cube) and see exactly the same results. We're running on a 2 way dual core server with 4 GB. Our test cube is tiny with only a few hundred thousand fact rows. We can change our browser code to use WHERE instead of SUBCUBE if we have to.

Performance of aggregation functions

In theory, should the new aggregation functions in AS2005 (LastNonEmpty, LastChild, AverageOfChildren etc.) perform as well as the traditional ones (Count, Sum etc.)?

Just wondering (and because I am using LastNonEmpty for an inventory snapshot scenario)...

In general, the build-in semi-additive measures perform the same way the additive ones do. We have been using LastNonEmpty for Project REAL and we've seen absolutely no differences between measures -- see http://www.microsoft.com/sql/bi/ProjectREAL

_-_-_ Dave|||

We've been struggling with this for several months. Any semi-additive measure or calculation using a semi-additive measure has severe performance issues when we use a date filter in BIDS. OWC and the KPI viewer use a sub-cube approach to this. This can be illustrated in the Project REAL sample data base:

CREATE SUBCUBE [REAL Warehouse] AS ( SELECT ( { [Time].[Calendar].[Calendar Month].&[12]&[2004] } ) ON COLUMNS FROM [REAL Warehouse])

// This query using a semi-additive measure runs in 1 minute & 5 seconds
SELECT
{
[Measures].[Available Qty]
}
ON COLUMNS
FROM [REAL Warehouse]
// This query using an additive measure runs in less than 1 second (31 ms)
SELECT
{
[Measures].[Sales Qty]
}
ON COLUMNS
FROM [REAL Warehouse]

DROP SUBCUBE [REAL Warehouse]

// This query using the same semi-additive measure and a where clause runs
// in less than a second
SELECT
{
[Measures].[Available Qty]
}
ON COLUMNS
FROM [REAL Warehouse]
WHERE [Time].[Calendar].[Calendar Month].&[12]&[2004]

AdventureWorks has a similar scenario using a date filter and the Average Rate measure (lastnonempty). The fact table is very small, so the performance issue doesn't seem as bad.

Any thoughts as to why we see this kind of performance issue? We've applied all the hotfixes, but still have the issue. We've also tested this under the SP2 CTP (using our cube) and see exactly the same results. We're running on a 2 way dual core server with 4 GB. Our test cube is tiny with only a few hundred thousand fact rows. We can change our browser code to use WHERE instead of SUBCUBE if we have to.