Address Dimension mapping to fact with diffrent grain

Hi,

Good Morning!

I have Address Dimension with (AddressId, City, State, Zip)

I have two Fact table like
Fact_A with Zip,Measure1,Measeure2
Fact_B with State, Measure1, Measeure2

Can I use Address Dimension on Fact_B. I don't have zip code over there.

Thank You,
Gaurav

No. You can't use address on either fact. Both fact tables are aggregates, if you use address it would no longer be an aggregate, it would need to hold measures by address. If some of those measures are not fully additive by address (such as population and income statistics), you cannot do it at all.

Just store ZIP and State as degenerate dimensions. If you want to store other attributes relating to ZIP and State you need to build ZIP and State level dimension tables.

I don't like the idea of putting address and Zip/City/Country/State in the same dimension. Data is regularly filtered or aggragated by Zip/City/Country/State. And putting the Address with the Zip can make the dimension HUGE.

ngalemmo, you are right, I was just thinking there might be some other method to handle this kind of scenario in SSAS. Might be I am expecting more from SSAS



Jeff Smith, You point is valid but I am having data only for one country and I also defining the attribution relationship in the SSAS which will help us to maintain aggregation.