Showing posts with label fact. Show all posts
Showing posts with label fact. Show all posts

Tuesday, March 20, 2012

nonempty performance inconsistency

I'm trying to understand the huge (almost 5 times) difference between these two queries:

select nonempty(dim1.dim1.dim1) on 1,

[fact count] on 0

from MyCube
where [dim3].[dim3].&[P1]
runs 5 secs

select nonempty(dim2.dim2.dim2) on 1,

[fact count] on 0

from MyCube
where [dim3].[dim3].&[P1]
runs 23 secs

some more details:

SQL 2005 Enterprise SP2

Both dimensions have regular relation type to the fact.

select count(*) from dim1 ==> 11M rows
select count(*) from dim2 ==> 15M rows
select count(distinct dim1) from fact where dim3 = 'P1' => 96 rows
select count(distinct dim2) from fact where dim3 = 'P1' => 103 rows
(meaning - both dimensions are big, but specific for dim3=P1 are very sparse.)

no aggregations exists in the DB at all !!

Both dimensions are pretty much similar in their properties, the only difference is that dim1 key is string (has no NameColumn) and dim2 key is int64 (has NameColumn pointing to a string attribute).


Looking at the profiler - both queries looks similar, the main difference is at the 5th line:

Query begin (start time = x)

Query cube begin (start time = x)

Calculate non empty begin (start time = x)

Calculate non empty begin (start time = x)

Get data from cache (query 1: start time = x + 1; query 2: start time = x + 10)

What am I missing?

The data type of the key is probably the cause. with SP2 the nonempty should be performed in block mode so the bulk of the time would be spent looking up the member keys in the respective indexes. An int64 key will take 8 bytes per member where as strings are stored as unicode, so a 16 character key will result in an index which is 4 times the size. Plus there is extra CPU work involved in string comparisons compared to integer comparisons.

With dimensions this large I would really encourage you to look into using integer keys.

|||

It sounds obvious it would be better using Int64 as keys but the interesting thing here is that the string key dimension was the faster one.

|||

Sorry, I got them transposed. Darn it, that would have been a neat answer Sad . Integer keys should put a lot less IO and CPU load on the system than string keys. You might be able to get the first query down to 1-2 seconds, but that does not explain why the second query is so slow.

Are there any scopes or assignments that could be affecting one dimension and not the other? Are they both related directly to the fact table or are there any many-to-many or non-materialized reference relationships involved? Are all the properties for the two attributes the same?

|||

That would have been a neat answer, but sorry, this is not in this case.

No scopes or assignment (what's that anyway?), both directly and regular related to the fact.
What is non-materialized reference?

All properties are the same beside the datatype and the NameColumn of the dim2 (the slow)

|||

When you create a referenced relationship there is a tick box that says whether to materialize the relationship (which is ticked by default). When referenced relationships are materialized they are resolved at processing time and should be as fast as regular relationships, when they are not materialized the relationships are resolved at run time.

Am I correct in assuming by the names that both of these attributes are the key attributes for thier dimensions?

Any security roles that might be applied to one and not the other?

Friday, March 9, 2012

Non aggregate case

Hello,
In the same fact table, I have real values and min/max values. The type of value (real, min, max) is a dimension. So, I want to aggregate value only for real value, because for min/max values, all the leaf are filled by my program.
So, it's possible to say "I want the not aggregate value in the database" in MDX query?
Thank you,
Guillaume.

Hi Guillaume,

Can you create a view (or named query, if you're using AS 2005) on the fact table, such that 3 separate measure fields can be created for the real, min and max values? In that case, appropriate aggregation functions of sum(), min(0 and max() can be applied to each measure. Something like:

create view splitfact as

select fact.dim1, fact.dim2, fact.dim3, ..,

sum(case fact.typedim when 'real' then fact.valfield else 0 end) as realval,

sum(case fact.typedim when 'min' then fact.valfield else 0 end) as minval,

sum(case fact.typedim when 'max' then fact.valfield else 0 end) as maxval

from fact

group by fact.dim1, fact.dim2, fact.dim3, ..

|||

If you look at the attribute in the cube editor, you will see that there is an aggregation usage property. Change it from default to none.

_-_-_ Dave

|||Thank you !

Ok, now I created 2 NamedCalculation on my DataSourceView. The first one is for 'real' value with aggregate function = SUM, and for the second NamedCalculation, I put 'min/max' values, with aggregate function = NONE.

But when I browse data, I can't see my data with aggregate function=None. But when I change the aggregate function, I can see my data. It's like if the none aggreagte function doesn't work correctly. Do you know this problem?