Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

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

3 posters

Go down

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

Post  KimStacks 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.

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

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

Back to top Go down

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

Post  ngalemmo Mon Feb 02, 2015 12:39 am

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

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

http://aginity.com

Back to top Go down

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

Post  KimStacks 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

Back to top Go down

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

Post  ngalemmo 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.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

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

Post  ron.dunn 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

http://ajilius.com

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top

- Similar topics

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