Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Attribute Key cannot be found when processing cube Expand / Collapse
Author
Message
Posted Friday, November 28, 2008 3:20 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 6:20 AM
Points: 24, Visits: 201
Hi,

I'm facing a strange problem when processing my cubes on SSAS. My fact and dimension table structure is such:

FACTTBL
ID int primary
DIM1CODE varchar(50)
MEASURE1 numeric(19,5)
MEASURE1 numeric(19,5)

DIM1TBL
DIM1CODE varchar(50)
DIM1NAME varchar(200)

I have linked these two tables in the DSV (DIM1CODE to DIM1CODE). The linking direction is correct as well... the source column (foreign key) is in the FACTTBL table and the destination column (primary key) is in DIM1TBL. I have not linked these tables at the RDBMS level.

There is a record in FACTTBL where the DIM1CODE value is 'ABC'. 'ABC' is also present as a record in the DIM1TBL table as well.

When I try processing the cube, I get the following error:

Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_FACTTBL', Column: 'DIM1CODE', Value: 'ABC'. The attribute is 'DIM1CODE'.


Why do I get this error even when the value is present in the dimension table?

Please help.
Post #610193
Posted Friday, November 28, 2008 8:56 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 22, 2013 7:25 AM
Points: 347, Visits: 1,344
I would make sure that you have processed the dimension first and then try processing the cube. Typically data will get refreshed in the fact table and you will need to make sure that you have updated the dimension first before processing the cube (measures groups and partitions).

I would also recommend using integer values for the key to the dimension table and also in the fact table. You will get much better performance with processing, look ups, and indexing.

I would recommend checking out the following site for design tips and practices http://kimballgroup.com. They have a monthly tips newsletter you can sign up for also if you find this type of information helpful.


----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Post #610428
Posted Monday, December 1, 2008 6:21 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, May 2, 2014 3:39 AM
Points: 350, Visits: 475
Hi,

Before reprocessing the cube dimension, as mentioned previously, you can consider uploading new records for your dimension table.

Regards,
Franky


Franky L.
Post #611260
Posted Tuesday, December 2, 2008 3:23 AM
Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Thursday, December 18, 2014 1:23 AM
Points: 739, Visits: 848
Just one other note. If the data wuality is poor it may be 'ABC' in the fact table and 'ABC ' in the dimension table.

I sometimes have to write the query in SQL to ensure that whats happening is really what I expect. I have already had to use rtrim on one dimension table.

As has already been said when you add to the dimension table you will need to reprocess the dimension. You could always browse the dimension if it does not have many members!

Mark.
Post #611926
Posted Wednesday, December 3, 2008 12:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, November 28, 2012 6:20 AM
Points: 24, Visits: 201
Hi all,

Thanks for the help. It worked after I processed the dimension before processing the cube. What surprises me is that I was getting this error even when I was doing a Full Process of the entire cube. Nevertheless, I guess I'll process the dimensions first from now on.

Dan: Unfortunately, the keys in the source tables are alphanumeric, so I guess I'll have to use the same here. Will however keep this in mind for future projects. I've seen the Kimball Group website, though not at length. Will do so. Thanks! :)

Franky: Thanks! An automated ETL process runs everyday, where the dimension and fact tables are updated with the latest data before the cube is processed.

Mark: There was no problem with data quality. In fact, I ran the following query to check for data mismatches and I didn't get any records in the output:

--BEGIN
SELECT
F.DIM1CODE
FROM
FACTTBL F
WHERE
F.DIM1CODE NOT IN
(
SELECT
DIM1CODE
FROM
DIM1TBL
)
--END

Once again, thanks, all! I really appreciate it.
Post #612599
Posted Wednesday, December 3, 2008 5:04 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, August 22, 2013 7:25 AM
Points: 347, Visits: 1,344
In regards to the keys in being alphanumeric in the source table, those would be referred to as natural keys. For best practices you want to typically stay away from referencing natural keys at least as using them as the keys in the star schema model. This is where you want to implement your own surrogate key. You can still store the natural key for reference. There are a few reasons for this. One like I stated for performance (improve joins). The other is because what would happen if by chance you acquired another company or needed to merge data from another system into this dimension table and they had the same natural (source) keys, but they were actually different dimension records.

It is totally up to you, but if you want performance and a way to cover yourself to avoid a collision or reuse down the road for some reason (I have seen natural keys reused in systems) I would go with the surrogate key - To Surrogate Key or Not.


----------------------------------------------------------------------------------------
Dan English - http://denglishbi.wordpress.com
Post #612712
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse