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

Storing binaries/images in DWH good idea?

2 posters

Go down

Storing binaries/images in DWH good idea? Empty Storing binaries/images in DWH good idea?

Post  jochem_van_grondelle 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?



Posts : 11
Join date : 2009-09-22

Back to top Go down

Storing binaries/images in DWH good idea? Empty Re: Storing binaries/images in DWH good idea?

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

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

Back to top Go down

Back to top

- Similar topics

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