Kimball Forum
Would you like to react to this message? Create an account in a few clicks or log in to continue.

Transposing from columns to rows

Go down

Transposing from columns to rows Empty Transposing from columns to rows

Post  nxlefrancois Wed Sep 09, 2009 2:25 pm

I have data extracts from transaction system containing multiple measures (columns) all being of the same type of indicator, for which I would like to transform into multiple rows in my fact table.
E.g. Extract contains (for each row):

date
location
nbr_visitors_can_bc
nbr_visitors_can_on
nbr_visitors_can_qc ... (one for each of the 10 Canadian provinces)
nbr_visitors_us_ma
nbr_visitors_us_wi
nbr_visitors_us_ok ... (one for each of the 50 US states)

I'd like my FACT_VISITATION table to have 60 rows, i.e. one row for each state/province.

I have an ETL tool that would allow me to do 60 passes to load all the rows in my fact table but is there a trick to transpose, i.e. doing that all in one phase. I use Pervasive as ETL tool.
Thanks

nxlefrancois

Posts : 4
Join date : 2009-09-09

Back to top Go down

Transposing from columns to rows Empty Re: Transposing from columns to rows

Post  ngalemmo Wed Sep 09, 2009 3:46 pm

I am not familiar with Pervasive, but most ETL tools have some means of normalizing data. If you are using Oracle 11g, it has an UNPIVOT clause in SELECT that would do it as well... other database may offer this functionality as well.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Transposing from columns to rows Empty RE: Transposing from columns to rows

Post  nxlefrancois Thu Sep 10, 2009 9:45 am

Thanks, I will have to see if Pervasive can actually do that.

nxlefrancois

Posts : 4
Join date : 2009-09-09

Back to top Go down

Transposing from columns to rows Empty Re: Transposing from columns to rows

Post  Sponsored content


Sponsored content


Back to top Go down

Back to top


 
Permissions in this forum:
You cannot reply to topics in this forum