Datastream job keeps restarting

Hi @jayqueue​ ,

I'm not sure if I completely understand the workflow, are you attempting to use an FME workspace webhook URL as a streaming dataset in power BI?

I've not worked with this in Power BI before however looking at this definition of the streaming dataset:

"With a streaming dataset, data is also pushed into the Power BI service, with an important difference: Power BI only stores the data into a temporary cache, which quickly expires. The temporary cache is only used to display visuals, which have some transient sense of history, such as a line chart that has a time window of one hour."

"With a streaming dataset, there is no underlying database, so you cannot build report visuals using the data that flows in from the stream. As such, you cannot make use of report functionality such as filtering, Power BI visuals, and other report functions."

this suggests that the workspace must be running continuously in order to provide a data stream, rather than writing to a database. Therefore the job will be resubmitted by Power BI any time it finishes. If I've understood correctly then you'd need to design your workspace in a way so that it runs continuously and streams data to the Power BI service rather than writing out to a database. It looks like you can do this using their Rest API or perhaps with one of the Message Streaming transformers in FME.

hello @hollyatsafe​ ,

The /fmedatastreaming workspace streams the data to a csv file and then I can use this in Power BI.

this is from another working example

Power BI: Add Datasource > Web and it recognizes it's a csv

it shows me the fields and values

But now when I'm trying to do this with a new project, the job keeps getting re-submitted when it's done.

The only difference is the amount of records. The working one has 10, the not working one has 300+.

I'm not using the datastream from Power BI, just adding the data from web.

I hope it's a bit clearer? :-)

Hi @jayqueue​ ,

Thank you, I think I understand much better now! I'm going to get started with a trial of Power BI and do some testing on my end, hopefully I'll be able to find something more out for you on this issue in the next few days.

@hollyatsafe​  thank you!

Fingers crossed. If I should find something too, I'll post it here.

let
    Source = Csv.Document(Web.Contents("https://FMESERVER/fmedatastreaming/TOPdesk/api_assets_HW.fmw?DestDataset_CSV2=%24(FME_SHAREDRESOURCE_DATA)%5CTOPdesk&token=aaabbcccc156161fffffffff",[Timeout=#duration(0,0,5,0)]),[Delimiter=",", Columns=11, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"displayName", type text}, {"aanschafdatum", type datetime}, {"serienummer", type text}, {"specification", type text}, {"garantieperiode-in-maanden", Int64.Type}, {"afschrijftermijn", Int64.Type}, {"aankoopbedrag-incl-btw", Int64.Type}, {"aangekocht-door", type text}, {"provider-2", type text}, {"assetId", type text}, {"name", type text}})
in
    #"Changed Type"

For some reason I got it to work, I just added the Timeout in PowerQuery. 

Now I can create reports 

But it doesn't explain why the job keeps getting resubmitted ofcourse.

Hi @hollyatsafe​ ,

I wrote a small howto on LinkedIn about it

Hi @jayqueue​ , 

Thank you for creating this additional resource. I was able to get a Power BI project set up with a web dataset pointing to the datastreaming URL. 

In my test I was able to follow the instructions in your earlier comment to add a datasource from the web. The SQL in PowerQuery was already correct without me needing to make any changes: 

let
    Source = Csv.Document(Web.Contents("https://<fmeserver>/fmedatastreaming/Holly/PowerBI_CSVStream.fmw?&token=<token>"),[Delimiter=",", Columns=10, Encoding=65001, QuoteStyle=QuoteStyle.None]),
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"TreeID", Int64.Type}, {"Address", type text}, {"Neighborhood", type text}, {"Height", Int64.Type}, {"Diameter", type number}, {"PlantingDate", Int64.Type}, {"Cultivar", type text}, {"Genus", type text}, {"Species", type text}, {"CommonName", type text}})
in
    #"Changed Type"

My test dataset has ~24,000 rows but I didn't have any problems loading the data, so at the moment I am not clear why you needed to add in a timeout parameter for this to load. How long does your workspace take to run on FME Server? 

While I was setting up the data connection I did see the job was submitted over 15 times, but as soon as the connection was established and the data loaded into Power BI I did not see anymore job submissions. After this I refreshed the dataset manually a few times, and also configured a couple of scheduled refreshes throughout the day. All of these worked as expected and submitted the datastreaming job just once. Therefore at the moment I don't have much idea what in PowerBI could be triggering this job to run continuously on your FME Server. 

I would be happy to jump on a call to try and continue troubleshooting this problem with you. Please email support@safe.com and include 'ATTN Holly' in the subject and we can schedule something in.