Continue in 2 seconds

Is there any possibility of linking the measure of a particular fact to another fact in multiple fact star schema which needs the measure?

By
  • Joe Oates, Michael Jennings, Les Barbusinski
Published
  • January 13 2003, 1:00am EST

Q:

Is there any possibility of linking the measure of a particular fact to another fact in multiple fact star schema which needs the measure? I know this is possible in the back end, but is it possible in the front end?

A:

Les Barbusinski’s Answer: Business intelligence tools that support "multi- pass" SQL generation (such as MicroStrategy or Informatica) can generate compound metrics that are sourced from multiple fact tables. An example would be "Identify the relative rankings in the XYZ campaign of the top 10 salespeople in region 12." This would involve computing the performance of every salesperson in the XYZ campaign (stored in the campaign fact table), ranking them, computing the YTD sales of every salesperson in region 12 (from the Sales fact table), ranking them, filtering out all but the top 10, then linking them to their campaign XYZ ranking. A fairly involved process, but very "doable" with the right BI tool.

Mike Jennings’ Answer: The analytics engine of a front-end reporting tool can use multi-pass SQL and common dimensions between the fact tables to link the measures.

Joe Oates’ Answer: While it is possible to link a measure in one fact table to another, it is normally not a good idea. One of the things that should be avoided in a data warehouse or other very large database (VLDB) applications is the joining of very large tables to very large tables. Fact tables usually qualify as very large tables.

Register or login for access to this item and much more

All Information Management content is archived after seven days.

Community members receive:
  • All recent and archived articles
  • Conference offers and updates
  • A full menu of enewsletter options
  • Web seminars, white papers, ebooks

Don't have an account? Register for Free Unlimited Access