4 Replies Latest reply on Mar 23, 2017 7:50 PM by Romane Amiot

    Building a database adapted to Tableau

    Romane Amiot

      Hello everybody,

       

      I have an issue with the format of the database (for controlling purposes) I created which does not seem adapted for Tableau.

       

      Indeed, I first built a first database on Excel only with all dimensions on a row and with one KPI per column. So it was displayed like “stairs” and then, when I uploaded this file on Tableau it worked quite well because I had all my KPIs directly available in “Measures”. I also have to display to kinds of currency which should be viewed as Filters on Tableau. So, in my Excel file, I have “EURO” and “entity currency” positioned on top of each other.

       

      TABLEAU_ISSUE_EXCEL1.pngTABLEAU_ISSUE_EXCEL2.png

       

       

       

      However, I can not use only Excel anymore because this is not powerful enough and we want to compile data from various sources and limit the calculations on Excel. As a result, I have to retrieve the core data from Excel but then compile everything on Access and also do some calculations on Access and create links between various tables. But Access works differently from Excel and to me, we have to have all our data in one column to be able to do calculations then. Concerning the  currency, I created one column for the euro values and another one for the entity currency thanks to a formula linked to an exchange rates.

      Here it is how it looks like:

      TABLEAU_ISSUE_ACCESS1.png

       

      The problem is when I upload this file on Tableau:

       

      TABLEAU_ISSUE_ACCESS2.png
      TABLEAU_ISSUE_ACCESS3.png

      Indeed, I do not have one measure per KPI anymore so I can not build graphs with several KPIs. I can only display them as filters but if I click on more than one then it sums them and that is not what I want.
      I only have one measure for euro values and one for local currencies (“LC”) which does not allow me anymore to set a filter per currency.

       

      I could not find any article concerning the constraints  for building databases for tableau (do KPIs have to be on different columns, presented as stairs in my Access file…).
      Also, could you tell me the constraints to build my datamodel on Access in order to be able to have graphs displaying various KPIs over years etc?

       

      I remain at your disposal for further information.

      Thank you very much in advance,

        • 1. Re: Building a database adapted to Tableau
          Chris McClellan

          If I've understood your post and screenshots properly, there's a fundamental change that you've made when migrating to Access.  In Excel your KPIs were separate columns, in Access your KPI names are all in a single column and another column to store the values.

           

          This is still usable in Tableau, but you could also create your Access tables to look exactly like Excel.

           

          Let's say that you want to stay with your current Access design, BUT you want your KPIs back as well, simply create calculated fields that do this:

           

          Calculated field name : Fixed Costs adj (EUR)

          Expression : if [KPI] = "Fixed Costs adj" then [EUR] end

           

          Calculated field name : Fixed Costs adj (LC)

          Expression : if [KPI] = "Fixed Costs adj" then [LC] end

           

           

          You could use combine this by using a parameter to choose currency ....

           

          Parameter name : [Choose currency]

          Parameter values : EUR, LC

           

          Calculated field name : Fixed Costs adj

          Expression : if [KPI] = "Fixed Costs adj" and [Choose currency] = "LC" then [LC] else [EUR] end

           

           

           

          But you only have to do this if you want multiple KPIs in the same graph at the same time, if you only want 1 KPI then just filter on it

           

          Does that make sense ?

          1 of 1 people found this helpful
          • 2. Re: Building a database adapted to Tableau
            Romane Amiot

            Hello Chris,

             

             

            Thank you very much for your quick and detailed answer.
            I tried what you advised me and it actually works well. However, I have hundreds of different KPIs so I fear that this method takes me really too much time.. And yes, I actually want multiple KPIs displayed in a common table so I cannot just filter on it.

             

            I assume your solution was the best one if we stay with the current Access design.

             

             

            But you suggested we could go for another solution if we keep the Excel structure on Access i.e. having one KPI per column and building it stair-shaped to have all the corresponding dimensions on the same row..
            Could you please tell me more about how you think I could build my Access database if I keep the same structure?
            Indeed, I completely changed the structure on Access to be able to link this table with other dimensions located in new tables .. For instance, my Access database contains a table with geographical dimensions (longitude and latitude) which are automatically added next to the name of the country in question thanks to a link between the geographical dimensions table and the table in which I have all my KPIs. Same thing for a table which contains the up to date exchange rates and allows us to have the euro values directly converted in local currencies..
            So, do you think it would be possible to keep the "Excel structure" in Access and keep on linking other tables to my main table to add up-to-date and automatic information?

             

            I hope this long text is clear enough for you.

            Thanks again for your help!!
            Kind regards,

            • 3. Re: Building a database adapted to Tableau
              Chris McClellan

              Hi,

               

              It all depends on the data model.  If you have too many KPIs to create calculated fields then having each KPI in a separate column is the proper way to go.  If you need to have a KPI dimension for storage and joining, then you can try creating a view which changes the data into a better format for Tableau.

               

              Either way it's the balance of ease of joining the tables, storing the data and usage in Tableau.  Which one you favour is really determined by the data, where you want to put the work and what just "makes sense"

              1 of 1 people found this helpful
              • 4. Re: Building a database adapted to Tableau
                Romane Amiot

                Hello Chris,

                 

                Thank you for your answer. I will try to rebuild my datamodel based on your pieces of advices.

                 

                Kind regards,