Hi all

I'm fixed with a strange situation. I was trying for a solution but in
vain.. please help me ASAP



This is the situation:

i have a fact table with 2 FK's comin into it. But both the FK's are
from the same table. i need to build 2 dimensions based on that.



here is my table structure:

Table A:

bs_sk numeric

bs_ab_sk_causedby numeric

bs_ab_sk_causedto numeric

bs_id



Table B:

ab_sk numeric

ab_id



here bs_ab_sk_causedby and bs_ab_sk_caused to come from table B and
point to ab_sk.



Hope you have got the issue. what i want is two dimensions; one based on
ab_id joined thru bs_ab_sk_causedby and the other thru bs_ab_sk_causedto



i can do this in SQL query by calling the table B one more time using a
alias. How can i build a dimension from a alias table? when i tried this
I'm getting Analysis services error "Unable to open recordset"!



i tried creating 2 dimensions seperately based on above joins and
created 2 cubes from each of those dimensions. I tried creating a
virtual cube but in vain, i was unable to bring both the
dimension members!!



is there any other way out? Please help!!!



Thanks in advance


--
Posted via http://dbforums.com

You should be able to do this in AS by creating two hierarchies from the
same dimension table.
Create your first dimension from Table A's bs_ab_sk_causedby field and name
it "bs.causedby" (must have a period in it) - which implies that "causedby"
is a hierarchy within the "bs" dimension. Then create a second dimension
from Table A using the "bs_ab_sk_causedto" field and make it a hierarchy
based on the "bs" dimension - it will be called "bs.causedto". They can be
treated like separate dimensions and the second one does not need to be made
virtual in this small cube. Add both dimension/hierarchies to the cube with
table B as your fact table.

Fred Watkin
www.advanceinfo.com

"shyam_chatty" <member34820 (AT) dbforums (DOT) com> wrote

Dear Fred



Thanks for the reply. I tried doing the same as suggested.

Wheni processed the cubes with two dimensions / hierarchies, its
resulting in processing error.



just a sample:

Key was found in bs.tracedby but not found in bs.tracedto



How to solve this??



Thanks


--
Posted via http://dbforums.com

Hi Fred



A small correction to my earlier post..



the error message is as follows



A member with key 'HLD - 8.0' was found in the fact table but was not
found in the level 'Pj Config Item Id' of the dimension
'Trace.TracedTo'.; Time:10/30/2003 1:13:57 PM



i think this is because, not all members in the Table A(fact table)
belong to Table B(Trace.TracedTo)..



i built a snow flake dimension because i need to bring the Config Id's
from Table A using the SK in Table B



Waiting to provide extra info inthis regard



Thanks


--
Posted via http://dbforums.com

Sorry, but you have lost me and likely most readers.



There was no "tracedby" in your original posting nor was there a
Trace.TracedTo table. Also I assumed that Table B was the fact table.
That is not what you have in your last note.



I would recommend that you state all the tables involved in your
dimension and cube definitions. Try to keep the table and field names
simple (i.e. drop the extraneous qualifiers which mean nothing to
others). Even a small sampling of fake data may help demonstrate the
relationships.



My "guess" is that your problem is due to dimension metadata in your
fact table not appearing in the corresponding dimension table. That is
an integrity problem in your source data.



Fred


--
Posted via http://dbforums.com