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

Data type matching between OLTP & DW

Go down

Data type matching between OLTP & DW Empty Data type matching between OLTP & DW

Post  az_pete Tue Dec 08, 2015 4:42 pm

Hello All,

Is it generally considered a best practice to have the data types in your data warehouse tables (Facts & Dimensions) to match those of the OLTP system?

For instance in our OLTP system (MSSQL Server) we have many transaction dollar amount columns as a money data type (horrible, I know). I would prefer to bring this data into our DW as a decimal data type.

Similarly, there are many columns in our OLTP tables that are unicode (NVARCHAR) that have no need to be. That is to say there will never be unicode data stored in these columns. Rather than waste storage space needlessly in our DW, I would prefer to convert this data to non-unicode (VARCHAR).

Is this considered ok? Or is it best to keep data types the same between the systems?

Thanks,
Peter

az_pete

Posts : 2
Join date : 2015-12-04

Back to top Go down

Data type matching between OLTP & DW Empty Re: Data type matching between OLTP & DW

Post  BoxesAndLines Tue Dec 08, 2015 6:58 pm

Considering that most warehouses are sourced from multiple sources it is almost inevitable that the source and target data types (and lengths) will be different. It is expected. Change with confidence.
BoxesAndLines
BoxesAndLines

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

Back to top Go down

Data type matching between OLTP & DW Empty Re: Data type matching between OLTP & DW

Post  ngalemmo Tue Dec 08, 2015 9:08 pm

Besides, the MONEY data type is a decimal data type. Basically, it is a scaled integer, similar to most NUMERIC type implementations.

As B&L indicated, its par for the course.
ngalemmo
ngalemmo

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

http://aginity.com

Back to top Go down

Data type matching between OLTP & DW Empty Re: Data type matching between OLTP & DW

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