Updating Fields Breaks Real-Time Report and Dataset

[Anonymous]

This issue has bugged me for the last week and everytime the report gets broken I couldn't find any other solution than re-creating dataset, report and dashboards from scratch.

I'm getting the data from Azure stream analytics and Power BI is the output. I haven't manually created the streaming dataset and let the ASA do its thing with all the input columns as pbi output. After creating the report and a dashboard from that dataset, I enjoyed my working real-time dashboard for some time.

Then I've decided to add a case condition to ASA job query, and instead of "select *", I've written all the field names and a case statement with the same field name as before. I've even copy-pasted from the dataset sample raw data not to make any typos. As soon as I started back the job, all the fields in the report got "Can't display visual" errors.

The error detail says "This visual contains one or more filters with deleted columns, type mismatches, or other breaking modeling changes." Even modifying the expression with the same column name and type, the model breaks. Another error gives "Something's wrong with one or more fields" and does not recognize the same field names on the report table's field list.

Odd thing is, the dashboard is still working fine, updating the real-time data with the latest changes I made with the case statement.

There are other cases where the report's data model breaks and all the visuals get these errors, and I have to start from scratch, such as modifying data types on the dataset, disabling/enabling the historic data. I'm not mentioning those, not to stray further from topic, but I've come to learn how delicate the real-time streaming datasets are.

Thanks for any thoughts.

 

[Eric_Zhang]

@Anonymous

You're talking about this Stream Analytics and Power BI: A real-time analytics dashboard for streaming data, right?

Per my test on the streaming dataset, when change the column type(Update Table Schema) in the steaming dataset, it would definitely lead to the error message in your case. 

What is the CASE WHEN statement like? I don't know ASA, however per my experience on SQL Server, the CASE When in a Query sometimes changes the datatype. eg, in below case, there're actually some implicit conversion. Do the CASE When do implicit conversion in ASA?

use testdb;

create table tablea
(
column1 numeric
);

insert into tablea
select 1
union select 2

select case column1 when 1 then 1 when 2 then 2 end column1 
into tableb
from tablea
 
select * from tablea
select * from tableb

SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
     TABLE_NAME = 'tableb' AND 
     COLUMN_NAME = 'column1' 

SELECT DATA_TYPE 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE 
     TABLE_NAME = 'tablea' AND 
     COLUMN_NAME = 'column1'

Basically when you change the query from "SELECT *" to "SELECT Case When", I think the Update Table Schema API is called to change some column's type in that Power BI dataset. To have a clear understanding on  what's going on actually underlying, I'd suggest your submit a support ticket of ASA.

[Anonymous]

Hi @Eric_Zhang,

I think you are right about the type casting but I believe this is not the issue here.

I'm implicitly casting a text column to a cast column:

CASE WHEN [MakeCode] = 'BMW Otomobil' THEN 'BMW' ...

and a few more string casts like this one.

Without changing the data type from the dataset settings and casting to a different type would certainly break the model but what I did was; stopping the ASA job, casting to same data type, clearing all the previous data from pbi dataset via 'clear table rows' api call, and restarting the ASA job. I expected to see the fresh data on the same report because the model did not change, data was fresh and report was unchanged.

Then the error below got me thinking, I gave the table name for the real time dataset 'RealTimeSales', and let the ASA create the dataset for me (even if I create the dataset manually, ASA overwrites it with the same name). There was also another error with a '(RealTimeSales) columnname' when hovered over that warning.

What I think happened is that when creating the PBI dataset, ASA job automatically gives the table name 'RealTimeData'. There are a few other posts on this, like this one.

Then when I restart the ASA job, real time streaming dataset expected a 'RealTimeData' table but all the data was in a 'RealTimeSales' table so it gave the error. When trying to add new visuals, I got another error like 'cannot find the field, put an existingfield instead'. Sorry I deleted the report so can't give you the exact errors on that one. So I tried changing the table name to RealTimeData, but report continued to give the error.

I'm going to test the same scenario with RealTimeData as the table name from the start and see how it goes. But there's no harm in submitting the issue to ASA team as well, as you suggested.

Thanks.