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

View previous topic View next topic Go down

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

Post  kjfischer on Tue Jul 02, 2013 4:32 pm

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

kjfischer

Posts : 28
Join date : 2011-05-04

View user profile

Back to top Go down

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

Post  ngalemmo on Tue Jul 02, 2013 6:40 pm

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.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  kjfischer on Tue Jul 02, 2013 7:01 pm

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

kjfischer

Posts : 28
Join date : 2011-05-04

View user profile

Back to top Go down

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

Post  ngalemmo on Tue Jul 02, 2013 10:18 pm

No.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

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

Post  sachij3u on Thu Jul 18, 2013 4:08 pm

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...
avatar
sachij3u

Posts : 19
Join date : 2013-07-11
Age : 36
Location : Herndon, VA

View user profile

Back to top Go down

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

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum