Hi all,
I'm trying to create a dimension in the analysis services based on only part
of the base table.
e.g. my base table has fields
criteria_id int && FK corresponding to gender, age category income category
etc.
value_id int && criteria_id + value_id form unique key.
text char && e.g. Male

with data
criteria_id value_id text
1 1 male
1 2 female
2 1 Age 10-20
2 2 Age 21-30
2 3 Age 31-40

I would like to create a dimension AgeCategory that only has the elements
from my base table with criteria_id 2.

I've tried setting the "Source Table Filter" in the advanced properties of
the dimesion editor. This looks fine in the data views but the source table
filter is not included in the initial join when I process a cube using the
dimension.

I could make a unique key rather than a compound key but wouldn't that
result in many unnecessary NULL elements?

I could break my table down into a set of count(criteria_id) tables in the
warehouse but that would be work and would reduce maintainability.

Can anyone tell me whether what I want to do is doable or what I'm doing
wrong?

Thanks
Jamie Beerbower

You could create a view of your base table and use it as the source for your
dimension.
HTH,
Brian
www.geocities.com/brianaltmann/olap.html

"JamieBeerbower" wrote:

Thanks for the idea...
Jamie Beerbower

"Brian Altmann" wrote: