We help IT Professionals succeed at work.

MS-SQL Analysis Manager cube processing

jlargent
jlargent asked
on
When I try to process a cube I get an error:
A member with key 'xxx.xxx.xxx.xxx' was found in the fact table but was not found in the level 'Client Ip' of the dimension 'the Client IP'.; (NOTE the actual IP addres is displayed, I x'ed it out for security reasons).

The fact table has a session id that links to the dimension table that contains the client ip.  I have 2 databases, one that doesn't get used and I can process the cube with no problem.....the other database is getting hit constantly and new records are created with new client IP and that is where the problems occur. I'm going thru the MS documentation and not getting very far. I'm not a DBA, just some poor sap that got this problem dump on them.

Can you tell me why this error occurs and how to go about trouble shooting it?
Comment
Watch Question

CERTIFIED EXPERT

Commented:
I just saw this with my cube that I generated 10 minutes ago.  It is strictly a timing thing where it has processed the dimension and then later when it does they query to calculate the data it finds the new record and then crashes.  I reprocessed mine and everything calculated fine the second time because no new records were added.  Do you have a date time that you could use so that you calculate your cube only using records up to a certain time or something like that.

Author

Commented:
I do have a date time column. How would I restrict the processing to specific time frame.
CERTIFIED EXPERT

Commented:
A little tricky in thinking about it.  Off the top of my head here is what I thought of.
Depends how you are recalculating your cubes.   I would create a table that had the current date in it.  I would change my view that is used to calculate the cubes so that it eliminated all records with a time less than the time that is in the table.   Then in my DTS Package setup to recalculate the cubes I would make the first step of my package to insert the current date my field.  I think that would do it.

Author

Commented:
I'm still trying to grasp the terminology of this, so bear with me.  There's the database, a data wharehouse, dimensions and cubes.  When you process a dimension, what does it do?  and then when you process a cube does that then cause the dimensions to be reprocessed?  I suspected that the changing data was causing a problem, but the cube has existing dimensions that are changing along with the client IP, but there's no error if I remove the client IP dimension.  The other dimension is a date time dimension, is that treated differently?  Thanks
CERTIFIED EXPERT

Commented:
Lets say I have a datawarehouse of sales
Customer Date         Sales
John     01/01/2002    5.50
John     01/27/2002    3.50
Alex     01/01/2002    4.50
John     02/02/2002   14.75

I have setup a cube (a summary table of my sales datawarehouse table is the best thing that I can think of describing it)

My cube has the dimensions of Customer and Date with a measure of sales.  When you process the dimensions in this simple example the server does a select distinct customer from sales, and then it will do another query select distinct year(date), month(date) from sales and then it will calculate the totals for each customer and month and it is when is doing this that I think it is giving you the error. Lets we were recalculating the dimensions the two select queries up above it would return John and Alex for customers and Jan 2002, Feb 2002 and then a new record was added to the table after the dimensions were processed lets say

Peter     02/02/2002    4.75

now it is going to calculate the subtotals for each combination of dimensions.  When it goes to do this it now finds a Peter in the sales table but Peter does not exist in our dimensions because Peter was added after it calculated them.  That is why I think it is crashing for you.  The date dimension I would imagine is fine because when you add it you can pick the level of detail you want to keep.  In my example here I would haved picked year and month because that is what it was doing in the select that calculated the dimension.  If the record that would have been added in the middle of calculating the cube
John     02/02/2002    4.75 it would have calculated fine because John already existed in the sales table so he would have been calculated in in the dimension.   In your case I would say the IP address that got added was a new IP address that was not in the table when it calculated the dimensions.

Author

Commented:
Thanks...that helps a LOT!  One more question....are the date parts independent?  In other words, using your example table, say that you were also keeping the date. During the processing of a cube, a new record John 2/27/2002 was added.  John is already there and all parts of the date are there, just not in the same order.  Would you expect this to be a problem?
CERTIFIED EXPERT
Commented:
I don't think it would as long as all parts are already there.

Author

Commented:
Thanks, your explanation really filled in some holes.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.