What kind of joins are these? Inner or Outer joins? I don't normally do a complete loop. Having said that, I don't know your data or what your requirements are and why you have opted for this join pattern.
If the join returns empty table, depending on the type of join, it means that one of the tables is possibly empty or there is no match between the fields you are joining the tables on. Just wondering why you are joining both Fact1 and Fact2 to Dim_org_structure. Have you tried removing the Fact2 to Dim_org_structure join and see what that gives you?
Thank you for the reply.the joins will be inner joins and the reason of this type of join is because Dim_date and dim_org_structure both are confirmed dimensions,hence I have joined like this to both the facts.
and Dim_org_structure is further joined with one more table dim_org_hierarchy.
I know this is not a star not a snowflakes but this is the structure of tables and i need to build reports on top of this.
Please suggest a way .
Thanks in advance.
What I'm trying to do is to guide you to discover why the join is returning empty table.
Since, you are doing an inner join, all the fields must match before a result can be returned. Check all the fields used in joining each table to make sure there are matching records in the tables in either side of the join.
The next step will be to start rebuilding the join incrementally. Add just one table then check the result if data was returned,carry on and add the next join, then stop and check if data was returned. Carry on adding each join one at a time and checking until you get to the offending table causing the problem, then you can figure out what is going on. Basically, you need to do some detective work with this kind of problem.