performance project portfolio
TRANSCRIPT
Performance Project
Construction Financial DashboardsClass 2008B3
John Stires
10/31/08
Page 1
I. Table of ContentsII. Overview 4
A. Data Sources Used...............................................................................................................4
1. Job Master...................................................................................................................4
2. Overhead.....................................................................................................................4
3. Materials.....................................................................................................................4
4. Labor...........................................................................................................................4
III. Page 1: Overhead Quarters 5
A. Use JobTotal/AllJobs for JOBFinancials...............................................................................5
B. Chart Format.......................................................................................................................5
C. Left Scoreboard...................................................................................................................5
1. Filter(s)........................................................................................................................5
2. KPIs..............................................................................................................................5
D. Right Scoreboard.................................................................................................................5
1. Use the Master cube...................................................................................................5
2. KPIs..............................................................................................................................5
E. Background.........................................................................................................................5
IV. Page 2: Purchase Breakdown by Client 7
A. Use the Materials cube.......................................................................................................7
B. Chart Format.......................................................................................................................7
C. Filter(s)................................................................................................................................7
D. Background.........................................................................................................................7
Page 2
V. Page 3: Overhead Cost by Category by Quarter 9
A. Use the Overhead cube.......................................................................................................9
B. Chart Format.......................................................................................................................9
C. Filter(s)................................................................................................................................9
D. Background.........................................................................................................................9
VI. Page 4: Top Labor By Quarter 11
A. Cube..................................................................................................................................11
B. Chart Format.....................................................................................................................11
C. Filter(s)..............................................................................................................................11
D. MDX...................................................................................................................................11
1. Modify the MDX to create a TOPCOUNT...................................................................11
2. Base the TOPCOUNT on the LaborQuarters filter.....................................................11
E. Background.......................................................................................................................11
VII. Page 5: Employee Labor Analysis 13
A. Displayed Data...................................................................................................................13
B. Chart Format.....................................................................................................................13
1. Table..........................................................................................................................13
2. Bar Chart Total labor by quarter................................................................................13
3. Line Graph Employee’s percent of the total labor per quarter.................................13
Page 3
II. OverviewIn this project, a 5-page dashboard will be created showing construction overhead information. Obtain the data for these pages from the database SetFocusAllWorksAnalysis OLAP database on thesfbisrv1 server. Four data sources will required for each of four cubes:
A. Data Sources Used1. Job Master2. Overhead3. Materials4. Labor
Page 4
III. Page 1: Overhead QuartersA. Use JobTotal/AllJobs for JOBFinancials
Show Client/Job total and children rows.
B. Chart FormatChart format will be percentages and KPIs.
C. Left ScoreboardUse the Overhead cube Utilize the named sets: OverheadTotal and AllOverHeads. (Show Overhead Total and the overhead children.)
1. Filter(s)Create a filter on quarters using the named set OverheadQuarters from the Overhead cube. Apply only to the left scoreboard.
2. KPIsa) KPIoverheadTrend
D. Right Scoreboard1. Use the Master cube
Utilize the named sets: ClientTotal/AllClients for Client Financials (you can use the named set ClientsOpenReceivables as a filtered named set for the list of clients under open receivables KPI)
2. KPIsa) KPIopenReceivables/asPctOfInvoicedb) KPIOverheadAsPctOfOTotalCost
E. BackgroundNone specified.
Page 5
Page 6
IV. Page 2: Purchase Breakdown by ClientChart Material Purchase Amount and Material Purchase Type Description by quarter.
A. Use the Materials cubeChart can be run for a single client, or an all client total.
B. Chart FormatChart format will be a bar chart.
C. Filter(s)Create a filter on Clients using the named set ClientsPlusAll from the Master cube
D. BackgroundSet the chart background based on the Client Name, and to the Client Filter.
Page 7
Page 8
V. Page 3: Overhead Cost by Category by QuarterChart the overhead dollar amount and overhead category description, by quarter.
A. Use the Overhead cubeChart may be run for multiple overhead categories.
B. Chart FormatChart format will be a line graph.
C. Filter(s)Create a filter on Overhead description, using the names set AllOverheads from the Overhead cube.
D. BackgroundNo background for the chart. Need to link explicitly on the overhead categories (specified in the line chart).
Page 9
Page 10
VI. Page 4: Top Labor By QuarterChart the top 10 jobs by labor dollars, and the top 5 employees by labor dollars, by quarter.
A. CubeUse the Labor cube
B. Chart FormatChart format will display hours and dollars for the respective job and employee.
C. Filter(s)Create a filter on Labor Quarters using the named set LaborQuarters from the Labor cube
D. MDX1. Modify the MDX to create a TOPCOUNT2. Base the TOPCOUNT on the LaborQuarters filter.
E. BackgroundNone specified.
Page 11
Page 12
VII. Page 5: Employee Labor AnalysisShow an employee’s labor dollars by quarter, along with the percentage of labor for the jobs the employee worked on.
A. Displayed DataShow that employee’s labor dollars by quarter, along with the percentage of labor for the jobs the employee worked on. For example, in the 3rd quarter, if the employee had $00 in labor dollars, and the total labor dollars for jobs that he worked on was $400 for that quarter, the % would be 25%.
B. Chart Format1. Table
a) Each job the employee worked on.b) Total labor for the job.c) The employee’s labor contribution, d) The employee % labor of the total for that job.
2. Bar ChartTotal labor by quarter
3. Line GraphEmployee’s percent of the total labor per quarter.
Page 13
Page 14