Nested categories?

[jacobya]

Hi All,

I'm new to this forum and very new to Cognos as well.  I'm trying to build a relatively simple cube in Transformer 7.3.  I have an .IDQ file that links to an Oracle query with the following columns

ID
NAME
PARENT_ID

which is essentially a listing of offices.  Each office can have 0 to many child offices, but each office can only belong to one parent office.

I'd like to set up a dimension in Transformer that would allow users to drill down, starting with the root office, through child offices and see roll-up data at each level.

I cannot seem to get my listing of offices to appear in any related manner, right now they just show up as a flat list.

Can anyone provide any advice on how this can be done?

Many thanks in advance.

[COGNOiSe administrator]

You have to change your query to be self-joined and bring in as many levels as required. Transformer will not do that for you.

[jacobya]

I'm currently doing a CONNECT BY PRIOR query in Oracle to bring back the whole structure, but are you saying the query should bring back the following columns:

ID
LEVEL1
LEVEL2
LEVEL3
LEVEL4
LEVEL5
LEVEL6
LEVEL7
LEVEL8
NAME
PARENT_ID


Thanks for your help, I appreciate it.

[COGNOiSe administrator]

Without the parent as it is without any value. Assuming LEVEL1-8 already has the reporting levels defined.

This looks awfully like my PeopleSoft days with Transformer. You need to bring your dimensional trees and your facts at the lowest reporting level. Make sure that the fields match by name between the fact and a dimension.  This is the way Transformer "joins" them together, so using ID in this case might be not as good as using OFFICE_ID, since I expect you to have more dimensions in your cube.

[COGNOiSe administrator]

Also take a look at Data Manager (aka Decision Stream). It can do lots of dimensional modelling for you from any data source.

[jacobya]

Sorry, but I'm still not sure that I follow.  There is no pre-determined limit on how many sub-levels our hierarchy will have, so one office may only have 3 sub-levels, whereas another may have 10.  Are you saying I need to come up with a query that will "flatten" this, or is there a way that it can be done without me having to define a pre-determined number of levels?

Sorry for my "latency", whole lot of new terms for me to digest :)

[nayitian]

I had such dimension issue, I think it is recursive join.  So, it can be done by one of the ways

1. if you use DecisionStream(or DataManager), when you create diemsion, use Auto-level Hierarchy, in 
    which self join can be done;

2. if you use Cognos 8, recursive join can be modeled within Framework Manager, there is certain charpter
    within online help, which can guide you

3. go back to the Impromptu catalog which generate that .iqd file, define self join, then produce the .iqd
    again.

those are my thought, hope could help you a little bit.
Good Luck.

[jacobya]

Hi, thanks for your reply.  I don't have DecisionStream and I'm using Cognos 7.3, so I created an Impromptu catalog and created a self join (had to create an alias table).  I exported the report as an .IDQ file that looks like the following:

COGNOS QUERY
STRUCTURE,1,1
DATABASE,APPLIWEB_DEV072_AIX
TITLE,Report3
BEGIN SQL
select T1."NAME" as c1,
      T2."NAME" as c2
from "NWS"."NWS_LEVEL" T1,
   "NWS"."NWS_LEVEL" T2
where (T1."NWS_LEVEL__ID" = T2."ID")
order by c2 asc

END SQL
COLUMN,0,English Name
COLUMN,1,English Name


When I add the datasource to my model and then add "English Name" as a dimension, it just ends up resulting in a single flat list of office names.

Any idea what I might be missing?

Thanks for your help

[COGNOiSe administrator]

What is "missing" is dimension level info for each level you plan to have in the dimension.

If you are going to have a ragged dimension (sounds like from what you were saying), it is more tricky to set up, but still possible with Transformer. Transformer will not balance the tree for you, so you need to use your SQL skills, or an ETL tool (like Decision Stream), to even it out (not flatten). What I'm trying to say is, you need to bring your facts at the lowest office level and you need to construct your dimensional SQL in such way that it will virtually add levels to office with shorter list of ancestors.

The other option is to use externally rolled up queries.

[jacobya]

Thanks for your help.  I'm pretty comfortable with SQL, so if I knew what the output should look like, I could most likely come up with the query to do it, but I guess I still don't grasp what you mean by "bring your facts at the lowest office level"

What are externally rolled up queries?

Thanks.

[COGNOiSe administrator]

I should have said externally rolled up measures, which basically bring all data pre-aggregated.

As for the model, in a ragged scenario, like in your case, some offices have less ancestors then others; however, an office without any sub-offices, is always in the lowest level of your dimension, and all offices, regardless of their position in the tree, have the same top-level ancestor.

So first, you need to know how many levels your dimension is going to have, meaning what is the longest path from your bottom level office to your top level ancestor. Then you need to prepare  a query, which will return at least one column for each level (ie. seven columns for seven levels).

Now, if your offices WITH children can have some facts on their own, then another node has to be created to reflect that, and again, put on the very bottom of your dimensional tree.

If a bottom level office is missing levels between itself and its parent, then those levels should be nullified or be empty strings.

Darek