Rank and Windowing functions in beast mode

dacorson · 2021-07-16T19:21:18+00:00

There was an error rendering this rich post.

Hi! I had my CSM enable rank and window in beast mode, and I have created a few that work great, but when I go to save my card I get an error that says the card cannot be saved and to contact support. I submitted a support ticket - but is anyone else having this issue? And if you have existing cards with this type of beast mode, can you create a new one and save it?

Find more posts tagged with

Accepted answers

All comments

GrantSmith

Hi @dacorson

How is your window function written? If your card has another field you're grouping by then the window function won't get interpreted correctly. You need to wrap your window function aggregate in another aggregate.

for example:

SUM(SUM(1)) OVER ()

I've documented this in another post of mine here: https://dojo.domo.com/discussion/52681/domo-ideas-conference-beast-modes-running-totals

Also I did a conference talk about this. You can find the video here: https://www.youtube.com/watch?v=gO8OLpsAk4M&index=6

MarkSnodgrass

I just created a few earlier today and the card saved without issue. It does sounds like a bug and you might have to wait until support gets back to you. You might also try creating a new card from scratch and see if the issue still occurs.

dacorson

Yes, I did that. It's just that it won't save my card. The window functions works fine.

dacorson

@MarkSnodgrass still an issue when creating a card from scratch, but thanks for the suggestion.

MarkSnodgrass

@dacorson given that Domo just did a big release on the 14th. My guess is that something got messed up in your instance that is causing the issue. If you want to post the syntax for the window function beast mode that is in use when you try and save the card, we might be able to reproduce in our instances and see if the same thing happens. @GrantSmith is one of the window function experts in the Dojo, so he will likely spot something.

dacorson

Thanks guys - it was actually happening even last week, but I have a feeling it's something about our instance.

dacorson

I got the answer from support. Initially, my dataset was processed through RedShift, which converts columns names to lower case. I built the beast modes with lower case column names. Then I added a Magic ETL to the end to uppercase the column names. I changed the column names in the beast mode to upper case and it saved with no error.

jaeW_at_Onyx

... that's a pretty roundabout solution and is probably not tied to the root cause of the issue (unless you were mistyping column names)

Analyzer should not recongize a difference between Magic and Redshift generated datasets, b/c once the dataset gets loaded into Adrenaline (the database layer) there is functionally no difference between the two table types other than the source.

dacorson

The fact that I first used Redshift is what caused the final output of column names to be lower case. When I changed to Magic, I made the columns upper case, but I did not go back and change the beast mode column names to upper case. The values displayed correctly in the card in analyzer, but when I tried to save the card, I got an error message. As soon as I edited the beast mode and changed the SQL to have upper case, I was able to save the card. So, it seems like beast modes are case sensitive in the sense that you can't save the card. I'm not talking about the difference between redshift and magic etl, I'm just saying that my problem ended up being a simple issue - though I find it strange that the beast modes would "work" in analyzer if they are supposedly case sensitive.

jaeW_at_Onyx

@dacorson re: case sensitivity.

keep in mind that Analyzer is a UI for building viz AND defining queries that get to SQL. it is possible (though I am uncertain) that when you create a beast mode that the validator checks for case sensitivity; however the database itself is not case sensitive for column names.

i don't know. but i could see it being true. for sure the collation is case sensitive insofar as if you search for column LIKE 'aBC' will not yield the same results as column LIKE 'ABC'

MarkSnodgrass

@dacorson I have had the same experience myself when converting Redshift dataflows to Magic ETL and have had to be very mindful of my naming conventions because Analyzer is case sensitive. It would be a nice enhancement if Analyzer did not care what case a field was when you changed datasets. I would recommend submitting it in the Ideas Exchange section of the Dojo.

user08845

Was experiencing same frustration. it was a case-sensitivity issue. thanks so much for this old thread