Error when optimizing the model

[former_member196865]

Hi Experts,

We are unable to optimize the model due to an error.

The error logs show this message:

The attribute key cannot be found when processing: Table: 'dbo_tblFactXXXX', Column: 'GRP_PROD', Value: '00-000'. The attribute is 'Grp_Prod_ID1'.

IIRC this happened after a user processed a dimension. Unable to run further packages as well.

How can we get around this?

Regards,

David

[Former Member · Accepted solution]

Hi David,

this message means that you have one or more records in the fact tables with this value that doesn not exists more in the dimension, search it with a query in SSAS. Probably you write it in the fact through a script. Delete this/these record and do a modify application with process application enabled and after do the process dimension.

Other possibility is to add the value Grp_Prod_ID1 in your master table.

Regards

     Roberto

[former_member196865 · Accepted solution]

Hi Roberto,

We searched the fact table for 00-000 and could not find it. The user seems to have uploaded 00-000 first and processed it. However, after realizing the mistake, the user changed the column to 00_000 and processed the dimension again.

At present we cannot see grp_prod property with 00-000. All we see is that the grp_prod column has 00_000 values.

[Former Member · Accepted solution]

Hi David,

In MS platform we have three different tables to store data.Lets consider i have finance model.

1) dbo.tblfactFinance   : Long Term Data Storage

2) dbo.tblfac2Finance  : Short term Data Storage

3) dbo.tblWBFinance   : Real Time Data Storage

Please check in three these three tables do you have any such record, as suggested by Roberto.

[Former Member · Accepted solution]

Hi David,

To find out these entry you can execute simple SQL query. Lets assume i have finance model, you can use your model name.

select * from tblFactFinance where ACCOUNT not in (select ID from mbrAccount where calc = 'N');

select * from tblFac2Finance where ACCOUNT not in (select ID from mbrAccount where calc = 'N');

select * from tblFactwbFinance where ACCOUNT not in (select ID from mbrAccount where calc = 'N');

This query should returns zero records. If it is returning something then analyse those records first then  delete those records using simple SQL query.

if you found any record in any table simply delete that entry then try to optimize model

delete  from tblFactFinance where ACCOUNT not in (select ID from mbrAccount where calc = 'N');

where

tblfactfinance  you can replace with your model name

Account you can replace with  your dimension "GRP_PROD"

mbrAccount you can replace with  your dimension "mbrGRP_PROD"

For your case,

select * from tblFact"model_name" where GRP_PROD not in (select ID from mbrGRP_PROD where calc = 'N');

select * from tblFac2"model_name" where GRP_PROD not in (select ID from mbrGRP_PROD where calc = 'N');

select * from tblFactwb"model_name" where GRP_PROD not in (select ID from mbrGRP_PROD where calc = 'N');

try to run for each dimension if problem persists.

Hope this will solve your problem. 

[Former Member · Accepted solution]

Hi David,

as I wrote before check all the 3 fact tables and after do a modify application with process application/model enables only when it ends without errors do a full process dimension.

Regards

     Roberto

[former_member196865 · Accepted solution]

Thanks, I will get back to you once I try this.

[former_member196865 · Accepted solution]

Thanks Roberto,

We could find the records.

Now to replicate the process that BPCUSER suggested.

Also, would a clear Data Manager Package work in this scenario?

[Former Member · Accepted solution]

Hi David,

yes it works but you have to rebuild the cube so you can also delete with SSMS and after a modify application with process app..

Regards

     Roberto