Storing binaries/images in DWH good idea?

View previous topic View next topic Go down

Storing binaries/images in DWH good idea?

Post  jochem_van_grondelle on Thu Oct 15, 2009 10:19 am

Hi there,

We are building a datawarehouse for educational purposes for managing student results. By example how many students have passed their studies and which courses did they exactly fail. Besides that we're replacing an existing system which after querying a student by number, shows the results of his students along with his contact information. In this old system (MS Access........which actually was called a kind of stovepipes datawarehouse a photo... omg) was also shown a photo with every student. These photos were saved in the access database.

If we have a student dimension, can we just put an attribute Photo in it with varbinary(max) as datatype? Or would this be very bad regarding performance. We're talking about 5000-10000 students with each a photo of less than 100KB. Would be approx. 1GB. We're managing everything with Microsoft products so I'm not sure if it could manage this well if you'd leave out photo in all querys.
It might be easier to store a photo in a seperate table and just combine it in a view where necessary.

What would be your advice?

Jochem

jochem_van_grondelle

Posts : 11
Join date : 2009-09-22

View user profile

Back to top Go down

Re: Storing binaries/images in DWH good idea?

Post  ngalemmo on Thu Oct 15, 2009 11:49 am

If the application calls for it, sure, why not?

If you create a separate table, simply use the same primary key value as the corresponding student dimension table. But, you don't need to do that. If you store the image as a BLOB, every database system I know of will physically store BLOBs in a separate data structure with only a pointer in the main table itself. So this has the same effect as modeling it in a different table without actually having to do that. If a query doesn't include the picture, there would be no material effect on performance. And, if the query did request the picture, it would probably perform better because the internal pointer is a direct physical reference rather than a foreign key needing to navigate an index structure.
avatar
ngalemmo

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

View user profile http://aginity.com

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