Rank and Windowing functions in beast mode

dacorson
dacorson Member
in Charting

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?

Answers

  • GrantSmith
    GrantSmith Coach

    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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • MarkSnodgrass
    MarkSnodgrass Coach

    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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • dacorson
    dacorson Member

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

  • dacorson
    dacorson Member

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

  • MarkSnodgrass
    MarkSnodgrass Coach

    @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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • dacorson
    dacorson Member

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

  • dacorson
    dacorson Member

    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
    jaeW_at_Onyx Coach

    ... 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.

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • dacorson
    dacorson Member

    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
    jaeW_at_Onyx Coach

    @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'

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
  • MarkSnodgrass
    MarkSnodgrass Coach

    @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.

    **Check out my Domo Tips & Tricks Videos

    **Make sure to <3 any users posts that helped you.
    **Please mark as accepted the ones who solved your issue.
  • user08845
    user08845 Contributor

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

Categories