clickstream fact data coming in with different levels of dimensional geography data
3 posters
Page 1 of 1
clickstream fact data coming in with different levels of dimensional geography data
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
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
Re: clickstream fact data coming in with different levels of dimensional geography data
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.
Re: clickstream fact data coming in with different levels of dimensional geography data
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
Re: clickstream fact data coming in with different levels of dimensional geography data
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...
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...
sachij3u- Posts : 19
Join date : 2013-07-11
Age : 43
Location : Herndon, VA
Similar topics
» Source Fact data coming in at different levels of a conformed dimension
» Geography Dimension with diffirent grain in fact tables
» Reporting table data repository vs. Dimensional data store
» Aggregating data at different levels of granularity
» Aggregate Fact for Hierarchy Levels
» Geography Dimension with diffirent grain in fact tables
» Reporting table data repository vs. Dimensional data store
» Aggregating data at different levels of granularity
» Aggregate Fact for Hierarchy Levels
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum