blog/mdad/data-warehousing-and-olap/index.html (view raw)
1<!DOCTYPE html>
2<html>
3<head>
4<meta charset="utf-8" />
5<meta name="viewport" content="width=device-width, initial-scale=1" />
6<title>Data Warehousing and OLAP</title>
7<link rel="stylesheet" href="../css/style.css">
8</head>
9<body>
10<main>
11<p>Business intelligence (BI) refers to systems used to gain insights from data, traditionally taken from relational databases and being used to build a data warehouse. Performance and scalability are key aspects of BI systems.</p>
12<div class="date-created-modified">Created 2020-03-23<br>
13Modified 2020-04-01</div>
14<p>Commonly, the data in the warehouse is a transformation of the original, operational data into a form better suited for reporting and analysis.</p>
15<p>This whole process is known as Online Analytical Processing (OLAP), and is different to the approach taken by relational databases, which is known as Online Transaction Processing (OLTP) and is optimized for individual transactions. OLAP is based on multidimensional databases simply by the way it works.</p>
16<p>The Business Intelligence Semantic Model (BISM) refers to the different semantics in which data can be accessed and queried.</p>
17<p>On the one hand, MDX is the language used for Microsoft’s BISM of multidimensional mode, and on the other, DAX is the language of tabular mode, based on Excel’s formula language and designed to be easy to use by those familiar with Excel.</p>
18<h2 class="title" id="types_of_data"><a class="anchor" href="#types_of_data">¶</a>Types of data</h2>
19<p>The business data is often called detail data or <em>fact</em> data, goes in a de-normalized table called the fact table. The term «facts» literally refers to the facts, such as number of products sold and amount received for products sold. Different tables will often represent different dimensions of the data, where «dimensions» simply means different ways to look at the data.</p>
20<p>Data can also be referred to as measures, because most of it is numbers and subject to aggregations. By measures, we refer to these values and numbers.</p>
21<p>Multidimensional databases are formed with separate fact and dimension tables, grouped to create a «cube» with both facts and dimensions.</p>
22<h2 id="places_to_store_data"><a class="anchor" href="#places_to_store_data">¶</a>Places to store data</h2>
23<p>Three different terms are often heard when talking about the places where data is stored: data lakes, data warehouses, and data marts. All of these have different target users, cost, size and growth.</p>
24<p>The data lake contains <strong>all</strong> the data generated by your business. Nothing is filtered out, not even cancelled or invalid transactions. If there are future plans to use the data, or a need to analyze it in various ways, a data lake is often necessary.</p>
25<p>The data warehouse contains <strong>structured</strong> data, or has already been modelled. It’s also multi-purpose, but often of a lot smaller scale. Operational users are able to easily evaluate reports or analyze performance here, since it is built for their needs.</p>
26<p>The data mart contains a <strong>small portion</strong> of the data, and is often part of data warehouses themselves. It can be seen as a subsection built for specific departments, and as a benefit, users get isolated security and performance. The data here is clean, and subject-oriented.</p>
27<h2 id="ways_to_store_data"><a class="anchor" href="#ways_to_store_data">¶</a>Ways to store data</h2>
28<p>Data is often stored de-normalized, because it would not be feasible to store otherwise.</p>
29<p>There are two main techniques to implement data warehouses, known as Inmon approach and Kimball approach. They are named after Ralph Kimball <em>et al.</em> for their work on «The Data Warehouse Lifecycle Toolkit», and Bill Inmon <em>et al.</em> for their work on «Corporate Information Factory» respectively.</p>
30<p>When several independent systems identify and store data in different ways, we face what’s known as the problem of the stovepipe. Something as simple as trying to connect these systems or use their data in a warehouse results in an overly complicated system.</p>
31<p>To tackle this issue, Kimball advocates the use of «conformed dimensions», that is, some dimensions will be «of interest», and have the same attributes and rollups (or at least a subset) in different data marts. This way, warehouses contain dimensional databases to ease analysis in the data marts it is composed of, and users query the warehouse.</p>
32<p>The Inmon approach on the other hand has the warehouse laid out in third normal form, and users query the data marts, not the warehouse (so the data marts are dimensional in nature).</p>
33<h2 id="key_takeaways"><a class="anchor" href="#key_takeaways">¶</a>Key takeaways</h2>
34<ul>
35<li>«BI» stands for «Business Intelligence» and refers to the system that <em>perform</em> data analysis.</li>
36<li>«BISM» stands for «Business Intelligence Semantic Model», and Microsoft has two languages to query data: MDX and DAX.</li>
37<li>«OLAP» stands for «Online Analytical Processing», and «OLTP» for «Online Transaction Processing».</li>
38<li>Data mart, warehouse and lake refer to places at different scales and with different needs to store data.</li>
39<li>Inmon and Kimbal are different ways to implement data warehouses.</li>
40<li>Data facts contains various measures arranged into different dimensions, which together form a data cube.</li>
41</ul>
42<h2 id="references"><a class="anchor" href="#references">¶</a>References</h2>
43<ul>
44<li><a href="https://media.wiley.com/product_data/excerpt/03/11181011/1118101103-157.pdf">Chapter 1 – Professional Microsoft SQL Server 2012 Analysis Services with MDX and DAX (Harinath et al., 2012)</a></li>
45<li><a href="https://youtu.be/m_DzhW-2pWI">YouTube – Data Mining in SQL Server Analysis Services</a></li>
46<li>Almacenes de Datos y Procesamiento Analítico On-Line (Félix R.)</li>
47<li><a href="https://youtu.be/qkJOace9FZg">YouTube – What are Dimensions and Measures?</a></li>
48<li><a href="https://www.holistics.io/blog/data-lake-vs-data-warehouse-vs-data-mart/">Data Lake vs Data Warehouse vs Data Mart</a></li>
49</ul>
50</main>
51</body>
52</html>
53