Data Storage advice

[Ramp85]

Hi PowerBI members

I would like to hear your best practice tips for data storage.

Currently we are recieving data from our sales system through snapshots sent to a SQL Server. 8 times daily. 

Thats gives us always newest data - but only the newest.

Some of the data we would like store, so we can track historical patterns. This is not provided in the data feed from our sales system, and not a possibility to develop either. Currently it is done manual via Excel, but far from optimal. And the data size is getting to large.

What would you recommend as best practice for this issue?. Is it possible to store the tables directly at the SQL Server, or in PowerBI?.

I'm not asking on the specific method and how to implement, but more your advice to lead me in the right direction.

[v-rongtiep-msft · Accepted solution]

Hi @Ramp85 ,

Could you please try to use R on Power BI Desktop. An R transformation could snap the data and save as a file, you can then load a folder of files and use the filename to to ensure you know when it was snapped.

You can use R in Power BI Desktop's Power Query Editor to:

• Prepare data models.

• Create reports.

• Do data cleansing, advanced data shaping, and dataset analytics, which include missing data completion, predictions, clustering, and more.

Limitations:

• All R data source settings must be set to Public.

• To schedule refresh of your R visuals or dataset, enable scheduled refresh and install an on-premises data gateway (personal mode) on the computer containing the workbook and R.

More details: Use R in Power Query Editor 

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ramp85]

Sounds like the way forward - thanks a lot 🙂

[Ramp85]

Hi Amit

Yes, thats what we do now. I need the save and load procedure to be automatic and able to handle +1.000.000 rows.

[amitchandak]

@Ramp85 , Means you have more than 1 M in excel. Then better to use a database.

[Ramp85]

Exactly, but is it possible to "copy" and store directly in SQL Server?

[amitchandak]

@Ramp85 , You can load historical data into dataflow. There is a new option to upload excel(use one drive)  and you can use it from there in your file