Best Of
Re: Date_Add or Date_Sub Trying to minus a year
@Ana I strongly recommend against building reporting logic into your connectors. reporting logic is something that would change regularly and should be something a little closer to the hands of the business analyst.
extract your data from your source system as cleanly as possible, then build any reporting functionality using dataflows, views or in analyzer.
Re: How do Dataflows impact Dataset Certification?
@Jack_Kaye certification (imho) was not a thoroughly baked feautre, and @GrantSmith already pointed out some of the shortcomings.
You could augment Certification with additional workflow steps. For example you could enable PDP on certified datasets. The upside there is that in order to create a dataflow on a dataset you must ahve access to all rows of the dataset. if PDP is enabled, people would not be able to construct downstream dataflows.
arguably you should not create ETLs on certified datasets (that really defeats the point!) so there's a win there.
if you're comfortable scripting in Python or Node.js it might be more expedient to build reports based on APIs
Re: Need help reviewing sql query used to create domo sql dataflow
in the kindest way possible ... if you are having difficulties writing SQL maybe you should consider implementing your dataflow as Magic 2.0 which doesn't require you to code in SQL.
- select distinct Customer from(
- select Customer, count(distinct SalesOrder) as Purchases from ArTrnDetail where InvoiceDate between '2022-01-01' and '2022-01-31'
- and OrderType = 'ST'
- and Customer NOT IN ( select Customer from ArTrnDetail where InvoiceDate not between '2022-01-01' and '2022-01-31')
- and ProductClass NOT IN('_FRT','_TAX','TAX2')
- and LineType <> '4'
- and Salesperson IN ('EPI','MGN','PH')
- Group By Customer having count(distinct SalesOrder)=1
- ) b
This query would be trivial in Magic 2.
break your two sub queries into two streams of Data.
Attach the appropriate FILTER tile.
JOIN the two datasets together where you have CUSTOMER NOT IN.
After the JOIN apply your GROUP BY clause and filter on having count_disticnt.
Although honestly I would recommend that you do your aggregation in Analyzer.
Re: Dynamic series line bar
@LCash i wouldn't recommend it, but you could put your product actuals into separate columns using PIVOT in Magic 2.0
it may seem counter intuitive but stacking the data (date, category, metric) would actually be the best solution , because if you had data that was not preaggregated (if you had multiple product x sales in one day) then you don't need your data to be compressed to a specific granularity as you do if your data is formatted (date ,category, actual,forecast)
Re: I am replacing power BI DAX formula into Beast Mode
@Arjuman in addition to posting raw code, it might be beneficial to describe what you're trying to accomplish and let the community know what shape your table is in.
it'll certainly help in cases when your SQL is bugged and you're looking for support trying to make it work.
(CASE when YEAR(`ResolvedDate`) = YEAR(CURRENT_DATE()) then SUM((CASE when YEAR(`ResolvedDate`) = YEAR(CURRENT_DATE()) then `DaysToResolution` else 0 end)) / COUNT(DISTINCT (CASE when `isBugResolved`= 1 then `JiraKey`else 0 end)) end)
in any case, you generally can't put an aggregate function, SUM, inside a CASE statement.
in @Godiepi 's response you'll notice all the aggregates are outside.
keep in mind 0 =!= NULL.
if you do a COUNT (DISTINCT case when ... else 0 end ) you'll include 0 as one of your counts.
ex. count(distinct of [a, b, 0 , 0] would be 3. That may or not be appropriate.
whereas count(distinct [a,b, null,null] would be 2.
Re: Dataset Upsert
yes, you do have to contact support / your CSM to have upsert available as a feature in your instance.
by default the feature is not 'turned on.'
once enabled, you configure upsert on a case by case basis.
by default datasets are set up as REPLACE when you run a connector.
UPSERT is a variation of APPEND.
might sound tedious, but if you can imagine, domo has no way of knowing what the unique row identifier for a dataset is.
also, unless your dataset is in the hundreds of millions of rows, it typically doesn't make sense to use UPSERT. UPSERT just like normal SQL does not support soft delete, so if there's a chance that rows of data are deleted from your dataset, you'd have to make a second manual process to remove the deleted rows. (upsert by definition can only insert or update).
Re: 12k beast modes and counting...
yikes! that sounds awful.
Unfortunately Domo doesn't really have strong tools for managing the creation of beast modes.
you'll notice that there are grants that help you limit who can create a calculation shared to a dataset. but once they are there ... getting rid of them can be a bit of a pain.
seems to me that training is the way to go. maybe use custom roles and make people earn the privilege of saving beast modes to datasets.
Re: Is there any way to schedule file uploads to Domo apart from Workbench?
@Raymond if you can get the files local you can use python + api's https://github.com/domoinc/domo-python-sdk
or more likely the java cli https://domohelp.domo.com/hc/en-us/articles/360043437733-Command-Line-Interface-CLI-Tool
Re: Amazon Athena Connector in Domo
@keeeirs10 ... domo support are correct but also not correct ;)
in Domo, to see the configuration of a dataset in Domo, the account that was used to configure the connector must have been shared with you. because it wasn't ... you're unable to see the configuration of the connector.
Domo are working on expanding functionality to make it easier to reassign connectors, but isn't a feature in product yet.
Regarding Amazon Athena, theoretically yes, it is possible to see the connections to a SQL database and see what query that connection is executing. I would start here: https://docs.aws.amazon.com/athena/latest/ug/query-metrics-viewing.html
Re: Beast Mode to remove values based on card selection?
@Joe_M it sounds like you're describing ... a normal filter card.
https://domohelp.domo.com/hc/en-us/articles/360043429313-Slicer-Checkbox-and-Radio-Button-Cards