Data type matching between OLTP & DW

View previous topic View next topic Go down

Data type matching between OLTP & DW

Post  az_pete on 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

View user profile

Back to top Go down

Re: Data type matching between OLTP & DW

Post  BoxesAndLines on 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.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: Data type matching between OLTP & DW

Post  ngalemmo on 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.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Data type matching between OLTP & DW

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