cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jeanxyz
Helper IV
Helper IV

how to create a snapshot dataset in power bi

Hi,

 

I have a table of employee informaiton stored in power bi. The source data is from SAPbyDesign, we export employee data from SAP to Excel and Power BI takes the data from Excel.

 

However, there is a new issue here. My users asks me to export employee data from SAP on monthly bases and track employee's salary change month over month. I realize that manual exporting data from SAP to Excel is not a good solution here.

 

However can I create an automatic snapshot of employee data on monthly basis and import them to Power BI automatically? I heard Cdata can create snapshot files from SAP and store them in SQL server, PB can then import data from SQL server. Has anyone done this before? Any tutorial or tips will be helpful.

@Greg_Deckler,

@amitchandak 

@Fowmy 

@MFelix 

 

8 REPLIES 8
v-xulin-mstf
Community Support
Community Support

Hi @Jeanxyz,

 

You can store the monthly data in a local folder or SharePoints and then take the approach I provided in the post below to the point where the monthly data is automatically imported into Power BI.
https://community.powerbi.com/t5/Desktop/How-to-pull-the-data-to-Power-BI-from-monthly-excels-placed...

If you still have some question, please don't hesitate to let me known.‌‌

 

Best Regards,

Link

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

I'm looking for a solution the other way around. My pbix dataset is connect to CRM and refreshed on daily basis via a gateway. So I need to pull out data from pbix to Excel and store it on sharepoint on monthly basis.I look for a flow which will do this for me automatically.

Greg_Deckler
Super User
Super User

@Jeanxyz Not that exact scenario but if you do end up going the export to Excel route, put all of the files into the same directory and use a Folder query to append binaries. Your other approach is likely better but I don't have much experience with the CData connector for SAP.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

as an alternative, is it possible to schedule export data from Power BI via Flow. instead of importing data from Power BI, I can import data directly from Dynamics 365. Is it possible to schedule a flow which will export data from power bi on monthly basis?

That should solve the problem. however, I only find a Flow which is triggered manually by a button in power bi. Is ther a way to schedule a flow which will automatically export from pbix?

You could do it with a Flow to send the data but that is tricky (but can be done with an HTTP web call to send the data as JSON to a Flow, parse it, create a file and store it on SharePoint for example).  If available to you, a simpler approach would be to publish your report that gets the latest SAP data, schedule auto refresh, build a simple paginated report just for data export, Subscribe to that report, and use a simpler Flow that is triggered off getting that file and storing it on SharePoint (and then trigger refresh of your other PBI report that combines all the files).

 

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


I did some research, I think your approach might work. I also found a post which list the steps to create the flow in more detail (https://www.thebiccountant.com/2019/04/01/export-data-from-power-bi-using-microsoft-flow/).

However, when I tried to duplicate the steps on my side. I received two errors,

1st error, when I tried to create an export query in my Power BI, there is an error about firewall.As a result, the output of the export query is empty.

export query.PNG

 

export query.PNG

 2nd error, when I test the flow, there is an error message on the compose step. I am not sure how the author input the Json() function in the flow. As I can see in his flow, there is a callout idon in the flow, I don't have this icon in my flow.

 

flow error.PNG

 

Thanks in advance for the help!

Hi @Jeanxyz,

 

Please check the following steps:

vxulinmstf_0-1630378951015.png

Add a body before you input the json function.

vxulinmstf_1-1630379039641.png

 

If you still have some question, please don't hesitate to let me known.‌‌

 

Best Regards,

Link

 

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

 

If I understand correctly the Flow would only save the png or pdf file in sharepoint. I need to export power bi dataset in excel or csv based on my schedule. Is that possible with Flow? Can you share a demo or post if this is possible?

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Top Solution Authors