Creating a Dataset

user021892

Hi All,

 

I have been testing/trialling a lot of Business Intelligence and Domo is the only BI that has instantly clicked with me. I think this is the BI which would automate a lot of work and provide smarter reports. 

 

I want to know what would be the best practice to join multiple sales reports - these needs to formatted. We get reports from the marektplace in spreadsheet format and I convert into Google Sheets. We will use Google Sheets as connection. Each reports have different columns and want to combine into a one data set. To create a consolidated sales figures. 

 

From each reports, I will need below columns. I will have to create and calcuate some of the columns. Do I upload raw datas then use ETL to format each reports in below columns, and then join each sales reports using appended row? 

 

Date

Fiscal Week

Channel

EAN Code

Style

Season
Category

Gross Sales

Returns Sales

Net Sales

Sales Units

Return Units

Reported Sales

Unit Cost

Cost of Sales

 

GrantSmith

Hi @user021892 - Welcome to the Dojo!

 

Your data sources and your use case will determine how you want to combine your datasets together.

 

Lets say for simplicity sake you have two different datasets with the following data:

 

Sales:

Date	Sale	Quantity
1/1/2020	5000	100
2/1/2020	7500	150
3/1/2020	2500	50

 

Revenue:

Date	Revenue
1/1/2020	1000
2/1/2020	1250
3/1/2020	250
4/1/2020	1000000000

 

 

If you utilize an APPEND the two datasets in Magic ETL it will automatically determine the different columns and allow your resulting dataset to include all columns (you can change it to only include columns found in one of the datasets). The resulting dataset would look like:

 

Date	Amount	Quantity	Revenue
1/1/2020	5000	100
2/1/2020	7500	150
3/1/2020	2500	50
1/1/2020			1000
2/1/2020			1250
3/1/2020			250
4/1/2020			1000000000

 

 

If you utilize a JOIN instead it will combine the data width wise where the column you're joining on (in this date Date) match across the two tables (This is considered an INNER JOIN, there are also LEFT JOIN and RIGHT JOINs which will return all data in the left table or right table respectively irregardless if there's a matching record in the opposite table.)

 

For more information here's a link to a tutorial describing the different SQL JOIN types: https://www.w3schools.com/sql/sql_join.asp

 

JOIN dataset:

Date	Amount	Quantity	Revenue
1/1/2020	5000	100	1000
2/1/2020	7500	150	1250
3/1/2020	2500	50	250

 

(4/1/2020 is missing because it doesn't exist in both tables and we're doing an INNER JOIN)

 

 

Again, this depends on the structure of your data and the resulting format you're wanting the data to be in but in summary:

APPEND (UNION) = High

JOIN = Wide

 

If you have the same columns (and importantly - they represent the same data) then you'd likely want to utilize an append.

 

As a side note you might want to look into the large selection of data connectors that Domo offers to see if you can utilize one of those to pull your marketplace data instead of having to manually convert it to a Google Sheet.

GrantSmith

Hi @user021892 - Welcome to the Dojo!

 

Your data sources and your use case will determine how you want to combine your datasets together.

 

Lets say for simplicity sake you have two different datasets with the following data:

 

Sales:

Date	Sale	Quantity
1/1/2020	5000	100
2/1/2020	7500	150
3/1/2020	2500	50

 

Revenue:

Date	Revenue
1/1/2020	1000
2/1/2020	1250
3/1/2020	250
4/1/2020	1000000000

 

 

If you utilize an APPEND the two datasets in Magic ETL it will automatically determine the different columns and allow your resulting dataset to include all columns (you can change it to only include columns found in one of the datasets). The resulting dataset would look like:

 

Date	Amount	Quantity	Revenue
1/1/2020	5000	100
2/1/2020	7500	150
3/1/2020	2500	50
1/1/2020			1000
2/1/2020			1250
3/1/2020			250
4/1/2020			1000000000

 

 

If you utilize a JOIN instead it will combine the data width wise where the column you're joining on (in this date Date) match across the two tables (This is considered an INNER JOIN, there are also LEFT JOIN and RIGHT JOINs which will return all data in the left table or right table respectively irregardless if there's a matching record in the opposite table.)

 

For more information here's a link to a tutorial describing the different SQL JOIN types: https://www.w3schools.com/sql/sql_join.asp

 

JOIN dataset:

Date	Amount	Quantity	Revenue
1/1/2020	5000	100	1000
2/1/2020	7500	150	1250
3/1/2020	2500	50	250

 

(4/1/2020 is missing because it doesn't exist in both tables and we're doing an INNER JOIN)

 

 

Again, this depends on the structure of your data and the resulting format you're wanting the data to be in but in summary:

APPEND (UNION) = High

JOIN = Wide

 

If you have the same columns (and importantly - they represent the same data) then you'd likely want to utilize an append.

 

As a side note you might want to look into the large selection of data connectors that Domo offers to see if you can utilize one of those to pull your marketplace data instead of having to manually convert it to a Google Sheet.

user021892

Thank you for the detailed explanation. I have managed to create a neat combined data file. But still think it could be improved.  See below a picture of the lineage.