Dimension attributes, or new dimension

[cjrinpdx]

I have a factless fact table that is used to track the relationships between events and employees. The event dimension has a city and state, as well as status, type, etc., that the users would like to slice-and-dice by in Excel. Are there any benefits of taking the city and state from the event and creating a location dimension; and adding the LocatioKey to the factless fact table? Thanks.

[BoxesAndLines]

Yes, that is a better design. Text attributes in the fact table impact performance.

[VHF]

Sounds like the City and State are already attributes in the Location dimension. The OP was was asking if there would a benefit to having a separate Location dimension.

My answer would be "no" for what you are doing now. Users can slice-and-dice using any attribute in any dimension. The only scenario I can think of where there would be some benefit to having Location be a separate dimension would be if there were other fact tables that related to Location. With a conformed Location dimension you would be able to drill-across fact tables if you wanted to compare event attendance with some other location-specific fact.

[Jeff Smith]

I like having a Geographic dimension what rolls up data by City, County, State. If Block Group is available, Block group to Census Tract, County, State is a nice hierarchy. If regions have been created, I usually add them to the geographic dimension.

If it's a stand alone dimension, it can be reused for other subjects such as, store locations, customers, etc. Which can be nice if you want to create a summary table with measures from multiple fact tables rolling up to geography.

[cjrinpdx]

Thank you for your posts.
I do have other fact tables that relate to Location, so I will create a new Location dimension. Good point about creating a conformed Location dimension and using it to drill-across fact tables. Thanks again!

[Sponsored content]