Mastering SQL+Data analytics with Excel

Few weeks back, a SAP specialist and a professional data scientist friend of mine commented: “All the BIG Data in SAP has no value without Excel. Nothing can beat Excel when it comes to data analytics”. This triggered my curiosity to dig deeper and i started playing around with Pivot Tables. I was astonished to realize, how much was i struggling all these days to build SQL queries and generate reports by ignoring this powerful tool. Yes!!. Excel can generate complex SQL queries which you can just copy and paste on your db workbench!!.

Today many decision makers are showing a lot of interest in Business Intelligence, Machine learning and Artificial Intelligence. All these tools need data. The SCADA control room database has got a huge amount of data accumulated over a period of many years, lying unused. There’s an elephant in the control room. Time to dig deeper and make some money :wink:

Let me share whatever i found interesting. You also please share your ideas, tips and tricks. Cheers.


Retrieve Data From MySQL Database Using MS Query

Caution: When you practice and play with these tools, don’t do it on the production database. Export the db to your test db and practice.

2 Likes

Nice Explanation On MySQL Database. Thank for sharing!

To master Excel data analytics, we must familiarize ourselves with 3 tools:

  1. Pivot Table (mandatory).
  2. Power Query + Power Pivot
  3. MS Query

1. Pivot Table quick tutorial:

2. Pivot Table step by step:

3. Excel 2013 PowerPivot Basics

4. Excel 2016 Power Query + Power Pivot:

To design a database or to build queries, understanding ER is very important. Watch on and dig deeper :slight_smile:

SQLeo Visual Query Builder
https://sourceforge.net/projects/sqleo/files/

You can visually build and generate sql code for many databases. Very good.

1 Like

@R.Alamsha excellent post, congratulations for sharing these topics

Regards GeraGarcia