If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

What if we have Orphans categories?

Started by Jaspire, 10 May 2008 07:50:11 AM

Previous topic - Next topic

Jaspire

Hi,
We are using a powercube which has Orphan categories in it. I appreciate if any of you could tell me the negative affects we might have in our cube because of those orphans. Also I appreciate if you could suggest me how to get rid of the Orphans.
Thanks,
Jaspire

rockytopmark

I Troubleshoot by using my Dimension Queries as part of a "NOT IN" where clause with the fact query.

Select * from Fact
Where fact.keyColumn NOT IN (Select Dim.keyColumn from Dim)

The results of this query are your orphans.  The rest is dependent on your situation... should there be orphans?  Why are there orphans?  Do I need to change something in a prior ETL process to handle this?

If it comes down to that there indeed can be these orphans, I would take that same test SQL as above, and use it to create a 2nd Dim IQD for that dimension and return all the orphans in an exact same construct as the original dimension IQD.  You can create category codes and labels in each level that "bucket" these orphans into an "Other" grouping or something.  This way your Fact information is a valid and in scope within the model, and your hierarchies are not broken by these orphans.

almeids

You can also handle orphans within the Transformer model by creating orphanage categories in your dimension(s), but I've always found this difficult to both understand and implement and believe it requires prepopulating the regular categories.  It fits in with a more manual approach to cube building which Cognos always seemed to emphasize in their Transformer documentation and which I've never found helpful but may work in your environment.
Rockytopmark's or other similar approaches to create "dummy" categories in your dimension(s) give you good control over placement and identification of these.
I'm not aware of any negative effects of this situation; even if unhandled Cognos will stick them in a blank category.  Maybe some performance implications at build time but probably not significant.

Jaspire


Thanks to rockytopmark and almeids.
I may just leave them as there is no problem with keeping the Orphans.