Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Geography Dimension Expand / Collapse
Author
Message
Posted Monday, March 7, 2011 7:37 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 4:21 AM
Points: 1,074, Visits: 1,153
I am currently working on a warehouse project that includes a dimension for geography. It includes Zip Code, City and State currently. Latitude and Longitude are available as well in the feed that populates the dimension. My question is should the lat/long be placed on the dimension or fact? I am leaning towards the dimension but am getting pushback as others are saying it is too narrow for the dimension (will cause too many rows on the dimension) and it should be placed on the fact.

Anyone else have this debate before?
Post #1074570
Posted Monday, March 7, 2011 8:01 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Yesterday @ 7:56 PM
Points: 1,816, Visits: 3,461
Seriously guessing this depends on what your fact is actually recording..... But assuming sales (when in reality, it's just as likely mining equipment moving round an opencut mine), then the count/volume of the Long/Lat wouldn't be any higher being in the dim than in the fact. Personaly, I'd go with the dim. To me, it's like saying we have 1MM clients, so thats a lot of phone numbers, so put them in the fact. They still belong in the dim


Steve.
Post #1074576
Posted Monday, March 7, 2011 8:16 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 4:21 AM
Points: 1,074, Visits: 1,153
That is exactly my thought and funny you should bring it up. We are tracking phone calls made by customers to a business and where the call was made from.

The consulting company we are working with wants to put the phone number and lat/long in the fact but my argument is it belongs in the dimension as it's not a measure. Should the number be called more than once, storage is less on the dimension than on the fact. Chances are though , either phone or lat/long will most likely appear only once in the data but it's still not a fact and belongs in a dimension.
Post #1074580
Posted Monday, March 7, 2011 9:24 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: Moderators
Last Login: Yesterday @ 7:56 PM
Points: 1,816, Visits: 3,461
Wondering if they also lose some reporting/analytical functionality by separating it from the rest of the Customer record? If you/they want to attribute this in *any* way then it's much better suited being in the/a dimension. Although you can definitely get exceptions, my rule of thumb on this would be that it's an attribute of the grain of the transaction - ie the phone call. I could theoretically want to build a hierarchy up over it. It's the equivalent of wanting to store the 'call reason' in the fact, which I'm guessing they're not asking for, because, it's an attribute of the call and again I may want to build a hierarchy up over that.




Steve.
Post #1074592
Posted Friday, April 19, 2013 10:57 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, September 5, 2014 10:05 AM
Points: 30, Visits: 297
One option is to include a field of spatial type Geography and insert into it a polygon of the geographic area. Review the Spatial data types in BOL for more information.


Post #1444522
Posted Friday, April 19, 2013 3:11 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, July 28, 2014 8:51 AM
Points: 132, Visits: 581
Are you going to be capturing any further location detail in the Geography Dimension like street? Is that level of detail needed? If no, then I would not capture the Lat/Lon in this table. Based on what you have right now, the granularity of the dimension is at zip/city level. Capturing the Lat/Lon would make the dimension exponentially larger since each record would essentially have a different Lat/Lon. Assuming a customer may make the call with a cell phone, a single customer could make a call from multiple Lat/Lon even if the customer is always calling from within the same area. If the Lat/Lon is important for auditing/analysis but not used for aggregate analysis, I would just store the detail in the fact since the granularity could potentially be equivalent to the fact granularity.

In situations where I have a detail where the granularity is very close to the granularity of the Fact, I usually just add it to the fact table. I believe details like these are referred to as degenerate dimensions. I would also add the customer number to fact as a degenerate dimension in your situation.

If you create a dimension that is essentially 1 to 1 with the fact granularity, there is no benefit.
Post #1444621
Posted Thursday, April 25, 2013 11:00 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, July 30, 2014 2:03 PM
Points: 124, Visits: 488
I would choose dimension because it is a attribute and not a measure.
Post #1446625
Posted Sunday, April 28, 2013 7:02 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:51 AM
Points: 460, Visits: 863
I would argue that the central lat/long of a particular ZIP code region is fine enough granularity. Unless you are a Telco or a Utility, mapping the exact coordinates of each customer might be overkill when you can just map by ZIP.

Since you already have ZIP code and possibly the suburb/county/council etc that names the ZIP code then a single lat and long record defining the centre of that ZIP region on the geography dimension will not create any more records than already exist in the dimension.

On the other hand, I've previously used coordinates of client offices in a 'customer' dimension, and this was useful for mapping sales on a fairly zoomed in view, the map was about 50km^2 (about 20Mi^2). I didn't have a geography dimension at all, but that design decision depends on the situation.
Post #1447346
Posted Monday, April 29, 2013 6:54 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 6:34 PM
Points: 134, Visits: 249
In my mind, this is a question of granularity. There are only 181 latitudes (90 degrees north to 90 degrees south including 0) and 360 longitudes (plus or minus 180 degrees) and they aren't going to change. Two dimension tables could be created since they are independent of one another holding only the degree value for each one which are linked to whatever fact tables require them. Then the precise latitude and longitude can be added as fields for the fact tables (which is probably the only way it could be done for decimal geolocation values). The facts can then be rolled up by the overall latitude and longitude without involving huge tables and still preserve the precise values for a given entry. Even giving the latitude/longitude tables a granularity of one minute (60 minutes per degree), would only involve a table of 21,600 records for longitude and 10,860 for latitude. Again, if the precise location is needed, the full latitude and longitude can be included in the fact tables. It is trivial for a computer to convert decimal to degree/minute/second and back if needed.

This is similar to the Kimball design tip 51 for handling time of day as a dimension in addition to a calendar dimension.


____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
Post #1447487
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse