PowerBI - Use R to export data from dataset in Power BI Service

[Anonymous]

Hi,

I need to persist data from MS exchange to 5 years while currently, only 2 years data is being saved on the server.

This means I need to save all meta data pertaining to the reports in a DBMS system like SQL or in excel.

However, my question here is

1. Can I use R to export data from Power BI Service to SQL DB?

2. Which packages would be required to write to SQL DB?

I intend to refresh dataset everyday and on success, I want today's data to be exported from Power BI service to SQL DB.

If I have one tile on the report which shows only today's data/ modify dataset to contain only today's data, is it possible to export data whenever model is refreshed?

3. I m confortable creating a Windows Service/ SSIS Package to pull the data everyday.

Is it simpler to work with R Scripts? Is it even feasible?

4. Is it possible to use Power Update for scheduling daily refresh?

I need more information pertaining to its capabilities and costs.

Any help in thsi regard would be very much appreciated. 

What would be the recommended approach?

Thanks & Regards,

Mannu

[v-jiascu-msft]

Hi @Anonymous,

Why not retrieving data from MS exchange directly? I'm afraid these items are hard to achieve. Power BI collects many data together and brings out many wonderful insights. These requirements here are in the opposite direction. 

1. The item 4 is already in the Power BI Service. You can set the Scheduled Refresh in the Settings. 

2. Please refer to power-bi/developer/what-can-you-do.

3. If you want these features, please create an idea here.

Best Regards,

Dale

[Anonymous]

@v-jiascu-msft

Yes. We can get the data directly from MS exchange. But then how do we maintain 5 years data?
We only have 2 years data on MS exchange at any point of time.
This is the reason we need a separate data store to maintain the information.
Yes we can use scheduled data refresh.
But as PowerBI doesn't do incremental refresh, we will lose out data prior to 2 years with every refresh

[v-jiascu-msft]

Hi @Anonymous,

There could be another way to maintain 5 years or more data of exchange. But that won't be the duty of Power BI. 

1. Can you extend the capability of the Exchange?

2. Can you archive the old mails?

3. Incremental refresh could be a good idea. But the records that don't exist in the data source should be considered as invalid data. 

Best Regards,

Dale

[Anonymous]

Hi Dale @v-jiascu-msft

Thank you for looking into this problem.

At this point, I have a very broad set of requirements.

I will be looking into Global mailbox and it would be safe to assume that archival is not possible.

However I would like to know how it can help if archival is enabled.

By extending the capability of exchange, if you meant "increasing the duration of data stored on MS Exchange"  the answer is - "No"  it is not feasible.

Is there a way I can implement incremental refresh or is it planned for upcoming update?

I suppose this feature is not yet supported on Power BI

Thanks,

Mannu

[v-jiascu-msft]

Hi Mannu,

It could help if you can archive the old mails. As far as I know, there isn't a special limit for the data from MS exchange. Please refer to this post.

Yes, the incremental refresh is planned. Please refer to this idea. But as I mentioned before, the incremental refresh couldn't help in your scenario. For example, the exchange only have data of 2016 and 2017. The Power BI could consider the data before 2016 invalid. Because the Power BI has to make the result accurate and helpful. (The final result depends on the design).

The workaround could be storing all the data first then sending to Power BI.

Best Regards,

Dale

[Anonymous]

Thanks again Dale @v-jiascu-msft

Ability to connect with .pst file is still an idea (so archival will not help me). If .pst files (archived files) could be accessed from PBI, it would have been easier to maintain it on One Drive and then we wouldn't have to worry about data refresh !!!

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/31581544-enable-power-bi-and-powe...

But alas, at this juncture I think we need to store information on a different system and pull this data into Power BI 😞   Complex and time consuming!!  And for all our efforts, if this feature is made available soon after... would drive me crazy !!!

🙂

Thanks for your help again

Regards,

Mannu