When creating a STAR schema using child datasets, even when I don't pick a field from a dimension, the underlying query still joins the dimensions that are not used in my analysis

Question & Answer
, ,
1

when creating a STAR schema using child datasets, even when I don’t pick a field from a dimension, the underlying query still joins the dimensions that are not used in my analysis
For instance in the below query
/ QuickSight 1ac88e67-7b6a-4b8f-a0b6-d9eacca6f6cf {“partner”:“QuickSight”,“entityId”:“98d8e8df-056c-404e-ad96-3d3b38ef7954”,“sheetId”:“98d8e8df-056c-404e-ad96-3d3b38ef7954_bcdfc249-c282-49ba-8dfd-b6237f817c93”,“visualId”:“98d8e8df-056c-404e-ad96-3d3b38ef7954_095ffdc9-bdff-49cc-b9f1-b86715af28c4”} /
SELECT
date_trunc(
‘day’,
“t0”.“b13c8bb5-3570-3451-bda4-1302833679d3”
) AS “af395367-b093-3c4d-a239-0a329f480ffa”,
COUNT(*) AS “count”,
COUNT(
DISTINCT CASE
WHEN “t”.“5418de73-055a-32fa-9717-c3228d25f8d4” > 0 THEN (
COALESCE(
CAST(
“t”.“8a858b77-debc-3de9-836d-8000873aa49b” AS VARCHAR
),
‘’
) || COALESCE(
CAST(
“t”.“09403179-5f47-3ca1-91f4-ed9461bb7d4d” AS VARCHAR
),
‘’
)
)
ELSE NULL
END
) AS “851ecfab-6193-306e-82d2-5d97aae29569”,
COUNT(
DISTINCT CASE
WHEN “t”.“94cad614-0bb7-3737-942e-89dca976ee36” > 0 THEN (
COALESCE(
CAST(
“t”.“4c2053ef-6a2b-37b8-aba2-39a14511123e” AS VARCHAR
),
‘’
) || COALESCE(
CAST(
“t”.“09403179-5f47-3ca1-91f4-ed9461bb7d4d” AS VARCHAR
),
‘’
)
)
ELSE NULL
END
) AS “18c2526d-149a-34f8-813b-ba8905287221”
FROM
(
SELECT
“company_id” AS “9a2fbf2e-21d5-367f-ba3e-4b3c8b419276”,
“date_id” AS “8863520e-c9bc-3396-b2e1-428e626aaced”,
“asset_id” AS “8a858b77-debc-3de9-836d-8000873aa49b”,
“session_id” AS “09403179-5f47-3ca1-91f4-ed9461bb7d4d”,
“web_page_id” AS “4c2053ef-6a2b-37b8-aba2-39a14511123e”,
“page_view” AS “94cad614-0bb7-3737-942e-89dca976ee36”,
“content_preview” AS “5418de73-055a-32fa-9717-c3228d25f8d4”
FROM
“public”.“ft_event”
WHERE
“company_id” = 1.0
) AS “t”
INNER JOIN (
SELECT
“id” AS “7d09f009-d692-3910-b3ae-39b53b85cc3b”,
“date” AS “b13c8bb5-3570-3451-bda4-1302833679d3”
FROM
“public”.“dim_date”
) AS “t0” ON “t”.“8863520e-c9bc-3396-b2e1-428e626aaced” = “t0”.“7d09f009-d692-3910-b3ae-39b53b85cc3b”
INNER JOIN (
SELECT
“id” AS “be4ac7a6-abaf-3d76-babf-a19adea082a2”,
“company_id” AS “b1b47180-5c03-3d1e-9f78-cb62655d455a”
FROM
“public”.“dim_asset”
WHERE
“company_id” = 1.0
) AS “t1” ON “t”.“8a858b77-debc-3de9-836d-8000873aa49b” = “t1”.“be4ac7a6-abaf-3d76-babf-a19adea082a2”
AND “t”.“9a2fbf2e-21d5-367f-ba3e-4b3c8b419276” = “t1”.“b1b47180-5c03-3d1e-9f78-cb62655d455a”
GROUP BY
date_trunc(
‘day’,
“t0”.“b13c8bb5-3570-3451-bda4-1302833679d3”
)
ORDER BY
date_trunc(
‘day’,
“t0”.“b13c8bb5-3570-3451-bda4-1302833679d3”
) DESC NULLS LAST
LIMIT
2500
I am using only necessary fields from the table dim_date then why dim_asset is also considered in the query?

2

Hello @Jyotica, welcome to the QuickSight community!

I may need a little more information on what the issue is for me to help you further. I am looking at the SQL statement you sent, and I see that you are using an INNER JOIN to bring in the date field from the dim_asset table and joining on the ID from that table. Are you receiving more fields from that table besides the date field that you are selecting in your query?
ex. date_trunc(‘day’, “t0”.“b13c8bb5-3570-3451-bda4-1302833679d3”) AS “af395367-b093-3c4d-a239-0a329f480ffa”
Or is it that when you use this dataset in an analysis and do not utilize the date field that you are bringing it, that it still runs the join in the SQL? With a little more information, I should be able to assist.

3

Hey @DylanM
Yes, the problem is when using the dataset in my analysis even when I am not using a field from the dataset it still runs the joins in the SQL…

4

Hello @Jyotica, are you running your datasets on Direct Query? I believe even if they are not used in the visuals on the sheet, they will run if they are on that analysis to bring in recent data. You may want to consider using SPICE for your datasets, so the data is stored in QuickSight and the query doesn’t run on your database every time you load the visuals.

5

Hello @Jyotica, since we have not heard back from you in a few days, I will go ahead and mark my above response as the solution. If you still need assistance on this, please follow-up with some more information so I can guide you further. Thank you!