ÿþ<html> <head><script type="text/javascript" src="/_static/js/bundle-playback.js?v=1WaXNDFE" charset="utf-8"></script> <script type="text/javascript" src="/_static/js/wombat.js?v=txqj7nKC" charset="utf-8"></script> <script type="text/javascript"> __wm.init("https://web.archive.org/web"); __wm.wombat("http://www.databaseanswers.org:80/columnar_data_vaults_and_eav_designs.htm","20200116072227","https://web.archive.org/","web","/_static/", "1579159347"); </script> <link rel="stylesheet" type="text/css" href="/_static/css/banner-styles.css?v=S1zqJCYt" /> <link rel="stylesheet" type="text/css" href="/_static/css/iconochive.css?v=qtvMKcIJ" /> <!-- End Wayback Rewrite JS Include --> <title>Columnar, Data Vaults and EAV Designs</title> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1"> <link rel="stylesheet" href="/web/20200116072227cs_/http://www.databaseanswers.org/styles/style.css"> </head> <body bgcolor="#FFFFFF" leftmargin="0" topmargin="0" marginwidth="0" marginheight="0"> <table name="CHANGE MY WIDTH" width="100%" border="0" cellspacing="0" cellpadding="0"><tr><td> <!-- CHANGING THE WIDTH ABOVE WILL CHANGE ALL PAGE WIDTH --> <table name="HEADER" width="100%" border="0" cellspacing="0" cellpadding="0" bgcolor="#FFFFFF"> <tr> <td height="100" valign="middle"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/dba_400_home.jpg" width="400" height="58" alt="Database Answers"></td> <td align="right" rowspan="2" width="100%"> <img src="/web/20200116072227im_/http://www.databaseanswers.org/images/spacer.gif" height="100"> </td> </tr> </table> <!--- START OF MAY 21ST. TOP MENU BAR ---> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td height="32" background="/web/20200116072227im_/http://www.databaseanswers.org/images/nav_bg.jpg" align="left" valign="middle"> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td width="1%"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/spacer.gif" width="15" height="15"></td> <td width="5%" wiiiidth="6%"><a style="text-decoration:none" href="index.htm"><font color="white" face="Verdana" size="2">Home</font></td> <td align="LEFT" width="5%" wiiiidth="10%"><a style="text-decoration:none" href="first_timers.htm"><font color="white" face="Verdana" size="2">First Timers</font></a> </td> <!--- <td align=LEFT WIDTH=5% wiIIIdth="10%"><A STYLE="text-decoration:none" HREF="about_us.htm"><font color="#FFFFFF" FACE=Verdana SIZE=2>About Us</font></A> </td> ---> <td width="8%" wiiiidth="10%"><a style="text-decoration:none" href="pi_ask_a_question/index.asp"><font color="#FFFFFF" face="Verdana" size="2"> Ask a Question</font></a></td> <td width="7%"> <a style="text-decoration:none" href="careers.htm"><font color="yellow" face="Verdana" size="2">Careers</font></a> </td> <td width="7%"><a style="text-decoration:none" href="clients.htm"><font color="#FFFFFF" face="Verdana" size="2">Clients</font></a> </td> <!--- <td align=LEFT WIDTH=8% wiIIIdth="18%"><A STYLE="text-decoration:none" HREF="contact_us.htm"><font color="#FFFFFF" FACE=Verdana SIZE=2>Contact Us</font></A> </td> ---> <td width="10%"><a style="text-decoration:none" href="data_models/index.htm"><font color="#FFFFFF" face="Verdana" size="2">Data Models</font></a></td> <td width="10%"><a style="text-decoration:none" href="tutorials.htm"><font color="#FFFFFF" face="Verdana" size="2">Tutorials</font></a></td> <td width="10%"><a style="text-decoration:none" href="site_map.htm"><font color="#FFFFFF" face="Verdana" size="2">Site Map</font></a></td> </tr> </table> </td> </tr> </table> <!--- END OF MAY 21ST. TOP MENU BAR ---> <table name="MAIN CONTENT" width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td width="90" background="/web/20200116072227im_/http://www.databaseanswers.org/images/px-left.gif" valign="top" align="center" bgcolor="#3399CC"> <img src="/web/20200116072227im_/http://www.databaseanswers.org/images/boschendal_vertical_sml.jpg" width="81" height="122" vspace="5"> <p><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/spacer.gif" width="90"></p></td> <td valign="top"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/spacer.gif" width="3" height="239"></td> <td width="100%" bgcolor="#B0D8FF" valign="top"> <img src="/web/20200116072227im_/http://www.databaseanswers.org/images/spacer.gif" height="20"> <table width="90%" wiiiidth="590" border="0" cellspacing="0" cellpadding="0"> <tr> <td rowspan="4" valign="top" width="30"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/spacer.gif" width="21" height="1"></td> <td width="61">&nbsp;</td> <td rowspan="4" width="6"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/spacer.gif" width="6" height="1"></td> <td colspan="3" width="100%" align="right" class="header1"> Columnar, Data Vaults and EAV Designs<img src="/web/20200116072227im_/http://www.databaseanswers.org/images/spacer.gif" width="5" height="1"> </td> <td width="3" bgcolor="#FF9900"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/px-orange.gif" width="3" height="20"></td> </tr> <tr> <td rowspan="3" valign="top" width="1">&nbsp;</td> <td colspan="4" height="3" rowspan="3" valign="top"> <table width="100%" border="0" cellspacing="0" cellpadding="0" height="3"> <tr bgcolor="#FF9900"> <td><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/px-orange.gif" width="1" height="3"></td> </tr> </table> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td bgcolor="#FF9900"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/px-orange.gif" width="3" height="1"></td> <td><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/spacer.gif" width="8" height="1"></td> <td width="100%"><font color="blue" face="Verdana" size="2"> Columnar Databases, Data Vaults and EAV Designs are topics engaging a lot of interest right now. <br>I haven't spent much time reading about them, so I would say that they are simply an example fo the EAV (Entity-Atttribute Value) Approach. <br>But please <a href="https://web.archive.org/web/20200116072227/mailto:dba_requests@barryw.org?subject=Comments on Columnar Databases, etc.">Email me</a> if you think I am wrong. <br>I believe the concept behind Columnar Databases is very simple. <br>Data is broken down into the lowest level of granularity that is appropriate. <br>Conventional Relational Database Management Systems ('DBMS') store records contiguously in physical storage. <br>Every DBMS generates a 'Query Execution Plan' (QEP)which can be explored to identify techniques for improving performance. <br>The basic technique here is to create Indexes and then check to make sure that the indexes are being used by the DBMS to achieve maximum performance. <br><br>Individual DBMS suppliers have come up with techniques for offering improved performance and to achieve a competitive advantage. <br>Oracle, for example, provides a facility to define 'Hints' which make it possible to say 'For this Query, use the Index called IDX_12345'. <br><br>Wikipedia says :- <br>"... online transaction processing (OLTP)-focused RDBMS systems are more row-oriented, while online analytical processing (OLAP)-focused systems are a balance of row-oriented and column-oriented. <br>In practice, row-oriented architectures are well-suited for OLTP-like workloads which are more heavily loaded with interactive transactions. <br>Column stores are well-suited for OLAP-like workloads (e.g., data warehouses) which typically involve a smaller number of highly complex queries over all data (possibly terabytes). <br>However, there are a number of proven row-based OLAP RDBMS that handles terabytes, or even petabytes of data, such as Teradata." </td> </tr> </table> </td> </tr> <tr> </tr> <tr> </tr> </table> <br> <table width="90%" wiiiidth="590" border="0" cellspacing="0" cellpadding="0"> <tr> <td rowspan="4" valign="top" width="30"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/spacer.gif" width="21" height="1"></td> <td width="61">&nbsp;</td> <td rowspan="4" width="6"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/spacer.gif" width="6" height="1"></td> <td colspan="3" width="100%" align="right" class="header1">Here is a series of Useful Links<img src="/web/20200116072227im_/http://www.databaseanswers.org/images/spacer.gif" width="5" height="1"> </td> <td width="3" bgcolor="#FF9900"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/px-orange.gif" width="3" height="20"></td> </tr> <tr> <td rowspan="3" valign="top" width="1">&nbsp;</td> <td colspan="4" height="3" rowspan="3" valign="top"> <table width="100%" border="0" cellspacing="0" cellpadding="0" height="3"> <tr bgcolor="#FF9900"> <td><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/px-orange.gif" width="1" height="3"></td> </tr> </table> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td bgcolor="#FF9900"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/px-orange.gif" width="3" height="1"></td> <td><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/spacer.gif" width="8" height="1"></td> <td width="100%" valign="top"> <img src="/web/20200116072227im_/http://www.databaseanswers.org/images/spacer.gif" height="1" width="1"> <br> <table wiiiidth="590" border="0" cellspacing="0" cellpadding="0"> <tr> <td width="25"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/spacer.gif" width="25" height="1"></td> <td bgcolor="#3399CC" width="20"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/circle1.gif" width="20" height="20"> </td> <td bgcolor="#3399CC" class="header2" width="100%"> <div align="center">Follow-up by checking out these Links</div> </td> <td bgcolor="#3399CC" width="20" valign="top" align="right"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/circle2.gif" width="20" height="20"></td> </tr> <tr> <td width="25">&nbsp;</td> <td colspan="3"> <table width="100%" border="0" cellspacing="3" cellpadding="3" bgcolor="#3399CC"> <tr bgcolor="#B0D8FF"> <td class="cell" width="50%"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/white-point.gif" width="17" height="8"> <a class="cell" href="https://web.archive.org/web/20200116072227/http://en.wikipedia.org/wiki/Columnar_database" target_new> Columnar Databases (Wikipedia)</a></td> <td class="cell" width="50%">Start with this one</td> </tr> <tr bgcolor="#B0D8FF"> <td class="cell" width="50%"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/white-point.gif" width="17" height="8"> <a class="cell" href="https://web.archive.org/web/20200116072227/http://www.teradata.com/database/Teradata-Columnar/" target="_NEW">Columnar Product from Teradata</a></td> <td class="cell" width="50%"> Enhances <a href="https://web.archive.org/web/20200116072227/http://en.wikipedia.org/wiki/Teradata" targe_new>Teradata's</a> reputation in Gartner's Magic Quadrdrant in Data Warehouses.</td> </tr> <tr bgcolor="#B0D8FF"> <td class="cell" width="50%"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/white-point.gif" width="17" height="7"> <a class="cell" href="https://web.archive.org/web/20200116072227/http://www.danlinstedt.com/" target="_NEW">Data Vault from Dan Linstedt</a></td> <td class="cell" width="50%">I was unable to find any definitive White Papers or publications that would tell me directly what exactly is a Data Vault.</td> </tr> <tr bgcolor="#B0D8FF"> <td class="cell" width="50%"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/white-point.gif" width="17" height="7"> <a class="cell" href="https://web.archive.org/web/20200116072227/http://en.wikipedia.org/wiki/Entity%E2%80%93attribute%E2%80%93value_model" target="_NEW">E-A-V (Wikipedia) </a></td> <td class="cell" width="50%" bgcolor="#B0D8FF">A good description of what Entity-Attribute-Value means</td> </tr> <tr bgcolor="#B0D8FF"> <td class="cell" width="50%"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/white-point.gif" width="17" height="7"> <a class="cell" href="data_models/user_defined_hierarchies/index.htm"> E-A-V in practice</a></td> <td class="cell" width="50%">Shown in the User-Defined Hierarchies Data Model on our Web Site</td> </tr> </table> <table width="100%" border="0" cellspacing="0" cellpadding="0" bgcolor="#3399CC"> <tr> <td><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/circle4.gif" width="20" height="20"></td> <td width="100%">&nbsp;</td> <td align="right"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/circle3.gif" width="20" height="20"></td> </tr> </table> </td> </tr> </table> </td> </tr> <tr> </tr> <tr> </tr> </table> <br> </td> </tr> </table> </td> </tr></table> <!--- START OF MAY 21ST. BOTTOM MENU BAR ---> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td height="32" background="/web/20200116072227im_/http://www.databaseanswers.org/images/nav_bg.jpg" align="left" valign="middle"> <table width="100%" border="0" cellspacing="0" cellpadding="0"> <tr> <td width="80%"><img src="/web/20200116072227im_/http://www.databaseanswers.org/images/spacer.gif" width="80" height="15"></td> <td align="LEFT" width="10%"><a style="text-decoration:none" href="about_us.htm"><font color="#FFFFFF" face="Verdana" size="2">About Us</font></a> </td> <td align="LEFT" width="15%"><a style="text-decoration:none" href="contact_us.htm"><font color="#FFFFFF" face="Verdana" size="2">Contact Us</font></a> </td> </tr> </table> </td> </tr> </table> <!--- END OF may 21st. BOTTOM MENU BAR ---> <!-- HERE BASE TABLE CLOSES, INSIDE WHICH ALL PAGE IS PLACED, AND WHICH WIDTH IS 90 PERCENTS --> </td> </tr> </table> </body> </html> <!-- FILE ARCHIVED ON 07:22:27 Jan 16, 2020 AND RETRIEVED FROM THE INTERNET ARCHIVE ON 17:16:51 Jun 27, 2023. JAVASCRIPT APPENDED BY WAYBACK MACHINE, COPYRIGHT INTERNET ARCHIVE. ALL OTHER CONTENT MAY ALSO BE PROTECTED BY COPYRIGHT (17 U.S.C. SECTION 108(a)(3)). --> <!-- playback timings (ms): captures_list: 173.744 exclusion.robots: 0.066 exclusion.robots.policy: 0.057 cdx.remote: 0.055 esindex: 0.008 LoadShardBlock: 149.526 (3) PetaboxLoader3.datanode: 161.027 (4) load_resource: 206.617 PetaboxLoader3.resolve: 131.917 -->