FM for multiple dimensions and one fact

[sstengler]

I have the following model scenario: one fact table (revenue "$"s) and many dimensions (Product, Salesperson, etc), all joining through the fact table.  We've pretty much made a straight-thru model in FM, which works for the reporting we are required to do (so far).

However, we now have a requirement to have the reports show various dimensions even when there is no revenue row in the fact table (outer join).  I've set up the outer join relationships for a couple of the dimensions as a test, and they appear to work as long as the report uses columns from only one dimension and the fact data (Product and Revenue, for instance).  Add another dimension (i.e., Salesperson, Product, Revenue), and I don't get all of the rows of missing revenue.

Not sure where to go from here - it's been awhile since I took the modeling class (Cognos was it's own company at the time). Using 8.4 BI.

Thanks for any insight!

[blom0344]

I do no understand your remark about 'various' dimensions. Combining dimensions makes sense in context of existing fact-data. What type of combination of dimensional data are you looking for?

Coding outer joins when there is no need (as FK's are not nullable) can have serious drawbacks regarding performance

[Lynn]

Here are a couple of thoughts:

1) Look into the possibility of modeling a factless fact table (as Ralph Kimball describes) to address reporting around things that didn't happen.
2) Consider building a cube or DMR model which will give you an intersection point for every fact and every dimension, making it easier to see products that didn't sell or salespeople who didn't perform etc.
3) Consider a Report Studio report that retrieves the desired dimensions and then unions or outer joins to facts within the report (this will involve local processing and may not perform well depending on your volumes and level of granularity).

I wouldn't set the joins in your model as outer joins before exhausting every other possible approach.

[sstengler]

blom0344 - let me give you a quick example.

We have a star schema with a fact table (call it Revenue), then a couple of dimensions: Sales Team, Product, and a Calendar. The two dimensions link to the fact table with a key field.

The existing report shows the Sales Team, then Products, then the Revenue for the products that the sales team has for a particular time period.  However, each sales team does not always sell all products for a given time period, so the report ends up not showing the products with zero revenue for a particular sales team.

What we need is to be able to show all of the Products on the report for every sales team, whether there is revenue for that Produce or not.

Does this help?

[sstengler]

"We have a star schema with a fact table (call it Revenue), then a couple of dimensions: Sales Team, Product, and a Calendar. The two dimensions link to the fact table with a key field."

Should have read "... then several dimensions: Sales Team, Product, and a Calendar. The three dimensions lik to the fact table with a key field."

[blom0344]

Well, let me start by pointing out that you can edit your own posts, so you do not need to repost.

Basically you problem is due to the relational instead of dimensional nature of the model. Dimensional models are much better in doing what you need.

One , rather radical solution - as described in various posts recently -  is to build this as a union:

set1: fetches the 'real' data based on existing joins
set2: fetches the combination of all date periods,products and sales teams through cross joins using a zero as dummy measure.

The last set may become fairly large and requires that cross joins are allowed against the model. It is not really a solution for the faint of heart or the novice though!

[RobsWalker68]

Hi,

Following on from the posts by Lynn and Blom have a look at the Kimball article below. It's entitled "What didn't happen" and details a number of design options open to you.

http://www.kimballgroup.com/html/articles_search/articles1999/9902IE.html?TrkID=IE199902

[tjohnson3050]

IBM has a proven practice for creating fake fact table query subjects that you might find interesting.

http://www.ibm.com/developerworks/data/library/cognos/modeling/design/page491.html

[sstengler]

All -  thank you for your replies.

Looks like creating a dimensional model is the best choice for us, since that allows us to see what we need as far as zero-revenue, and yet doesn't involve Cartesian or cross-joins.

It's been several years since I've created a dimensional model - any recommendations on resources to review?

Thanks!

Sam

[tjohnson3050]

IBM Cognos Information Center:

http://publib.boulder.ibm.com/infocenter/cbi/v10r1m0/index.jsp?topic=%2Fcom.ibm.swg.im.cognos.cbi.doc%2Fwelcome.html

Navigate to the section Model and Design -> Framework Manager User Guide 10.1.0 -> Modeling Relational Metadata

Even though the section is titled Relational, DMR (Dimensionally Modelled Relational) data is covered here