In Prepare view I've unchecked show in drill anywhere and now I can't get back into view.

Chad Miller shared this problem 2 years ago
Resolved

Ever since I unchecked the box on one dimension to not show it in drill anywhere I've had nothing but trouble with the view. I needed to make another change to the view and I've never been able to get back into it. I can get to the first step Model but when I try to get back into prepare it fails. I've made a copy of the view, I've taken that dimension out, stop and restarted service on host server, and tried a few other things and I'm having no luck. I don't see any errors in the log it is just spinning and never loading.

Comments (19)

photo
1

Hi Chad,

Thank you for getting in touch. That is very strange. Are you still able to create reports from the view?

If removing the dimension has no effect, another idea is to remove the entire table with that dimension from the ER diagram, and replace and re-join the table.

Worst case, you may need to re-create the view entirely.

I will attempt to replicate this issue on my end and see if you have found a bug. Did you make any other changes at the time that may also have triggered this issue?

Regards,

Nathan

Edit: What type of DB underlies this view?

What type of field did you switch the "drill anywhere" selection on?

photo
1

It's a SQL DB and I believe it's just a varchar(20). I unchecked some other boxes in the access area too but that was the only change was unchecking a couple of those boxes. I've been unable to reproduce the problem on another view and am fearing I will just have to delete the table or view and redo it and of course it is the primary central table. Does deleting the view doom my reports I've created with it or should I just export them and then they will be able to be imported? Just wondering what the preferred solution is for deleting a view and rebuilding with reports attached to it.

photo
1

Hi Chad,


Deleting the view will remove all content associated with that view.


If you export the reports, then create a new view, you should not have any problems importing them onto the new view. However, I would highly recommend not deleting any content until the transfer has proven successful.


I will continue to look into this issue, and hopefully you do not run into any additional problems with your new view.


Regards,

Nathan

photo
1

Nathan

It seems it was a coincidence that this occurred when changing that check box there is a deeper problem with Yellowfin, our DB, or I'm doing something wrong. Probably option C. So I set up the same view and ran into the same problem. process of elimination I ended up at a calculation. Sum(decimal) operator Sum(decimal2) = never loading. I was trying to calculate margin so I had sum(sales)-sum(cost) this leads to never ending refresh but if I do sales-cost it works fine. then I need margin % so I need sum(margin)/sum(sales) = never ending loop or is this operand more resource heavy and it is just taking a long time to load. I waited over lunch (1hr) and came back to same screen.

photo
1

Hi Chad,


How is this calculation being applied? Freehand/simple calculated field, virtual table?


If you create this calculation in the report builder, with the primary key present and not aggregated in the table (so we are working with the entire data set) do you run into any errors? Finally, do both requisite fields have calculations enabled (it should not be possible to create a calculation without this, but it might be worth checking)?


While the "prepare" tab is loading can you check your browser's console for javascript errors? Also, inspecting the "Network" tab of the console may provide insight into what specifically Yellowfin is working on at the time.


Regards,

Nathan

photo
1

I think you've figured it out. Our cost amounts are negatives so I use a simple calculated field and do (cost *-1) to revert them back to positives. Then I use that to get margin and other items. as you can see in the attached image there is no calculations check box. So just from that I'm assuming it doesn't have calculations enabled. So how do I get that box to show up?

I still checked javascript errors and there were none and the network tab showed the below as loading.


URL Protocol Method Result Type Received Taken Initiator Wait‎‎ Start‎‎ Request‎‎ Response‎‎ Cache read‎‎ Gap‎‎

/MIViewFieldsAjax.i4?action=loadViewPreviewModel&reloadPreviewData=true&manualData=true&_=1482183918375 HTTP GET 502 text/html 1.60 KB 29.39 s XMLHttpRequest 56270 0 29390 0 0 0

photo
1

Hi Chad,

Calculated fields will not have the "calculations" checkbox (which is set to "on" as default), so unfortunately, this may not be the issue. A couple additional thoughts:


  • Is there any way that your calculations could be circular? Where one calculation requires another calculation that in turn requires the previous calculation? (the fact that no error is being thrown suggests that this could be the case)
  • Strip the calculations out, then add them back one at a time trying to isolate which one is causing the problems.
  • Bypass the simple cost (+/-) conversion and do that math in each subsequent calculation.

Unfortunately I cant give you any specific suggestions until we completely isolate the trouble maker.

Regards,

Nathan


Edit: As a circular calculation should not be possible to create, I would instead recommend focusing on which specific calculation is the problem. Are any of these calculations built on virtual tables?

photo
1

The calculations when I do a SUM of a decimal field. Screen shot after trying to do SUM(Sales Amount(Actual)). Again this is just a simple calculation.

photo
1

Here is another error I got when just trying to add two metrics Cost and Sales. this was the only time I got this error.

I keep creating a new view from scratch and can consistently add Cost and Sales metrics and do calculations with them. As soon as I go back and try and add a dimension I get the error posted previously.

I've tried other decimal fields also and get similar results. sometimes I can sum them sometimes I can do other calculations sometimes nothing.

I've also tried these inside SSMS and don't have any problems.

photo
1

Hi Chad,

I have done a many tests on SQL Sever decimal calculations in 7.3 and they are working fine for me. Looking at those errors, I am beginning to think that this may be an issue with a poor connection to the data source:

1. Prior to creating your calculated field in the view, turn off "auto-refresh", create the calculations as desired, and then go save and see if they work in a report. (Essentially cheating your way around the data previewer)

8771158308054557c72713a77e9efcd8

2. Can you please send me your logs?

3. One thing that will probably be worth trying, is enabling the "volatile data source" connection settings within the configuration DB:

http://www.yellowfinbi.com/YFForum-haproxy-and-mysql-reconnection-issues-Volatile-Sources-?thread=129785

Regards,

Nathan

photo
1

Here's the logs as it was locked/loading on me after turning off auto-refresh and adding a dimension.

I tried #3 and it just takes longer for the error to come up but it still comes up.

Files: logs.zip
photo
1

Hi Chad,


Thank you for sending the logs. I am assuming that this view is built upon your "Superb" data source?


Looking through the source log for this data source:


2016-12-20 08:48:34 WARN: [61081] [21010] No connections available. Waiting for a connection to be returned.


2016-12-20 08:49:08 WARN: [61081] All connections are checked out and 3 threads are waiting!


It seems that it may be useful for you to increase the connection settings in the data source editor. I would recommend increasing these to an unnecessarily high number to start (timeout of 15+min and 15+ max connections). Hopefully increasing these will give Yellowfin the time to finally throw its own error, which will tell us far more than the javascript 502 error.


Also, is your SQL sever on the same machine as your Yellowfin install?


As the error is on the server side rather than within Yellowfin, I think that our best bet at this point will be to look further into the connection settings on both sides.


Regards,

Nathan

photo
1

Yes Superb is the Data source.

I've increase connections and timeout to 15 and still get 502 error.

SQL server is on a different machine than Yellowfin. Yellowfin is on WEB server and SQL is on NAV-SQL server.

photo
1

Hi Chad,


I am going to discuss this issue with the product team in Australia, but unfortunately they wont be coming online for another 2 hours. To give us a final bit of information to work from:

1. Are you able to create these calculated fields in the report builder without any problems?


2. Are you able to run complex queries against the Superb data source, from the same server that Yellowfin is on, using a 3rd party tool such as DBVisualizer?


Nathan

photo
1

Yes attached is a report showing in Data view these calculations working along with other Dimensions added to the report.

I downloaded DBVisualizer and am able to run the same Query I'm trying to replicate in Yellowfin.

photo
1

Hi Chad.


Thank you, I have spoken with the team in Australia, and they have confirmed that the 502 error you are noticing is most likely the effect of poor communication between your Yellowfin and MS SQL servers.


However, as the validation process for calculated fields at the report level differs from that at the view level, there is a chance that this could be an issue with the underlying data.


  • If you create a new DB filled with very simple test data on your SQL Server and connect to it, are you able to create view level calculated fields?
  • If you connect to a different DMBS (MySQL, PostgreSQL) with the same or different data, do you experience the same problem?
  • Are you clustering?
  • Are you employing anything that might be unique to your environment that would help us in replicating this issue?


If these final queries yield no progress and we are still unable to replicate this on our end, it may be necessary for us to request a temporary user login to your Yellowfin server.


Regards,

Nathan

photo
1

Hi Chad,


Has this come to any resolution on your end?


NAthan

photo
1

Nathan

I this can be closed. I'm not having the problem any more and I don't remember what I did to fix it. Appreciate the check up and sorry I don't remember the answer. I'm guessing it was user error in my calculations.

photo
1

Hi Chad,

Glad to hear you got it working!