Data source In Joins not changed while Imported View

Guest ●

Hi Pratap,

so far I am unable to reproduce this error. I tried by copying a MySQL database and changing its name and changing the data for one of the tables. Therefore after I switched data sources during importing the report, I was able to tell by the different data in the report that it was using the substituted data source.

So I think I must try and get closer to what your content and environment is in order to be able to do so.

Could you please tell me which build of 7.3 you are using (e.g. 20171201). And also please tell me which DBMS you are using.

And also I think it might help if you send some screenshots of the view's Model screen to give me an idea of what your view is.

regards,

David

Reply URL

1

Pratap Singh ●

Hi Dave,

Thanks for the response, I am using Oracle 12C, yellowfin version/build: 7.35 20171201.

attached screenshot for more info.

Regards

Pratap

Files:

Reply URL

1

Guest ●

Hi Pratap,

thanks, with that information I was able to replicate the issue over here. So I have raised a product defect (YFN-9520) so that it gets fixed.

After investigating the data source table (ReportViewSource) and the view table (ReportView) what I found was that the imported view was actually pointing to the correct data source, but was adding the incorrect Oracle schema in front of the table name.

To see this for yourself, go to your ReportViewSource table and make a note of your 2 different Oracle data source IDs (from the SourceId column) .

Then go to the ReportView table and look for your newly imported view. The view name is actually stored in the ViewDescription column. Notice that its ViewTypeCode column is holding the value "DRAGANDDROP", this signifies that it is the actual Yellowfin view. Underneath it you will see some entries with the ViewTypeCode value being "CHILDELEMENT" which means they represent the individual tables within your view. For both the view ("DRAGANDDROP") and tables ("CHILDELEMENT") rows you will see that their SourceId value is the correct data source. However, you will see that the Oracle schema names in the ViewName column are incorrect.

In other words, your view and reports will be showing the correct data from the correct data source, however, the Oracle schema names in the SQL will be wrong.

You can update these incorrect table names with the following query:

UPDATE ReportView
SET ViewName = '<correct Oracle schema>.<table>'
WHERE ViewId = <insert viewId here>

Then you will have to restart Yellowfin for those changes to take effect.

I hope this all makes sense, if not, and you have some further questions please don't hesitate to ask.

regards,

David

Hi Pratap,

thanks, with that information I was able to replicate the issue over here. So I have raised a product defect (YFN-9520) so that it gets fixed.

After investigating the data source table (ReportViewSource) and the view table (ReportView) what I found was that the imported view was actually pointing to the correct data source, but was adding the incorrect Oracle schema in front of the table name.

To see this for yourself, go to your ReportViewSource table and make a note of your 2 different Oracle data source IDs (from the SourceId column) .

Then go to the ReportView table and look for your newly imported view. The view name is actually stored in the ViewDescription column. Notice that its ViewTypeCode column is holding the value "DRAGANDDROP", this signifies that it is the actual Yellowfin view. Underneath it you will see some entries with the ViewTypeCode value being "CHILDELEMENT" which means they represent the individual tables within your view. For both the view ("DRAGANDDROP") and tables ("CHILDELEMENT") rows you will see that their SourceId value is the correct data source. However, you will see that the Oracle schema names in the ViewName column are incorrect.

In other words, your view and reports will be showing the correct data from the correct data source, however, the Oracle schema names in the SQL will be wrong.

You can update these incorrect table names with the following query:

UPDATE ReportView
SET ViewName = '<correct Oracle schema>.<table>'
WHERE ViewId = <insert viewId here>

Then you will have to restart Yellowfin for those changes to take effect.

I hope this all makes sense, if not, and you have some further questions please don't hesitate to ask.

regards,

David

Reply URL

1

Pratap Singh ●

Hi Dave,

Thanks for the response. updated and working fine now, while investigating there are a lot of other child elements having the incorrect schema (Updated for now and working fine). also, there are some child elements which don't have Oracle Schema in front of table name, now my question is in which case it adds schema and in which case it doesn't? In our case, we use single schema to build reports and import reports in some other Schema(Client's Database).

Regards

Pratap

URL

1

Guest ●

that's good news about the workaround, I'm glad it's temporarily fixing the issue.

Regarding the question about when does Yellowfin add a schema to the tablename and when it doesn't....I'm actually not sure about this, and so far I have been unable to replicate it over here, all my CHILDELEMENT rows have a schema name before the tablename in the View Name column.

I'm wondering if it might be to do with not setting the schema - do you always set the schema name in the data source? Or sometimes do you leaved it unconfigured like below:

/w0y9K9WED3AAAAAElFTkSuQmCCAA==

regards,

David

URL

1

Pratap Singh ●

Hi Dave,

We always use to add Schema, actually, we have multiple schemas in the same database so we used to select schema for every connection.

Regards

Pratap

URL

1

Guest ●

Hi Pratap,

I still can't manage to get any Oracle tablename entries in my reportView table that don't have a prepending schema.

However, I noticed that in other Yellowfin instances I've got, there are CHILDELEMENT rows that just have the tablename without the schema prepending it, but these were databases other than Oracle, such as MySQL, and I suspect this must occur when the default schema is being used.

Could you please verify whether the data source is Oracle or a different database for those CHILDELEMENT rows that don't have a schema in the ViewName column.

And if the data source is indeed Oracle for those CHILDELEMENT rows that have a tablename with no prepending schema , could you please verify if reports that use that data source work or not? And if they work, what does the report SQL look like, does it have the schema or not?

regards,

David

URL

1

Pratap Singh ●

Hi Dave,

I am using single Oracle Datasource to create Views and CHILDELEMENTs also from the same schema. while reports are in the same schema there is no problem in running reports either schema is prepending or not but while exporting to some other schema it is causing the problem as the view is in the main schema but child elements pointing to the different schema.Here I have attached data in Excel from "Reportview" Table from for one SOURCEID.

Queries also using same structure (some tables pre-appended Schema name some not).

Regards

Pratap

Files:

URL

1

Guest ●

Hi Pratap,

I'd be surprised if the "no schema" issue is not caused by the same bug that is causing the "wrong schema" issue. Seeing as I can't replicate the "no schema" issue would you be content if we just wait for the fix for defect YFN-9520 and see if it fixes both issues? Keep in mind that the workaround will work for both issues.

regards,

David

URL

1

Guest ●

Hi Pratap,

could you please turn off the Data Source setting called "Include Schema in SQL" and test for this issue again:

/dsRAAAAAASUVORK5CYIIA

I have found that this prevents that issue happening for me, so I would like to confirm so for you.

thanks,

David

Reply URL

1

Pratap Singh ●

Hi Dave,

I have unchecked it and after that created view, but it is still including schema name.

Is this checkbox is for the same purpose, please correct me if I am wrong?

Regards

Pratap

Files:

URL

1

Guest ●

Hi Pratap,

I asked one of the head developers and he said he actually wasn't sure if checking that box should affect whether the schema is included as a prefix to the tablename in the ReportView table, this is because the code behind the functionality might tell Yellowfin to ignore or replace the tablename.

But anyway, I'm going to go back to the issue of this ticket because I think I've found out the steps to take to successfully be able to import a view and point it to a new data source.

I planned 4 test cases:

1) export file made of a view that was made when the original datasource had "Include Schema in SQL" unchecked. During import the view was pointed to the new datasource that also had "Include Schema in SQL" unchecked

2) export file made of a view that was made when the original datasource had "Include Schema in SQL" unchecked. During import the view was pointed to the new datasource that had "Include Schema in SQL" checked

3) export file made of a view that was made when the original datasource had "Include Schema in SQL" checked. During import the view was pointed to the new datasource that had "Include Schema in SQL" unchecked

4) export file made of a view that was made when the original datasource had "Include Schema in SQL" checked. During import the view was pointed to the new datasource that also had "Include Schema in SQL" checked

And the results were:

1) Report SQL had no schema prefixing the tablename.

2) Report SQL still had the original schema prefixing the tablename.

3) Report SQL still had the new schema prefixing the tablename.

4) Report SQL still had the original schema prefixing the tablename.

So going by these results I would urge you to try the scenario no.3 and hopefully that will give you the outcome you are hoping for.

Please let me know how it goes for.

regards,

David

URL

1

Guest ●

Hi Pratap,

I'm cleaning up my worklist and i noticed that this ticket is still open and in the "Awaiting Reply" status, so I'm wondering how you got on it and whether the steps from scenario 3 resolved your issue as it did mine?

If you could please let us know then that would be great!

regards,

David

Reply URL

1

Guest ●

Hi Pratap,

I'm wondering how you got on with the issue, and whether we should close this off?

regards,

David

Reply URL

1

Guest ●

Hi Pratap,

this ticket's status is being set to Closed because there has been no response to the last two emails over the last two months. However, you can always add a new post to it if you want, and in doing so the ticket will become open again.

regards,

David

Reply URL

Data source In Joins not changed while Imported View

Replies (7)