4 Replies Latest reply on Sep 25, 2014 8:51 AM by Shankar Saikia

    Reporting from Relational Database

    kevin decker

      I have a question concerning using Tableau to generate reports directly from a relational Database.

       

      We have developed a project management tool for a customer that uses a relational database.

      The customer has purchased Tableau and wants to use it to pull reports from our Project Management tool.

      I have included the Database schema below to help answer the question.

       

      The Forums feedback would be extremely helpful.

       

      Database Schema:

       

      PM_PROJECT – master table containing projects, tasks (incl.  sites) and their hierarchies. Also stores project/task templates.

      ELEMENT_TYPE defines entity type. Possible values: [ PROJECT | TASK | PROJECT_TEMPLATE | TASK_TEMPLATE ]
      CHILD_LEVEL depth of given node on project/tasks tree structure
      ROOT_PARENT id of top level node ( project )
      PARENT_FK id of parent node
      PATH description of path to given node in the project tree. format: /project_name(project_id)/task1(task1_id)/task2(task2_id)
      CHILDREN_COUNT number of child nodes of a given node
      PROJECT_LEVEL numbering value for given node. Top level nodes ( projects ) has value 0, first level child nodes ( tasks ): 1.1, 1.2, second level nodes: 1.1.1, 1.1.2 and so on

      TAG table holds tags related data, incl. relation with their “owners”
      ATTACHMENT_FK id of related attachment
      PROJECT_FK id of related project
      DELIVERABLE_FK id of related deliverable
      CHANGE_LOG_FK id of related change log entry
      ISSUE_LOG_FK id of related issue log entry

       

      PM_DEPENDENCIES table holds data describing scheduling dependencies between projects/tasks
      TYPE relation's type. possible values: [ START_TO_START | START_TO_FINISH | FINISH_TO_START | FINISH_TO_FINISH ]
      example: FINISH_TO_START means that successor task can start only when predecessor task is finished
      PREDECESSOR_FK predecessor project id
      SUCCESSOR_FK successor project id

      PM_DELIVERABLE table holds deliverables’ entity records
      ASSIGNEE_ID id of user to whom the given task was assigned
      STATUS possible values: [ NEW | DELIVERED ]
      PROJECT_FK id of corresponding row in PM_PROJECT table

      PM_ISSUE_LOG table holds issue log entries
      ASSIGNEE id of user to whom the given issue was assigned
      STATUS possible values: [ NEW | IN_REVIEW | RESOLVED]
      PROJECT_FK id of related project
      CATEGORY possible values: [ OSP_PERMIT | OSP_CONSTRUCTION | OSP_OTHER | CUSTOMER_NTP | CUSTOMER_POWER | CUSTOMER_OTHER | OTHER_NTP | OTHER_TOWERMOD | OTHER_EQUIPMENT | OTHER ]

      PM_CHANGE_LOG table holds change log entries
      OBJECT_TYPE type of related entity. possible values: [ PROJECT | TASK | DELIVERABLE ]
      OBJECT_ID id of related entity
      STATUS possible values: [ NEW | IN_REVIEW | APPROVED | REJECTED ]
      PROJECT_FK id of related project
      CATEGORY possible values: [ ADD | DROP | CHANGE | OTHER ]

      PM_PROJECT_ATTACHMENT table holds project/task attachments’ data
      ATTACHMENT_FILE_FK id of corresponding row in PM_PROJECT_ATTACHMENT_FILE table storing actual attachment content
      PROJECT_FK id of related project

      PM_TRIGGERED_TASK table holds triggered tasks related data
      STATUS_TO_SET possible values: [ DRAFT | NEW | ACTIVE | AWAITING | ON_HOLD | CANCELLED | COMPLETE ]

      PM_PROJECT_NOTES holds notes entity data and relations between projects/tasks and notes
      PM_RESOURCES holds relation between projects/tasks and resources incl. the users' role ( watcher/asignee )
      PM_PROJECT_ISSUES table holds relation between projects/tasks and issues
      PM_PROJECT_CHANGE_LOGS table holds relation between projects/tasks and change log entries

      Common columns across all tables:
      ID, CREATION_DATE, MODIFICATION_DATE, REMOVAL_DATE

        • 1. Re: Reporting from Relational Database
          Alex Kerin

          What are you trying to do - join tables? What relational database is it? If it's one that is supported? http://onlinehelp.tableausoftware.com/current/pro/online/en-us/exampleconnections_overview.html

          • 2. Re: Reporting from Relational Database
            kevin decker

            Its an SQL database. We have jasper reports free version embedded in our software platform. The customer has purchased Tableau to use the drag and drop functionality. I am asking the question because I have been told that we need to export data to a flat file in order for the customer to build reports.

             

            Its is also worth noting that I am the Sales Guy on the account so my technical knowledge is limited. The database schema I put up is the SQL database structure. I am trying find out if we need to export the data from out project management module on a near real time basis in order for the customer to build reports using tableau. 

             

            I am probably not explaining it in the correct technical manner, I apologize for that.

            • 3. Re: Reporting from Relational Database
              Jonathan Drummey

              The general answer is, "Yes, Tableau can do that."

               

              The specific answer(s) requires a number of other questions to be answered, based around what you want these reports to be, how much data you're dealing with, what the hardware environment is like, etc.

               

              Depending on what data the reports need at what level of granularity, you will need to set up one or (possibly) more data sources in Tableau. The Tableau data source could be entirely constructed using Tableau's connection functionality, using Custom SQL in Tableau, custom queries or views that are set up in your database, or some combination of the above. A definite complication here is that Tableau doesn't natively parse a linked node structure, so you're going to need to "flatten" the data to display the hierarchies.

               

              Depending on how you've set up the data sources, data volumes, hardware sizing, performance tuning, performance expectations, and what kind of views are being created in Tableau, you may be able to do a live connection, or need to have an intermediate source like a Tableau data extract.

               

              Either on your end or your customer's end there's going to need to be someone with some data analyst skills and knowledge of Tableau to interpret the requirements and figure out the ideal data structure for Tableau.

               

              Jonathan

              • 4. Re: Reporting from Relational Database
                Shankar Saikia

                Jonathan,

                 

                When the source data is in a relational database and in 3NF normalized format, would you recommend that it is better to first convert the data into a multidimensional model before connecting to Tableau? I know that Tableau allows us to connect to relational sources like SQL Server, Oracle etc. However, if the data in the relational database is in an operational system such as an order management system and/or purchasing system or any ERP, wouldn't it be better to create a multidimensional model before running queries using Tableau?