2 Replies Latest reply on Dec 31, 2015 10:25 PM by Bora Beran

    Using normalised data from Microsoft SQL DataBase

    Christian Contino

      Hello,

       

      I have time series data with multiple dimensions in a Microsoft SQL database.

       

      It is normalised shaped something like this:

      (Real data has about 60 fields, 100's of cities, and dozens of other headings (suburb etc)

       

      DateCityFieldValue
      1-Jan-2000SydneyIce Cream Sales20
      1-Jan-2000SydneyTemperature31
      2-Jan-2000SydneyIce Cream Sales22
      2-Jan-2000SydneyTemperature33

       

      How do I make this data useful in Tableau, as looking at their own documentation, http://kb.tableau.com/articles/knowledgebase/preparing-excel-files-analysis, it seems that normalised is the way to go.

       

      But when you look at all of the inbuilt demo files in Tableau, they are actually using denormalised data shaped like this:

      DateCityIce Cream SalesTemperature
      1-Jan-2000Sydney2031
      2-Jan-2000Sydney2233

       

      By using data shaped like the second table, I get a pill/measure for "Ice Cream Sales" and "Temperature", and you can easily drag them around etc.

       

      Am I missing something?

      Thank you all for you help.

        • 1. Re: Using normalised data from Microsoft SQL DataBase
          Bill Lyons

          What you call "normalized," I would call "over-normalized." In none of the examples in the document you reference do they suggest having a field name be a column value. Each column should have values that would be considered together in a set to be aggregated in some way (sum, average, etc.). Temperature and ice cream sales may be correlated in some way, but you would never add a temperature to ice cream sales. Every value in a measure column should be in the same units (e.g. currency in dollars, weight in pounds, quantity, etc.). Dimension values should all be values in the same category (e.g. names of cities, flavors of ice cream, etc.).

           

          A more common problem encountered by new Tableau users is to have multiple columns for values that should be a single column, for example they have separate columns for sales in 2010, 2011, 2012, 2013, 2014, etc. This frequently needs to be reshaped or "pivoted" to have a column for "year," and a column for "sales." This is what the old Excel reshaper tool did, and what the new data connector does in 9.0.

           

          I hope that helps.

          • 2. Re: Using normalised data from Microsoft SQL DataBase
            Bora Beran

            Hi Christian,

            Depending on what you're trying to do, you can achieve this without transforming your data by simply using Field on a number of shelves. E.g. you can show average ice-cream sales and average temperature on the same chart as different color line by dragging Field onto Color shelf.

             

            If you want to use different aggregations or use them in the same sheet in different calculations you can break them up by writing a calculation like

             

            IF [Field]='Temperature' then [Value] END

             

            And another calculated field like

             

            IF [Field]='Ice Cream Sales' then [Value] END

             

            This way you're breaking each field into a separate column since each of these fields only return the values in the [Value] column if field is of particular type.

             

            If you really want to transform your data, you can use Custom SQL option in the Data tab and write a SQL query that takes advantage of PIVOT function on SQL Server.

             

            http://sqlhints.com/2014/03/10/pivot-and-unpivot-in-sql-server/

            1 of 1 people found this helpful