IBM Cognos BI report error against SSAS – At least one invalid member reference was encountered in the query
At least one invalid member reference was encountered in the query
Problem
When encountering this error message “At least one invalid member reference was encountered in the query…”, when reporting against SQL Server Analysis Services with IBM Cognos Report Studio 10.1 using a crosstab, the problem may be caused by having no data in a row and column intersection.
Solution
A potential solution is to go to the query properties and, under Query Hints, change Suppression to Nulls. This can also improve performance as it can reduce the data returned from the cube. Note that this is a different setting than the Zero Suppression that suppress rows and/or columns. It’s on the query, not the reporting object.
There could be many reasons that a missing member reference was encountered, but this simple change often fixes the problem.
Errors in the OLAP storage engine: A duplicate attribute key has been found when processing
Duplicate attribute key error
A frequent error when processing an SSAS cube begins “Errors in the OLAP storage engine: A duplicate attribute key has been found when processing”. I have found that this is usually one of two conditions:
Typical Causes
- An actual duplicate key is found where a value for a child in a hierarchy occurs with two different parent values.
- A value for a data item is NULL.
Solution for actual duplicate key
The former is easier to discover and can be resolved by using a compound key for the parent that includes the child key. Look for it when the field in question is part of a hierarchy. A child can only have one parent.
Solution for null data
The latter can be confounding because the error message is often misleading. It can show a value in the error message, not indicating that the real underlying cause is the NULL value. This can be repaired by using a COALESCE statement to replace a NULL with a value such as ‘Unknown’ or simply ”.
A good practice is to use views as the source of data to a cube, rather than making any modifications within the SSAS data source view. It is in the view that I add the COALESCE function around the data item.
For example, if there are nulls in the MIDDLE_NAME data item, the view
CREATE VIEW vwPeople AS
SELECT FIRST_NAME [First Name],
COALESCE(MIDDLE_NAME,”) [Middle Name],
LAST_NAME [Last Name]
FROM PEOPLE
will use the MIDDLE_NAME value for [Middle Name] unless it is NULL and then will use the empty string instead.
About
A Market Street Solutions business intelligence advisory consultant with deep experience in Microsoft and Cognos data warehousing architecture, data modeling, data loading and reporting. Microsoft certified – MCITP, MCT (Microsoft Certified Trainer) - in Business Intelligence and Database Development, Wharton MBA, chess player, dog lover, and husband. I live in Chattanooga, Tennessee, with my wife Jill, son Henry, dogs Bubba and Caesar, and cats Priscilla, Elvis, Prudence and Precious.
Email me, please: will.thrash@marketstreetsolutions.com
Twitter: @willthrash1
Website: Market Street Solutions
-
Recent Comments
-
Archives
- April 2012 (1)
- September 2011 (1)
- April 2011 (1)
- June 2010 (1)
- May 2010 (1)
- April 2010 (1)
- March 2010 (1)
- January 2010 (1)
- December 2009 (1)
- May 2009 (3)
- April 2009 (2)
- March 2009 (5)
-
Categories
-
RSS
Entries RSS
Comments RSS
