Power BI real-time Dashboards with no intermediate services/APIs

[haymanezzeldin]

Here's my situation:

1. I'm connecting Power BI Desktop to Dynamics CRM/365
2. I would like to retrieve the total count of opportunities that I have on regular time intervals (every 2 days for example)
3. Power BI should draw a line chart for example that shows these 2 days time intervals as the x-axis, and the total count of the Opportunities on the y-axis

I know this can be done using services such as PubNub, or APIs

But is it not possible to have this scenario without the need of any intermediates?

Thank you,

Hayman

[dkay84_PowerBI]

Perhaps you can use MSFT Flow to automate a daily operation of getting this info from Dynamics and dumping it in storage (with an added Date field using the query date). Then you connect to he storage with PBI. You can group by date and then, using an index column with integer divide and rounding you can get an index in groups (ie 1,1,2,2,3,3 etc for 2 day groups). Then group again based on the index. Then create a column for delta that you would use in your report visuals.

[Greg_Deckler]

Just so I'm clear, you would essentially have data like:

Date,# Opportunities

1/1/2017,15

1/3/2017,19

1/5/2017,13

1/7/2017,14

So, on the 1st of January you had 15 opportunities and then by the 3rd you had a net gain of 4 more opportunities, then on the 5th a net loss of 13 opportunities and so on?

Just making sure I understand the scenario. Effectively, you are more or less "snapshotting" the opportunity count.

[haymanezzeldin]

Dear @Greg_Deckler

You are completely right, this is the exact scenario I would like to have, but I don't care for the net gain, rather the total changes, whether they are up or down.

Also note, that these dates don't exist in the entity, but it's the date of the count retrieval.

Thank you,

Hayman

[Greg_Deckler]

So, the problem is that Power BI is not really setup for snapshotting data natively. At least, I'm not aware of a clean, simple solution for it. Tables in the data model are sourced via queries and when the queries update, the entire table updates its data. Hopefully someone else has some better solutions, but the one that I have seen is to essentially call an R script that updates some sort of table or file outside of Power BI in order to record the "snapshot" and then use Power BI to ingest that external table as a table within the data model, so:

Query 1

- Connect to CRM

- Count the number of opportunities

- Call R script to record snapshot

Query 2

- Connect to where snapshots are recorded and import

[haymanezzeldin]

Dear @Greg_Deckler,

That's a nice approach, but I have few questions here:

1. Where would the snapshots be recorded
2. How can this be automated
3. Have you seen any show case for this? if yes, I would appreciate some guidance.

Thank you,

Hayman

[Greg_Deckler]

So, the snapshots could be stored anywhere, I would recommend something like Azure SQL DB because it is super cheap and maintains the cloud aspect of the solution. I first came across this approach on a completely unrelated subject here:

https://powerbi.microsoft.com/en-us/blog/power-bi-azure-ml/

I believe the reference you are looking for is:

http://blog.revolutionanalytics.com/2015/05/using-azure-as-an-r-datasource.html

Oh, and essentially, it would be automated by setting your refresh schedule for your data set in the Service. Every time the query runs it grabs the count and records it into the database.

[haymanezzeldin]

So, I have to have an intermediate location for saving these snapshots.

Can this location be Dynamics CRM/365 itself, for example can I add these snapshots as a new records in a custom entity, and then use Power BI to retrieve just the records from the new entity?

The reason I'm asking for no intermediate services/APIs is because I'm developing my own solution with some reports, and not all customers would have the luxury of having access to these services, even if they are cheap.

Thank you,

Hayman

[Greg_Deckler]

Yeah, as long as you can get to the custom entity data through Power BI, it could definitely be CRM itself. I like the Flow idea as well but I was going on the basis of no intermediate stuff, just CRM and Power BI. (Although I guess technically an intermediate location is intermediate stuff...)