BI architecture advice

Question

I would like to get some advice on our BI architecture, which is pretty complex to maintain.

I work in a e-shopping company, our prod works on a LAMP system (Linux Apache MySQL PHP).

Every night:

• data from our prod DB (MySQL) are extracted with Talend then inserted in another MySQL database named DWH for data warehouse
• data from this DWH are then extracted by Jedox/Palo to load OLAP cubes, which are used by Excel with a plugin for restitution
• data from this DWH are also accessed by Access/Excel one-shot reports, but this is not working very fine

Each time we need to modify an existing workflow or create a new report, there is a lot of steps and different technologies to use, which leads us to a pretty complicated platform.

What can I do to simplify this process?

Answer 1

You should be able to load the Palo OLAP tables with Talend as part of the data warehousing load process using the Palo components provided which should cut down on doing ETL stuff in a separate way to your main ETL process.

Roberto Machetto's blog has some interesting tutorials on how to do this and I'm sure there's plenty more examples across the internet and of course you could ask here for any specific issues you're having.

Once all of your data is properly loaded into the data warehouse and any OLAP cubes then your users should be able to run any bespoke or otherwise queries against the data as it's stored. If you're seeing instances where users don't have access to the proper data for their analysis then that should be resolved in the initial data warehouse/OLAP cube load by proper understanding of dimensional modelling.