bob_the_web - 29 May 2009 10:27 AM

I guess a second reason NOT to pre-calculate is if the country code mapping changes - as it could easily do.

But it is a good reason to precalculate it!
Suppose you are a fact table, and a fact regarding some click (or anything else). You’re determining a country code for this fact, for the moment it occured.
But if in next two months country code mapping will change, it should not change the past. A country code for any existing facts should not change, only the future ones should be affected. And JOIN solution will work opposite…

Unfortunately I have no more ideas for optimizations. Except waiting some time for optimized version of ICE.

Regards,

Hello bob_the_web,

We had quite a long discussion about your queries at Infobright recently.

Could you please let us know whether the solution suggested by Jakub helped?

Thanks and best greetings,

Dominik

I’ve had success “exploding” ranged dimensions similar to the country_code table here into repeated rows for each value. This enables equality join operations in the queries (eg “table_1 a inner join country_code c on a.ip1 = c.ip), which are significantly more robust and perform WAY better. Obviously, that procedure, if implemented without any regard to the data in question here (ip addresses and traffic data by the looks of the tables) would result in a rather large (4 billion row) country_code dimension. However, by taking into account some aggregate traffic pattern information and knowledge of the unused ranges of IP addresses, you can drop the number of rows significantly. Worth a shot, I’d say.

Hi,
Sorry for the delay - been off on other things for a while

I solved this by adding the CC to the raw data before loading in to ICE. I have to parse raw log files to produce the data set so I simply did the lookup (via a simple dictionary) at that time. eg; precalculated

Not as elegant but it works just fine.
Thanks

Dominik Slezak - 11 June 2009 03:50 AM

Hello bob_the_web,

We had quite a long discussion about your queries at Infobright recently.

Could you please let us know whether the solution suggested by Jakub helped?

Thanks and best greetings,

Dominik

Hello,

Thanks for the update! After all, it looks like the simplest solution. I’m glad it works fine now.

Best greetings,

Dominik