clickstream fact data coming in with different levels of dimensional geography data

[kjfischer]

A record in our webserver log file contains a field which contains geographic browser information. The level of information can vary depending upon the geographic region, for example if the browser is in the United States, then the (1) area_code, city, state, country, continent is provided. However, if the browser is outside of United States, then the(2) city, country, continent is provided. And sometimes, there are gaps as in the concept of state does not exist (3).

Example 1.
area_code=518&city=Saratoga Springs&&state_province_code=NY&state_province_desc=New_York&country_code=US&country_desc=United States&continent=North America

Example 2.
city=Haarlem&country_code=NL&country_desc=Netherlands&state_province_code=NH&state_province_desc=Noord Holland&continent=Europe

Example 3.
city=Hong Kong&country_code=HK&country_desc=Hong Kong&continent=Asia

What does my geography dimension look like? Should carry all levels down to area code? How do I handle the missing levels? Or if I think that the lowest level of reporting will be at the country level, just build the dimension up from country and link the fact row at the country level?

Thanks, Kim

[ngalemmo]

Treat it like a junk dimension. Collect all the attributes the business is interested in and create new rows for new combinations. Then it is a matter of the end-user deciding which attributes to use for analysis.

[kjfischer]

Would doing that prevent you from drilling up/down as they might like to do, i.e. state -> country -> continent?

[ngalemmo]

No.

[sachij3u]

The hierarchy to drill down or roll up is defined within the reporting tool and not how table structure is defined.
I had a similar case of alternate Geographical hierarchy. I handled it by creating just one Geo Dimension with all the possible geo attributes. And in BO we defined alternate hierarchy (something like DMA hierarchy, District Hierarchy etc) to traverse the reports based on the business needs.
DMA hierarchy DMA-->Region-->Country
District Hierarchy : District--> State-->Region-->Country

Hope this helps...

[Sponsored content]