Example with Aggregated Worksheet to Table Join

Curious if anyone has an example of a business case that requires aggregated worksheet to table join. We currently do not have this functionality enabled to test ourselves, but I still want to see the an example and the resulting data in real world application. 

Thanks!

8replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • One additional callout - I have a use case that calls for joining aggregate worksheet to a table, creating a worksheet, and having interactive pinboard with filters built on top of the worksheet. 

    Reply Like
  • Any updates or examples from TS Support Pankaj Ahire ?

    Reply Like
  • Here is an example of Aggregated Worksheet to Table joins - what were the top 5 account of last year and how are they doing this year. To do that you will do one aggregated worksheet

    account sales last year top 5

    Then you will join it to the sales fact table on account key and get the current year's sales.

    Reply Like
  • Thanks Siva Singaram - can the results of the join be leveraged for a worksheet?

    Reply Like
  • Yes. The aggregated worksheet and the fact table can be added to a worksheet. The only reason that we don't allow it per se is the performance hit that such joins have.

    Reply Like
  • Thanks Siva Singaram - I keep asking to have it enabled so we can test and understand if it solves our business case and what are the performance impacts. I want to know if TS can handle the logic or if we are going to have to request an etl transformation (which we do not want to do). 

    Reply Like
  • The joining of the aggregated worksheet to base tables works with good performance only under the following conditions:

    1. Number of rows in the aggregated worksheet is small (~1000) and 

    2. Number of tables involved in the aggregated worksheet is not high (<5)

    For other cases, you will need to do an ETL.
    Starting in 4.4, we offer ELT capability in the product where you can do a CTAS (create table as select) in the backend to create large tables which can then be joined to other tables - this will perform very well.

    Reply Like
  • Thanks Vijay Ganesan - understandably we want to avoid ETL for use cases where a BI Tool's analytical/calculation engine should be able to handle it. We are looking for the first sale date tied to an account (this would be the aggregated worksheet). From there we want to join this aggregated worksheet to a Order Sales table, so we can flag if an account is new or existing based on first sale date vs consequent sale dates for a given time frame. 

    Reply Like
login to reply
Like Follow
  • 11 days agoLast active
  • 8Replies
  • 145Views
  • 4 Following