If you are unable to create a new account, please email support@bspsoftware.com

 

Deleting a column reference triggers error MSR-VRF-1107 in data module

Started by ImSteve, 28 Oct 2021 10:18:16 AM

Previous topic - Next topic

ImSteve

Hi there!

OK, it's time. I'm gonna try an actual Please Help Me post.

We're on 11.1.7, getting our feet wet in data modules again, after trying -and deciding to wait a bit- in an earlier version. It's improved a lot! We still have issues though, most of them to do with changes in the source, and I'm about done with relinking, rejoining, rebuilding without knowing what is going on. Every bit of dox or youtube I found is 'Building My First Module' level. Tried to search the forum but no specific results, so here we go:

I made a Dataset with some calculated measures. Built a Data Module on top, also to add an Excel sheet as extra source for some clustering. We use a two layer setup in modules, using views and shortcut aliases, a Joined table view in this case. So far, so good, all working fine.
Now, as I'm building my dashboard, it turned out one of my measures is unnecessary, even confusing, so I'm trying to delete it.

Error. Went back and tried again, and again. I edited the join table, deleted the measure from the data layer first, or from the model layer, tried editing the expression first (to reference another source measure). Tried cleaning the module before or after actually changing the source. Every time I delete it in any place, I get the same validation error. This pops up in various places in the module, even on the Excel sheet, where it's not referenced at all.

***
MSR-VRF-1107 The table "
" has an invalid column dependency that references column "[column]" which is missing
***

Well yes, I know it's missing, Cognos, I just deleted it. Where is this dependency? Where is the reference? What more can I delete? How can I even *look* for dependencies? 
I assume I'm overlooking something really obvious (like the Source View icon that can add columns, took me weeks to discover), hope anyone can point me there.

Thanks in advance,
Steve

bus_pass_man

The reference is in the column dependency.  Select the query subject which has the error icon and choose column dependency from the context menu.  Delete the deleted calculation from the column dependency group in which it is located.

I think the big problem is that if that message which you have quoted is actually the text of the message and the identifier of the query subject and the column are missing from the message then something has gone wrong.

If that is the case and there's no actual icon, you would need to select all the query subjects in question in turn and check the column dependency. 

If I understand correctly, you deleted the calculation, got the error and then you tried to delete it from other places?  I'm not quite following what you did here.   One of the fundamental rules of troubleshooting is, if you don't know what to do don't do anything.  In this case, if you get an error message complaining about something to do with column dependency the seemingly obvious approach is to check the column dependency. Not trying to be snarky though so sorry if it sounds like it.

QuoteError. Went back and tried again, and again. I edited the join table, deleted the measure from the data layer first, or from the model layer, tried editing the expression first (to reference another source measure). Tried cleaning the module before or after actually changing the source.

Just out of curiosity, can you go into more detail about the structure of your module and what is in each layer.  I'm guessing you have the data set and the xls in your data layer and created another folder where you created a joined view.  Is that a correct understanding? Just out of curiosity, why did you create a joined view and not create a relationship between the data set and the xls and create an ordinary view?


Not a list of RTFM links:

https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=dependencies-defining-column

https://pmsquare.com/analytics-blog/2020/2/2/cognos-power-of-data-module-column-dependencies

https://www.atlanticcognosanalytics.com/post/column-dependencies-in-data-modules

https://www.ibm.com/support/pages/multi-fact-multi-grain-and-relative-dates-defining-column-dependencies-prevent-double-counting-cognos-analytics-proven-practice-capp-document

https://community.ibm.com/community/user/businessanalytics/blogs/ian-henderson1/2020/04/30/migration-of-cognos-query-engine-primary-key-query

ImSteve

Hey bus_pass_man,

thanks so much for a quick answer and the care I see in those links! I can handle a deserved snark just fine, no worries. In fact I'm kinda hoping for one ;-)
Unfortunately there are no column dependencies to delete.

Sorry for the confusion; I was describing several distinct attempts. Also, the errormessage contains the actual names, I blanked it for public posting, company policy.

Instead of giving many more details, let me start over, removing factors from the equasion. The most bare bones reproduction is, surprisingly:

1. Create a new Data module (containing, in this case, only one simple flat Dataset). One click, done. No Excel, no layers, no views, nothing.
2. Remove any item from the module. It does not matter if it is, or isn't, deleted from the dataset first.
3. Validation error.
That's it!

My question seems to become: how am I managing to fail this most basic edit?
IBM, in https://www.ibm.com/docs/en/cognos-analytics/11.2.0?topic=sources-updating-columns-in-data-module , simply says to 'remove the columns that no longer exist' which is what I'm doing, and nothing more about it. The chapter elaborates about refreshing, metadata, references in expressions, etc, all not relevant here, I'd think.

Thanks in advance for any more insights!
Steve

bus_pass_man


I'm relieved to learn that the message isn't displaying empty strings for the identifier of the query subject and the column; I'm not sure I'm happy about you not mentioning that edit.  It will tend to require a certain extra skepticism about any other statement you have made or will make as I will need to try to guess if any thing that you say actually means what it common everyday usage is and what information you are not mentioning.  What is the object being mentioned in the message?  I'm guessing the joined view.

Quote1. Create a new Data module (containing, in this case, only one simple flat Dataset). One click, done. No Excel, no layers, no views, nothing.
2. Remove any item from the module. It does not matter if it is, or isn't, deleted from the dataset first.
3. Validation error.
That's it!
I performed the actions of your test case and did not encounter a problem. I used a dataset, that is to say, I used an object which cognos calls a data set. I imported the data set into a new module.  I then deleted a query item from the query subject which represents the data set. I did not get a validation error.  I did not understand what you meant by ' It does not matter if it is, or isn't, deleted from the dataset first.' The statement seems to imply additional, unmentioned steps. 


As I said before, don't just do any old thing; have a general idea what the action will do before you do it.




ImSteve

bus_pass_man,
First off; the issue is gone. Yay.

I was really just trying to not be too long winded, which I tend to be. Sorry for being confusing, and especially for editing the error message without mentioning that. I know better.

The bit you quoted is the most bare-bones way I found to recreate the issue, ignoring the extra sentence in step 2. When I deleted anything from the module, Cognos gives me the dependency error. Simple as that.

This happened for a couple of days. Today I tried the exact same thing again, and no error. I have no idea what I could be doing different, but here we are.

Quote from: bus_pass_man on 31 Oct 2021 01:21:14 PM
As I said before, don't just do any old thing; have a general idea what the action will do before you do it.

Yes, I prefer that myself. The general idea here was that removing something would, well, remove it. Sometimes it's just try, and try again.

Anyway, maybe it'll happen again, maybe not. In any case, thanks for the effort!
Steve