Post reply

Multiple Fact Tables

  • October 21, 2003 at 11:32 am

    #106463

    Hi,

    I need to build a BI app for an E-Procurement system that has 6 facts (quotes, orders, invoices, etc.). Should I join all of these into a single fact table, or build separate ones? The end-users need to compare two o rmore facts (i.e. low long has it taken from quote to invoice?). Help is appreciated.

  • October 21, 2003 at 2:18 pm

    #479000

    You can use only one fact table, but you can create a view to join all the fact tables and use the view as a fact table in AS.

  • October 21, 2003 at 8:52 pm

    #479024

    I would go with the separate facts, merged into one in a view if required, based on the assumption that the fields related to each fact could and prob would change over time.

    Steve.

  • October 21, 2003 at 9:38 pm

    #479025

    Hi. AS2K has many more options. The fact table design depends on the dimensions of each fact and its grain (detail). Grouping silimar facts together in one table is a good idea unless you update each fact at a different time. Make it easy on yourself and design the fact table around the nature of the data. Then you can make a virtual cube linking several cubes together along the shared dimensions.

    You can also have a cube that has several partitions, each with its own fact table. Its a bit more work, but not much.

    There are alot of options aside from the one big fact table.

    William

  • October 22, 2003 at 7:14 am

    #479090

    Have to agree to keep the fact tables separated and join all data using Virtual Cubes / Cube partitions.

  • October 22, 2003 at 10:10 am

    #479138

    I would recommend checking out "The Data Warehouse Toolkit" by Ralph Kimball, (published by Wiley). This is an excellent resource for best practices in data warehousing.

  • October 22, 2003 at 12:27 pm

    #479162

    Thanks everyone! I believe I have the right vision now.

  • October 23, 2003 at 2:20 am

    #479232

    As you can only have measures generated by the fact table I useually find that it's the measures I need that dictates which tables are Facts and which are split out into starts or flattened into the fact. I find it best to start with "What do I want to count/sum etc" rather than "which tables have I got" or "how do I want to break it down"

    Keith Henry

    DBA/Developer/BI Manager




    Keith Henry


    According to everyone I know I "do something with computers?" for a living, so there you go.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply