1 / 22

Introduction to Accessing PPM Data Using Microsoft Office

Introduction to Accessing PPM Data Using Microsoft Office. Amanda Oliver 2008 PPM User Conference. Overview. Reporting Structures Access Projects PPM_Views Advanced Reporting Pivot Tables. Reporting Structures. PPM_Views Always available – reflects contents of PPM database

elom
Download Presentation

Introduction to Accessing PPM Data Using Microsoft Office

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Introduction toAccessing PPM Data Using Microsoft Office Amanda Oliver2008 PPM User Conference

  2. Overview • Reporting Structures • Access Projects • PPM_Views • Advanced Reporting • Pivot Tables

  3. Reporting Structures • PPM_Views • Always available – reflects contents of PPM database • Report Layer -> Advanced Reporting • Must be built AFTER processes complete in PPM • Aggregated views of data for reporting purposes • Advanced Reporting – reflects contents of Report Layer database • Standard structure and naming • Query the data, save queries (can be viewed by others within organisation who access database on server).

  4. OLAP Cubes • Built as part of Report Layer build • Used by other PHS reporting tools • Accessible using Microsoft Excel – pivot table data source.

  5. Why use Access Projects • Queries & data on the server, not local PC • Can add queries to the database • Share queries with other staff • Familiar interface (similar to ‘normal’ access) • Can access data from advanced reporting & PPM view database. Refer to PPM Manual, Section ‘PPM View Database’ for connecting to Access Projects.

  6. PPM_Views - Exercise Auditing and analysing PPM data using ppm_views: • Standard Views • General Ledger Reconciliation Template

  7. Naming Convention <Module>_<Level>_<Type> where <Module> = GenLedg, PatCost, Rev <Level> = CostCentre, Episode, Reference, Service, etc <Type> = Results, Category, etc

  8. Report Layer Documentation • Menu items on the left • Navigate the list of tables and views • Review table details and link to related tables

  9. Advanced Reporting - Exercise Querying data using Report Layer database and documentation Return a listing of Cost Outputs with: • Total costs • Ordered by highest to lowest cost • Using:PatCost_EpisodeCostOutput_ResultsPatCost_Reference_CostOutputConfig_Version

  10. Pivot Tables (OLAP Cube) • Data organised into cubes • Combining several dimensions (eg time, geography and services) • With summarised data (eg orders, costs and averages) • Fast – pre-aggregated data • Powerful – visualise data in different ways • Effective – simplifies analysis & drilldown.

  11. Pivot Tables - Exercise Analysing the Report Layer data OLAP Cubes: • General Ledger Cube – Product Fraction Amount by Area Mapping and Cost Output • All Episodes Cube – Average Price by Area Mapping/Service Code.

  12. Conclusion • PPM_Viewsauditing & reconciling data in current PPM db • Advanced Reporting (Report Layer)aggregated views for reporting purposes – snapshot in time • Pivot TablesOLAP Cube (Report Layer) – pre-aggregated data viewable in different ways, drilldown capability

  13. Pivot Tables – Connecting to Data Source • In Excel - select Data menu >> PivotTable • PivotTable and PivotChart Wizard Step 1 displays.

  14. Pivot Tables – Connecting to Data Source • Step 2 of wizard • Click Get Data.

  15. Pivot Tables – Connecting to Data Source • Choose Data Source dialog displays with 3 tabs. • Select the OLAP Cubes tab. • Select <New Data Source> option, then OK.

  16. Pivot Tables – Connecting to Data Source • Create New Data Source dialog displays: • Enter name for data source, eg AllEpisodes. • Select Microsoft OLE DB Provider for OLAP Services 8.0. • Click Connect.

  17. Pivot Tables – Connecting to Data Source • Multidimensional Connection dialog displays: • Select Analysis server. • Enter the name of the server with the Report Layer database. • Enter the user id and password to access the server. • Click Next >>.

  18. Pivot Tables – Connecting to Data Source • You are returned to the Create New Data Source with the connection to the report_layer database: • Select the required cube. • AllEpisodes and AllGeneralLedger are high level cubes. • Click OK.

  19. Pivot Tables – Connecting to Data Source • You are returned to the Choose Data Source dialog, with your new data source added to the list. • Select and click OK.

  20. Pivot Tables – Connecting to Data Source • Step 2 of the Wizard displays: • Click Next >>.

  21. Pivot Tables – Connecting to Data Source • Step 3 of the Wizard displays: • Select the location of the PivotTable report.If unsure, select New Worksheet. • Click Finish.

  22. Pivot Tables – Connecting to Data Source • Excel Worksheet displays with pivot table & field list on the right.

More Related