Transformer Question

[pramsam1]

Hello:
I am very new to cognos. I am trying to do the following:

1. Use iqd file to retrieve data from an oracle database. This gives me most of the data I need for analysis. For example, I get the following columns from this query:
Month_End_Date, Region and Sales.

2. However the data retrieved above needs to be adjusted using an excel file which contains three columns, the same as above - Month_End_Date, Region and Sales but just a handful of records.

I have successfully added the two data sources to Transformer. I have added Month_End_Date and Region from (1) as dimension and Sales from (1) + Sales from (2)as a measure. When I generate the cube, it appears the grand total of sales from (2) is being added to each and every cell in (1). In other words, the adjustment is not taking place by matching the Month_End_Date and Region in both files. What am I doing wrong?

Thanks in advance.

 

[blom0344]

You are not establishing the relationship between the 2 datasets prior to loading the cube.

1. Upload the Excel data to a new table in Oracle
2. Alter the IQD so that the proper joins are made between Month_End_Date and Region
3. Load the cube with the new IQD

The IQD file itself originates from either Cognos7 (Impromptu?) or Cognos8 (Framework manager)

Ties Blom

 

[pramsam1]

Unfortunately, I am not able to load xls file into Oracle as I am just a business user as far as Oracle DB is concerned. I read up somewhere that Transformer does the join automatically on the two data sources based on the attribute names and their data types, but I may be wrong...

 

[drlex]

Transformer was sold on the basis that one could link disparate data sources within it.
pramsam1, to make the association, one would need to ensure that the names of the fields in the data sources are identical (it is case-sensitive) and if the data source is not at the apex of the dimension, the Unique checkbox is ticked, thereby forcing Transformer to associate all identically-named data at that level.

soi là, soi carré

 

[blom0344]

Hmm, I reread the OP and believe I misunderstood your intentions. I am sure drlex knows lots more about transformer, though I can imagine that it would somehow still would have an issue with linking 2 different TYPE of datasources. So, in that case you could find out by performing the action with 2 spreadsheet sources.

drlex, can you elaborate on:

is not at the apex of the dimension, the Unique checkbox is ticked, thereby forcing Transformer to associate all identically-named data at that level.

Ties Blom

 

[pramsam1]

I am a little bit confused here regarding Unique checkbox. If there are multiple attributes (in above example there are just two - date and region, but there can be more) on which to join two data sources, do we make each of those attributes unique? I wonder why Cognos does not allow the joins being explicitly specified by the users. Wouldn't that have been much simpler, cleaner and intuitive way to do this?

 

[blom0344]

Uniqueness has to be understood as not needing another level to define it. Monthnumber is the prime example of a non-unique level. It is only unique when taking the higher year level into account.
In that light both Region and Date are very much unique.



Ties Blom

 

[drlex]

Ties,
I'll do my best to explain what I wrote, although I parse your last comment as indicating that you understand. I assume, from the emboldening, it is just my use of the word apex?

I use apex to refer to the first level in a dimension, as I envisage each dimension as a pyramid, with the subsequent levels increasing the "base size" of such. For multiple sources with the same "apex" or top level, there is no need to specify "uniqueness", as it is just a distinct list of data from the specified field.

soi là, soi carré