December 14, 2009 at 2:39 am
Hi,
I'm in the process of scoping a project for a system that gathers information on companies, the area they cover and the service they provide. There will be approximately 2-3000 companiesproviding information on approximately 100 different services. The database I intend to design will have a table for company including a tickbox for each of approx 6 geographical locations, a table for services and a link table containing company and service provided. The type of queries run on the system will be usually one of the following:
Part fo the Company Name
All Companies within a Geographical Area
All Companies offering a particular Service
A combination of the above
Now, I have been asked to ensure the Cubes/ OLAP/ RELAP (I presume that this should be ROLAP) are set up correctly. I had no intention of setting up an OLAP database for this because I know that there is no need because the data and queries are too simple and will run very quickly. My problem is how do I explain this to someone who thinks they know all about OLAP: are there guidelines as to when it should be employed etc?
Also, I have never used OLAP so I don't understand the consequences of setting it up e.g. added overhead for maintenance/ filling/ should it run on a separate server etc?
Any guidance greatly appreciated.
January 1, 2010 at 6:49 am
The thing here is that you have two rough options. I say rough because as technology get better and better, servers get faster etc the area between the two gets mistier.
So you have OLAP (Online Analytical Processing and OLTP (Online Transaction Processing), and you say you have no intention of setting up an OLAP because you know there is no need. The main difference between the two really has to do with transaction throughput OLTP supporting more where as OLAP reports quicker due to less joins etc.
To be honest, with your description I would do the same thing and go OLTP because it will be easier to manage and grow if your boss really has an OLAP obsession then just give him some views or if it gets big enough set up a data warehouse (DW) as a OLAP DB.
I have just finished doing a review on one of our DB’s and we have gone with the DW option and setup an agent job which cleans the OLTP tables out and moves them into more of an OLAP design (flatter) and puts them in a DW with other data. This allows us to keep the working data at a manageable level but not loose any of the analytical capability.
All I have done then is changed the stored procs which create the datasets I render to screen to pull data from both DB’s and cache it. I am also playing with views to see what will work best. So far I have gained significant performance with this approach not to mention the other benefits of smaller backups and more manageable data sources.
I hope that helps.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply