Storing binaries/images in DWH good idea?

[jochem_van_grondelle]

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

[ngalemmo]

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.