Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

nable to build SQL Server OLAP Cube when there is no rows corresponding to foreign key in a table Expand / Collapse
Author
Message
Posted Sunday, October 28, 2012 6:11 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 7:53 AM
Points: 16, 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
Post #1378068
Posted Monday, October 29, 2012 5:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:19 AM
Points: 243, Visits: 2,686
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.
Post #1378195
Posted Monday, October 29, 2012 6:18 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 12, 2013 7:53 AM
Points: 16, 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?


Post #1378210
Posted Monday, October 29, 2012 7:30 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 4:19 AM
Points: 243, Visits: 2,686
It will not show in your Excel Pivots.
Post #1378242
Posted Wednesday, October 31, 2012 11:42 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 2:05 PM
Points: 300, Visits: 810
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.
Post #1379471
Posted Wednesday, November 7, 2012 1:03 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, August 27, 2014 3:05 PM
Points: 355, Visits: 870
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).
Post #1382145
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse