Topic
  • 2 replies
  • Latest Post - ‏2010-12-06T12:13:14Z by SystemAdmin
SystemAdmin
SystemAdmin
280 Posts

Pinned topic 2 data sources referring to the same DB table (different links to fact)

‏2010-12-06T10:28:09Z |
Hi all,
I am in the process of adding a new dimension (person2) which has the same data source as an existing dimension (person1). The two corresponding data sources result from the same database table (named "person").
The reason of the change is: existing data source (person1) should link to fact table (F1) via column F1.personID_1 while the additional data source (person2) links to fact table via another column F1.personID_2
The problem is, the newly added dimension person2 in the generated cube still refers to data source of person1 instead of person2. Here is some sample data of the result:
Row1:
  • Dimension Person1, value = Mr.A1
  • Dimension Person2, value = Mr.A2
  • Measure, value = 100
Row2:
  • Dimension Person1, value = Mr.A2
  • Dimension Person2, value = Mr.A1
  • Measure, value = 200

When selecting A1 from dimension person2, I would expect the measure value = 200 (row2). But the result I got is 100, resulted from row1 (so, dimension person2 still refers to data source of person1)
Background information/activities:
  • In Cognos 7 catalog: Create an alias of table person, name it person2
  • In Cognos 7 catalog: Create link (join) between person2 with fact table F1 (person2.personID = F1.personID_2)
  • Create imr report with data source from person2, save as person2.iqd
  • In Cognos Transformer: Add new data source (person2.iqd), add new dimension and necessary levels (person2)
Any other changes I should make in either the catalog (.cat) or the model (.mdl) itself to have the expected result?
Any help or suggestions to previous discussions about the similar case could be very much appreciated!!!

SB
Updated on 2010-12-06T12:13:14Z at 2010-12-06T12:13:14Z by SystemAdmin
  • SystemAdmin
    SystemAdmin
    280 Posts

    Re: 2 data sources referring to the same DB table (different links to fact)

    ‏2010-12-06T11:36:40Z  
    Transformer will match different data sources based on matching names. The joins from the catalogue are only applied in the report/iqd itself and not between two data sources in the Transformer model. What you will likely need to do is rename each of the columns in the person2 data source and make sure that your second dimension in Transformer uses these renamed fields. The lowest level of the person2 data source (the ID field) should have the same name as the person2 id from the fact data source. For example:

    Person1:
    Person1_Group
    Person1_Sub-Group
    Person1_Name
    Person1_ID

    Person2:
    Person2_Group
    Person2_Sub-Group
    Person2_Name
    Person2_ID

    Fact:
    Person1_ID
    Person2_ID
    Measure1
    Measure2
  • SystemAdmin
    SystemAdmin
    280 Posts

    Re: 2 data sources referring to the same DB table (different links to fact)

    ‏2010-12-06T12:13:14Z  
    Transformer will match different data sources based on matching names. The joins from the catalogue are only applied in the report/iqd itself and not between two data sources in the Transformer model. What you will likely need to do is rename each of the columns in the person2 data source and make sure that your second dimension in Transformer uses these renamed fields. The lowest level of the person2 data source (the ID field) should have the same name as the person2 id from the fact data source. For example:

    Person1:
    Person1_Group
    Person1_Sub-Group
    Person1_Name
    Person1_ID

    Person2:
    Person2_Group
    Person2_Sub-Group
    Person2_Name
    Person2_ID

    Fact:
    Person1_ID
    Person2_ID
    Measure1
    Measure2
    Many thanks, Philip

    That is exactly the problem, I did not think about column name mismatch!

    Have a gr8 day!

    SB