Clarity PPM

Tableau Part 1 - Getting Started

By am1 posted 01-13-2017 12:30 PM

  

There's been a lot of hype for using Tableau with CA PPM, but not much written about how to actually get started.

By the end of this tutorial you will have a simple project dashboard pulling data directly from CA PPM.

 

Before we go any further if you haven't already seen it, check out the recent community webcast from Altrice

CA PPM NY/NJ Quarterly Web Share -Tableau Integration with CA PPM 14.2 

 

What is Tableau?

Tableau is a Business Intelligence (BI) tool which is super easy to use for both admin and user. It enables you to build beautiful dashboards and reports, from a variety of data sources and share them effortlessly.

It's an alternative to jaspersoft and sadly this isn't included in your licence agreement for CA PPM so you need to purchase separately

 

Here's an example of what you can do

Example 1: Record Breaking Coasters | Tableau Public 

Example 2: Earth quake story

 

What do I need?

There are two main components

  • Tableau server - Where end users can view dashboards/reports,and edit existing
  • Tableau desktop - a poweruser tool to build datasources and dashboards, then uploaded to the server to view

 

Ok, so how do I get Clarity data into tableau?

We have a number of different strategies we can use to get data into the tool and how to shape it before.

In the example below i'll show step by step how to get data from an on-premise instance directly. If you are on-demand, since the database isn't accessible we'd have to approach a different way i.e. webservices from a query

http://www.tableau.com/learn/tutorials/on-demand/getting-started 

 

Example - a very simple on-premise project dashboard - time to build 5 minutes

 

Pre-requisites:

  • Set up a read only user (i.e. tableau_user) in your database
  • Have tableau server set up with a site and the correct permissions
    • if you don't have the server then you can still build but not share it "online"
  • Install tableau desktop
    • trial can be used for this example

 

Click new and open a workbook

Select datasource tab at the bottom

Build the datasource - in this case a microsoft SQL server

  • We can now build the datasource by:
    • adding the tables and joining together
    • custom SQL
    • Use a view/dwh pre-complied with the data in the format you need
  • let's start with a simple example of adding inv_investments to the datasource
  • Drag it over.  Notice the connection is Live (we'll visit extraction strategy later)
  • add a filter so only project data returns
  • Now go to the workbook

  • Let's build a simple chart of number of projects by goal
    • drag goal into the rows
    • drag number of records measure into the grid
  • Should end up with something like this:
  • Click the Horizontal Bars in "Show me"
  • We'll end up with something like this
  • We'll add one more, so add a new workbook and have some fun (I built a simple gantt in a few mins)
  • Now place these onto a dashboard 
  • save the workbook and publish it to your server
  • You'll now see the dashboard in the server and can share it with stakeholders

 

Conclusion

The tool is incredibly powerful and there's so much more we can do than i've shown.

We can even integrate inside Clarity, automate user creation/groups.

 

  • How does your organisation use it?
  • What are you looking for that is missing in jaspersoft?

 

Hope you found this interesting and educational

14 comments
2 views

Comments

05-29-2018 11:40 AM

Hi.  

 

I'd be happy to.

 

Please let me know what times would work to meet.

 

Jesse

05-29-2018 11:39 AM

Hi. 

 

We are on-prem.  

 

Jesse Tutt

403-506-8199

jesse.tutt@ahs.ca

05-29-2018 11:33 AM

JesseTutt82316949, are you on-demand or on-premise? I am interested in connections for on-demand. please let me know. Thank you. 

04-19-2018 09:07 AM

Yes please that would be fantastic - will drop you a private message.

04-05-2018 09:46 AM

Hi. 

 

My team has done many visualizations of the data in the CA PPM data warehouse using Tableau. 

 

Let me know if you are interested in connecting.

 

Jesse Tutt

12-18-2017 03:59 AM

There's a number of ways to implement row level security, a blog post on it's own.

 

You can take advantage of the rights tables CA PPM has, simply push into the logic of your datasource and use username() to filter by the logged in user. 

 

If you only want a basic model, another sneaky option is to use the group membership in tableau.

i.e. in the rows have groups allowed to access comma separated, then use contains() to filter by logged in users groups. This avoids needing to use the CA PPM rights table.

12-15-2017 02:26 PM

Thanks - that is super helpful - would love to see a write up / guide on how to do this to make it more official / supported or at least working.

 

Did you notice any limitations with regards to the DWH (JasperSoft) measures / domains etc... or was it easy enough to navigate?

 

As a separate topic we are keen to understand how we enable row level security where the individual looking at the Tableau dashboard can only see the data they are mapped to in the underlying CA PPM / JasperSoft systems.

 

That would be our "holy grail" functionality and eliminate the need for using JasperSoft dashboards (still useful for pixel perfect / document based reports), we have a lot more Tableau skilled people than JasperSoft at our company.

12-01-2017 11:59 AM

We are in trials with Tableau.  With the client app demo installed on my machine, connecting to the DWH was as simple as opening the Connections menu and adding a new connection with URL to DWH, it's SID, username and password - just like connection with SQL Developer.

 

For the online version of Tableau, this appears to be a bit more difficult - requiring access to the Tableau server, rights to do this, making the connection there and then publishing so that users can access. See Tableu-Connect to Database

 

Whereas I could easily set this up using client app myself, I'm having to work with our IT Tableau Trial team to get the connection establish and published for the online app, as I don't appear to have the 'bridge app' or rights needed to get me to the Tableau server.  Once established in online app, expect it will work as well as it does with client app.

 

Hope this is helpful,

 

Dale

11-30-2017 03:43 PM

Has anyone experienced a way of connecting Tableau to the JasperSoft DWH component?

 

We have a lot of historical / blended data we put into JasperSoft DWH for performance reasons so connecting Tableau to the CA PPM directly doesn't make a lot of sense right now. So far I've not been able to find any related documentation on this so any guidance is appreciated.

06-05-2017 02:42 PM

Sure via the WSDL.

it shouldn't be too hard to re-write as a SQL query if you need it though.

05-26-2017 05:24 PM

Thanks for that information Andrew. I would like to know if I can get the System Data Provider Portlet Query. The reason, I'm asking this is I want to feed the System portlet data into Tableau. Unfortunately, I wasn't able to replicate the query.

It will be good, if atleast I can have the tables that the are referring in that portlet.

Lavanya

05-26-2017 07:56 AM

Lavanya,

 

Tableau includes all of the tools needed to connect to a datasource. If you are on premise, then this is simply connecting to the database of choice.

On-demand users will need to figure out an extraction mechanism (i.e. wsdl), since DB direct access isn't provided.

CA are working on a "adaptor" to make this simpler

 

Andrew

05-26-2017 07:21 AM

Hi Andrew,

 

I would like to know is there any Adaptor with which I can use from Clarity to feed data into Tableau?

01-13-2017 01:43 PM

Wow! Thanks for sharing this with the community Andrew!

Tableau Part 1 - Getting Started