Joins in Transformer

[halkosj]

We are quite confused over how to join columns in a Transformer Model.

We're using a relational model from a Framework Manager package as our source.

We were under the impression that the joins defined in Framework Manager would be imported into Transformer. But a Cognos consultant who was here last week said Transformer joins are based on identical column names.

So which is it? And how can we use column names, when those column names do not appear explicitly in the relational model.

And when a join is done in transformer, how do we know? In other words, where in transformer can we find the join information? Is there a diagram somewhere that shows them (like in Framework Manager). Or is there a list of the relationships somewhere on some property page?

[prikala]

Unfortunately Transformer does not use joins. It only associates data based on column names.

When documentation says "define joins in FM" it actually means you must then create a model query subject that includes items from all tables/querysubjects that are to be joined together.

When you want to import data (in the transformer model) from multiple data sources, make sure that column names are identical when appropriate.
For example, your dimension is (City name, city code) and fact is (city code, population), transformer can associate City name with population.
If you do not mind "heavier" queries, you can combine all data in your datasource querysubject (in FM) as (City name, city code, population).

Transformer can be quite tricky and confusing. Read the user guide wery carefully and then take some courses. I am not familiar with v8 courses but "IBM Cognos 8 BI Transformer:
Design OLAP Models" seems like a good choise.

[halkosj]

Hmmmm - when we bring in objects from FM, we have been putting them all in the same transformer data source.  We're not sure why we would use multiple data sources - is there a reason we should use multiples?

With a single data source, transformer will not allow two columns of the same name. So taking your example, in transformer we would have:

dimension is (City name, city code)
fact is (population)

Transformer is correctly joining these two, yet there are no matching column names -  so we assume that transformer is using the joins defined in FM.

We've been studying the User's Guide, and we have several people who have attended that class. Unfortunately its only a 3-day class -- not nearly enough to cover all the idiosyncracies of Transformer.

[JGirl]

Transformer gurus, correct me if i'm wrong please:

There are a number of separate possibilities in Transformer:

1.  If using a single IQD or flat file
No 'joins' would be used as the single query subject contains all columns, and any required joins to produce the single query subject would be in the IQD file.  Obviously flat files dont contain joins.

2.  If using multiple IQDs or mulitple flat file data sources:
Columns with the same names are determined to provide the same data (so if you had a query subject for your product dimension, and a query subject for your fact, and both query subjects have a ProductID, transformer would determine that ProductID is the key that links the two query subjects) and the data would be 'joined' on ProductID by transformer.  This would be a 'join' that is done in transformer though, not sent to the database.

3. If using FM or a C8 reports to supply data to the cube:
The joins setup in your FM model should be utilised within each individual data source you have included in your model.

4.  If using multiple query subjects from FM or multiple reports
Each report would be executed separately (The SQL and the joins within each can be viewed by using the 'datasource viewer --> profile' when right-clicking on the datasource), and separate query subjects would be linked in the transformer model by column names.

There is no formal concept of 'Joins' in transformer, so there is no where for you to view a logical data model, view cardinalities etc.

You could use multiple datasources if you wanted to separate your dimensional structure from your facts.  Scenarios where this may be useful include:
* When you have enormous volumes of fact data to process and dont want to bring back dimensional attributes for each
* When you have facts being supplied from multiple query subjects
* When you have your dimensions being populated from a DB and facts in a flat file
* When you want to include all dimensional values in your cube model, but not all values have facts against them.

Hope this helps!
J