Complex join issue

Hi There,
I'm having problem creating the following output:
Fact Table 1 (Sales)
--
Date_Key smallint,
Emp_Key smallint,
SalesNbr smallint,
SalesAmnt money
Fact Table 2 (Attendance)
--
Date_Key smallint,
Emp_Key smallint,
ScheduledTime int,
Attendance int
Dim Table #1 (Date_Dim)
--
Date_Key smallint,
Calendar_Date smalldatetime
Dim Table #2 (Emp_Dim)
--
Emp_Key smallint,
Dept_Id smallint
... ...
What i'm tring to do is to join the two fact table with the two dim
table knowing that for one particular day, it is possible that an
employee have a record in the sales table but not in the attendance
table and vice-versa...
I've found a workaround but it's veeeeery slow:
select into #temp
from (
Select Date_Key, Emp_Key from Sales group by Date_Key, Emp_Key
Union select Date_Key, Emp_Key from Attendance group by Date_Key,
Emp_key ) q
select emp_dim.Dept_Id, sum(salesnbr) as Sales,
sum(Attendance)/isnull(sum(attendance),1) as %Attendance

>From #temp t
inner join Emp_Dim on Emp_Dim.Emp_Key = t.Emp_Key
inner join Date_Dim on Date_Dim.Date_Key = t.Date_key
left outer join Sales on Sales.emp_key = emp_dim.emp_key AND
Sales.date_key = date_dim.date_key
left outer join Attendance on Attendance.emp_key = emp_dim.emp_key AND
Attendance.date_key = Date_Dim.Date_key
So I need to speed things up by eleminiting the need for a "middle ware
table" as my middle table is now 476 000 rows the performance of the
system decrease day by day.
Thanks!Hello, Mathieu
Try something like this:
SELECT e.Dept_ID, SUM(x.salesnbr) as Sales,
SUM(x.Attendance)/ISNULL(SUM(x.Attendance),1) as AttendancePercent
FROM (
SELECT ISNULL(s.Emp_Key, a.Emp_Key) as Emp_Key,
ISNULL(s.Date_Key, a.Date_Key) as Date_Key,
s.SalesNbr, a.Attendance
FROM Sales s FULL OUTER JOIN Attendance a
ON s.Emp_Key=a.Emp_Key AND s.Date_Key=a.Date_Key
) x
INNER JOIN Emp_Dim e ON e.Emp_Key=x.Emp_Key
INNER JOIN Date_Dim d ON d.Date_Key=d.Date_Key
The query is untested. Please provide DDL, sample data and expected
result; see: http://www.aspfaq.com/etiquette.asp?id=5006
Also, I don't think that is a good idea to have a Date_Key column that
reffers to another table; instead, you should put the smalldatetime
values in the Sales and Attendance tables.
Razvan|||That is the proper way to build a data warehouse, though I hope that they
actual date dimension has more than these two columns or it is really not
all that useful.
The real problem (and one that I run into all of the time) is using SQL to
query dimensional data is generally painful and often slow. OLAP is the way
to go 99% of the time.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1129658679.291245.248730@.f14g2000cwb.googlegroups.com...
> Hello, Mathieu
> Try something like this:
> SELECT e.Dept_ID, SUM(x.salesnbr) as Sales,
> SUM(x.Attendance)/ISNULL(SUM(x.Attendance),1) as AttendancePercent
> FROM (
> SELECT ISNULL(s.Emp_Key, a.Emp_Key) as Emp_Key,
> ISNULL(s.Date_Key, a.Date_Key) as Date_Key,
> s.SalesNbr, a.Attendance
> FROM Sales s FULL OUTER JOIN Attendance a
> ON s.Emp_Key=a.Emp_Key AND s.Date_Key=a.Date_Key
> ) x
> INNER JOIN Emp_Dim e ON e.Emp_Key=x.Emp_Key
> INNER JOIN Date_Dim d ON d.Date_Key=d.Date_Key
> The query is untested. Please provide DDL, sample data and expected
> result; see: http://www.aspfaq.com/etiquette.asp?id=5006
> Also, I don't think that is a good idea to have a Date_Key column that
> reffers to another table; instead, you should put the smalldatetime
> values in the Sales and Attendance tables.
> Razvan
>|||You're right... I've only written a simplified version of my problem...
my Date_Dimension is the following:
CREATE TABLE [Date_Dim] (
[Date_Key] [smallint] NOT NULL ,
[Calendar_Date] [smalldatetime] NULL ,
[Calendar_Year_No] [smallint] NULL ,
[Calendar_Quarter_No] [tinyint] NULL ,
[Calendar_Month_No] [tinyint] NULL ,
[Calendar_Month_Name_E] [nvarchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Calendar_Month_Name_F] [nvarchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Calendar_W_Of_Year_No] [tinyint] NULL ,
[Calendar_Day_Of_Month_No] [tinyint] NULL ,
[Calendar_Day_Of_W_No] [tinyint] NULL ,
[Calendar_Day_Name_E] [nvarchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Calendar_Day_Name_F] [nvarchar] (9) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Calendar_Stat_Holiday_Flag] [bit] NULL ,
[Calendar_Wday_Flag] [bit] NULL ,
[Calendar_Last_Day_Of_Month_Flag] [bit] NULL ,
CONSTRAINT [PK_Date_Dim] PRIMARY KEY CLUSTERED
(
[Date_Key]
) WITH FILLFACTOR = 90 ON [PRIMARY]
I think I cannot work with Razvan Solution cause instead of 2 fact
table, the real system work with like 15 fact table, grouping and
reporting over a dozen resuls set from different application...