/
Interim Solution - Adding Data to Cognos or Tableau

Interim Solution - Adding Data to Cognos or Tableau

Summary

  • What:

    • Connect BI Tool directly to data source

    • Report developer creates Tableau data source or Cognos data module

    • Report developer then creates reports off the Tableau data source or Cognos data modules

  • Pros:

    • Allows report developers to build Cognos or Tableau report prior to the data source being added the applicable Activity Hub(s)

    • Once the data source is available in the applicable Activity Hub(s), the reports can be re-pointed

    • Can be blended with Activity Hub views (may reduce speed of report)

    • Works best with on-prem servers

  • Both Pro & Con:

    • Maintenance of Tableau data source or Cognos data module is performed by Report developer

  • Cons:

    • May not be available for applications located in the cloud or Amazon Web Services (AWS)

    • A poorly written report can negatively impact the performance of the data source

    • Does not allow for data to be used by data integrations

    • May not use AH naming standards or BIA development standards, therefore when the your data source is added to an Activity Hub you’ll need to update your reports to point at the new fields

  • How:

Cognos Process

  1. The Business Team opens a ticket with BIA by emailing busintel@ucsd.edu

    1. The person who opens this ticket should be a Cognos report developer or a team lead with a report developer resource

    2. Include the following:

      1. Name of the data source

      2. Connection details for the data source: port and url or server name

      3. The name of the data base administrator, the person who can create username logins and passwords for the data source

      4. The Active Directory group that should have report developer access to this data.

      5. The Active Directory group that should have consumer access to this data.

  2. BIA requests firewalls to be lowered between data source and Cognos

  3. BIA requests a username and password for the data source

  4. BIA creates the folder structure in Cognos

    1. One folder will be created to hold the in-progress data source and in-progress reports

    2. One folder will be created to hold the final data source module

  5. BIA imports data into Cognos - this will be all of the data

  6. Business Team models the data in a Cognos data module

    1. BIA can provide training as needed

    2. This doesn’t have to be all of the data from the data source, more can be added to later if needed

  7. Business Team can combine data modules of different data sources

    1. This was the report developer can create data module joining the uploaded data source and Activity Hub packages

  8. Business Team builds reports off the data module

  9. Additional support needed after the data module is created can be secured by emailing busintel@ucsd.edu.

Cognos Supported Connectors

Cognos Connectors FAQs

  • Unlike Activity Hub views, data modules directly connected to data sources can only support one connection to a server at a time. If you would like to attach to a -DEV, -QA and -Production server you will need to create 3 data modules, one for each environment/server.

  • Direct connection to production servers will create additional demand on that server resource. To prevent issues with your production server be sure to write clean, effecient reports and use as little data as you can.

 

Tableau Process

Tableau data sources that connect to data source servers should not be created or published from a laptop or desktop.

Tableau data sources that connect to data source servers should only be created and published from a virtual machine (VM).

  1. Request a Virtual Machine

    1. Tableau report developer opens a SNOW ticket with ITS-WorkstationLifecycle to request a Tableau virtual machine (VM).

    2. Best practice is to isolate your live connections to data bases. You can do this by using a VM.

    3. Tableau has a limitation where users are unable to publish a live connection to Tableau server if multiple people are using the same VM.

  2. Request connection to data source

    1. Tableau report developer opens a SNOW ticket with the DBA for the data source they want to use, requesting a username and password.

    2. Contact your DBA if you are having issues connecting Tableau desktop to data base, if you are only using your desktop.

    3. Contact your DBA and ITS-WorkstationLifecycle if you are having issues connecting Tableau desktop to data base, if you are using a VM.

    4. The data connector you use should be Tableau created and supported. See image at the bottom of this page for the current list.

  3. Model data source

    1. Business Team models the data in Tableau desktop on the VM

    2. BIA can provide training as needed

    3. Tableau data source should not include any visualizations

    4. See Blink > Tableau > FAQS for more tips.

  4. Request folder on Tableau Server

    1. The Business Team requests folders for the data source and workbooks to be published to, if the folders do not already exist, by emailing busintel@ucsd.edu

    2. Include the following:

      1. The Active Directory group that should have report developer access to this data.

      2. The Active Directory group that should have consumer access to this data.

    3. BIA creates the folder structure in Tableau, if one does not already exist

      1. One folder will be created to hold the in-progress data source and in-progress reports (optional)

      2. One folder will be created to hold the final data source

  5. Live connections or extract from live data

    1. Additional firewalls will need to be lowered between your data source and Tableau server in order to support live connections or extracts from live data.

    2. Email busintel@ucsd.edu with the name of your data source, server (aka connection) name, port number and type of connector you are using

    3. BIA will review and approver or deny based on connector type

    4. If approved, BIA will request that the Tableau admin lower the firewall

    5. Business Team can now publish data source to Tableau Server and verify connection is working

  6. Build Report

    1. Business Team builds reports off the data source

  7. Additional Support Contacts

    1. ITS-Fieldsupport for issues with your VM.

    2. Your local database admin (DBA) can help with drivers and security issues between Tableau desktop and your data source.

    3. ITS-BIA if you have any issues after publishing to Tableau server, need to modify folder security, need new folders or have questions about workbook development.

Tableau Connection FAQs

Q: I’m getting an authentication error with linked servers.

A: Open a ticket with ITS-DBA requesting an account be mapped to the linked server. Users may be able to connect to the lined DB in Tableau Desktop,  but will get a password/authentication error after publishing to Tableau Server.  If this occurs, the user should open a ticket with ITS-DBA to have their service account mapped to the linked server.

Q: I’m getting an error after publishing my data source to Tableau server, even though I’m not having any connection issues in Tableau Desktop.

Examples: Cannot connect. No listener at host. An error occurred while communicating with the data source. A communication error has been detected.

A: Open a ticket with busintel@ucsd.edu, a firewall may need to be lowered on Tableau server or a driver may need to be installed.

Q: Why can’t I connect my laptop/desktop to my data server?

A: UCSD policy is for firewall to always exist between your laptop/desktop and data servers. https://blink.ucsd.edu/technology/bi/data-guidelines.html#Data-and-Security-Resources

Q: Why do I have to use a VM to connect Tableau desktop to my data server?

A: Tableau desktop requires no firewalls between it and your data server. A VM allows this connection and follows security best practices.

Q: Why can’t I share a VM with others?

A: Tableau has a limitation that prevents publishing Tableau data sources to Tableau server when a Tableau desktop installation is being shared on a VM.

Q: Why is my extract not working?

Please contact your DBA for that server. We have found that in these cases something has occurred with the source server, either that server is unavailable during the time of extract or something has changed on that server.

Tableau Supported Data Connectors

 

 

Tableau supported connectors.png