Hi all,

How do I link a dimension table to the fact table when the dimension table
contains more entries?

eg:
Fact Table:
Bicycle
Motorbike
Car

Dimension Table:
Bicycle
Motorbike
Car
Bus
Train

All I want is too see the data for Bicycle, Motorbike and car... And I dont
want to modify my dimension table.

Thanks

Clint

when you access your cube, generally the non empty statement is used to
eliminate unused members in a dimension.

but if you want to insure that your dimension contains only used members you
have to use a view instead of a table to fill the dimension, and in this
view you have to filter the dimension table with used members into you SQL
statement.
another option is to remove unused rows from the database.


"Clint" <ccpatriot12 (AT) yahoo (DOT) com> wrote

Thanks for your help.

My problem lies at the processing time. The cube will not process as it
cannot find the member in the fact table. If possible I do not want to
modify the table in SQL.

This functioned fine in AS2000 thus it should be possible?

Clint
"Jéjé" <willgart (AT) BBBhotmailAAA (DOT) com> wrote

if the dimension contains more members then the cube himself, there is near
no-impact for the processing time.

and what is the problem when you process the cube?
generally the fact table contains product ID not found in the dimension,
this cause an error, but the opposite doesn't generate any error! (I mean
there is no row in the fact table for a specific product)
This simply generates empty cells in the cube, which is a standard behavior.

so maybe I have missed something in your case, can you explain what's
appends exactly and what you expect?

"Clint" <ccpatriot12 (AT) yahoo (DOT) com> wrote

The error I get is:

The attribute key cannot be found..
"Clint" <ccpatriot12 (AT) yahoo (DOT) com> wrote

Hi,

I thought so too... the cube functions fine in AS2000...

Here are the actual messages which display when the cube fails to process:

Errors in the OLAP storage engine: The attribute key cannot be found: Table:
dbo_Vw_Summary_Reports, Column: Product_, Value: Bike.

Now I have looked in the fact table and there is no value of Bike.. However
there is in the dimension table.

I am at a loss..

Thanks for your help so far

Clint
From: "Jéjé" <willgart (AT) BBBhotmailAAA (DOT) com>
Subject: Re: dumb question on Dimensions
Date: Wednesday, 8 March 2006 2:00 PM

if the dimension contains more members then the cube himself, there is near
no-impact for the processing time.

and what is the problem when you process the cube?
generally the fact table contains product ID not found in the dimension,
this cause an error, but the opposite doesn't generate any error! (I mean
there is no row in the fact table for a specific product)
This simply generates empty cells in the cube, which is a standard behavior.

so maybe I have missed something in your case, can you explain what's
appends exactly and what you expect?

ok, its the error I've described,

your fact table contain a product id which is missing in the product
dimension.
this error occurs when you have no referential integrity between the 2
tables and when you optimize the cube.

first solution:
insure that the product dimension contain all your products. (add missing
products in the product table)

second option:
don't optimize the cube, so AS will execute a query with a inner join clause
between the 2 tables, so the cube will be processed with rows with existing
members in the dimension. (this reduce the processing time due to the extra
join)
to undo what the optimize action do, you have to change the key column of
the product dimension to use the key from the dimension table instead-iof
the key from the fact table.

third option:
when you process the cube, chosse the option to ignore all your errors.

"Clint" <ccpatriot12 (AT) yahoo (DOT) com> wrote

Thanks,

I am positive that my fact table is missing the product (it does exist in
the dimension table)

How do I not optimize the cube?

Clint
"Jéjé" <willgart (AT) BBBhotmailAAA (DOT) com> wrote

optimizing is a step done thourgh the menu in the cube editor.
so if you have not executed this command, the cube is not optimized and the
system will execute an inner join when you process the cube.
any missing members are not loaded and no key error should occurs.

do you use string (varchar) columns between your fact table and your
dimension table?
if yes, maybe the varchar size of the fact table is different from the
dimension table and the string is truncated, so you'll miss some values.

maybe the problem come from another link. the query executed by AS do an
inner join with all the linked tables!

but this doesn't explain the error (if the cube is not optimized)

"Clint" <ccpatriot12 (AT) yahoo (DOT) com> wrote