Question on storing tax rates in a data warehouse.

View previous topic View next topic Go down

Question on storing tax rates in a data warehouse.

Post  tgrady on Mon Jul 30, 2012 9:34 am

Hi,

Is it considered good paractice to store tax rates (and other tax related informaiton) in a data warehouse? The reason I ask is that we have a couple of different
views on this and this has me wondering if I am mistaken on this.

I am thinking that is an acceptable practice to store tax information in a data warehouse. Is it? If so, would storing the tax information as a type 2 slowly changing
dimension be the best way to do this?

Thank you in advance.

Tom

tgrady

Posts : 7
Join date : 2011-06-22

View user profile

Back to top Go down

Re: Question on storing tax rates in a data warehouse.

Post  BoxesAndLines on Mon Jul 30, 2012 12:36 pm

There's not a whole lot of data that I wouldn't include in the warehouse. I would certainly include tax rates. How to model it would depend on its usage.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Question on storing tax rates in a data warehouse.

Post  tgrady on Mon Jul 30, 2012 2:54 pm

Thank you.

tgrady

Posts : 7
Join date : 2011-06-22

View user profile

Back to top Go down

Re: Question on storing tax rates in a data warehouse.

Post  ngalemmo on Mon Jul 30, 2012 5:53 pm

And, if you are talking about sales tax, you typically need to break it out by tax authority. So usually on the fact you have a locale key where the tax was charged and a bridge between locale and tax authority, with the bridge containing a rate, and effective period, attributed to that authority so you can produce reports by authority (such as city, county, state, air quality management district, parish and any other political group with their hands in the till). You wind up with both a tax locale and tax authority dimension. Discuss this with the tax accountants.

You will also wind up with some pretty weird situations. For example, Los Angeles county (but not LA city) has a 10 cent charge for paper bags. But only at stores that sell food (including WalMart), but not restaurants. Non-food retailers give you bags for free. Also, in California, if you go to a Subway and have the sandwich toasted, it is taxed... if you don't there is no tax.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Question on storing tax rates in a data warehouse.

Post  M. Khan on Tue Jul 31, 2012 7:04 am

ngalemmo wrote:And, if you are talking about sales tax, you typically need to break it out by tax authority. So usually on the fact you have a locale key where the tax was charged and a bridge between locale and tax authority, with the bridge containing a rate, and effective period, attributed to that authority so you can produce reports by authority (such as city, county, state, air quality management district, parish and any other political group with their hands in the till). You wind up with both a tax locale and tax authority dimension. Discuss this with the tax accountants.

You will also wind up with some pretty weird situations. For example, Los Angeles county (but not LA city) has a 10 cent charge for paper bags. But only at stores that sell food (including WalMart), but not restaurants. Non-food retailers give you bags for free. Also, in California, if you go to a Subway and have the sandwich toasted, it is taxed... if you don't there is no tax.

You have given a pretty good points about dimensional modeling of tax rates. Couple of qucik questions for the clarity:
- What would you put in Locale Dimension in your example.
- In case of Subway example, suppose customer request to toast his sandwich and there is county tax as well, would the fact table have two rows for tax calculation and a seperate table for sales amount(i.e. sales price of sandwich).

M. Khan

Posts : 11
Join date : 2012-07-24

View user profile

Back to top Go down

Re: Question on storing tax rates in a data warehouse.

Post  ngalemmo on Tue Jul 31, 2012 3:28 pm

Tax locale would represent the area where the sale took place and the tax conditions of the sale. Essentially a locale represents the rate being charged and the breakdown behind it. It would probably contain a description and the rate. Tax charged and the locale FK would appear once on the same line as the sale (item level facts). The bridge to tax authority would allow you to break the tax collected down to individual tax authorities.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Question on storing tax rates in a data warehouse.

Post  tgrady on Wed Aug 01, 2012 8:04 am

Great information ngalemmo - thanks for sharing.

tgrady

Posts : 7
Join date : 2011-06-22

View user profile

Back to top Go down

Re: Question on storing tax rates in a data warehouse.

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