November 28, 2008 at 3:20 am
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.
November 28, 2008 at 8:56 am
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
December 1, 2008 at 6:21 am
Hi,
Before reprocessing the cube dimension, as mentioned previously, you can consider uploading new records for your dimension table.
Regards,
Franky
Franky L.
December 2, 2008 at 3:23 am
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.
December 3, 2008 at 12:19 am
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. 🙂
December 3, 2008 at 5:04 am
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply