{"id":726,"date":"2007-08-02T01:00:22","date_gmt":"2007-08-02T09:00:22","guid":{"rendered":"https:\/\/systematichr.com\/?p=726"},"modified":"2007-08-02T01:00:23","modified_gmt":"2007-08-02T09:00:23","slug":"business-intelligence-data-marts-and-cubes","status":"publish","type":"post","link":"https:\/\/systematichr.com\/?p=726","title":{"rendered":"Business Intelligence:  Data Marts and Cubes"},"content":{"rendered":"<p><a href=\"https:\/\/systematichr.com\/?p=725\" target=\"_blank\">Continued<\/a><\/p>\n<p><strong>Business Intelligence:  Data Marts<\/strong><br \/>\nLife finally gets exciting.  We\u2019ve loaded our data through the ETL into an ODS.  We\u2019ve enriched the data into a usable form for aggregated, multidimensional reporting.  Now we just need to create the facilities for that multidimensional reporting.  This is going to be a challenge to explain in layman\u2019s terms, but I\u2019ll give it a shot.<\/p>\n<p>We\u2019re all pretty used to relational databases (RDBMS) by now.  These standard table structures allow us to easily and efficiently store data.  However, the structure of RDBMS does not facilitate multidimensional reporting very well.  For that, we need a star schema.  What the heck is a star schema you ask?  Star schemas are basically a way of organizing data into what we call facts and dimensions.  Let\u2019s take a couple of real world examples.<\/p>\n<ul>\n<li>Let\u2019s say we want to look into Joe\u2019s performance management.  We\u2019ll call Joe\u2019s performance score the \u201cfact.\u201d  This fact is nice to have, but to really see what\u2019s hiding behind it, we\u2019d want to add a time dimension so that we could drill through and understand the trends in Joe\u2019s performance.<\/li>\n<li>To make this a step more complex, let\u2019s say we wanted to go a level higher and view everyone\u2019s performance score correlated with time and job code.  Now, the performance score is still the fact, but the dimensions are now job code and time.  Note that we have aggregated the data for the organization.  We\u2019d probably want to go one step further and ad the organizational unit as a dimension so we could compare across businesses and departments as well.  Let\u2019s take this out one level further.  Now that we have organizational unit, that might actually be a set of data fields.  We might want to be able to look at performance over time by org unit, and further break that into location, manager ID, state, or country.  You can see how this quickly gets fairly convoluted and you\u2019d never want to write an ad hoc report linking all these tables.<\/li>\n<\/ul>\n<p><strong>Business Intelligence:  Cubes<\/strong><br \/>\nSo if RDBMS tables are not appropriate for analytical multidimensional reporting, what\u2019s better?  If RDBMS tables are 2 dimensional, then I guess what we want is a cube \u2013 literally called a cube.  In general, a cube does not actually exist anywhere.  The rules and structure of a cube generally exist either in the data warehouse or in the end user reporting tool (which is where I prefer it).  This cube is simply a virtual representation of the data mart that allows easier visualization and reporting.  But really, everything you needed was already in the data mart.<\/p>\n<p>So here\u2019s what we\u2019ve discussed:<\/p>\n<ol>\n<li>The ETL pulls data from multiple sources into the ODS<\/li>\n<li>The ODS holds raw data for possible operational reporting and data extract<\/li>\n<li>The ETL pulls data from the ODS into the enriched data warehouse (we now have a data warehouse)<\/li>\n<li>The Data Marts create star schemas that organize data into meaningful structures which will later facilitate multidimensional reporting<\/li>\n<li>The reporting tool creates cubes which refer back to the data marts and create those beautiful analytical reports!!!<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>Continued Business Intelligence: Data Marts Life finally gets exciting. We\u2019ve loaded our data through the ETL into an ODS. We\u2019ve enriched the data into a usable form for aggregated, multidimensional reporting. Now we just need to create the facilities for&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_coblocks_attr":"","_coblocks_dimensions":"","_coblocks_responsive_height":"","_coblocks_accordion_ie_support":"","footnotes":""},"categories":[27,2],"tags":[],"class_list":["post-726","post","type-post","status-publish","format-standard","hentry","category-data-metrics","category-hr-technology"],"_links":{"self":[{"href":"https:\/\/systematichr.com\/index.php?rest_route=\/wp\/v2\/posts\/726","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/systematichr.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/systematichr.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/systematichr.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/systematichr.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=726"}],"version-history":[{"count":0,"href":"https:\/\/systematichr.com\/index.php?rest_route=\/wp\/v2\/posts\/726\/revisions"}],"wp:attachment":[{"href":"https:\/\/systematichr.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=726"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/systematichr.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=726"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/systematichr.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=726"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}