SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Attribute Key cannot be found when processing cube


Attribute Key cannot be found when processing cube

Author
Message
naramsg
naramsg
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 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.
denglishbi
denglishbi
Right there with Babe
Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)

Group: General Forum Members
Points: 763 Visits: 1362
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
Franky Leeuwerck
Franky Leeuwerck
SSC-Addicted
SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)SSC-Addicted (416 reputation)

Group: General Forum Members
Points: 416 Visits: 490
Hi,

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

Regards,
Franky

Franky L.
Ells
Ells
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1198 Visits: 931
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.
naramsg
naramsg
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 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! Smile

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. :-)
denglishbi
denglishbi
Right there with Babe
Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)Right there with Babe (763 reputation)

Group: General Forum Members
Points: 763 Visits: 1362
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search