cube processing more records than fact table contains

#1

Hi,
Has any body come across cube processing more number of records than
the fact table contains
If yes in what scenario does it happen? and after processing whether
the summarised data is dirty or OK

Please reply

Regards
Prasanna

#2

Check whether the Mapping between Fact Table and Dimension
table is proper.

Are there more than one mapping link between the Fact
Table and any of the Dimension Tables.

Quote:

-----Original Message-----
Hi,
Has any body come across cube processing more number of
records than
the fact table contains
If yes in what scenario does it happen? and after
processing whether
the summarised data is dirty or OK

Please reply

Regards
Prasanna
.

#3

The problem is the dimension table has 7 levels last bottom 2 levels
assume SKU5 and SKU7 and the fact table contains data at SKU5 level.So
i have disabled the lower level of SKU7 and optimised the schema at
SKU5 in this cube and processed.
the fact table contains 35 lac records and the cube process continues
even till 47 lacs and still processing eventually i had to stop the
cube processing.
I have to use the same product hierarchy as we need to create virtual
cube using this cube and other 2 cubes(which are at SKU7 level).
Please reply

"Sanka" <loonysan (AT) mailcity (DOT) com> wrote

Quote:

Check whether the Mapping between Fact Table and Dimension
table is proper.

Are there more than one mapping link between the Fact
Table and any of the Dimension Tables.

-----Original Message-----
Hi,
Has any body come across cube processing more number of
records than
the fact table contains
If yes in what scenario does it happen? and after
processing whether
the summarised data is dirty or OK

Please reply

Regards

Prasanna
.

#4

Joining at intermediate level of dimension can cause this kind of
problem, but proper schema optimization or a snowflaked dimension table
should address this. Here is an earlier thread on this topic that may
help:

http://www.developersdex.com/sql/mes...3636831&p=1252

Quote:

Re: Custom Level Rollup

From: Jeremy Highsmith
Date Posted: 11/25/2003 2:08:00 PM

Thanks! I changed the dimension structure to snowflake (multiple
tables) using a view. Works great and so simple.

Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote

Quote:

This 2002 post provides 2 options, snowflake and unique level key,
which
work without need for custom rollups:

http://groups.google.com/groups?q=di...snowflake&hl=e
n&lr=&ie=utf-8&oe=utf-8&selm=3d6a3014.a0c9db0d%40dsslab.com&rnum=2

You need to either optimize the joins away for the SKU and Category
tables,
or snowflake them so that SKU information is in a separate table from
category information. (You could do both, too.)

In order to optimize the joins, ensure that Category keys are unique
within
the level. Then, AS2K won't attempt to join in the product dimension
when it
processes the cube. The join is what throws off the numbers, as you
get
N
fact rows per 1 dimension row.

Your Forecasts will still have SKU disabled and join to Prod_catg_key
in
the
category table (manually ensure this is set in the cube editor). You
SKU
information will join to SKU key.

HTH
..

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

#5

If it happens because of joining at intermediate level.
Will processing of the cube more than fact table record yields bad
data
Since i dont have control on the DWH database.I have to design the
cube as
is.
Will you recommend process it the same way and process it and
summarised data is still correct
Please reply.

Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote

Quote:

Joining at intermediate level of dimension can cause this kind of
problem, but proper schema optimization or a snowflaked dimension table
should address this. Here is an earlier thread on this topic that may
help:

http://www.developersdex.com/sql/mes...3636831&p=1252

Re: Custom Level Rollup
From: Jeremy Highsmith
Date Posted: 11/25/2003 2:08:00 PM

Thanks! I changed the dimension structure to snowflake (multiple
tables) using a view. Works great and so simple.

Deepak Puri <deepak_puri (AT) progressive (DOT) com> wrote in message
news:<eXo1YAJsDHA.1088 (AT) tk2msftngp13 (DOT) phx.gbl>...
This 2002 post provides 2 options, snowflake and unique level key,
which
work without need for custom rollups:

http://groups.google.com/groups?q=di...snowflake&hl=e
n&lr=&ie=utf-8&oe=utf-8&selm=3d6a3014.a0c9db0d%40dsslab.com&rnum=2

You need to either optimize the joins away for the SKU and Category
tables,
or snowflake them so that SKU information is in a separate table from
category information. (You could do both, too.)

In order to optimize the joins, ensure that Category keys are unique
within
the level. Then, AS2K won't attempt to join in the product dimension
when it
processes the cube. The join is what throws off the numbers, as you
get
N
fact rows per 1 dimension row.

Your Forecasts will still have SKU disabled and join to Prod_catg_key
in
the
category table (manually ensure this is set in the cube editor). You
SKU
information will join to SKU key.

HTH
..

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

#6

Processing the cube with redundant fact table rows could lead to
misleading results. If you can't change the dimension table, try to
select a key for the intermediate level (SKU5) that is unique at that
level, and which can join to the fact table in the cube. In that case,
after the cube schema is optimized, the key field that appears in the
cube editor for that level should be from the fact table. Then no join
should occur when the cube is processed.

If you can at least create new relational views (without touching the
tables), then the solution should be simple.

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

cube processing more records than fact table contains

microsoft.public.sqlserver.olap microsoft.public.sqlserver.olap

cube processing more records than fact table contains - 01-08-2004 , 12:12 AM

cube processing more records than fact table contains - 01-08-2004 , 08:59 AM

Re: cube processing more records than fact table contains - 01-08-2004 , 11:10 PM

Re: cube processing more records than fact table contains - 01-09-2004 , 02:13 AM

Re: cube processing more records than fact table contains - 01-10-2004 , 12:16 AM

Re: cube processing more records than fact table contains - 01-10-2004 , 09:01 PM