0

I have a galaxy schema that looks like this galaxy schema

what I want to extract from this schema is data from dim1, dim 2, fact1 and dim 4. I have written this code but I am not sure if it is correct. lets suppose that dim1 has d1 value, dim2 d2 , fact1 f1 and so one and their primary keys are dim1id, dim2id and so one.

My code: **

select d1,d2,f1, d4 
from fact1
join dim1 on fact1.dim1id=dim1.dim1id
join dim 2 on fact1. dim2id=dim2.dim2id
join fact2 on fact2.dim2id=dim2.dim2id
join dim4 on fact2.dim4id=dim4.dim4id

**

David Browne - Microsoft
  • 80,331
  • 6
  • 39
  • 67
rexhi
  • 1
  • 4
  • Does your code give the correct result? If it does then, presumably, it is correct. For someone else to tell you if it is correct you would need to provide sample data for your tables, the result you want to achieve and an explanation of any logic that needs to be applied – NickW Aug 14 '22 at 19:00
  • Thank you for the response, well i am using athena db and it says time out since the real select query is very long, is there a way to simplify the select statement since there are many colums from many dim that need to be extracted. – rexhi Aug 14 '22 at 19:07
  • A couple of thoughts 1) you don’t normally join fact tables, you query them (and their related dims) independently and then combine the resultsets 2) You aren’t applying any filtering so your query is selecting every fact record and every associated dim record - is this really want you want to do ? If it is then there is really no scope for tuning your query, especially if you are using Athena – NickW Aug 14 '22 at 21:39
  • The two fact tables have different dimensionality (different unique key), so including it in the join would cause elimination or duplication of rows from Fact1. You need to explain the all keys/dimensions – tinazmu Aug 14 '22 at 22:20
  • @NickW lest suppose that i write a query for each fact table and its dimension how do i combine them, i cant use union. – rexhi Aug 15 '22 at 14:53
  • You can normally only do this if you limit the queries to using the same subset of dimensions and then you can use UNION or join on the dimension values. However, without knowing your data and what you are trying to achieve, this question is unanswerable. Consider each fact and its associated Dims as a logical dataset, how you combine 2 or more such datasets into a result that actually makes business sense is something only you can know - there are plenty of ways of combining them that are technically possible but give a result that is meaningless – NickW Aug 15 '22 at 16:47

0 Answers0