Dimension design large cube RRS feed

  • General discussion

  • I am building a large cube for a telco company on top of an oracle database to analyze their customers. There are 3 big fact tables: a subscriber table to count subscribers, a usage table and a revenue table. Most of the dimensions are connected to the subscriber table. This subscriber table allows me to count the complete customer base per month.

    What is the most performant way to allow analyze usage and revenue data against the dimensions connected to the subscriber table? Should I build a view on oracle to combine revenue/usage and subscriber data, so I can build regular dimension relationships on the measure groups? Or should I use referenced relations? I have noticed that the sql queries created by using referenced dimensions create a join PER referenced dimension.

    The oracle tables are partitioned on country and month level, without indexes, because of the size. To give you an idea on the size: one of the smallest countries has 1.550.000 records in the subscriber table in 2006.

    Any ideas, suggestions to optimize processing times are welcome!

    Wednesday, November 22, 2006 11:25 AM