3

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

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.

enter image description here

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.

EDIT

My entire 3NF MySQL database is currently about 20mb so far growing about 1 mb every month or so.

Improve this question
2
  • 1
    How much data do you have? Personally, for a classic (Open Source) db, I would recommend PostgreSQL (CTEs, Windowing functions, CHECK constraints), but if your expertise is MySQL, then YMMV. MySQL is good for read heavy OLTP apps, but PostgreSQL really starts to shine for BI with its extra functionality.
    – Vérace
    Feb 1, 2015 at 15:28
  • Could you elaborate how PostgreSQL starts to shine for BI? My data currently is not huge. Exporting my entire 3NF MySQL database is currently about 20mb so far growing about 1 mb every month or so. I don't mind switching to PostgreSQL if it makes sense for the long term.
    – Kim Stacks
    Feb 2, 2015 at 3:32

2 Answers 2

Reset to default
4

In response to your reply to my comment, I mentioned some of the areas in which PostgreSQL shines for DW work - notably CTEs (Common Table Expressions - AKA the WITH clause) and Window functions (AKA the OVER clause).

Without these in MySQL, you'll end up writing your own hacks to emulate these (and, no disrespect, but it'll take a while for your code to be bug free), whereas with PostgreSQL you get them out of the box. PostgreSQL also has superior GIS support (should you require it) and better JSON - only experimental in MySQL.

Take a look here

PostgreSQL has traditionally focused on reliability, data-integrity and integrated developer-focused features. It has an extremely sophisticated query planner, which is capable of joining relatively large numbers of tables efficiently.

MySQL, on the other hand, has traditionally focused on read-mostly web apps, usually written in PHP, where the principal concern is with optimising simple queries.

DW type apps need far more of the former than the latter.

Also, check this out - a very recent comparion of the two systems. My take on this is that PostgreSQL is the better of the two, esp. for DW work.

You could peruse these pages for PostgreSQL and MySQL features. There is a certain "religious war" element here, and I'm mindful of the fact that you already have MySQL experience which may be a clincher for you. I wish you all the best with your DW project.

[EDIT in response to OP's comment]

I would certainly urge you to have separate databases for your OLTP and your OLAP work - if that is within your budget. OLTP and OLAP are fundamentally different and if you mix them you'll have many conflicts - I know the pain caused by "managers" insisting on running reports against a live system during the day - so if I were you, I would have MySQL as my OLTP system and PostgreSQL as my OLAP.

But whatever configuration you choose, I strongly suggest separate servers.

If I were forced to choose one database and one only, it would be PostgreSQL without hesitation. MySQL was essentially lucky and in the right place at the right time with the right software during the first internet boom. IMHO, PostgreSQL is undoubtedly technically superior.

I bear MySQL no ill-will - I have used it a lot (clients!) and find that if you can live with or workaround its quirks, it can be quite performant and has some interesting features - notably the storage engine choices one can make (which one can't with PostgreSQL).

In the DW sphere for MySQL there are two interesting developments which may bode well for it in this area in the future - these are columnar store engines - Infinidb and Infobright. The problem at the moment is that Infinidb appears to have died - even though the code is still available, the installation guide has disappeared and ICE (Infobright Community Edition) is essentially crippleware. You may wish to evaluate the Entreprise edition.

I urge you to set up a few test cases - imagine what sort of reports you will be running and try them on both systems. Think especially about the complex reports required for OLAP systems - I believe that you will find the far richer PostgreSQL SQL language to be a persuasive argument in its favour.

Improve this answer
4
  • However, your answer does not answer my question directly. It does do a good job telling me why I should favor PostgreSQL for DW/BI purposes. So should I separate? Are you familiar with Kimball methodology of DW/BI?
    – Kim Stacks
    Feb 2, 2015 at 4:29
  • 1
    Thank you for the detailed reply. I learned a lot of things from this. :)
    – Kim Stacks
    Feb 2, 2015 at 5:26
  • 1 more question. So according to the above diagram in my question, does OLTP database count as "source transactions"?
    – Kim Stacks
    Feb 2, 2015 at 5:56
  • Getting your answer has prompted me to ask another question. dba.stackexchange.com/questions/90768/… If you have a good idea, I would appreciate it.
    – Kim Stacks
    Feb 2, 2015 at 6:36
3

While not focusing on a specific RDBMS, to the question of:

should I have the data warehouse star schema in a separate database from the original 3NF database

I would say: absofrickenlutely YES!!

OLTP (what you are calling "the original 3NF database") and OLAP are very different ways of looking at data and there are usually very different expectations of the two different systems. Hence there are different needs which leads to different configurations, etc.

And, if interpreting your question strictly with regards to the term "database" such that you could be meaning having two separate databases on the same server, one for OLTP and one for OLAP, I would clarify my answer to say that you truly need separate servers:

  • You should have the ability to take one of the systems down for maintenance without affecting the other.
  • You might need to grow the OLTP side in terms of nodes, at some point, but for the OLAP side you might instead want to grow that server with more memory, disk, etc.
  • You might end up aggregating several different OLTP systems into a single OLAP system
  • OLTP = OnLine Transactional Processing
    These are the "Source Transactions" / "operational source system"
  • OLAP = OnLine Analytical Processing
    This is the "Presentation Area" within "Front Room" / "data presentation area"
Improve this answer
5
  • I want to thank you. I did not know the term OLTP existed in the first place. Right now, my client only has 1 physical server. Hence, is it wise to have both OLTP and OLAP databases in the same server including the web application coded in PHP?
    – Kim Stacks
    Feb 2, 2015 at 5:25
  • 1 more question. So according to the above diagram in my question, does OLTP database count as "source transactions"?
    – Kim Stacks
    Feb 2, 2015 at 5:56
  • 1
    @KimStacks I just added some definitions at the bottom of my answer. But yes, OLTP = Transactional side. And if there is only one physical server, then at the very least these need to be two separate databases. That way you can later migrate one of them to a new server. Ideally they should be two servers. It is most definitely not wise to have both on the same server in Production. And not wise to have either of them on the App server (unless this is a very small system or a personal/non-business project).
    – Solomon Rutzky
    Feb 2, 2015 at 6:05
  • 1
    According to the Kimball architecture, I need a ETL system between the source transaction and the star schema. So let say I accept that my source transaction will be the 3NF database in MySQL and that OLAP database will be star schema inside PostgreSQL, what do you recommend for the ETL that straddles these 2 databases? and how often should the ETL process happen? Daily? Every hour?
    – Kim Stacks
    Feb 2, 2015 at 6:15
  • Getting your answer has prompted me to ask another question. dba.stackexchange.com/questions/90768/… If you have a good idea, I would appreciate it.
    – Kim Stacks
    Feb 2, 2015 at 6:36

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.