Best Practice for Ranking

Looking for advise or best practice for ranking of top 10 companies (out of 100s) for aggregates on a shipping comparitive value (tonnage). Would like to make each of the top 10 companies a column with values broken down by region (rows). I also need a column for a single chosen company (not necessarily top 10), and another column for that company's percentage of market share.

I'd like to utilize our data cube in a matrix, but can't solve how to add the last two columns above. We have the option of using the cube or the transactional db. Any advice is helpful, thanks.

One option would be to use the sort number 1-10 as your grouping key and have your report dataset based on a union of the 1-10 dataset, the 11 extra column dataset, and 12 extra column dataset. Then do a Matrix to line it up.|||Thanks, we'll give this a try.

Best practice for indexed views and aggregates tables

Hi, all experts here,

Thank you very much for your kind attention.

I am having some questions on indexed views and aggregate tables.

My question is: To improve the performance of the queries, is it better to use indexted views or aggregates tables for those aggregates which are often queried?

I am looking forward to hearing from you.

Thank you very much in advance for your help.

With best regards,

Yours sincerely,

Well...it depends. Indexed views are great because they keep the data from a view materialized...but the database engine must maintain the indexes, which may slow OLTP operations. An aggregate table may be a good idea, but also might be complicated to design and make sure it is updated properly, not to mention that you have to make sure it is updated.
Tim|||

Hi, Tim,

Thank you very much for your kind advices.

In my case, the aggregates residing in a historical data warehouse, where the update of data is not so much likely and it will not have much to do with any operational database. As what you advised, indexed views would be a better idea in this case?

Looking forward to hearing from you.

With best regards,

Yours sincerely,

|||If the data isn't getting updated often, then I personally would design an aggregates table. That will give you a lot of flexibility for your project...flexibility that may be prohibited by the restrictions on designing indexed views (there are a lot of things you need to adhere to so that your view can be indexed). Hope this helps.|||

Hi, Tim,

Thanks a lot for your advices. They've been very helpful.

With best regards,

Yours sincerely,