Q: In a star schema can there be eight fact tables? If there can be eight fact tables, what happens if you want to join data from two fact tables? Can you send an example of a schema with two or more fact tables and an example of a join between the fact tables?

Joe Oates' Answer:

It is rare to have a single star schema with eight fact tables. However, it is quite common to have eight star schemas with each fact table sharing some of the same conformed dimensions. In order to join two or more fact tables together, there must be at least one common dimension between the fact tables.

I think that an example might help you visualize how this works. There is a Web site that has some information and examples that may help you. Specifically, there is an example that shows what you asked for. While it does not actually show the SQL syntax for joining the two fact tables, it should be obvious from the diagram how to do so.

Chuck Kelley's Answer:

I see no problem as long as you are not using a query generator. They sometimes resolve these are circular queries, and hence errors. The easiest way to see this is to use two fact tables (f1 and f2) and one dimension on time (DateTimeDim)

Select f1.*, f2.*, DateTimeDim.* from f1, f2, DateTimeDim

Where F1.DateTimeKey = DateTimeDim.DateTimeKey

And F2.DateTimeKey = DateTimeDim.DateTimeKey

Now you may also need to use an outer join based on the information in the fact tables and the data you need required. This should give you a good starting point.

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