| OLTP & OLAP Desgin question [message #626254] |
Thu, 23 October 2014 13:57  |
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
|
Member |
|
|
Hello Experts,
Not sure in which section to post this question but I will try here.
Could you please help me with an architecture question?
I have worked for many years in "traditional" OLTP/OLAP shops with various architecture models such as Kimbal/Inmon models.
Currently I am in a product company which has a OLTP/OLAP solutions. The product is installed on customers site.
I have a push from product management to combine OLAP/OLTP into one schema as opposed to different schemas, one for OLTP and other for OLAP.
What are the pro's and con's for each approach?
This application could be installed either Oracle or SQL Server as backend.
Thanks for your time.
-Ravi
[Updated on: Thu, 23 October 2014 14:01] by Moderator Report message to a moderator |
|
|
|
|
|
| Re: OLTP & OLAP Desgin question [message #626259 is a reply to message #626258] |
Thu, 23 October 2014 17:52  |
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
|
Member |
|
|
|
So you suggest that we take functional spec as-is written by people who doesn't have any technical architectural experience and convert as-is into technical spec? I have been in the industry for over 15yrs, never have i seen a technical architectural design dictated by non-technical person. May be i am old school and it is quite common these days. Suggestion well taken. Thanks!
|
|
|
|
|
|
| Re: OLTP & OLAP Desgin question [message #626263 is a reply to message #626260] |
Thu, 23 October 2014 23:07 |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
|
For OLAP, use the typical star schema. OLTP, use the relational model and it needs to be at least normalized to 3NF. Coming to your question, having all this in the same schema or two different schemas? Which one do YOU think would be easy to maintain?
|
|
|
|
| Re: OLTP & OLAP Desgin question [message #626266 is a reply to message #626263] |
Fri, 24 October 2014 01:08 |
ravikanth_b
Messages: 42
Registered: November 2007
Location: Bay Area, CA
|
Member |
|
|
Two issues:
1. Combine everything into once database/schema
2. Running OLAP/reporting on OLTP system
Apart from fundamental principles of software engineering best practices like
functional, logical and physical separation which is advocated by many experts like
Kimbal and Inmon, following are few considerations we could debate on.
Plan cache issues:
When you submit a query for execution the database engine will check to see if a plan already exists in memory. If it does, great! This saves time as the query optimizer will not need to create a new plan. The area of memory that contains the query plans is named the plan cache.
In OLAP, ad-hoc queries tends to occupy plan cache affecting OLTP queries
based on LRU algorithm.
==============================================================================
Quote from a SQL Server DBA:
"Here's the problem: OLTP is not the same as OLAP. When end users try to use an
system designed for OLTP as an OLAP system it leads to performance issues.
For SQL Server this is usually where locking and blocking rear their heads.
It was not uncommon for me to see contention between users that wanted to
insert data and users that wanted to generate reports. I had an alert built
just to notify me when a session was blocked for more than five minutes.
Can you imagine waiting that long and consider it to be part of your "normal"
processing? I can. I see it frequently when OLTP systems are being used for OLAP purposes.
What you want to do here would be to build a reporting solution for those OLAP users"
===================================================================================
Tuning an OLTP database is quite different than tuning an OLAP database.
For example, OLAP databases can benefit from many indexes, but too many indexes
in an OLTP database can hurt data modification activity.
If we are using OLTP to run OLAP queries, i am sure we would want to create more indexes
to support ad-hoc queries. This is where we get into endless spiral trying to do a balancing act between the two.
===================================================================================
OLAP type queries tend to return large amounts of data, which often lock tables, which decreases concurrency, which hurts performance. So if a user wants to return
a million rows in an OLTP database, there may be many very unhappy users.
"Ideally" they should be logically/physically separate.
===================================================================================
OLTP is desingned to get small amount of data, quickly which has large CPU requirements.
OLAP is desingned to get process and get large amount of data (throughput) has large memeory requirements.
OLAP queries tends to kick out OLTP queries from cached memory/SGA based on LRU algorithm.
Storage requirements are different for each of these systems.
===================================================================================
Parallel executions are not as good for OLTP, where as good for OLAP.
We do not have luxury of setting these parameters globally. We have set parallelism query by query.
===================================================================================
|
|
|
|
| Re: OLTP & OLAP Desgin question [message #626268 is a reply to message #626266] |
Fri, 24 October 2014 01:46 |
John Watson
Messages: 8962
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
All those statements above apply equally well to one schema or two. I think you are confusing "schema" with "database". People who work with SQL Server often do that.
They are also typical SQL Server problems, not Oracle problems.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: OLTP & OLAP Desgin question [message #634858 is a reply to message #626315] |
Mon, 16 March 2015 19:43 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>All in single schema. Facing hell lot of problems as I warned. concurrency issues, locking issues, data inconsistencies, security issues and much more.
Any concurrency issues, locking issues, data inconsistency issues or security issues are NOT the direct result of having a single schema.
Concurrency & locking issues result when more than 1 session is competing for same object at the same time;
regardless of the number of existing schemas.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
