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

Star Schema Cardinality Question

Started by tracer1, 29 Oct 2008 12:51:29 PM

Previous topic - Next topic

tracer1

Hi All,

I hope this has not been asked before, I did a quick search but found nothing. 

I am building a star schema and am joining the dimension tables with a 1 to many relationship.  The problem comes when the report author wants to report on detail and there is no detail for say one company.  I have 3 dimension tables, Company Info, Address Info and Phone Info. 

Company ABC shows up in the report when I use the Company Info and Address info dim tables as there are records there associated with the company.  But when I try to pull in phone number from the Phone Info dimension there is no phone record there, so the report removes this company from the report.

The problem is the join, a 1 to many.  When I change the cardinality 0:1 to 1:N it then brings back the missing company and the phone cell is blank as expected. 

My question is, can I use outer joins like this in a star schema?  I could find nothing in the documentation or internet searches.

Any help is appreciated,

Thanks

Brian

blom0344

Outer joins are potential performance-killers.
A possible solution is to refrain from having nullable FK's in the fact.
Use default FK's where no real FK's exist and add a corresponding entry in the associated dimension.
If you follow this strategy you will not need outer joins, but still have every combo returned..