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
__________________
Regards
Guru Shyam CV

You can do this in Analysis Services. One way is to create a view for each
table and then create two shared dimensions. One would be the "caused by"
dimension and the other would be the "caused to" dimension. Then you can
create your cube and bring in both dimensions without a problem.

Sean

--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.



"Guru" <shyam_chatty (AT) lycos (DOT) com> wrote

Thanks Sean Boon for the reply,
I dont have permissions to access the production database
for creating views. Let me give you an overview of the
system.

We got a production server on Oracle9i database hosted at
a different location. I need to provide BI reports to the
management from the data.
I found from the SQL knowledge base articles that there is
an identified bug in Analysis services on connecting to
oracle databases with an alias name. BUG #: 11458
(plato7x)

My requirement is the same as identified in the bug list.
Is this fixed or is there any turn around other than the
one you suggested earlier.

Thanks & Regards

Guru

The only workaround at this point is to create a view and use the view to
source the dimension.

Sean


--
Sean Boon
SQL Server BI Product Unit

--
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm.

"Guru" <shyam_chatty (AT) lycos (DOT) com> wrote