Moving from normal web application to a DW/BI purpose app
3 posters
Page 1 of 1
Moving from normal web application to a DW/BI purpose app
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.
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
Re: Moving from normal web application to a DW/BI purpose app
Yes, the DW should be hosted on a separate database server.
Re: Moving from normal web application to a DW/BI purpose app
Thank you ngalemmo.
My follow up question is, in that case, the 3NF database is the "source transactions" in the diagram?
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
Re: Moving from normal web application to a DW/BI purpose app
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.
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.
Re: Moving from normal web application to a DW/BI purpose app
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
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
Similar topics
» Purpose of Hierarchies in a Dimension
» Date Dimension with multiple keys at different levels, is that normal?
» Natural Key in the fact table for ETL purpose ?
» Naming conventions for fact and dimension table
» Operational Reporting : DWH vs Legacy Application on Mainframe
» Date Dimension with multiple keys at different levels, is that normal?
» Natural Key in the fact table for ETL purpose ?
» Naming conventions for fact and dimension table
» Operational Reporting : DWH vs Legacy Application on Mainframe
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum