4 Replies Latest reply on Apr 3, 2018 1:13 PM by priti.shah.0

    Need guidance in creating datasource


      Quick Question:

      I have financial database on Oracle server.

      I’m creating dashboard for Financial Analytics.  It has max 4+ millions rows

      What is the best way to create datasource?

      1. Live connection with Tables and join in Tableau
        or create a view and connect with the views?
      2. Live connection or extract the data with refresh
        schedule (assume user ok with previous day data)
      3. Publish datasource or packaged workbook?

      Thanks for your guidance.

      -Priti Shah

        • 1. Re: Need guidance in creating datasource
          ShivaRam Chennapragada

          Hi Priti,


          As part of my job I develop, publish dashboards and data sources almost everyday. When I have new project to develop a dashboard, the first question I ask myself is about the data source. If I know a particular report would be used ad-hoc purpose then I would just join the tables within Tableau and publish it to the server.


          But, if I am developing a dashboard that provides weekly, monthly updates then I do this (my preferred method that involves good amount of SQL coding)- I create a new table in the database which would eventually act as a data source, I load this table with data through a Stored Procedure that JOINs, aggregates, pivots data from different tables (like an ETL). I setup a VisualCron job to do this task for me either everyday or week depending on the requirement. Once, the table is setup next step is development of dashboard, based on the performance I would decide if I want to create an extract or leave it as a live connection and publish it to Server. If I am creating an extract I setup the refresh schedule such a way that it runs after my Cron job is completed. With the new Hyper engine, my dashboards just fly. I love the performance.


          Ultimately, it depends on what level your data is aggregated to, user permissions to the underlying database tables etc.


          Here are some links that could be useful,

          Best Practices for Published Data Sources

          Set Up Data Sources


          Hope this information helps. Good luck!


          Thank you,

          Shiva Ram.

          • 2. Re: Need guidance in creating datasource

            Thanks ShivaRam,

            This is very helpful information!

            I'm planning to upgrade it to hyper very soon. anything you suggest what I should look for when upgrading to hyper?

            As you said you live with Tableau everyday, have you done financial Analytics on GL with large dataset?

            Thank you for your quick response.

            Priti Shah

            • 3. Re: Need guidance in creating datasource
              ShivaRam Chennapragada

              Hi Priti,


              Glad the information helped.


              The upgrade was smooth for us, however I upgraded out Test environment first, tested it for a month before upgrading our Production. It has been an easy transition, all your extracts would automatically be converted to Hyper extracts. There is nothing different you'd need to do.


              Coming to your next question, currently our GL TDS is a JOIN of GL table with 4 other tables in SQL Server, published as a live connection. It is one of our most popular data source to look at various KPIs. Though it doesn't fly, it is relatively faster than other POS TDS due to level of aggregation. However, as I mentioned in earlier response, I have some custom built aggregated tables based off of GL that serve a particular reporting purpose. The data is rolled-up, pivoted, transformed and loaded into a table which results in better performance.


              Hope this helps.


              Thank you!

              Shiva Ram.