Post reply

Very Large Fact Tables

  • March 30, 2004 at 11:35 pm

    #160790

    Hi,

    The client has an Interbase DW that has one very large fact table +/-180mil rows and growth is 600,000 rows per day. The Interbase ODBC driver is somewhat slow in that it read +/- 110000 rows per minute during a cube benchmark read process.

    Estimated no of dimensions is apprx 15 including a time dimension.

    If anyone could please comment on architecture, design, processing times, hardware requirements and possibly even replacing the DW software with something else?

     

    Thanks

    Andre

  • April 1, 2004 at 8:19 am

    #501454

    First thing you should do is review your DW design. Is your fact table to granular? Can you do better summarization during ETL of the data to reduce the number rows? If you can do this you will save lots of headaches. For example my financial ETL takes credits and debits on accounts and just totals them out before going to the warehouse. I drop about 20 million rows just by doing this. But I have this luxury because of extensive work with end users to ensure they only care about totals and not individual transactions.

    A common mistake is to just dump your data into the dw and not modify granularity - do lots of research with end users to determine their needs.

    If you cant reduce rows, then you should dump interbase soon. My experience with it has shown very poor performance after 40 mill rows. I have Oracle and SQL server dbs with 40mill + rows taking 20 minutes to process a cube. These run on big hardware, the Oracle box is a 8 way 8 GB RAM, Fibre channel SAN ... The SQL Server box is a 4 way 4GB RAM on Fibre Channel SAN.

    I created a open warehouse (I don't maintain anymore) at http://www.utcodes.org/queryabledata/queryabledata.htm that has over 2 million rows, 40 dimensions ... runs on a dual xeon 2.4GHZ with 2GB RAM and it take about 1 hr to process - with the dimensions being the biggest hit.

  • April 1, 2004 at 8:46 am

    #501468

    Thanks,

    Unfortunately the DW was implemented prior to our engagement with the customer, and some of the table designs / schemas are very questionable and not clear as to their intention. Probably recommendable to approach the customer for a DW redesign.

     

Viewing 3 posts - 1 through 2 (of 2 total)

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