AS2K, SP3A

There seems to be a problem with using the
Source Table Filter on a Dimension build....

1...IF you wish to filter on a table other than the one
used to build the Dim levels from you have to create a
join, ok understand that...

you can then use a fragment to filter upon a value comming
out of this other table, using the Source Table filter.

Problem Scope ,
First the other table requiring this link to a table used
to build the dim , so you have to join 2 keys

Second if you use the Source table filter using a
different key

The sql generated for the Dim, puts the Filter and the
joinkey together, THIS IS THE PROBLEM.

If you have to join the tables up, because its a
requirement, then the SQL logic cannot be used its not
logical, because the joinkey is there for being there
sake, and not a true condition, it shouldn't belong in the
where fragment.

sample

Dimension 'Predictive ' Execute : SELECT DISTINCT DatePart
(year,Case when (DatePart
(month, "dbo"."OPFPredictiveDates"."Date") < 4 or (DatePart
(month, "dbo"."OPFPredictiveDates"."Date") = 4 and DatePart
(day, "dbo"."OPFPredictiveDates"."Date") < 1))
then "dbo"."OPFPredictiveDates"."Date" Else DateAdd(year,
1, "dbo"."OPFPredictiveDates"."Date")
End ), "dbo"."OPFPredictiveDates"."Year", ( DatePart
(year,"dbo"."OPFPredictiveDates"."Date") * 100) + DatePart
(month,"dbo"."OPFPredictiveDates"."Date"), 'Patient Wait
Calculation to End of : '
+ "dbo"."OPFPredictiveDates"."FMonth"
FROM "dbo"."OPFPredictiveDates", "dbo"."OPF_CensusDate"
WHERE ("dbo"."OPFPredictiveDates"."Date"
LastCensusDate")

Source table filter
("dbo"."OPFPredictiveDates"."Date"


The latter part of the Fragment is the join on DIM Tables
shouldn't be part of the where but is,

AND
("dbo"."OPFPredictiveDates"."Date"="dbo"."OPF_Cens usDate"."
LastCensusDate")

, I know work around is in the RDB, but since the source
fragment is available its only natural you would want a
filter to cross many tables

Any body throw further light around this area, is it a
restriction....


Regards
SP

If you have complex view filtering requirements, then I typically recommend
that you use a view to hide the specifics from Analysis Services. We can
load from a view just as easily as a physical table.

Without knowing the exact semantics of the data, it is hard for Analysis
Services to adjust its join techinques for all possible situations. So we go
for the obvious ones.

If you don't fit within the limits of this technique, as it seems like here,
then use a view and hide all of the specifics from us..

--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI Practices Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"sp" <sanjayp (AT) ardentia (DOT) co.uk> wrote

Thanks For Replying

Actually you can reference the data tables, you just
specify the Table reference in Source table filter,without
actually adding the table into the schema design....


Regards
SP