Self referencing table

[BlueBrand]

I want to build a dimension in Transformer from a self-referenecing table. i.e.

CuatomerID, Name, ParentCustomerID
1, Jack, 0
10, Jill, 1
11, Jason, 1
20, Fred, 10
21, Frank, 10

So, a customer can both be a parent and have a parent, and I want a dimension that represents the hierarchy. What I want is to create different levels based on the position in the hierarchy, but to do that I need individual column names. Any idea of how to do this?

Thanks.

 

[drlex]

BlueBrand,
A starting point would be to alias the table and then joining it to the original table using CustomerID & ParentCustomerID, either by an equi-join or with logical constraints determined by the other fields in the table.
lex

 

[5395]

Hi drlex,
I need also to use self join.
But for that, I have to use impromptu. In transformer, I can't do self referencing table. Do I understand right?
ras

 

[5395]

Hi BlueBrand,
can you please write in this forum how you did with your example more in detail? For example, how you aliased the table and how you did joins after the drLex's recommendation?
I have exactly the same case. I have a self referencing table and wanna build in Transformer as Levels. (Nothing to do with Impromptu). I will be also looking for how to do it.
thanks in advance.
ras

 

[drlex]

If your customer dimension is truly exclusive, then you should be able to read the table as many times as you have levels, setting the level id appropriately.
So on Bluebrand's example, a hierarchy
Name ID Label
----- ---- ------
Parent PCID Parent Name
Customer CID Customer Name

can be formed by reading the customer table once as
CID (CustomerID), Customer Name, PCID
and then again as
Parent Name, PCID

If you have exclusivity issues and you're happy to hack, you can write your own iqd in notepad and put in the self join like so:

COGNOS QUERY
STRUCTURE,1,1
DATABASE,<yourdb>
TITLE,Report1
BEGIN SQL
select T1."CustomerID" as c1,
	   T1."CustomerName" as c2,
	   T1."ParentID" as c3,
	   T2."ParentName" as c4,
from "yourDB"."dbo"."CustomerTable" T1,
	"yourDB"."dbo"."CustomerTable" T2
where (T1."ParentID" = T2."CustomerID")

END SQL
COLUMN,0,CustomerID
COLUMN,1,CustomerName
COLUMN,2,ParentID
COLUMN,3,ParentName

soi la, soi carré

 

[5395]

Hi DrLex,

Thank you for your answer.
Theoretically, I have a question to your explanation.

You say- (If your customer dimension is truly exclusive, then you should be able to read the table as many times as you have levels, setting the level id appropriately.)

This is what I thought to do in case the cognos transformer doesn't offer a proper "concept" to make self referencing table. Proper concept for me means that I should able to make self referencing table, independent to the number of hierarchy levels. In your example, however, the levels are fixed as two: T1 and T2. But if I have more than two degrees in the hierarchy, it is a problem, right? And this is for me a second choice solution. Not proper concept.
Do you know for sure that Cognos offer a proper concept for it or not?

p.s. To let you know that I am very interested to hear your opinion and I also spend time to find it out:
in the mean time, I created self-join in the cognos impromptu and trying to open it in the transformer. I thought I can see how the self-join build in impromptu looks like in the transformer. Unfortunately, I faced another problem (database couldn't be read) ....

 

[5395]

Hi, DrLex,

I will give a short report about what I have so far.
- I have created a self join in Impromptu and looked at it. Unfortunately, it doesn't look at all like your code. No similarity so far.
- Then, I opened the IQD files in Transformer (got removed the error). The result is that I have to build the dimensions anyway on my own, because the from IQD sources automatically created dimensions are not at all what I wanted. (so useless). -> here I wonder why people use impromptu...

As next, I would try to put your code....