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


nable to build SQL Server OLAP Cube when there is no rows corresponding to foreign key in a table


nable to build SQL Server OLAP Cube when there is no rows corresponding to foreign key in a table

Author
Message
grasshopper26
grasshopper26
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 108
I am new to SQL Server OLAP Cubes. I am having the following issue like

ex I have purchase order and invoice tables which are used in data source view. These two tables are related by purchase order ID which have one to many relationship with invoices.

I am getting the following error for the purcahse orders which i dont have invoices

Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: purchase order

Can anyone throw some light on this to help me
Scott Murray-240410
Scott Murray-240410
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1003 Visits: 3142
grasshopper26 (10/28/2012)
I am new to SQL Server OLAP Cubes. I am having the following issue like

ex I have purchase order and invoice tables which are used in data source view. These two tables are related by purchase order ID which have one to many relationship with invoices.

I am getting the following error for the purcahse orders which i dont have invoices

Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: purchase order

Can anyone throw some light on this to help me


I would first check that your dimensions are fully processed with the most current data. If data has been added to the underlying source, but the dimensions have not been processed, that new data is not reflected in the dimension, so when the cube attempts to join the keys columns from fact to dimension, it is not available. If the dimension is up to date, then you will need to check the underlying source and find which invoices are missing. Generally, you should have a value for all keys or you can choose to how to process these error rows by choosing to ignore the missing values. I do not prefer this second option.
grasshopper26
grasshopper26
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 108
Scott Murray-240410 (10/29/2012)
grasshopper26 (10/28/2012)
I am new to SQL Server OLAP Cubes. I am having the following issue like

ex I have purchase order and invoice tables which are used in data source view. These two tables are related by purchase order ID which have one to many relationship with invoices.

I am getting the following error for the purcahse orders which i dont have invoices

Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: purchase order

Can anyone throw some light on this to help me


I would first check that your dimensions are fully processed with the most current data. If data has been added to the underlying source, but the dimensions have not been processed, that new data is not reflected in the dimension, so when the cube attempts to join the keys columns from fact to dimension, it is not available. If the dimension is up to date, then you will need to check the underlying source and find which invoices are missing. Generally, you should have a value for all keys or you can choose to how to process these error rows by choosing to ignore the missing values. I do not prefer this second option.


How can i have the value for the missing invoices in invoice table should it be a dummy insert row ? if yes how is it going to effect the data in Excel UI for Summations?
Scott Murray-240410
Scott Murray-240410
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1003 Visits: 3142
It will not show in your Excel Pivots.
herladygeekedness
herladygeekedness
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1336 Visits: 813
grasshopper26 (10/29/2012)
Scott Murray-240410 (10/29/2012)
grasshopper26 (10/28/2012)
I am new to SQL Server OLAP Cubes. I am having the following issue like

ex I have purchase order and invoice tables which are used in data source view. These two tables are related by purchase order ID which have one to many relationship with invoices.

I am getting the following error for the purcahse orders which i dont have invoices

Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: purchase order

Can anyone throw some light on this to help me


I would first check that your dimensions are fully processed with the most current data. If data has been added to the underlying source, but the dimensions have not been processed, that new data is not reflected in the dimension, so when the cube attempts to join the keys columns from fact to dimension, it is not available. If the dimension is up to date, then you will need to check the underlying source and find which invoices are missing. Generally, you should have a value for all keys or you can choose to how to process these error rows by choosing to ignore the missing values. I do not prefer this second option.


How can i have the value for the missing invoices in invoice table should it be a dummy insert row ? if yes how is it going to effect the data in Excel UI for Summations?




Not sure about dummy records, but if you have a PO then presumably there is one or more invoices for it, correct? Otherwise, the relationship is broken. Doesn't matter what you plan to view it with, it's broken, cube won't process.

we do not use dummy records here. If there is a key in a fact table, that is because it looked up that key in the dimension table during fact ETL.

If I get an error like you did, it means, as previous poster stated, that the dimension table may not have been processed. Do that first. If you still get the error, pluck out the query that processing is running and run it yourself to see the errors and do recon.

If it isn't the dimension, double-check the relationship you have set up.
MMartin1
MMartin1
SSCertifiable
SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)SSCertifiable (7.2K reputation)

Group: General Forum Members
Points: 7209 Visits: 2033
You have an orphaned fact record, with no key lookup to the Dimension. As mentioned process the dimension. You may have to consider how the data is moved, (what the query is because you may have to modify the from date to pull raw data from and insure you are not inserting duplicates into the dimensional database table.) There may have been a hiccup in the Job or whatever moves the data or some disruption in network connectivity. These things can happen.

On that note, when there is no match from fact to dimension you may want to set the factkey to -1 to represent a NULL, it will keep the cube from erroring out during processing (so long as you have a -1 in the Dim).

----------------------------------------------------
How to post forum questions to get the best help
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