1 Reply Latest reply on Jun 21, 2016 4:03 PM by Patrick A Van Der Hyde

    Optimal data structures and systems for data...

    Buster Christenson

      Were at a point in our warehouse development that a decision needs to be made on semantic layer.  I need some guidance on data structures optimal for us by Tableau.

       

      We are at a point where we have a 3NF data warehouse on Netezza with much of the data we need and now are looking to add a semantic layer and optimization of data structures to make it easier to consume for our Tableau community.

       

      My big question is around optimizing the data structures for end-user ease of use primarily and secondarily for performance.

       

      The options as I see it are;

      1. Don’t do an SSAS cubes or De-normalized Star/Snowflake models
        • Leverage materialized views on Netezza to create completely flatten/de-normalize views
          by subject that we’ve already built for SAS analytics OR leverage SQL or Data
          Sources to establish joined data sources.
        • Establish aggregate extracts for full history of warehouse.
        • Establish non-aggregate extracts limited to current+last year data in the warehouse
        • Establish non extract direct connections to 3nf structures with fewer users permitted
      2. Build dimensional marts off of our 3nf warehouse
        • Usual CIF approach here by creating marts by subject or department
        • Aggregated star schema
        • Establish non-aggregate extracts connected to the star schema
        • Establish non extract direct connections to 3nf structures with fewer users permitted
      3. Build Cubes off of our 3NF warehouse
        • Build cubes with aggregation off the 3nf atomic tables
        • Establish non-aggregate extracts connected to the cubes
        • Establish non extract direct connections to 3nf structures with fewer users permitted
      4. Build Marts on SQL server with columstore
        • Extract data from netezza to sql servers
        • Build out Column Store Indexes against every table
        • Connect Tableau directly

       

      Any other approaches I should be considering here?