Island Table as Lookup Table

[#1]

I am in a situation where I have a table that is as a lookup table.  If I join it to my model I will create the dreaded loop problem.  Looking around the community I found this solution, just making the table an island and then in your expression using and the IF function to join "on the fly" (if that makes sense).  Something like this -->

Sum(If(%DimKey1 = _%IslandKey1 AND %DimKey2 = _%IslandKey2, Value))

Yes this works BUT performance is horrible since it has to read all the rows in the three tables.  Is this the only way?  Any other ideas?  Thanks

[fosuzuki]

Hi,

I'm not sure if this will work, but give it a try:

sum({<%DimKey1=P(_%IslandKey1), %DimKey2=P(_%IslandKey2)>} Value)

the P() function returns all possible values for a field.

Hope this helps you

Regards,

Fernando

[vgutkovsky]

The P() set won't fix the problem if you're using a field from the same table as %DimKey1 as one of your dimensions in that chart. It's used for reducing the total dataset, not checking a condition on a particular row. I think that your solution will have to be in the data model. Your description is pretty broad, maybe if you can post a reduced/scrambled example someone on here can try to help.

Regards,

Vlad

[#4]

Guys I really appreciate the feedback.  I am adding the .qvw that I originally found on this discussion board showing me how to use an IF() to do a lookup within an expression (that is the best way I can explain it).  Two things to note in the .qvw:

1)  I would love to add this island lookup table as part of the connected model but then we would start getting into loop issues.  For example in the "Goal" island if we renamed the fields to %Week_key and %Store_key it would join but cause a loop.  Is there a design way around that?

2)  If you agree that creating the island is the best way to go my original question centers around the Goal expression in the "Revenue" object.  See how he is using an IF() to force join the island?  It doesn't cause performance issues in this .qvw but my real-world example has thousands of rows not a handful.

Thanks for anything!

[vgutkovsky · Accepted solution]

Try denormalizing into one big table. See attached. Thousands of rows is OK, millions would probably be a problem. Give it a shot.

Regards,

Vlad

[#6]

Vlad - Thanks again.  I marked your response as the correct answer (to denormalize) but in actuality what worked out the best for me is a combination of a little denormilization and keeping the IF() statement in the expression to force join.

My simple chart went from taking 15 seconds to load to less than 1.  The reason I didn't want to denormalize all 4 tables is that I would have to include my master calendar dimension which has over 20 fields and is used in almost every object in the document.  Instead I joined two dimensional tables (denormalized) and lookup the correct calendar attributes using the IF() in my expression.

Thanks again for the time you put into helping me!