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:
- Dimension Person1, value = Mr.A1
- Dimension Person2, value = Mr.A2
- Measure, value = 100
- 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)
- 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 help or suggestions to previous discussions about the similar case could be very much appreciated!!!