Moving from normal web application to a DW/BI purpose app

View previous topic View next topic Go down

Moving from normal web application to a DW/BI purpose app

Post  KimStacks on Sun Feb 01, 2015 11:03 pm

I have built a typical web application using PHP and MySQL for enterprise usage.

My client is the country business unit of a telecommunications MNC.

The MySQL database is 3NF as much as possible.

However, as time goes by, the requests for the data appear to be more for a data warehouse so that they can slice and dice the data in whatever way they want.

Hence I picked up a book the 3rd Edition Data Warehouse Toolkit of the Kimball Group.

Inside the authors described a Kimball DW/BI Architecture and using star schema for dimensional and fact tables for DW/BI purposes. See below.



My question is, since I am most familiar with PHP and MySQL, should I have the data warehouse star schema in a separate database from the original 3NF database?

No commercial BI tool will be used. Anything that looks like an OLAP or BI would likely be an open source software that I may augment myself.

Please advise.


KimStacks

Posts : 13
Join date : 2015-02-01

View user profile

Back to top Go down

Re: Moving from normal web application to a DW/BI purpose app

Post  ngalemmo on Mon Feb 02, 2015 12:39 am

Yes, the DW should be hosted on a separate database server.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Moving from normal web application to a DW/BI purpose app

Post  KimStacks on Mon Feb 02, 2015 12:58 am

Thank you ngalemmo.

My follow up question is, in that case, the 3NF database is the "source transactions" in the diagram?

KimStacks

Posts : 13
Join date : 2015-02-01

View user profile

Back to top Go down

Re: Moving from normal web application to a DW/BI purpose app

Post  ngalemmo on Mon Feb 02, 2015 5:54 am

Yes.

You should plan for at least dev/QA/Production environments. Dev should be on its own machine. Sometimes QA and Production co-exist on the same hardware. They could be separated at a later time.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Moving from normal web application to a DW/BI purpose app

Post  ron.dunn on Mon Feb 02, 2015 10:19 pm

Let me add two good reasons why you should separate the DW to a database, AND a new server - IO and Memory.

Your DW queries are largely scan oriented, running sequentially over large sets of data. That needs very different storage characteristics to an OLTP application, which will be randomly reading and writing 1-smallNumber rows at a time.

The second is memory. The working set for a data warehouse query can grow quite large. You may find that this has a negative effect on other database operations, as items get pushed out of the cache to make room for the DW query.

So ... get a new server ... give it some dedicated disk ... and you'll be in performance heaven

ron.dunn

Posts : 55
Join date : 2015-01-06
Location : Australia

View user profile http://ajilius.com

Back to top Go down

Re: Moving from normal web application to a DW/BI purpose app

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

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