Hi,

This is a bit odd and certainly puzzling.

I have a very simple cube - 1 fact and two dimensions. Processing the
cube throws up no errors despite the fact that the fact table contains
FKs to one of the dimensions which do not exist in that dimension
table. The rows with the missing keys are not processed in that they
do not appear in the result set but neither are they rejected anywhere.


The dimension is a shared one and has been processed seperately. The
cube is set to fail on key processing errors and none have been allowed
in the limit.

The only thing I can think of is that the dimension table does not have
a primary key and the documentation stresses that processing will fail
when the primary key does not exist in the dimension. Also, the join
column between fact and dimension is not used in any of the Cube
dimensions. Other columns from the dimension table are used.

Can anyone shed any light on this please?

Thanks,

Stuart

If you look at the SQL statement issues, AS does an inner join between the
fact table and the dimension tables. This is the default -- and is causing
the missing data to disappear.

If you ran the optimize schema wizard (in the cube editor), as you should as
a best practice, you will notice that the join operation disappears and the
fact table will start to generate errors (because of the missing keys).
Running the optimize schema wizard is a best practice because on a large,
high complexity cube, it can significantly reduce the join operations
needed. If you had 20 dimensions, then we need a 21-way join to process the
cube/partition. Ultimately if you are using partitioning, you can reduce the
join to a single table scan of the fact table -- which is what we'd like to
see.

However, in your case, if you have a low complexity cube, and you are
comfortable with how the system automtaically cleans up your data, then
don't run the optimize schema wizard and leave the joins in-place.

The net-net is that this is expected behaviour.

Hope that helps.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Stu180" <stu180 (AT) hotmail (DOT) com> wrote

OK, thanks for that Dave. That clears things up a bit. And I
certainly take on board what you say about optimising schemas in the
many dimensioned environment.

The only thing I'm still unclear of is when the Stop Processing on Key
Errors kicks in. Without the join to the dim, how does it know that
the key value does not exist? Is this where it is important that a
dimension has a primary key?

Thanks,

Stu

the matching member doesn't exist. If you look at the SQL, we issue an inner
join which means that the rows in the fact table with missing members won't
even be see by Analysis Services. Without the join, we see the row, but when
we go to look to see if the member exists in the dimension (remember
dimensions are processed independently in Analysis Services), then we notice
that a member key for that dimension is missing and we return an error.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.


"Stu180" <stu180 (AT) hotmail (DOT) com> wrote

Thanks. Of course. D'oh.