Creating a Dataset

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

 

Best Answer

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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:

    DateSaleQuantity
    1/1/20205000100
    2/1/20207500150
    3/1/2020250050

     

    Revenue:

    DateRevenue
    1/1/20201000
    2/1/20201250
    3/1/2020250
    4/1/20201000000000

     

     

    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:

     

    DateAmountQuantityRevenue
    1/1/20205000100 
    2/1/20207500150 
    3/1/2020250050 
    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:

    DateAmountQuantityRevenue
    1/1/202050001001000
    2/1/202075001501250
    3/1/2020250050250

     

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

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

Answers

  • GrantSmith
    GrantSmith Coach
    Answer ✓

    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:

    DateSaleQuantity
    1/1/20205000100
    2/1/20207500150
    3/1/2020250050

     

    Revenue:

    DateRevenue
    1/1/20201000
    2/1/20201250
    3/1/2020250
    4/1/20201000000000

     

     

    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:

     

    DateAmountQuantityRevenue
    1/1/20205000100 
    2/1/20207500150 
    3/1/2020250050 
    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:

    DateAmountQuantityRevenue
    1/1/202050001001000
    2/1/202075001501250
    3/1/2020250050250

     

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

    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • 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. 

     

     

    image.png

This discussion has been closed.