Best Of

Re: Best Practice: ETLs Vs SQL ETLs

Isaiah,

I have not heard of a limitation of the number of input datasets (or seen it in the documentation), but you could probably ask your CSM to research that for you to get a firm answer. From a management standpoint, I would suggest breaking your ETLs into smaller ETLs, so it is easier to troubleshoot if something goes wrong. Sifting through a single ETL with that much going on could prove to be quite challenging.

From a performance standpoint, Magic ETL will outperform SQL ETL by a longshot. I had a SQL ETL that I converted to Magic ETL and the runtime went from 30 minutes to 30 seconds. I also like the notes feature in Magic ETL so I can comment on each tile and explain what its purpose is.

Magic's limitations are around complex joins and looping. You can usually get around the complex join by adding a filter tile after your join remove records. The looping can be more challenging.

If you haven't read this KB article on recursive dataflows in the new Magic ETL, this could be helpful as well.

https://domohelp.domo.com/hc/en-us/articles/360057087393-Creating-a-Recursive-Snapshot-DataFlow-in-the-New-Magic-ETL

Hope this helps.

MarkSnodgrass

4

Re: Is there an API for viewing and manipulating DataSet tags?

@mhouston Thanks so much for all of your help. You've helped me get going in the right direction and I've got a path forward. Thanks!

jlrosenlof

1

Re: Beast Mode - Multiple functions

Thanks Mark, I followed point 1. I didn't need the second beast mode, I just needed the graph setting in the date filter.

BTPG

1

Re: Period over Period (MOM/YOY) option in Multi-Value card for 2 months prior

You can use the DATE_SUB function to subtract different intervals at a time, such as month.

DATE_SUB

Subtracts date or datetime values (as intervals) to date values in a date column.

DATE_SUB('DateCol', interval 1 month)

In this example, if the value for DateCol was 2/27/2022, it would subtract 1 month and result in 1/27/2022. Now, if you needed to get to the last day of the month if the month you just subtracted to, you can wrap the LAST_DAY() function around the Date_Sub function. This would make 1/27/2022 end up being 1/31/2022.

LAST_DAY(DATE_SUB('DateCol', interval 1 month))

Let me break down a part of my first example in case that might help you as well.

DATE_SUB(CURRENT_DATE(), INTERVAL (DAYOFMONTH(CURRENT_DATE()) - 1) DAY)

There are several functions wrapped together here.

CURRENT_DATE() is returning today's date whenever the card is viewed.

DAYOFMONTH(CURRENT_DATE()) - 1) is returning the day of today's date and reducing that by 1. So, if today is the 3rd, then this would return 2. This number is then used in the DATE_SUB function to subtract the number of days from today's date.

The result of the entire function returns the first day of the current month. So, right now, it would return 3/1/2022.

MarkSnodgrass

4

Re: Heat map tables - Ranges per column

If you are using the Heatmap Table card under the Tables and Textboxes, you can set the heatmap to be on an individual column basis. Under Chart Properties, go to Scale and select Independent Column Ranges.

MarkSnodgrass

4

Re: Period over Period (MOM/YOY) option in Multi-Value card for 2 months prior

@NathanDorsch with your concatenation:

Date(concat(
case when Month(`Date`) > 1 then Year(`Date`)
when Month(`Date`) = 1 then Year(Date) - 1
end,
'1',
case when Month(`Date`) > 1 then Month(`Date`) - 1 
when Month(`Date`) = 1 then 12
end
  ))

A date of today would return 202213. The date() function is going to have a hard time converting that to a date.

If you want to go this route, I would change it to:

Date(concat(
  
case when Month(`Date`) > 1 then Month(`Date`) - 1 

when Month(`Date`) = 1 then 12

end
 ,

'/1/',

case when Month(`Date`) > 1 then Year(`Date`)

when Month(`Date`) = 1 then Year(Date) - 1

end
 ))

MarkSnodgrass

2

Re: Is there any way to find how many times a dataset/dataflow has failed?

Use an ETL to group it based on the ID and the status and then count the IDs. This will break down your dataset based on the statuses and give you a count. You can filter out then for specific codes

Alternatively if you're using a beast mode you could do a conditional sum:

SUM(CASE WHEN `Status` = 'Failed' THEN 1 ELSE 0 END)

GrantSmith

1

Re: Is there any way to find how many times a dataset/dataflow has failed?

It depends on how your company wants to define success but I'd recommend 'Invalid' and 'Success'

SUM(CASE WHEN `Status` IN ('Error', 'Invalid') THEN 1 ELSE 0 END)

GrantSmith

1

Re: Is it possible to create a combo chart in the tiles?

Domo calls combo charts Line + Bar charts. In a standard implementation, the value put in the y-axis becomes the line and then the series becomes the bar. You can have multiple lines by entering a value in the General chart properties "series on left scale". It will then use the y-axis and the first item in the series if you entered 2, for example.

I created a video about this that may help you.

https://youtu.be/JlTJ6z401r4

Hope this helps.

MarkSnodgrass

2