Figure 3-1 shows the flow of data.
To use data refresh, you need a way to pull data from the data source (SQL Server, in this example) and push it directly into the SSAS model generated by Power BI. In other words, you want to create a data flow that circumvents Excel and OneDrive (both operations are done outside of Power BI) to make it flow as shown in Figure 3-2, in which the steps that we want to remove appear in a blue box.
Here is what you need to do to make this happen:
• The data set cannot be a plain Excel table, because Power BI needs to know how to query the source database (SQL Server, in this example) in order to refresh the data. Obviously, it cannot rely on asking you or asking IT. The method must be formalized and use a language that Power BI can understand.
• The SSAS engine running in Power BI needs a way to access the source database. Such a database is usually located within your company (or on your laptop). Thus, you will need software that implements the connection with Power BI.