Context issue?

building semantic layer
1

Hi,

I have a dimension table called Time and a fact table called
Charge_Off_Fact. The fact has 2 dates that can be joined to the Time
dimension. I set up separate joins for each date to the dimension.
Now when I run a query and pull in one of the dates, it pulls SQL for
both into the where clause of the
SQL with an AND and I do not want this. When I create a separate context
for each of the dates it works fine. Is this my only option? I have
about 30 facts with hundreds of dates and this sounds like a maintenance
nightmare!

Here are some details:
Time

Listserv Archives (BOB member since 2002-06-25)

2

Cori,

Contexts are not a good solution here, I don’t think. In the example you
gave, your users will have to choose which context they want to use, and
depending upon which context they pick, they may actually get different
results! That’s bad. Very bad.

I could give you a quick answer to your question, but I want to take you the
long way around, for reasons I’ll explain in a moment.

What you need to do is take a step back and look at your logical design.
Logically, the time_key and charge_off_date attributes (fields) do not have
the same definition, obviously. Yet physically you have joined both to a
single attribute in your Time entity (table). Logically, that is
inconsistent. The Time entity’s attribute can only have one definition,
too, and that definition can match one or the other (or neither!) but never
both. For a consistent logical data model, you would have to have two
separate Time entities, one for time_key and one for charge_off_date.

Now, once you move from the logical to the physical, it is entirely
reasonable and even desirable to implement the two separate Time entities in
a single table. However, you have to preserve the logical relationship and
entity/attribute definitions. How do you do that? In BO Designer, you can
use an alias.

Create an alias for the Time table, call it Charge_Off_Time. Join
Charge_Off_Time to the charge_off_date field. Drop the contexts. Now,
you’ll have to have two separate objects for fiscal year, one for Time and
one for Charge_Off_Time, but that’s okay … in fact, it is DESIRABLE in my
opinion.

The reason why I didn’t just say “use aliases” at the beginning is that I
wanted you to think about how your physical design had violated the logical
design. You don’t want to do that. Now, no one wants two identical tables
sitting right next to each other in the same database – that’s redundant.
But the physical design – a single table – oversimplified it to the point
where the logical design was violated. With a physical design involving a
single table with one (or many more!) aliases (or views, or synonyms), you
enjoy the simplicity of maintaining a single table while still supporting
the logical design.

Okay, lecture mode off. Back to work!

Erich Hurst
Compaq Computer Corporation

Listserv Archives (BOB member since 2002-06-25)