Hi,

I'm trying to create a cube filtered on a certain date range. I have the
datekey in the fact table linked to the actual dates in a Time dimension
table.

The filter I have set up in the partitions are as follows:
"fact_table"."servicedatekey" in
( Select "TimeKey" from "Time_Dim"
Where "Time_Dim"."Date" >= '01/01/2004'
)

The reason I have partitions is because I'm slicing each partition on a
separate division. But when I process a partition, I get the following error
message:

"Data source provider error: Parameter Information cannot be derived from
SQL statements with sub-select queries. Set parameter information before
preparing command.;42000; Time:10/8/2004 1:53:40 PM"

Any help would be greatly appreciated!!


Note that when I directly run the SQL query that gets generated during cube
processing in query analyzer, that runs fine. The query is as follows:

SELECT "dbo"."fact_table"."NetPayRangeKey",
"dbo"."fact_table"."ClaimTypeKey", "dbo"."fact_table"."LPIKey",
"dbo"."fact_table"."ModifierKey", "dbo"."fact_table"."ProcCodeKey",
"dbo"."fact_table"."ProviderNameKey",
"dbo"."fact_table"."ProviderNumberKey", "dbo"."fact_table"."RevenueCodeKey",
convert(CHAR,"Service Date"."Date", 112), "dbo"."fact_table"."Region",
"dbo"."ProviderZip_Dim"."ProviderZipKey",
"dbo"."ProviderSpecialty_Dim"."tiProviderSpecialty ",
"dbo"."fact_table"."ClaimNumber", "dbo"."fact_table"."Charges",
"dbo"."fact_table"."NetPay", "dbo"."fact_table"."GrossPay",
"dbo"."fact_table"."siUnits" FROM "dbo"."fact_table", "dbo"."Time_Dim" AS
"Service Date", "dbo"."ProviderZip_Dim", "dbo"."ProviderSpecialty_Dim" WHERE
("fact_table"."servicedatekey" in
( Select "TimeKey" from "Time_Dim"
Where "Time_Dim"."Date" >= '01/01/2004'
)) AND ("dbo"."fact_table"."DivisionNameKey"=?) AND ("Service
Date"."TimeKey"="dbo"."fact_table"."ServiceDateKey ") AND
("dbo"."fact_table"."ProviderZipKey"="dbo"."Provid erZip_Dim"."ProviderZipKey
") AND
("dbo"."fact_table"."PrimarySpecialtyKey"="dbo"."P roviderSpecialty_Dim""tiPr
oviderSpecialty")


Thanks in advance,
Chumma Dede

One solution is to incorporate the filter criteria in a derived fact
table column. For example, use a SQL view:

fact_view>>
select ft.*, td."Date"
from "fact_table" ft
join "Time_Dim" td
on ft."servicedatekey" = td."TimeKey"

fact_filter>>
"fact_view"."Date" >= '01/01/2004'

- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Deepak, thanks for the suggestion. I was just wondering, how is this better
than the fact_view directly filtering out the records on the date range and
not using filters in the cube at all?

Thanks,
Chumma

"Deepak Puri" <deepak_puri (AT) progressive (DOT) com> wrote

Hi Chumma,

The benefit lies in being able to use the same view to define different
subsets of data. For example, I use such a view to filter a subset of
fact records for cube incremental update, using the previous day's date.


- Deepak

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!