Hello reaching out as I have two data sets which I am trying to join at 3 points, batch ID, Dates, and building name(FC). at the current state only if I use one connection to the building names does any data populate, once I add the other two clauses for the join it does not. the format of the dates and the naming convention for the batch ID are the same on both . attaching some of the snips below .
Hello @nellsarg !
The feature you are using is nice for quick joins in QuickSight, but I have found it easier to create a dataset with Joins via a Custom SQL query:
@duncan Hello, I have 4 tables on this, when I try to use a custom query with a join, no data gets loaded even if the query after some time but loads in my SQL program. so that is not a solution in my case because nothing gets loaded.
Hi @nellsarg - Thanks for posting the question. In case of custom sql, can you please check the sql execution in the database via a client tool and check the performance of the sql. What is your data source ( is it my sql/ postgres or redshift). You can always optimize the query and then put that custom sql in the quicksight. Also please ensure you do not have cartesian join the data sets or else it will return billion of rows which in turn affect the performance.
For multiple tables join, custom sql is one of the good approach.
Regards - Sanjeeb
1 Like
Hey Sanjeweb, thank you for the response, I have both of these are custom sql pulls, they are pulling from fcifinance (MySQL) data. both queries separately pull items without an issue, when joined either view QS or joined in a queries that when the problems bgin. Although I made a joined custom query for them when loaded into sql it doesnt produce results although it does in HeidiSQL. when I separate them it does not get joined in QS either.
Hi @nellsarg - Thanks for the details. If I understood correctly, you have 2 custom sqls which is able to pull the data correctly but when you do a join, it is taking time or no results. Can you please check whether you are able to join in both custom sqls via a client tool using CTE like below
with table1 as ( << your first custom sql>>),
table2 as ( << second custom sql>>)
select * from table1, table where << joining condition>>
Also check whether you have m:m ( many to many) relations on the joining conditions that leads to huge amount of data and its taking time.
First check the details outside QuickSight to ensure there is no issue in sqls.
Regards - Sanjeeb
1 Like
Hi Sanjeeb, I have tried and it works fine outside of QS in heidi SQL, but if I use the full custom query in sql, it does not load any data and fails to refresh .
Hi @nellsarg - Ok, can you run the custom sql by the below way
with temp as
(<< put the custom sql>>)
select count(1) from temp
Please provide the execution time of the above query. This provide the exact time the query took to run the statement and give the count. Also what is the data volume.
If you are getting the result within 2 to 3 mins, QS should work for you. For deeper analysis, possibly better to raise a ticket to AWS customer support team so that they can extract QS logs and see why the query is not providing the result. To submit a case, please follow the below link -
Regards - Sanjeeb
1 Like
One thing you can try is create your first table (DAT_FIXED_COSTS) as a separate dataset first. Check that both datasets (DAT_FIXED_COSTS and DAT_PPR_DATA_V5) work independently. Then, join the two datasets to create a third dataset.
1 Like
Hello @nellsarg !
Were you able to try @Sanjeeb2022 or @David_Wong 's suggestion? If one one them solved your problem, could you mark their comment as the solution to help the QuickSight community?