Tableau exploration with Portfolio Management System
Gathered and analyzed requirements for a portfolio management system following an iterative development approach. Created an OLTP in MySQL and star schema, OLAP for the data warehouse, followed the standard design convention. The OLAP was created accommodating slowly changing dimensions recording historical data of employees and time stamped status of project information. Create facts and dimension table in MYSQL using complex SQL join statements on the OLTP tables. The dimensions and facts are created with indexes on foreign keys to enhance query performances. Created a view of the MYSQL data and the data extracts were loaded to the tableau server for query performance.
The tableau dashboard and workbooks are hosted in the tableau public server. Created calculated fields to find the margin based on the revenue gained and expense incurred. Structured the data as hierarchies with portfolio as the highest level followed by application and project details. Created groups for the application data to do category based analysis of the data. Created sets to display project data based on conditions like the top 3 projects for the given portfolio.
Created parameters to set working hours goals by checking if the working hours is more than a certain definite average. Created animated visualization to understand the growth of margin with respect to revenue and expense over time using the pages card. Created symbol maps to understand the geo-spatial distribution of projects with custom symbols made to represent the application category. Manually organized the dimensions and measures like moving the surrogate keys, date, descriptions, flags and categories as dimensions.
Customized the tooltips and text to end user understandable format, the numerical measure was changed to its appropriate formats like representing the revenue, expense and margins as currency values. Created dashboards in Tableau to analyze the revenue, expense over various attributes of employee, project, time and location dimension. The dashboard is built with floating objects and capitalizing the various dashboard features like URL, images, web integration’s. The portfolio data is represented as tree map based on the margin, the detailed application data is represented as column graph with highlight table distributing the margin across employee roles. The detailed cross tab data of each employee is provided to understand the underlying data across attributes. Created the appropriate filter for project level and employee level data. The project level filter was single select and employee level filter was a wild card search. Experimenting with social media analytics to provide application related data from twitter REST API and streaming API.
The next step is to create a portal style dashboard using the existing dashboard and workbooks. Exploring tableau’s javascript API features to implement this.
