CRUD vs Select in OLAP

In RDBMS-OLAP,we mainly have it denormalized help with fast reads (SELECT). But it does CRUD occasionally (daily, weekly etc) to refresh the data for reporting

Is there anything that can be done to speed up the CRUD operation? CRUD operations are faster only when DB is normalized, right?

thanks
LVL 6
anushahannaAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dqmqCommented:
>CRUD operations are faster only when DB is normalized, right?
IMHO, that's a vast over-simplification if not an outright misconception.  Indexing, partitioning, and other characteristics of the physical implementation are more important.

Fact tables should be predominantly (if not exclusively) inserts.  Performance on inserts can be improved with a clustered index on an incrementing key.  

Dimension tables are more commonly subject to other kinds of update.  However, the same indexing strategy on the surrogate key usually makes sense.  In addition, indexes on virtually all demoted keys will help with performance during both CRUD and query operations.

Summary tables benefit from indexes on their dimension component, not from indexes on the

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
anushahannaAuthor Commented:
>>>CRUD operations are faster only when DB is normalized, right?

Thanks for correcting my over simplification and false assumption. Yes, Indexing will be very key, too.

>>Performance on inserts can be improved with a clustered index on an incrementing key (in a fact table)

Is an Identity column suitable for this Clustered key on the fact table? If so, then this will be the surrogate key(PK) in the dimension table.

What are demoted keys?
dqmqCommented:
>Is an Identity column suitable for this Clustered key on the fact table? If so, then this will be the surrogate key(PK) in the dimension table.

Absolutely, provided the index is ascending for an incrementing identity property or descending for a decrementing identity property.

What are demoted keys?
Dimension tables are typically de-normalized carrying the logical keys to business entities as non-key fields.  Those keys have thus been demoted.  For example, a date dimension might invite access by day, month, or year, none which are keys to the dimension table.  All however, are demoted keys since they represent a uniquely identifiable thing.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

anushahannaAuthor Commented:
Thanks dgmg, that was helpful.

can you please finish this line, so I can follow your exact thoughts there...
"Summary tables benefit from indexes on their dimension component, not from indexes on the "
dqmqCommented:
Summary tables benefit from indexes on their dimension components; seldom do they benefit from indexes on their summarized components.  For example, if you have a summary table like this:

MonthlySales
--------------
EmployeeKey
MonthKey
TotalSales
AverageSale


The first two columns scream for indexes, the last two do not.





anushahannaAuthor Commented:
Thanks for the simple examples that help very much.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.