title: Dimensional modeling on columnar databases
body: <p>I have started learning cloud architecture and found out that they all are using columnar databases which claims to be more efficient as they are storing column rather than a row to reduce duplicate. </p>
  
  <p>From a data mart perspective (lets say for an organization a department only want to monitor internet sales growth and some other department want to focus on outlet performances), <strong>how can I design an architecture which can handle data load and provide easy data access.</strong> I know how data mart can be easily designed on top of it and end user doesn't have to bother about calculation at all.</p>
  
  <p>I had have experience in SSAS (OLAP) in which all calculation on a large data warehouse is already computed and a normal business user can directly connect to the cube and analyze the data with self service BI tool (as simple as drag and drop) on the other hand columnar databases seems to follow ELT approach and leaves all computation on either queries(views) or on reporting tool. </p>
  
  <p>As I have experience in SQL Server, I presume that my query (for example below)</p>
  
  <pre><code>SELECT 
    region,
    state,
    City,
    Country,
    SUM(Sales_Amount),
    AVG(Discount_Sale),
    SUM(xyz)
    ....
  FROM Columnar_DataTable
  </code></pre>
  
  <p>is going to scan complete table which can increase cost. Imagine if above query is executed more than 1000 times in a day for a large enterprise. </p>
  
  <p><strong>So, is it appropriate to create a OLAP on top of columnar databases with dimensional modeling or it is better to load the data first then filter/transform it on reporting tool?</strong> Considering that most Self service BI tool already have this in mind and limit the usage of data consumption (ex: Power BI desktop community edition allows 10 GB per data set) and forces user to do his/her own calculation. </p>
  
  <ul>
  <li><p>If we segregate the data into multiple tables then all reporting tools, anyways, needs relation between tables for filtering.</p></li>
  <li><p>If we keep single table format then reporting tool has to read all data before making any calculation. </p></li>
  </ul>