Loading SQL Server data into Power BI

[new_user]

Hello,

I am new to PowerBI and have no programming skill. However, I am eager to learn as much as possible. 

I am importing data historical sales data from last 2 years with daily update from SQL Server into PowerBI. I am unable to finish importing the data because it always took a very long time to pull those rows into PowerBI. My current workaround is to only look at data for the last three month and always refreshing each day to include new data. In order to update daily sales this way, would mean refreshing 2 years worth of data each days.

I have looked at incremental refresh solution with custom filter of rangeStart and rangeEnd but it doesn't work. I think incremental refresh is for data that has already been imported into PowerBI Desktop and my problem is importing those datas. Is there a better way to do this? Looking forward to any advices. 

The SQL query I was given to use by a third party programmer is: 

EXEC PowerBI_SalesReturn '2021-9-1', '2021-12-29' - If I want to get the most current data I usually change the date with orange highlight
 

Thank you

[v-janeyg-msft]

@new_user  Any updates?

[new_user]

Hello @v-janeyg-msft 

Thank you for your advice. I tried using incremental refresh however when I did a right click refresh on the field view. It reloads all rows of the data. I am not sure what I am doing incorrectly. The incremental refresh that I use was I choose to refresh only 10 days and archive 5 year of data. Do you have any other suggestion?

This was the incremental refresh I used

Query used was EXEC PowerBI_SalesReturn '2021-1-1', '2022-6-30

Thank you for following up 

[v-janeyg-msft]

@new_user 

It looks like you have deployed it. If you read the document a few times, you can learn more about the usage of incremental refresh. I didn’t describe it completely before.

Incremental refresh is to deploy the policy in the desktop and refresh in the service. So, it's useless to refresh in the desktop. Since incremental refresh belongs to the processing of big data, the report needs to be refreshed in the premium workspace.

In addition to deploy policy, public and refresh and re-publish, there are some precautions. Please follow the two links I sent. 

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[v-janeyg-msft]

Hi, @new_user 

According to your description, I think incremental refresh is in line with your needs. You may have some misunderstandings. Using incremental refresh, you can refresh only the latest data after importing all data for the first time, and the previous data won't be refreshed when it is refreshed afterwards. 

Like this:

You can check the document to learn more about incremental refresh and how to use it:

Incremental refresh for datasets and real-time data in Power BI - Power BI | Microsoft Docs

Configure incremental refresh and real-time data for Power BI datasets - Power BI | Microsoft Docs

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[new_user]

Hi @v-janeyg-msft, 
Using incremental refresh, you can refresh only the latest data after importing all data for the first time, and the previous data won't be refreshed when it is refreshed afterwards. I am not entirely sure what you mean. I already imported the data for the first time,however, when I try to do incremental refresh by right clicking the option to refresh data, it refresh not only new datas but all of the datas.

I might not be explaining myself clearly. From my understanding there is two type of refresh, 1. Desktop/Query refresh to get new data from the SQL server and 2. Visual Refresh to reflect new datas to the visual model in mobile apps.

What I am having trouble with is to incrementally refresh data from SQL Server to PowerBI Desktop. For example:

I have two days worth of data from 01.01.2022-01.02.2022. At the start of 01.04.2022, I want to add the data of 01.03.2022 incrementally without refreshing rows of data from 01.01.2022-01.02.2022. Only data from 01.03.2022 is imported how do I do that? As you have seen I have tried using the incremental refresh but failed. What am I missing? Please help 

[v-janeyg-msft]

@new_user 

Hi, @Anonymous 

Incremental refresh will only take effect in the service, it is impossible in the desktop, otherwise there is no need to buy a premium license.

Second, if you only consider refreshing the date you want in desktop, it's not possible, either you only import the data for the date you want to refresh.

Here are some tips for disable refresh:

But it's all for a single query (single table) and only works in desktop.

Did I answer your question? Please mark my reply as solution. Thank you very much.

If not, please feel free to ask me.

Best Regards,

Community Support Team _ Janey

[amitchandak]

@new_user , Are you a premium user, then you can explore the deployment pipeline.

I have explained all steps here, how to load more data to test or prod

Deployment Pipeline, Load More Data on Test/Prod : https://youtu.be/l69cnWkoGX0