1 Reply Latest reply on Mar 20, 2013 2:53 PM by Joshua Milligan

    Table Structure

    Michael Herring

      We receive data from a vendor, which we would like to store is one SQL Table. The measures are non additive and the data is presented at different grains. There are possibly 60 different permutations (reports) that we want to capture. I wanted to store the data in one table as follows:

       

       

      StoreHierarchy
      Dimension1Dimension2
      Non Additive Measure
      ARegion | CountryNorth AmericaUSA12
      ARegion | CountryNorth AmericaCanada51
      ARegion

      North America

      -57
      B

      Region | Country

      North AmericaUSA23
      BRegion | CountryNorth AmericaCanada12
      BRegion North America-27
      Total


      66
      A
      Type | Sub-typeStrip MallAnchor Store12
      AType | Sub-typeStrip MallNon-Anchor14
      AType | Strip Mall
      11
      BType | Sub-typeMallUpper Level15
      BType | Sub-typeMallLower Level 19
      BType | Sub-typeMall
      16
      Total


      14

       

      Is there an optimal way to store the data:

       

      1) Parse the data into tables By Dimension (Region, Type) and Grain (1 vs 2) which would create 60 plus permutations of the data

      2) Store in single table and create views which represent (1)

      3) Store in single table and use Tableau to dynamically change the name of Dimension1, Dimension2 to represent the Hierarchy

       

      Note the data is governed by a confi and therefore I can't post a datafile  - the above is purely representative of the issue.

        • 1. Re: Table Structure
          Joshua Milligan

          Michael,

           

          I don't have a comprehensive answer, but hopefully a response will bump this up and others might chime in.

           

          I think you'll find option #1 is the best approach.  I've typically found it best to model data using facts and dimensions that are at natural grains and don't mix the grains within a single table.  Tableau will work well with a flattened view of the data (though if you have high volumes of data, definitely use a good star-schemas with very little or no snowflaking for performance).

           

          Option 2 is definitely possible, but you might have some performance issues as Tableau is very good at optimizing queries when it understands the tables and joins in play.  A single view can obscure that and prevent optimal queries.

           

          Option 3 would likely work at first, but you'll probably find yourself limited later on.

           

          Regards,

          Joshua