how to create a snapshot dataset in power bi

[Jeanxyz]

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 

[v-xulin-mstf]

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!

[Jeanxyz]

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]

@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.

[Jeanxyz]

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?

[mahoneypat]

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

[Jeanxyz]

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.

 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.

Thanks in advance for the help!

[v-xulin-mstf]

Hi @Jeanxyz,

Please check the following steps:

Add a body before you input the json function.

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!

[Jeanxyz]

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?