Pulling WP Data from many sites to one data warehouse

  • Peter Nolan

    (@joschuaboehm)


    8 years, 11 months ago

    Hi All,
    I do data warehousing and Business Intelligence as my profession.

    I have started some WP stores and blogs etc in preference to dot net nuke. I still have quite a few DNN sites.

    What I am interested in is this. I want to build a cloud service for data warehousing WP data from client sites. What this will allow our client to do is as follows:

    1. Connect 1 or more wordpress sites to our service.

    2. Extract, clean, integrate, and store all their wordpress from separate sites into one data warehouse. They will have their own database or share a database as options.

    3. Add additional data to the data warehouse to be integrated to their wordpress data.

    4. Sophisticated BI will be placed over the top so allow greater insight in to the performance of their business or businesses.

    This will be particularly aimed at people who are selling products or services.

    We already have very sophisticated BI data models that have been used in many companies all around the world. We are looking at bringing such models and such capabilities to “the masses” who use such things as woo commerce etc.

    The only bit we really do not have a handle on is connecting to the wordpress databases and pulling the data out and transforming it to a tablular format that makes sense inside a data warehouse.

    We have a C++ ETL engine and we have an ODBC class that connects to mysql. It does have the 32K chunk limit for ODBC for data which will not be a problem in most cases. Just very long posts.

    But taking a look inside the mysql databases for wordpress the data is stored in posts that are interpreted by WP at the time of execution. The posts have post types and then the data must be interpreted by post type.

    I was wondering if anyone has undertaken a project to move the wordpress data to another database and unpacking it by post type on the way.

    Failing that I was wondering if anyone has any pointers or tips for us on the best way to approach getting data out of the WP mysql database and across to a mysql staging area including unpacking the variable data in the various post types along the way.

    I am thinking I am not the first person to think it would be a good idea to extract all the data from WP and transform it so that it is queryable in a database.

    Thank you in advance for any assistance you may be able to afford us.

    Best Regards

    Joschua

Viewing 4 replies - 1 through 4 (of 4 total)
  • RossMitchell

    (@rossmitchell)

    8 years, 11 months ago

    It is possible for different WordPress sites to share a common database, they differentiate themselves by the prefix on the table names.

    I suggest that rather than reworking WordPress to work in a different database environment, that you would do better to develop plugins that do the analysis you need upon the tables as used by the WordPress sites. No data being duplicated, no redevelopment of code that already works.

    I am thinking I am not the first person to think it would be a good idea to extract all the data from WP and transform it so that it is queryable in a database.

    I will let you in upon a secret.
    The WordPress data is already in a database, plugins and templates have full access.

    headonfire

    (@headonfire)

    8 years, 11 months ago

    Have a look at WP REST API. See here and here for details.

    Thread Starter Peter Nolan

    (@joschuaboehm)

    8 years, 9 months ago

    Hi Ross,
    “I will let you in upon a secret.
    The WordPress data is already in a database, plugins and templates have full access.”

    What we are proposing to do is to build a data warehouse where people who have wordpress can subscribe to our data warehouse service and send their wordpress data into the DW in order to be able to more easily query their data.

    Also, most companies that use wordpress have many other computer systems and our service will enable them to integrate their WP data with the data from these other systems.

    The way in which WP stores its data does not lend itself to analytics very well…especially not integration to other data and analystics with that other data.

    Best Regards

    Joschua

    Thread Starter Peter Nolan

    (@joschuaboehm)

    8 years, 9 months ago

    Hi Ihor…thanks for the pointers…much appreciated.

    Best Regards

    Joschua

Viewing 4 replies - 1 through 4 (of 4 total)
  • The topic ‘Pulling WP Data from many sites to one data warehouse’ is closed to new replies.