Excel has a limitation that is making the process not as smooth as I hoped. Some tables in the database I'm working on have over 20 million rows of data. This is beyond what Excel can handle. So for me to build an automated reporting dashboard, I need to reprocess a lot of the data, join tables, find patterns and ensure there is no left out metric. And to do this I need to run an elaborate analysis on all the tables, individually and collectively. Excel becomes unsuitable for this. And so is Access. Access has a size limitation too and can be unbearably slow. Luckily, the data is stored in a MySQL database server and MySQL has many tools to work with in running analytical SQL queries. I have used Sequel Pro on Mac and find it very cool but I enjoy working on Windows, so I decided to look for a great Windows based MySQL tool. And after many trials I found a perfect tool: MySQL Workbench.
MySQL Workbench is almost an IDE. It comes with many database management tools that you will enjoy working with it. It's got this programming-like environment that makes doing serious work enjoyable. You almost feel like it has a tool for everything you want to do. I also tried Heidi but I found MySQL Workbench preferable.
So now I use the MySQL Workbench to do some pre-processing of the data and then export to Excel to do final analysis. Once I am done with the design of the data analysis model and made a final (acceptable) dashboard, I will now begin to work on porting the SQL commands for the pre-processing to Excel and automating the subsequent analysis and dashboard. Then I will work with a web developer to replicate the same dashboard on the web, making it realtime, queryable and accessible on any internet connected computers, tablets and smartphones.
It's a very exciting project and I can't wait to get it done. It will be expanding my knowledge of SQL data querying from Excel and online dashboards. I will be sure to update you all on how it goes and share any exciting discovery I make.