how to use distinct in FM?

[JPCogAdmin]

Hi,

How could select distinct rows based on 2 columns. I'm getting different total counts when I log onto Oracle and count all records vs. the count cognos gives me. Using Cognos 8.2 Anybody KNow?

Thanks

 

[blom0344]

Pretty easy, the trick is to create a composite first , like :

Object1||Object2

and perform a distinct count on this new object.

Ties Blom

 

[JPCogAdmin]

Pretty good Ties,

However, I'd have to to that to more than 4 huge tables. The other thought is that how would I pass a pariable to this count query? The other thing is that even if my diagram has been created as a one-to-many relationship, how can I possibly find out if my query is picking up more than one record?

Do you know a trick for troubleshooting Ties?

-Jim

 

[blom0344]

I am not sure I can follow you.
In general terms a database table has a primary key assigned to it. This can either one field or a combination of several fields. A primary key is unique within the table, so the number of keys indicates the number of rows in the table.
So isolate the primary key for each table and return the corresponding field(s) in your query.

Can you specify your problem with an example. I suspect your need is different from what I am making of it..

Ties Blom

 

[JPCogAdmin]

Hi Ties,

yes. In my virtual star schema the fact table has single entries. The dimension has many entries for one record.
So, is the diagram I create the relationship to one-to-one and I still get more records that if I manually write sql code that selects distinct rows.

-jim

 

[blom0344]

In a normal schema the relation between a facttable and a dimension table should be n:1.
A query over these tables should never need a distinct clause cause no duplicates will be returned.
In some cases the dimension tables stores multiple entries for a certain key. In that case (without adding an additional constrain in the join) the relation may be n:m and you will see that duplicates are returned.
You need to check whether the join strategy is correct for the tables involved.

Ties Blom

 

[JPCogAdmin]

Ties,

During the development of the model in FM I created a virtual star schema and using Report STudio looked at the Cognos SQL and native SQL and it looks right. However, how could I troubleshoot the subject query in FM so that I can verify that the dimension table only select 1 of the many entries and one from the fact table? How could you check that in FM?

-Jim

 

[blom0344]

You've got it all in reverse. Query would pick up 1 or more records from the facttable for a given value from the dimensiontable.

Example:

Picture a facttable like:

Company      Product    Volume sold YTD

Shell        Gasoline   150.000.000
Q8           Gasoline   70.000.000
Mobil        Gasoline   120.000.000
Shell        Oil        60.000.000
Mobil        Oil        40.000.000
Texaco       Gasoline   200.000.000
Shell        Twostroke  3.000.000

and a dimensiontable on Company:

Company      Country of origin

Shell        Holland
Mobil        USA
Q8           Koeweit
Texaco       USA

Running a query with a condition like 'Country = 'Holland'' would yield 1 dimension record and 3 factrecords.

company is the primary key of dimensiontable whereas the value 'Shell' can occur multiple times in the facttable.

Now consider a slowly changing dimension table like:

Company      Country (Tax)    Begindate    Enddate

Shell        Holland          1-1-1960     1-1-2004
Shell        USA              1-1-2004
Mobil        USA              1-1-1967
Q8           Koeweit          1-1-1989
Texaco       USA              1-1-1946

You will get multiple results when query reads:
Company = 'Shell', cause you would need to specify:
Enddate is null as well to make Shell unique.

Ties Blom

 

[JPCogAdmin]

Ties,

Thank you for your explanation. Your explanation supports my understanding of star schema and how a flag in the dimension table select only one item. I did alot of manual sql on the database and just found that one of the tables has multiple entries and this table is not a slowly growing dimension. I've realized in this case that Report STudio is correctly giving me multiple report rows because there are multiple records with the same ID in the dimension table. ;-( However, I really appreciate your help. Thanks Ties.

-Jim