TIME dimension processing fails saying "..attribute key cannot be found.." in EPM 10
- After upgrading from version 7.5 to EPM 10, when we ran a ‘Full Process’ on the TIME dimension, it ran into an error saying “Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'dbo_tblFactCapEx', Column: 'TIMEID', Value: '20090013'. The attribute is 'Time_TIMEID'. (1/13/2015 2:41:02 PM)”.
- Full error message is attached herewith – ‘Time Dimension Error.txt’
- After researching, we did discover that MONTHNUM needed to be converted to BASE_PERIOD. Re-processed which produced the same error.
- Prior to migration to version 7.5, we ran a full process on TIME dimension there. It completed successfully, confirming the issue is only with version 10.
- Confirmed we could see the TIMEID value of 20090013 in the following places:
- Time Dimension in the appropriate TIMEID attribute column.
- Confirmed mbrTIME table had base member ID with TIMEID attribute filled out correctly.
- Data in tblFactFINANCE could be pulled using that TIMEID
- We truncated all the records in all the fact tables associated to this TIME dimension.
- Eventually, when none of the tables had any records, the TIME dimension then processed successfully.
- We this began to suspect the issue may not really be related to bad records.
- We conducted one more test to confirm this.
- Using an input form in EPM 10, we manually entered data in one of the models (at this point none of the fact tables have any records)
- Ran Full Optimize on that model with Compress Database and Index Defragmentation checked – This step failed with the error attached in ‘MatrixRateFullOptimize.txt’
- Ran Full process on Time Dimension – Failed indicating issue with TimeID 2012001 (that’s my manual entry). Attached error report ‘TimeDim Error MatrixRate.txt’
- At this point, the table only contains the manually entered records (no suspected bad records)
- We then suspected there could have been an issue with the upgrade process.
- So we reprocessed all the dimension and optimized all the models in version 7.5, made a new backup and restored it to version 10.
- The issue still persisted!
- At this point, we have tried all the possibilities we could think of. Each time the fact table is populated with records, the TIME dimension process fails indicating ‘the attribute key’ cannot be found.
- There is probably something in the OLAP partition that is not able to link the dimension attributes to the cubes.
- Please find attached the existing Time Dimension – TimeDimensionMembers.xlxs
- Version of Excel used: Excel 2007, SP3 MSO (12.0.6683.5000)
- System Specs: Please see screenshot below.
MatrixRateFullOptimize.txt.zip (433 B)
TimeDim Error MatrixRate.txt.zip (1000 B)
Time Dimension Error.txt.zip (865 B)
Thank you all for responding! This issue is resolved.
Here’s what the issue was:
- The time structure is TOTAL >> Years >> Quarters >> Months (e.g. T.ALL >> 2012.TOTAL >> 2012.Q1 >> 2012.P01)
- As shown in the screenshot below, the LEVEL for ‘T.ALL’ member was set to YEAR, which is incorrect (we can’t have Year rolling up to a Year)
- We changed the LEVEL to ‘TOTAL’ and this fixed the issue!!
- If only it gave a better error message than the “..attribute key not found” message