@hamzaa First, if you haven't seen this page, and the accompanying white paper that you can download, I would highly recommend you read through it as it describes all this.
But - on to your questions:
Correct me if I am wrong but I understand that if I import data from excel\csv file into Power BI Desktop and publish it to Power BI Service the data from the file is stored on Azure.
This is correct.
I read on another post that in case of a DirectQuery, which is basically a live connection, no data is stored in the cloud, just some caching. But If I have a on-premise gateway connection and don't want that to be a live connection, then how much of the data from the database is stored in the cloud?
First, if you are referencing SQL Azure DB, then you don't need a gateway. If it is on-premises, then you do. In both these cases, if you change the connection type to "import" - then the data will be stored in the Power BI Azure backend.
Also if I connect to an excel file that is on our company's sharepoint, would that be considered a live connection? is the data from that file stored in the cloud or just sharepoint?
The easiest way to answer this is with describing the connection types. Import - all data sources. Direct Query - SQL db's where the data stays in the database. Live Connection - refers to live connections to SSAS Tabular or Multidimensional. Your scenerio would fall under "import", so the data would be stored in the cloud. (Power BI Azure backend)
