Which Data Import Method to Use in this Scenario ?

[happy1001]

Hello

I have got a few Excel Files in which I need to import the data from a few different queries from a database in SQL Server installed on my local machine. Then I intend to use this imported data inside Power Pivot Data Model.

I am not sure which particular data import method should be used, which will provide me the following flexibility -

1. Ability to change / modify the SOURCE data query which resides inside SQL Server. I will have to add or remove columns from that query based on the testing work that I am doing. The New Columns that I add to that SQL query in the future, should be able to get imported inside Excel.

2. Auto Refresh - I need to auto refresh the data that I will getting inside excel. Because the data is such that it will keep on getting modified everyday within the SQL Server Database and I need to have the LATEST Data inside Excel.

I have tried to import the data from within Power Pivot Data Model, but it does not import the latest data columns that I add within the SQL Server later on.

I am interested in using the Power Query based data import method, but I am not sure if that will help me in the above 2 requirements.

Please share any possible solutions for data import, which would satisfy the above conditions.

And if there are any links available about the BEST PRACTICES for creating such Excel and SQL Server type of Data Connections, then please share those links as well.

I am using the following software versions -
Microsoft SQL Server Management Studio version- 12.0.2000.8,
Microsoft Office Professional Plus 2016 x64
and Windows 7 x64

Thanks a lot for any help.
Regards

PS: Please do not suggest that method in which there is a risk for the Data Model to get broken because of changes in the SQL Server data source query. Because then I loose all the work that I have done within the Data Model and I have to recreate everything from start.

 

[Hui]

Have you tried using the Data, From Other Sources, From SQL Server menu?

You will have to follow through the prompts but using this also allows the connection to automatically refresh at certain intervals eg: every hour or day etc

 

[happy1001]

Have you tried using the Data, From Other Sources, From SQL Server menu? You will have to follow through the prompts but using this also allows the connection to automatically refresh at certain intervals eg: every hour or day etc

Thanks for your reply Hui. But I am sorry, I do not get it.

You seem to be telling about options available in SQL Server Menu, but I have to import the data from within the Excel File, by using a method, which fulfills the above 2 requirements. I might have misunderstood your reply, so I am not sure about it.

Regards

 

[Phattails]

Power Query is designed to push queries to the SQL server message through a process called Query folding in order to reduce the burden on Excel. From my understanding, you cannot alter the columns at the source of the query through Power Query (not sure if I'm understanding the first part of your question). It merely loads an instance of the data and allows you to Query it without touching the source, from there you can load it into the data model (into Powerpivot).

You can set it to auto refresh upon loading the excel document or to refresh on a timed basis. You could also use VBA to optimize the refresh. PQ's very easy to use and it can't hurt to try it and find out if it works for you.

 

[happy1001]

Power Query is designed to push queries to the SQL server message through a process called Query folding in order to reduce the burden on Excel. From my understanding, you cannot alter the columns at the source of the query through Power Query (not sure if I'm understanding the first part of your question). It merely loads an instance of the data and allows you to Query it without touching the source, from there you can load it into the data model (into Powerpivot).

You can set it to auto refresh upon loading the excel document or to refresh on a timed basis. You could also use VBA to optimize the refresh. PQ's very easy to use and it can't hurt to try it and find out if it works for you.

Thank you for your response Phattails. I appreciate it.

I am doing different experiments now, to see which one works well for me. I am really amazed by the features available in PQ. Microsoft has done a real good job.

Thanks and regards

 

[Phattails]

Thank you for your response Phattails. I appreciate it.

I am doing different experiments now, to see which one works well for me. I am really amazed by the features available in PQ. Microsoft has done a real good job.

Thanks and regards

No Problem. Try to break down your queries into Staging and Loading Queries. Perform as many filtering and grouping operations on your staging queries (the initial connection to the server). As soon as you reference a query, the folding stops and PQ has to finish the work. Use the loading queries to load the data into the data model. Another reason to do this is to avoid "formula firewall" errors. Other best practices would be to turn of the default, "Load to Table" and keep the query as a connection only.

Good luck,

 

[happy1001]

Thanks a ton Phattails. Very Valuable Points. You seem to have real good experience in this.

With my Best Regards

 

[happy1001]

Phattails, since all these concepts are completely new to me, therefore I was doing a bit searching to understand more about the concept of having staging and loading queries. And I stumbled upon this link, which has good information and also a flowchart of the concept, which is easy to understand for newbies like me -

http://www.excelguru.ca/blog/2015/03/11/power-query-errors-please-rebuild-this-data-combination/

And in the same article he mentions about another concept of using a PARAMETER -

http://www.excelguru.ca/blog/2014/11/26/building-a-parameter-table-for-power-query/

http://businessintelligist.com/2015...meterize-connection-variables-in-power-query/

This Parameter concept also looks very important to me and I think that it could save me from a lot of trouble in the future, if I am able to use it wisely. Although I must admit that all this is looking Very Complicated to me at this moment and I am not sure, if I will be able to Practically Implement them in my own work or not "because of lack of my programming skills". But you have given me real good ideas and I am very grateful to you for that.

Thank you so much for your help.

With my Best Regards

PS: The concept of Query Folding is also totally new to me. I had no idea about it earlier. I have found the following info regarding it so far -

Power Query rolls up it's own SQL statement and sends it to the database.
With regards to the staging query, they will still offer the benefits of Query Folding, as they are pulling from the database. Any queries that refer to the staging queries, however, will not.

So the majority of your filtering should be done in the staging query, before you get into serious reshaping at the next stage.

As soon as your first line of non-ui M code enters the equation, query folding stops. That's a big downside if you're filtering tables.