Multiple keys to the same dimension in one fact table possible ?

954011 Oct 5, 2012 8:05 AM

I have a fact table having two keys source_ip_key and dest_ip_key both pointing to the same dimension say iptable.
I tried to create the cube in AWM, in the join condition, i was not able to do it.

Is this not possible to have multiple keys to the same dimension or is it a limitation in AWM.
Please advice.

1. Re: Multiple keys to the same dimension in one fact table possible ?

DavidGreenfield Oct 5, 2012 12:15 PM (in response to 954011)

A cube cannot be dimensioned by the same AW dimension, IP say, twice. The solution is to define two AW dimensions, SOURCE_IP and DEST_IP say, in the AW that both point to the same base dimension table. You can then dimension the cube by both SOURCE_IP and DEST_IP and map accordingly.
Like Show 0 Likes(0)

Actions
2. Re: Multiple keys to the same dimension in one fact table possible ?

954011 Oct 8, 2012 11:43 AM (in response to 954011)

Thanks. What do you mean by Base dimension, by which we can map source and dest dimensions.

We have thought of this approach :

-Have just one relational table IPTable
-Create two views SourceIP and DestIP from this IPTable
-Map SourceIP relational table to SourceIP dimension. Same way for DestIP.

Is this approach, right ?
Like Show 0 Likes(0)

Actions
3. Re: Multiple keys to the same dimension in one fact table possible ?

DavidGreenfield Oct 8, 2012 12:58 PM (in response to 954011)

Obviously I don't know all your requirements, but the approach you outlined makes sense. When I said "base dimension table" I meant something like the "one relational table IPTable" you mentioned. If you define two views on top of this one table, then the mapping becomes very clean.
Like Show 0 Likes(0)

Actions

Go to original post

Join the world’s largest interactive community dedicated to Oracle technologies.