Attribute Relationship help

[DebbieKat]

We have a strange situation where we have a fact (table a) that contains a subset of data that is derived from another fact table (table b). We are trying to do a report that allows us to bring in certain dimensions (tables c through n) where the keys only exist in table "b". table "a" and "b" are related on a subset of the keys (key 1 and key 2), but we can't bring these keys directly into table "a" because we might have multiple of these keys associated with key 2 at different points in time which are represented by two different columns in table "a". What we are trying to do is get microstrategy to acknowledge the relationship between fact table "a" and "b", but when we bring in the dimension (table "c&quot, we only wind up with a cartesian product.

We have played around with the relationships between these two tables ("a" and "b&quot but it doesn't seem to recognize what we are trying to do. Has anyone tried to 'pass through' one fact table to another to get to a dimension? Is there a way to force microstrategy to recognize relationships here?


columns of table a
===============
key 1
key 2 (represents a key in table b point in time 1)
key 3 (dt point in time 1)
key 4 (time point in time 1)
key 2 (represents a key in table b at a different point in time 2)
key 3 (dt point in time 2)
key 4 (time point in time 2)

columns of table b
================
key 1
key 2
key 5
other keys

columns of table c
================
key 5
description 5

 

[nlim]

I'm not sure what your report looks like, but if it's something like this:

key5, key2 from table b, key2attime1(from table a), sum(fact from table a)

here's what I suggest you do:

1) create 3 attributes
key5 lookup = table c
key2 lookup = table b
key2attime1 lookup = table a, idexpress = specificcolumnkey2attime1

make key5 parent of key2 parent of key2attime1. This is very important.

2) create fact as facta source=table a

your report should work now.

the key is to recognise that key2attime1 to MicroStrategy is a separate attribute since it is not "strictly" the same as key 2. If you need to further limit by time1, you can just create another attribute called time1 lookup=table a and you can then filter on the time.

hope this helps.

 

[DebbieKat]

Hi nlim!

I think I follow what you are saying, but I am a little confused on the "key2attime1 lookup = table a, idexpress = specificcolumnkey2attime1" attribute. Do you mean that we should create a second ID form expression on that same attribute? If so, does that one point to table b?

 

[nlim]

to make things clearer, I'm going to rename your columns of
columns of table a
===============
key 1
key 2 (represents a key in table b point in time 1)
key 3 (dt point in time 1)
key 4 (time point in time 1)
key 2 (represents a key in table b at a different point in time 2)
key 3 (dt point in time 2)
key 4 (time point in time 2)


to

columns of table a
===============
key 1
key 2a (represents a key in table b point in time 1)
key 3 (dt point in time 1)
key 4 (time point in time 1)
key 2b (represents a key in table b at a different point in time 2)
key 3 (dt point in time 2)
key 4 (time point in time 2)

then

key2attime1 lookup table is table a, and the id form is key2a

good luck!