Another frabjous day! You can now edit Unions, intersects, and excepts
With the release of Cognos Analytics (CA) 12.0.2, you can edit set operations (Unions, intersects, and excepts). This is an extension of the ability to edit views, which was introduced in CA 12.0.1.
You no longer need to recreate the set operations if you find out you need to revise them; now you can do that revision in the editor of the set operation.
You can now add or remove columns from the union, intersect, or except. You can also replace a table used in any set operation with another table of the same structure. In addition, you can now add additional tables to a union.
You continue to be allowed to have any number of tables in an union. The number and data types of the columns of the source tables of the union continue to need to match.
You continue to be restricted to using two tables in an intersect or except. The source tables continue to need to match the number of columns in the tables and their data types.
Background
One common situation would see customers editing the source tables of an union, intersect, or except. They would then discover that they could not have those edits propagate to the set operation object.
The workaround was to recreate the set operation, swap in the old object’s identifier, and then delete the old object. This swapping of identifiers would allow existing reports and dashboards to pick up the new object automatically. Any additional modelling, such as calculations, which had been performed in the union, intersect, or except would need to be replicated in the new set operation. This workaround was slightly suboptimal.
Editing
This is the workflow for editing a set operation to propagate to a set operation the changes made to its source tables.
If an union, intersect, or except has more or fewer columns than its source tables then you will observe validation warnings. If the source tables have a mismatch of the number of columns you will be warned as well.
In addition, if you edit a source table and change the data type of one of the columns this, too, could be identified as a problem for the union, intersect, or except, as the data types of the source columns need to be compatible.
To add columns to the set operation, select the set operation and choose edit table from the context menu. Press the finish button. This will add to the set operation those columns which were added to the source tables.
To remove columns from the set operation, select the set operation and choose edit table from the context menu and press the finish button. This will remove from the set operation those columns which had been removed from its source tables.
To remove a table, select the set operation and choose edit table from the context menu.
You will see a list of the tables used in the set operation. If you press the custom table button (the first one) you will see the columns in the set operation.
Press the previous button in the dialog. This will display the tables of the set operation.
Here is the view all tables button.
If you press the view all tables button, you will see a flat list of all the tables in the data module.
If you press the tree view button the tables will be presented in a tree, just as seen in the module metadata tree. The tables used in the set operation will be indicated by their associated checkboxes being set on. Uncheck the checkboxes for the tables you want to remove from the set operation.
If you want to add a table, you would check the checkbox for that table.
You would then press the next button to proceed into the next page of the dialog and press finish to save your edit.
The actions will be evaluated by Cognos. If the edit has resulted in an invalid condition for that object, then the button to advance to the next page of the editor will be disabled. One example of an invalid condition would be if only one table has been left in any of union, intersect, or except. Another example would if the data types of the columns in the tables do not match. The section Editing validity indication details the necessary conditions for the edited set operation to be saved.
Normally when you edit a set operation, similar to editing a view, the editor will open the Edit an union of tables/ Edit an intersect of tables/ Edit an except of tables page. If the set operation source tables do not match, then editing the set operation will open to the edit table page of the editor. This is because the options available to you are to either edit the source tables to make the number of columns in them match and, if necessary, edit the set operation to propagate those changes to the set operation or to edit the set operation to replace one of the tables used in the set operation with a table which has the same number of columns as the other table in the set operation.
Objects created using the union will be screened out of the list of available tables when you edit the union. This will prevent you from creating a circular reference.
There is a minor UI change in the context menu. When you select a set operation and invoke the context menu from the more button there will now be a menu item called edit table. This allows you to edit the set operation. This menu item replaces the previous menu item for editing views, edit table view.
Source structure resolution
The set operation’s folders will be preserved during an edit, even if the source table folder structures differ. If a column is added to the source tables within the structure of the existing columns then the folders will be discarded. This is different from creating set operations, where folders will not be honoured and a flat list of the query items is generated.
For example, assume this union.
Assume this is one of the source tables of the union.
Assume this is the other source table of the union.
Assume that the union is edited and one of the source tables is removed and replaced by a table with this structure.
The edited union will retain its folder structure.
The picture below shows the result of a column being added to the sources causing the union to discard the folders and use a flat list of columns.
Editing validity indication rules
The rules for matching tables in a set operation are as follows.
General rules
1. The number of columns of the source tables must be the same.
2. The data type of a column must be compatible to the column in the same position in the other source table (or tables if you are creating or editing a union).
Union
There must be at least two tables in the union. If this is not the case, then the next button is disabled.
Intersects and excepts
You can add or remove columns from intersects and excepts so that they match the columns of the source tables.
You can replace tables of an intersect or except. You must have two and only two tables.
The editor ensures that the columns are compatible. If not, then the next button is disabled.
If only one table is specified in the intersect or except then the next button is disabled.
If more than two tables are selected the next button is disabled.
Swapping table order hack
Assume you have a union (or other set operation) where one or more columns in the source tables have different labels for each of the source tables. When you create the union the labels of the columns will use the first table which was selected. You can edit the columns in the union to change this. Another situation would be where you’ve created an except and then realized that the table order is the opposite of what you really wanted.
The following is a technique which I found for dealing with this situation.
You would select the union and choose edit table.
Once in the editor, click the previous button. Then, unselect the source table which is being used to generate the labels of the union. Then click the view all tables button.
This will change the display of the editor from displaying just the tables in the union to displaying all the tables of the data module. By default, it is a flat list. You can press the tree view button to display the tables as they appear in the data module metadata tree. You then would select the table you want to add to the union, which is presumably the table you just removed from it. You would then press the next button. You will notice that the column labels in the data grid will reflect the column labels of the appropriate source table. Proceed through the rest of the pages of the editor and save the changes you have made.
View the columns of the edited union. You will see that the labels have changed. The identifiers will have changed too.
It is just in the same ballpark as any other time you change the identifier of something: you need to realize that this can affect reports. You need to make sure that you either won't be doing that or, if they do, either modify the identifiers so that they match what is in the reports or be proactive to edit the reports to fix the problem before too many people complain. As always, there’s tradeoffs for all the options and you need to decide what ones you want to accept.