Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Updating Fields Breaks Real-Time Report and Dataset

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 da...
Need Help
Eric_Zhang's avatar
Eric_Zhang
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
    Anonymous
    Not applicable
    9 years ago

    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.