title: SQL or OLAP schema design for funnel analysis
body: <p>I have an ecommerce system where users go through a few stages before they make a purchase (visit website, browse catalog, pick a product, goto purchase page, insert credit details etc...).</p>
  
  <p>Each event is saved in the following SQL events table:</p>
  
  <pre><code>SessionId
  EventTypeId (PageView, Click, Type),
  EventValue (HomePage, Button-1 etc...)
  Timestamp
  </code></pre>
  
  <p>Each session is saved in the following Sessions table (simplified):</p>
  
  <pre><code>SessionId
  SelectedProductId
  SessionPurchaseAmount (0 for sessions without a purchase)
  </code></pre>
  
  <p>My question is how to design the Database schema schema so I could use it to filter / analyze sessions by funnel? One more thing I need to figure out is how to flatten the SQL schema to OLAP structure.</p>
  
  <p>The funnel should be defined dynamicaly like they do it in MixPanel / GA / other web analytics platforms.</p>
  
  <p>I want to use PowerPivot to analyze it.</p>
  
  <p><strong>Example of data:</strong></p>
  
  <p>Events:</p>
  
  <pre><code>SessionId EventType EventValue Timestamp
  
  1         PageView  Home       01:01:00
  1         PageView  Catalog    01:02:00
  1         PageView  Cart       01:02:30
  2         PageView  Home       04:01:03
  </code></pre>
  
  <p>Sessions:</p>
  
  <pre><code>SessionId SelectedProduct PurchaseAmount
  
  1         Dress-AA2       $12.00           
  2         NULL            $0.00
  </code></pre>
  
  <p>I want to see the result data in a funnel which i define dynamically.
  For instance, if I set the funnel to be <code>Home -&gt; Catalog -&gt; Cart</code> the data I want to see is:</p>
  
  <pre><code>Home     100%
  Catalog  50%
  Cart     50%
  </code></pre>
  
  <p>Thanks!</p>