Can't Add new columns to an existing Union Table in Data Module

[timness]

Hi All,

Been a while. Hope everyone/thing is good around here.

I'm working on a data module for a customer - setting up data for dashboard use. Cognos version is 11.1.7 sitting on an Informix database on Linux.

Cognos is hooked directly into ERP transactional database (pretty unique setup), so no ETL - just FM packages to bring relational data into Cognos. ERP runs on Informix database as well.

The customer has a large volume of transactions in the the source table, with daily updates happening, so we've split the data into two datasets. Anything older than current month in the "long term" dataset (weekly or monthly updates), and current month in "short term" dataset which gets updated hourly.

To make it all work from the DM point of view, i've created a custom Union table in the DM, completed the modelling on that object and hidden the datasets from end users. Nice clean table, all data present, updates quickly.

However, the customer has come back, after two dashboards and a handful of reports have been written on the DM, and wants a few more columns in this union.

Updating the datasets is easy. Re-linking the datasets into the DM is easy. Loading the new columns from the re-linked datasets is easy.

What i can't for the life of me figure out is how to get the new columns to add to the existing Union table. There's no option anywhere that i've been able to find (or google) that lets me do that..

So i'm reaching out to the gurus of the Cognos world to see if anyone can help...?

Any help greatly appreciated.

=)

timness

[bus_pass_man]

You can't edit a union.  I don't know why.   

There's a work around.

1. Create the new union.
2. Select the old union and copy the identifier property.  It is in the advanced section of properties.
3. Add an x or something to change the old union's identifier.
4. Select the new union, go to the identifier property and paste in the old union's identifier value.
5. Do the same actions for the label property.

If you open your dashboard the new union should be used in the query.



Your data sets are the data warehouse and your actions to create them the ETL.  Just to confirm, are all the dimensions conformed to all the facts?  Are there multiple fact grains involved?  Think about using column dependency to normalize your union's metadata.

[timness]

ah! lovely.

Thanks bus_pass_man.

I'll be trying this out today. Your assistance is very much appreciated.

[timness]

Thanks again for this. Have used this several times now, and it's working a treat.

Much Appreciated.

8)

You can't edit a union.  I don't know why.   

There's a work around.

1. Create the new union.
2. Select the old union and copy the identifier property.  It is in the advanced section of properties.
3. Add an x or something to change the old union's identifier.
4. Select the new union, go to the identifier property and paste in the old union's identifier value.
5. Do the same actions for the label property.

If you open your dashboard the new union should be used in the query.



Your data sets are the data warehouse and your actions to create them the ETL.  Just to confirm, are all the dimensions conformed to all the facts?  Are there multiple fact grains involved?  Think about using column dependency to normalize your union's metadata.