search

Azure / azure-stream-analytics

Azure Stream Analytics
MIT License
224 stars 935 forks source link

Stream Analytics Job getting failed while configuring the Power BI as a output. #118

Closed A-DavidJeyapaul closed 2 years ago

A-DavidJeyapaul commented 2 years ago

Hi Team,

We are trying to write the data from IoT hub to Power BI using Stream analytics job. We have tested the connection and its success. We have the static columns to be written into Power BI as per the below query.

SELECT DisplayName as Parameter, Value.SourceTimestamp as Timestamp, Value.Value as Value INTO [output-bi03] FROM [input-hub]

When we start the job, its getting failed and its showing the error as Send Events: Failed to send data to Power BI due to incompatible schemas. But the Data sets and tables are created in the Power BI with no data.

Note: The Stream analytics Job is running on Dedicated Cluster.

Fleid commented 2 years ago

Hi there, I'm wondering if that could be a type conversion error. Could you try to explicitly convert them by using either TRY_CAST (will output NULL if the conversion can't be done) or CAST (will crash the job if the conversion can't be done).

Also, do you have a sample input payload we could look at?

A-DavidJeyapaul commented 2 years ago

Hi @Fleid , Thanks for your quick response. I have updated the query like below. Its working fine. SELECT DisplayName as Parameter, Value.SourceTimestamp as Timestamp, TRY_CAST(Value.Value AS bigint) as Value INTO [output-bi03] FROM [input-hub]

Does the job was getting failed due to the Conversion?

Fleid commented 2 years ago

That would be my guess yes. We would need to look at some of your input events to be sure.

If you're going from a loosely typed environment (documents over a stream) to a strongly typed environment (SQL Table, Power BI...), the best is always to do your conversion yourself. See that one for more details: https://docs.microsoft.com/en-us/azure/stream-analytics/input-validation

I'm closing the issue, but I can still answer your questions :) Please re-open if need be ;)

please-close