I have a cube; and I have applied a source table filter (on one of the
dimension tables)

I do this so that I can have one DimDate table for different granularities--
for example; sometimes I need to join on DATE, and sometimes; I need to join
by month.

So when I am making a cube based off of the fact Table with the monthly
granularity; I add a source filter:
dbo.DIM_DATE.DAY = 1

I am getting cartesianing-- instead of 120k records; it is returning ~3.6m--
so I ran a sql trace; and the SQL statement doesn't include the additional
filter:
dbo.DIM_DATE.DAY = 1

My understanding is that this filter should be appended to the where clause.

What am I doing wrong?


SELECT
COALESCE("dbo"."BUDGET_CENTER"."IT_Group_Code", 'UNK'),
"dbo"."BUDGET_CENTER"."Department_Number",
CONVERT(INT, "dbo"."vwDIM_ACCOUNT"."Eplan_Group_Code"),
convert(int, "dbo"."vwDIM_ACCOUNT"."Eplan_SubGroupCode"),
convert(INT, "dbo"."vwDIM_ACCOUNT"."Account_Code"),
"dbo"."DIM_DATE"."YEAR",
"dbo"."DIM_DATE"."MONTH",
"dbo"."vwFACT_FINANCIAL_TRANSACTION"."Transaction_ Amount"
FROM "dbo"."vwFACT_FINANCIAL_TRANSACTION",
"dbo"."BUDGET_CENTER",
"dbo"."vwDIM_ACCOUNT",
"dbo"."DIM_DATE"
WHERE
("dbo"."BUDGET_CENTER"."Budget_Center_ID"="dbo"."v wFACT_FINANCIAL_TRANSACTIO
N"."Budget_Center_ID")
AND
("dbo"."vwDIM_ACCOUNT"."Account_ID"="dbo"."vwFACT_ FINANCIAL_TRANSACTION"."Ac
count_ID")
AND
("dbo"."DIM_DATE"."MONTH"="dbo"."vwFACT_FINANCIAL_ TRANSACTION"."Accounting_P
eriod")
AND
("dbo"."DIM_DATE"."YEAR"="dbo"."vwFACT_FINANCIAL_T RANSACTION"."Fiscal_Year")

Why would you need to join per month at some times....is it because you're
comparing one
set of data which is per month and another that is per day ?

You can just disable the day level in the cube - then the user won't see
this .... but
still be using it as the joining condition...

But I'm not quite sure what your business needs are to be performing this
task....

If its because you need to different time hierachies then I'm not the one to
advice you but I know that
out there somewhere is a description on how enabling to different time
hierachies on the same cube.

Fiscal year and calendar year...

/Michael v.

<aaron_kempf (AT) hotmail (DOT) com> wrote

I have some fact tables that use the month granularity and others use the
day granularity. Pretty straight forward.
Like, i have a budget table where there is a field MONTH and DATE (IE:
MONTH= 12, YEAR=2004)
And I have an expenses table where we have an EXPENSEDATE (IE: EXPENSEDATE=
'12/2/2004')

I only want to maintain one DimDate table-- whether I am building an
expenses cube (based off of the expenses table) or a budget cube (based off
of the budget table).

So when I build by Expenses cube, i just join to the date-- when I create
the Budget cube, I join on YEAR and MONTH.

I don't have a day level in any of my cubes. I don't have it in the
dimension-- i don't have it disabled-- it just isn't there. I just need to
be able to join to this DimDate table from FactTables with either a monthly
or a daily granularity.

And I know it would be nice to rework this into something that had been
scrubbed; that had integers everywhere-- but that isn't gonna happen on this
project.

-Aaron






"Michael Vardinghus" <michaelvardinghus (AT) hotmail (DOT) com> wrote

Hmmm....how about puting a view on top of the budget table that makes
a dummy-date out of month and year:

select *.dbo.budgettable, cast([year]+'-'+[month]+'-'+'01' as datetime) as
newdate
from dbo.budgettable

Then you should be able to map it to the date dimension table...?

/Michael V.

<aaron_kempf (AT) hotmail (DOT) com> wrote

well, i figured out a work-around..

If I add a column to the factTable view where DAY = 1 then i can join and
everything will be peachy again.

i'm just tired of dealing with unrealible products.. if it worked
yesterday-- the same code should work today.


-Aaron




<aaron_kempf (AT) hotmail (DOT) com> wrote

Because I don't want to do that-- it is more efficient to join on the
numeric datatype since it is smaller.

I'm just pissed that the Filter worked yesterday; and now it is just not
working.

I mean-- what kindof reliability is that??

-aaron


"Michael Vardinghus" <michaelvardinghus (AT) hotmail (DOT) com> wrote

I don't agree.

You could use smalldatetime (like microsft themselves sugggest in the bi
accelerator which contains best practices for developing
bi solutions)....

/Michael V.


<aaron_kempf (AT) hotmail (DOT) com> wrote

it doesn't matter _how_ i should do this.

i just want to know why my source table filter was working these past 2
days; and now it's just magically not being applied.

do i _really_ need to rebuild this cube from scratch (havent we played that
game before?)


"Michael Vardinghus" <michaelvardinghus (AT) hotmail (DOT) com> wrote

I'm afraid I cannot give more advice - I avoid source table filter myself
.... havent seen that much use for it -
perhaps there has been some other changes so you have a different
architechture in the cube than a few
days ago..?

At one of my clients we could consult microsoft at a small fee.... (much
smaller than normal consultant
fee's - even smaller than mine...

Then we could send data to them and they would state their opinion...

<aaron_kempf (AT) hotmail (DOT) com> wrote