How can I use calculated fields in composite view?

In addition I faced up another problem. When I moving from the model step to prepare step I get 'no result returned" message in the area where data is supposed to be (However I see all the fields' names at the top of the table). At the model step I can see the data within the used view. What should i do to get the data on this step?

Could you also provide some links to detailed description of the composite view functionality, because in the wiki there are only a few lines about it=(

Hi Timofey,

I tried to replicate this issue over here, and yes, I kept getting the "no result returned" error message in the Prepare screen. And it didn't seem to matter how I joined my 2 views I could not get any data returned at all.

Then I found there is already an existing defect for this issue, its ID is YFN-6296.

So because of this issue I would strongly recommend that you try the Yellowfin feature called Advanced Subquery which is a totally different way of joining different data sources:

http://wiki.yellowfin.com.au/display/USER74/Advanced+Sub+Query

Please read the above wiki article and watch the video and then give it a try, and if you have any questions on it please let me know. I have just tried it out and it is all working properly.

regards,

David

Hi Timofey,

good news, after some further investigation it has been found out that even though the Composite View doesn't allow you to cache the joined data from when you are within the view (as it used to in earlier versions of Yellowfin), it actually still creates a Scheduled Task, and thus if you save your Composite View after you have dragged across the fields you require in the Prepare screen and then go to Administration->Schedule Management then you will find your Composite View schedule there and then you can run it (which will populate the writeable Data Source with the de-normalised data from your joined data sources):

I hope this makes sense, please let me know if it doesn't.

Having said that, please keep in mind that we actually do recommend the Advanced Sub Query functionality over the Composite View, mainly for reasons of flexibility and robustness (especially if the view is quite complex).

regards,

David

Hi David!

Thank you for answer!

Now I'm trying to use subquery feature and faced up with anouser problem. I have a master query from Clickhouse datasource and subquery from Postgres datasource. After joining this queryes there is a error message:

Error retrieving results

Code: 62, e.displayText() = DB::Exception: Syntax error: failed at position 156 (line 10, col 63): AS C0, extract(target, 'utm_medium=(.*?)(&|$)') AS C1, extract(target, 'utm_campaign=(.*?)(&|$)') AS C2, extract(target, 'utm_content=(.*?)(&. Expected one of: FROM, PREWHERE, WHERE, UNION ALL, Comma, ORDER BY, SETTINGS, WITH, HAVING, GROUP BY, token, LIMIT, e.what() = DB::Exception

There is only one pare of fields for join. Both queries working good separately, but not together

I think that point is in different datesource languages (Clickhouse and Postgre), because tere are some differences in join syntax, but I'm not sure.

About composite view: I saved composite view on prepare screen, turn on run now option in Schedule Management. But after that nothing chanched in my composite view - still no data( No data when I try edit view and no data when I try to create new report based on it.

Anyway, I need to join two queries from two different datasources (Clickhouse and Postgresql). Now I can't do it by compositeview or subquery.

Hope you help!

Hi Timofey,

after you clicked the "Run Now" button and then the "Refresh" did the Status display "Success" or "Failure"?

And if it was a failure, what was the error message displayed?

regards,

David

Hi David!

After run now and refresh buttons the status is "Running"

Hi Timofey,

if there is a lot of data to cache and lots of joins to flatten it will not be instantaneous, in fact it could take quite a while, for example up to 30 mins or even 1 hour.

If you look at it now, 3 days later, what does the status of the composite view job say?

Also, if it is indeed a large view and takes longer than 10 minutes then there is a chance that Yellowfin's connection to the Yellowfin database will time out, in which case you will need to increase its timeout value as described in the following Knowledge Base article:

https://community.yellowfinbi.com/knowledge-base/article/how-to-increase-the-connection-timeout-to-the-yellowfin-database

Please let me know how you get on with it.

regards,

David

Hi David,

I checked the view status. Now it's FAILURE. You can see error details on screen:

Thank you for advice about database timeout!

Hi Timofey,

thanks for the latest screenshot - that error is interesting, it looks like Yellowfin is trying to store a string that is more than 1 character into a column whose datatype is "character" which of course only holds 1 character.

I'm wondering that as a dirty hack workaround whether the column that is of datatype 'character' could be changed to string or whatever they call that in PostgreSQL (varchar, text?). To do this you'd just have to go into your PostgreSQL database and look for a table with a similar name to the following and then manually change the datatype:

But as I said, that would be a bit of nasty hack. I suppose we should investigate what is actually causing this to happen, and we can do this by setting your logging level to DEBUG and then you run the Composite View scheduled task again, and then if we're lucky, we'll be able to see what query Yellowfin runs just before that error occurs.

regards,

David

Hi David,

I've asked our admins to set logging level to debug.

And about Composite View running error. Maybe the reason is that one of 'simple' view based on PostgerSQL database and anouther view based on Clickhouse database?

Hi Timofey,

great, I await your debug logs!

Regarding your idea about PostgresQL & Clickhouse database, yes that may possibly be the reason, for example perhaps a column size is too big for PostgreSQL. It will certainly be interesting to investigate the error in the logs.

regards,

David

Hi David,

there is our debug logs in attach. I 've run View again and it's the same failure now. Please look at it.

Hi Timofey,

thanks for the debug logs, I actually found quite a few errors in them (12), but unfortunately the initial ones are not helpful at all because they don't have a stack-trace:

YF:2018-06-14 11:14:04:DEBUG (JDBCConnection:debug) - Connecting with: jdbc:derby:memory:08d7ab492a214dfdae4f9457a0202545;create=true
YF:2018-06-14 11:14:04:ERROR (ReportRunner:error) - Error running report: java.lang.NullPointerException
java.lang.NullPointerException
YF:2018-06-14 11:14:04:DEBUG (ReportFromReportProcess:debug) - creating inmemory database

So because of this, I think the best way forward here would be if we can replicate the issue over here by setting up a similar environment to yours. Are you able to send across a dump of your Yellowfin database? And also we will need the schemas of your 2 data sources that you are trying to combine (just the schemas, hopefully we will be able to replicate the issue without your actual data)

We have an FTP website which you don't need a login for, you just upload your files:

https://yellowfin.brickftp.com

Please let me know if this will be possible.

regards,

David

Hi David,

I've uploaded 3 files with datasources schemas to your FTP website.

clickhouse_schema_affiliate.sql

postgresql_schema_events_shifter.sql

yellowfin.sql.gz

I can upload it here, you get this ticket to private mode.

Hi Timofey,

thanks, I've downloaded those 3 files. And I've installed ClickHouse according to the instructions on https://clickhouse.yandex/#quick-start. But now I notice that only the source code for the JDBC driver is available, so I'm wondering whether you could send me the binary of the Clickhouse JDBC driver?

regards,

David

Hi David,

In an attach you can find our driver for clickhouse)

Hi Timofey,

thanks for that!

But as a test I'm trying to connect using SQuirreL and it is failing with clickhouse exception code 210.

So if you had the same error as me and remember any tricks you did, please let me know, otherwise I'll keep on researching it...

regards,

David

Hi David,

No, we hadn't any errors with connection.

But today we found update for our clickhouse driver and it's working good. May be it'll be useful.

Update in attach

Hi Timofey,

I'm just keeping you updated, but unfortunately at this stage there is still no improvement - I am using your latest JDBC driver (thanks for that!) but am still getting the 210 exception. I am using the Default user, and have made sure that the network setting in the users.xml file is set to being open to all networks (<ip>::/0</ip>).

I am not giving up, but sorry for the delay so far. You are not the only client using ClickHouse actually, so it is important that we get an instance of it working correctly over here.

I will keep you updated, hopefully with better news next time.

regards,

David

Hi Timofey,

sorry but this week I haven't had a chance yet to do further investigation as to why I can't connect remotely to my ClickHouse database, and at some point I definitely will sort it out (as I explained previously, we definitely do need an instance of it over here). However, in the meantime I have just realised that there is still yet another Yellowfin feature that I should tell you about that can be used to combine different data sources! It is called Data Transformation, and you will need to use 2 Input Steps called "Extract from Single Table" and then join them using the Transformation Step called "Merge" and then of course write the resulting data it to a table of your choice by using the Output Step called "Output to SQL Database"

I hope you have a chance to give this new feature a try and if you do, please let us know what you think.

thanks,

David

Hi Timofey,

I know that you moved on from using the composite view feature to using the new Data Transformation to combine different data sources (which I think was a very good decision indeed!), and with that in mind I'm just wondering if it's OK to close off this Question now?

regards,

David

Hi David,

Now this question is not actual for me, so it's OK to close it.

Thank you!

OK Timofey, will do!

thanks,

David