ETL: Add an auto-uncollapse (pivot) feature

Domo has an ETL Edit Columns action called "uncollapse columns", what I'd call a "pivot". This feature works, but it requires manual configuration. In cases where the DataSet includes a clear category (split/break/name) column and a clear value column, please add an auto-pivot option. Instead of having to manually describe dozens of specific labels (category/split/break/name) values, have Domo figure it out. You would still want the existing uncollapse feature, but the auto-uncollapse would simplify pivoting name-value-pair data.

 

More details. Starting from the existing feature, here's the picture from the docs. Starting with this input:

 

etl_rowdenormalizer_datain.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Split the name-value pairs out into distinct columns/facts like this:


etl_rowdenormalizer_dataout.png

 

 

 

 

 

 

 

 

 

 

There are five steps in this process:

 

 

etl_rowdenormalizer_sampleui.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

This process works, but it is a non-starter if you've got a lot of categories or dynamic categories. Please add an ETL feature that automates this process using only three configuration settings: LabelKeyValue. These are the three basic elements any pivot requires, and you've got them set up beautifully in Domo already. (It's very nice that you anticipate keys that are defined by combining several columns.) 

 

Notice that all of the configuration in the bottom three steps can be automated completely. The label value is always from the Product column, the identifying key isalways from the Month column and the value isalways from the Sales column. Here's a mock-up of the auto-pivot setup:

 

pivot_auto.png

 

 

 

 

 

 

 

 

 

 

 

That's it. Domo then has to:

  1. Find the distinct values in the first (label) column. These define the pivot columns.
  2. Find the distinct rows in the second (key) column(s). These define the pivot rows.
  3. Copy the values in the third (value) column into the column and row they match.

I assume that if you do this that you should do something similar for unpivot, but I havne't needed it or thought it through.

 

Thank you.

11
11 votes

· Last Updated

Comments

  • P.S. As it stands, we aren't able to use Domo's pivot, which is a shame. Instead, we have to write custom scripts or use the Postgres crosstab extension.

  • DOMO Please develop this!

    It would be so good to have. 

    DS, great idea. Great job explaining it so clearly.

  • Thanks for posting and commenting @DataSquirrel and @dsutclif!

    Please vote this one up so we can get it assigned to Engineering for review.

     

    Regards,

    Dani

    Dani aka "Mr.Dojo"

    Dojo Admin
    **Say "Thanks" by clicking the "heart" in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
    **You can update your Dojo Community name and avatar by clicking on your avatar then the "My Profile" button.
  • Well done! This is a no-brainer and I'm surprised that this is still not developed!


    @DataSquirrel wrote:

    Domo has an ETL Edit Columns action called "uncollapse columns", what I'd call a "pivot". This feature works, but it requires manual configuration. In cases where the DataSet includes a clear category (split/break/name) column and a clear value column, please add an auto-pivot option. Instead of having to manually describe dozens of specific labels (category/split/break/name) values, have Domo figure it out. You would still want the existing uncollapse feature, but the auto-uncollapse would simplify pivoting name-value-pair data.

     

    More details. Starting from the existing feature, here's the picture from the docs. Starting with this input:

     

    etl_rowdenormalizer_datain.png

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Split the name-value pairs out into distinct columns/facts like this:


    etl_rowdenormalizer_dataout.png

     

     

     

     

     

     

     

     

     

     

    There are five steps in this process:

     

     

    etl_rowdenormalizer_sampleui.png

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    This process works, but it is a non-starter if you've got a lot of categories or dynamic categories. Please add an ETL feature that automates this process using only three configuration settings: LabelKeyValue. These are the three basic elements any pivot requires, and you've got them set up beautifully in Domo already. (It's very nice that you anticipate keys that are defined by combining several columns.) 

     

    Notice that all of the configuration in the bottom three steps can be automated completely. The label value is always from the Product column, the identifying key isalways from the Month column and the value isalways from the Sales column. Here's a mock-up of the auto-pivot setup:

     

    pivot_auto.png

     

     

     

     

     

     

     

     

     

     

     

    That's it. Domo then has to:

    1. Find the distinct values in the first (label) column. These define the pivot columns.
    2. Find the distinct rows in the second (key) column(s). These define the pivot rows.
    3. Copy the values in the third (value) column into the column and row they match.

    I assume that if you do this that you should do something similar for unpivot, but I havne't needed it or thought it through.

     

    Thank you.


     

This discussion has been closed.