introduction to accessing ppm data using microsoft office amanda oliver 2008 ppm user conference
TRANSCRIPT
![Page 1: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/1.jpg)
Introduction toIntroduction to
Accessing PPM Data Accessing PPM Data Using Microsoft OfficeUsing Microsoft Office
Amanda OliverAmanda Oliver2008 PPM User Conference2008 PPM User Conference
![Page 2: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/2.jpg)
Slide 2 PowerHealth Solutions 2008 PPM User Conference
OverviewOverview
1.1.Reporting StructuresReporting Structures
2.2.Access ProjectsAccess Projects
3.3.PPM_ViewsPPM_Views
4.4.Advanced ReportingAdvanced Reporting
5.5.Pivot Tables Pivot Tables
![Page 3: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/3.jpg)
Slide 3 PowerHealth Solutions 2008 PPM User Conference
Reporting StructuresReporting Structures
PPM_ViewsPPM_ViewsAlways available – reflects contents of PPM Always available – reflects contents of PPM databasedatabase
Report Layer -> Advanced ReportingReport Layer -> Advanced ReportingMust be built Must be built AFTERAFTER processes complete in processes complete in PPMPPMAggregated views of data for reporting Aggregated views of data for reporting purposespurposesAdvanced Reporting – reflects contents of Advanced Reporting – reflects contents of Report Layer databaseReport Layer databaseStandard structure and namingStandard structure and namingQuery the data, save queries (can be Query the data, save queries (can be viewed by others within organisation who viewed by others within organisation who access database on server).access database on server).
![Page 4: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/4.jpg)
Slide 4 PowerHealth Solutions 2008 PPM User Conference
OLAP CubesOLAP Cubes
Built as part of Report Layer Built as part of Report Layer buildbuild
Used by other PHS reporting Used by other PHS reporting toolstools
Accessible using Microsoft Accessible using Microsoft Excel – pivot table data Excel – pivot table data source.source.
![Page 5: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/5.jpg)
Slide 5 PowerHealth Solutions 2008 PPM User Conference
Why use Access ProjectsWhy use Access Projects
Queries & data on the server, not Queries & data on the server, not local PClocal PC
Can add queries to the databaseCan add queries to the database
Share queries with other staffShare queries with other staff
Familiar interface (similar to ‘normal’ Familiar interface (similar to ‘normal’ access)access)
Can access data from advanced Can access data from advanced reporting & PPM view database.reporting & PPM view database.
Refer to PPM Manual, Section ‘PPM View Database’ for connecting to Access Projects.Refer to PPM Manual, Section ‘PPM View Database’ for connecting to Access Projects.
![Page 6: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/6.jpg)
Slide 6 PowerHealth Solutions 2008 PPM User Conference
PPM_Views - ExercisePPM_Views - Exercise
Auditing and analysing PPM Auditing and analysing PPM data using ppm_views:data using ppm_views:
Standard ViewsStandard Views
General Ledger General Ledger Reconciliation TemplateReconciliation Template
![Page 7: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/7.jpg)
Slide 7 PowerHealth Solutions 2008 PPM User Conference
Naming ConventionNaming Convention
<Module>_<Level>_<Type><Module>_<Level>_<Type>
wherewhere
<Module><Module> == GenLedg, PatCost, GenLedg, PatCost, RevRev
<Level><Level> == CostCentre, CostCentre, Episode, Reference, Service, etcEpisode, Reference, Service, etc
<Type><Type> == Results, Category, Results, Category, etcetc
![Page 8: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/8.jpg)
Slide 8 PowerHealth Solutions 2008 PPM User Conference
Report Layer Report Layer DocumentationDocumentationMenu items on the leftMenu items on the left
Navigate the list of tables and viewsNavigate the list of tables and views
Review table details and link to related tablesReview table details and link to related tables
![Page 9: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/9.jpg)
Slide 9 PowerHealth Solutions 2008 PPM User Conference
Advanced Reporting - ExerciseAdvanced Reporting - Exercise
Querying data using Report Layer Querying data using Report Layer database and documentationdatabase and documentation
Return a listing of Cost Outputs with:Return a listing of Cost Outputs with:
1.1.Total costsTotal costs
2.2.Ordered by highest to lowest costOrdered by highest to lowest cost
3.3.Using:Using:PatCost_EpisodeCostOutput_ResulPatCost_EpisodeCostOutput_ResultstsPatCost_Reference_CostOutputPatCost_Reference_CostOutputConfig_VersionConfig_Version
![Page 10: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/10.jpg)
Slide 10 PowerHealth Solutions 2008 PPM User Conference
Pivot Tables (OLAP Cube)Pivot Tables (OLAP Cube)
Data organised into cubesData organised into cubes
Combining several dimensions Combining several dimensions (eg time, geography and services) (eg time, geography and services)
With summarised data With summarised data (eg orders, costs and averages)(eg orders, costs and averages)
Fast – pre-aggregated dataFast – pre-aggregated data
Powerful – visualise data in different Powerful – visualise data in different waysways
Effective – simplifies analysis & drilldown.Effective – simplifies analysis & drilldown.
![Page 11: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/11.jpg)
Slide 11 PowerHealth Solutions 2008 PPM User Conference
Pivot Tables - ExercisePivot Tables - Exercise
Analysing the Report Layer data Analysing the Report Layer data OLAP Cubes:OLAP Cubes:
General Ledger CubeGeneral Ledger Cube – Product – Product Fraction Amount by Area Fraction Amount by Area Mapping and Cost OutputMapping and Cost Output
All Episodes CubeAll Episodes Cube – Average – Average Price by Area Mapping/Service Price by Area Mapping/Service Code.Code.
![Page 12: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/12.jpg)
Slide 12 PowerHealth Solutions 2008 PPM User Conference
ConclusionConclusion
PPM_ViewsPPM_Viewsauditing & reconciling data in current PPM auditing & reconciling data in current PPM dbdb
Advanced Reporting (Report Layer)Advanced Reporting (Report Layer)aggregated views for reporting purposes – aggregated views for reporting purposes – snapshot in timesnapshot in time
Pivot TablesPivot TablesOLAP Cube (Report Layer) – pre-OLAP Cube (Report Layer) – pre-aggregated data viewable in different aggregated data viewable in different ways, drilldown capabilityways, drilldown capability
![Page 13: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/13.jpg)
Slide 13 PowerHealth Solutions 2008 PPM User Conference
Pivot Tables – Connecting to Data Pivot Tables – Connecting to Data SourceSource
In Excel - select Data menu >> In Excel - select Data menu >> PivotTablePivotTable
PivotTable and PivotChart Wizard Step 1 displays.PivotTable and PivotChart Wizard Step 1 displays.
![Page 14: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/14.jpg)
Slide 14 PowerHealth Solutions 2008 PPM User Conference
Step 2 of wizardStep 2 of wizard
Click Click Get DataGet Data..
Pivot Tables – Connecting to Data Pivot Tables – Connecting to Data SourceSource
![Page 15: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/15.jpg)
Slide 15 PowerHealth Solutions 2008 PPM User Conference
Choose Data SourceChoose Data Source dialog displays with 3 dialog displays with 3 tabs.tabs.
Select the Select the OLAP CubesOLAP Cubes tab. tab.
Select Select <New Data Source><New Data Source> option, then option, then OKOK..
Pivot Tables – Connecting to Data Pivot Tables – Connecting to Data SourceSource
![Page 16: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/16.jpg)
Slide 16 PowerHealth Solutions 2008 PPM User Conference
Create New Data SourceCreate New Data Source dialog displays: dialog displays:
Enter name for data source, eg AllEpisodes.Enter name for data source, eg AllEpisodes.
Select Microsoft OLE DB Provider for OLAP Select Microsoft OLE DB Provider for OLAP Services 8.0.Services 8.0.
Click Click ConnectConnect..
Pivot Tables – Connecting to Data Pivot Tables – Connecting to Data SourceSource
![Page 17: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/17.jpg)
Slide 17 PowerHealth Solutions 2008 PPM User Conference
Multidimensional Connection dialog displays:Multidimensional Connection dialog displays:
Select Select Analysis serverAnalysis server..
Enter the name of the server with the Report Layer Enter the name of the server with the Report Layer database.database.
Enter the user id and password to access the server.Enter the user id and password to access the server.
Click Click Next >>Next >>..
Pivot Tables – Connecting to Data Pivot Tables – Connecting to Data SourceSource
![Page 18: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/18.jpg)
Slide 18 PowerHealth Solutions 2008 PPM User Conference
You are returned to the Create New Data Source with You are returned to the Create New Data Source with the connection to the report_layer database:the connection to the report_layer database:
Select the required cube.Select the required cube.AllEpisodes and AllGeneralLedger are high level AllEpisodes and AllGeneralLedger are high level cubes.cubes.
Click Click OKOK..
Pivot Tables – Connecting to Data Pivot Tables – Connecting to Data SourceSource
![Page 19: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/19.jpg)
Slide 19 PowerHealth Solutions 2008 PPM User Conference
You are returned to the Choose Data Source dialog, You are returned to the Choose Data Source dialog, with your new data source added to the list.with your new data source added to the list.
Select and click OK.Select and click OK.
Pivot Tables – Connecting to Data Pivot Tables – Connecting to Data SourceSource
![Page 20: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/20.jpg)
Slide 20 PowerHealth Solutions 2008 PPM User Conference
Step 2 of the Wizard displays:Step 2 of the Wizard displays:
Click Click Next >>Next >>..
Pivot Tables – Connecting to Data Pivot Tables – Connecting to Data SourceSource
![Page 21: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/21.jpg)
Slide 21 PowerHealth Solutions 2008 PPM User Conference
Step 3 of the Wizard displays:Step 3 of the Wizard displays:
Select the location of the PivotTable Select the location of the PivotTable report.report.If unsure, select New Worksheet.If unsure, select New Worksheet.
Click Click FinishFinish..
Pivot Tables – Connecting to Data Pivot Tables – Connecting to Data SourceSource
![Page 22: Introduction to Accessing PPM Data Using Microsoft Office Amanda Oliver 2008 PPM User Conference](https://reader036.vdocuments.us/reader036/viewer/2022062312/5519b4f75503467a578b4776/html5/thumbnails/22.jpg)
Slide 22 PowerHealth Solutions 2008 PPM User Conference
Excel Worksheet displays with pivot table & field list on the right.Excel Worksheet displays with pivot table & field list on the right.
Page fields act as report parameters, filtering data based on the selected value.
Row in the report table
Column in the report table
Actual data
Drag fields and drop in pivot table
Pivot Tables – Connecting to Data Pivot Tables – Connecting to Data SourceSource