FM Filters removing metadata fields from Cognos 11 queries?

[BIengineer]

I have a 2nd layer (Transformation layer) in my Framework Manager package, where I want to filter out rows from several tables. The filter utilizes a table with one single column, which should act as a reference to the filter, containing acceptable values for that column.

The filter I create is like so:

[Transformation layer].[TableX].[Column_A] in ([Transformation layer].[TableAcceptableValues].[Column_Values])

which is applied to the corresponding tables "TableX". This should restrict any rows containing non-acceptable values from going further into Cognos Analytics.

What happens however when I run the report, is that the report won't validate due to several fields/data items used in the report Queries go missing! How is this possible? I have checked back and forth between a valid package not containing the filters - where the report validates just fine. As soon as the filters are added, the report breaks and the data elements from the queries go missing. The report runs, but the illegal rows pass through, so I assume that the report uses a previous version of the package.

Some related error codes that appear when I try validating the report:


RSV-VAL-0003
UDA-QOS-0006
QE-DEF-0459

And several sub-errors
RQP-DEF-0177
UDA-SQL-0107
ORA-00942
UDA-SQL-0327
UDA-SQL-0333

[bus_pass_man]

Is this a filter in general or a security filter?  What is its purpose?

If there are useless values in your data warehouse, why are they there?

Is this filter in the query subject? Is it a stand alone filter?

When you test the query subject in FM, does it work?  What results do you get?

If the table [Transformation layer].[TableAcceptableValues].[Column_Values] has only one column, what do you use as keys? 

Is each thing which is allowed a separate record or is there only one record with a value of 'this', 'that', 'whatever'
(given that the expression you show has [Transformation layer].[TableAcceptableValues].[Column_Values] in brackets)

I want to filter out rows from several tables.

Where are things which you want to use to filter the other tables?

How is this possible?

Can't say as I have not encountered the problem.  I use data security filters where I use the user id session parameter in a macro to lookup expressions of what data particular users are allowed to see, which is the technique I was taught.

As soon as the filters are added, the report breaks and the data elements from the queries go missing. The report runs, but the illegal rows pass through,....

"data elements"= query items?  Filters? Plural?  If the report breaks, how can it run?


Also, usually error messages are accompanied with a text message. Some times the message actually tells me what I've done wrong.  Just saying I got error # x doesn't help the cause that much, especially as there isn't a table of error messages hanging around, some error message numbers appear for several different errors, and the error message text, as I said, can actually tell us something.

What the heck is a "transformation layer"?  What's wrong with the ol' query, business, dimension, presentation layer cake?

Start the trouble shooting by investigating in FM and controlling for that.

[BIengineer]

[Transformation layer].[TableAcceptableValues].[Column_Values] is a query subject containing only 1 column of acceptable values for a column that exists in multiple tables, and which is used as a key for joining tables. The contents of this table is determined conditionally upon querying the table in - and this query works. It returns acceptable values, as well as filters out unacceptable values when the user does not have permissions to view this data. The Column_Values are a large amount of distinct numerical keys, N>1mill distinct values. A small subset of this total should not be viewable to some users of the reports.

The filter is a general one.

Is this filter in the query subject? Is it a stand alone filter?

The filter is defined as a standalone (right-click layer, ad..., filter) with the expression given above in the OP. The filters are then added into the relevant query subjects.

When you test the query subject in FM, does it work?  What results do you get?

It returns the acceptable rows, due to excluding the ones the user doesn't have access to. If I for example add a where clause to the query subject's SQL like: where column_A='unacceptable_value', the test returns no rows - rightfully.

If the table [Transformation layer].[TableAcceptableValues].[Column_Values] has only one column, what do you use as keys?

The table is not joined on anything, it is just used as a reference for the filter. The same column however exists in other tables, which I want to filter using the table containing the acceptable values. In the other tables, these values are used as keys on joining together other tables.

"data elements"= query items?  Filters? Plural?  If the report breaks, how can it run?

Yes, the query items in Cognos 11 report query. I have no idea how the report can still run, but I am assuming it utilizes a former version of the package that works?