performance of BI

View previous topic View next topic Go down

performance of BI

Post  dellsters on Wed Jan 19, 2011 5:12 pm

To run faster ad hoc queries against a star schema, and improve performance, what are the things to follow? Can somebody share some guidelines for an Oracle database? Bitmap indexing, aggregate tables, etc. But when to use what? Anything else to consider?

dellsters

Posts : 39
Join date : 2009-02-11

View user profile

Back to top Go down

Re: performance of BI

Post  Jeff Smith on Wed Jan 19, 2011 7:06 pm

Hate to say this, but it depends. It's a little difficult to tune for ad hoc queries unless the ad hoc queries tend to be similar. With standard reports, I start with tweaking the indexes. If that doesn't improve the performance, then I'll create aggregate tables.

I was once told that if more than 10% of the queries are going against detail, then you are missing an aggregate table.

When creating aggregate tables, I try to get an 80% reduction in the volume of records.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: performance of BI

Post  dellsters on Wed Jan 19, 2011 7:51 pm

Can you elaborate a little more on tweaking indexes? I put bitmap indexes on all the dimension columns.
Anything else other than indexes and aggregate tables?

dellsters

Posts : 39
Join date : 2009-02-11

View user profile

Back to top Go down

Re: performance of BI

Post  LAndrews on Wed Jan 19, 2011 9:14 pm

There are many things to consider.

You probably need to work with a DBA to analyze explain plans, but here are a couple tips on overall performance considerations.

- correct index strategy (e.g. bitmaps on surrogate keys and fact tables)
- DBStats, used by the optimizer. Are the stats up to date? Are the sample sizes sufficient? (e.g. I like to sample 100% on dimensions if possible)
- is the database configured for star_transformation?

An interesting presentation touches on some of the above
http://www.nyoug.org/Presentations/SIG/DataWarehousing/AIS-DWSIG-StarSchema.pdf

Good Luck.

LAndrews

Posts : 132
Join date : 2010-05-13
Location : British Columbia, Canada

View user profile

Back to top Go down

Re: performance of BI

Post  ngalemmo on Thu Jan 20, 2011 2:08 am

General rules for star schemas in Oracle:

1. Turn on the star optimization option
2. Bitmap indexes on each of all fact table foreign keys, rebuild after updating
3. Patitioning always helps, always use local indexes
4. Keep fact tables as thin as possible
5. Don't snowflake
6. Put as much memory on the system as you can afford (64 bit systems)
7. Btree index on dimension PKs, index dimension attributes as you see fit.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: performance of BI

Post  dellsters on Thu Jan 20, 2011 1:32 pm

What do you mean local indexes on number 3?
About number 7, I put bitmap indexes on dimension attributes. Is that a good choice vs btree index? Should these be rebuilt after updating as well? Should btree index on dimension PKs also get rebuilt?

What is the reasoning behind rebuilding indexes?

dellsters

Posts : 39
Join date : 2009-02-11

View user profile

Back to top Go down

Re: performance of BI

Post  ngalemmo on Thu Jan 20, 2011 1:48 pm

When you define indexes for partitioned tables, you can create local or global indexes. A local index only indexes those rows in the parition (one index structure for each parition) while a global index is an single structure for an entire table.

Local indexs are preferable as it allows for parallel use of partitions.

There are no hard and fast rules for dimension attributes, although bitmaps tend to be a better choice. The problem with bitmaps is they quickly grow in size and become fractured and inefficient (real bad in 9, supposed to be better in 11) so, it is always a good idea to rebuild them after updates. When loading paritioned facts, it is usually best to disable all indexes then rebuild the ones in partitions that were updated.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: performance of BI

Post  dellsters on Thu Jan 20, 2011 2:00 pm

That's helpful. Thank you.

One last question. So I can leave the btree indexes alone and not rebuild them after updates?

dellsters

Posts : 39
Join date : 2009-02-11

View user profile

Back to top Go down

Re: performance of BI

Post  ngalemmo on Thu Jan 20, 2011 2:13 pm

Yes. BTrees do not have a problem with becoming inefficient after update activity.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Re: performance of BI

Post  BoxesAndLines on Fri Jan 21, 2011 3:19 pm

For bitmaps, mark the index unusable and then rebuild.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: performance of BI

Post  gvarga on Sun Jan 23, 2011 12:58 pm

You should use in Oracle materialized views for storing aggregate data with query rewrite option and if you have star shema with hierarchies in the dimension(s) you can define the so called Oracle dimensions which can cause additional query rewrites for enhancing the performance

gvarga

Posts : 43
Join date : 2010-12-15

View user profile

Back to top Go down

Re: performance of BI

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum