cube report :At least one invalid member reference was encountered in the query

[nsaxena]

Hi , I am getting below error in one of the report source of which is transformer cube package. Cube is getting refreshed successfully.

Error :
At least one invalid member reference was encountered in the query.'[transformer cube datasource].[X].[Y].[Day]->:[PC].[@MEMBER].[99999999~155]'

Please suggest how should i proceed to debug this.

Thanks!

[MFGF]

Hi , I am getting below error in one of the report source of which is transformer cube package. Cube is getting refreshed successfully.

Error :
At least one invalid member reference was encountered in the query.'[transformer cube datasource].[X].[Y].[Day]->:[PC].[@MEMBER].[99999999~155]'

Please suggest how should i proceed to debug this.

Thanks!

Hi,

This is a common issue when you have non-unique members (categories) in your dimensions in Transformer. OLAP sources require every member within a dimension to have a unique ID (or category code in Transformer-speak). If Transformer reads data into a dimension that would result in non-unique members (categories), it tries to fix the problem by appending a tilde and a random number to the category code (eg ~155) to make it unique. This is fine if the members are static and don't change in the model, but if you regenerate the categories in Transformer, you will most likely get a different category code generated next time (eg 99999999~183). If you have reports that directly reference the original member under its old category code (99999999~155) these reports will then break, since that member no longer exists in the cube. This is the issue you are seeing here.

It isn't a bug that you can debug, it's a fundamental design problem in your Transformer model that you need to fix. The simple solution is to make sure that all categories within each dimension have a unique category code. Look carefully at the values you are using for your category codes and if necessary, start appending other values to make each one unique. Once you have done this, you will have a stable model that will not alter your category codes, and you can then go back and fix all the reports to use these new, stable members.

See the section on Resolving Uniqueness Problems in your Data Source in the Transformer User Guide:

https://www.ibm.com/support/knowledgecenter/en/SSEP7J_10.2.2/com.ibm.swg.ba.cognos.ug_cogtr.10.2.2.doc/c_bp_uniqueness.html#bp_uniqueness

Cheers!

MF.

[nsaxena]

thank you very much...it well explained. We have as now fix the report by re-dragging the member in report.

However basic check on my time dimension shows , (year, quarter, month and day) are set as unique-- Source for time dimension is and sql query imported through IQD-- Any overview what should i check

Also i have read, whenever we see tilde in MUN, it means we are giving  transformer right to assign MUN.

Is it advisable to take MUN generation right to us instead of letting cognos assign?

Thanks!