deltek insight 2011: business intelligence with the mpm data warehouse
TRANSCRIPT
EPM 309: Business Intelligence with the MPM Data WarehouseEPM 309
May 16, 2011
Presented byTaiwo Awosika
SGT, Inc.
2 ©2011 Deltek, Inc. All Rights Reserved
Agenda
May 16, 2011
Overview of Data WarehouseBenefits of Data WarehouseMPM Data Warehouse Structure (Project, WBS, RBS, …)Creating an OLAP cube with MPM Data WarehouseCreating cost reports from the OLAP cube in excelCreating cost reports from the OLAP cube with reporting services
3 ©2011 Deltek, Inc. All Rights Reserved
Key Session Takeaways
May 16, 2011
By attending this session you will be able to:Understand the MPM data warehouse structureCreate OLAP cubes from MPM data warehouseGenerate reports with excel Generate reports with SQL reporting services Understand basic MDX query
4 ©2011 Deltek, Inc. All Rights Reserved
Overview of Data Warehouse
May 16, 2011
Data Warehouses are designed for storing structured large data for historical information access and reporting.
5
Overview of Data Warehouse
May 16, 2011 ©2011 Deltek, Inc. All Rights Reserved
"A data warehouse is a subject-oriented, integrated, time-variant and non-volatile collection of data in support of management's decision making process“ (Bill Inmon, 1990).
Subject Oriented: Data that gives information about a particular subject instead of about a company's ongoing operations.
Integrated: Data that is gathered into the data warehouse from a variety of sources and merged into a coherent whole.
Time-variant: All data in the data warehouse is identified with a particular time period.
Non-volatile: Data is stable in a data warehouse. More data is added but data is never removed. This enables management to gain a consistent picture of the business.
http://www.intranetjournal.com/features/datawarehousing.html
6
Overview of Data Warehouse
May 16, 2011 ©2011 Deltek, Inc. All Rights Reserved
A data warehouse is a database used for analyzing overall business strategy rather than routine operations.
A data warehouse database is not a relational database. A relational database stores data of routine operations. A data warehouse database usually store historic data and are suitable for reporting.
Data in an operational database can be transformed and moved into a data warehouse database.
A data warehouse consists of fact tables and dimension tables The most commonly used data warehouse schema architecture are star
schema and snowflake schema.
7
Benefits of Data Warehouse
May 16, 2011 ©2011 Deltek, Inc. All Rights Reserved
Data from multiple data sources can be combined to create meaniful information
Ability to extract, load, and transform data into a data warehouse system Designed to handle large data set Inconsistent data can be Identified and and resolved. Data in the data warehouse can be used to create an online analytical
processing (OLAP) cube Ability to slice and dice data in the data warehouse into mutiple dimensions
and present information at diffrent level of granularity. OLAP can be used to apply security at the granularity Users will be able to access information without understanding the data
sources and data structure Data mining can be applied on data in the data warehouse to extract patterns
that can be used to put the company in a better competitive position. Reports with chats can be generated for excutives to analize and help make
critical decisions.
8 ©2011 Deltek, Inc. All Rights Reserved
MPM Data Warehouse StructureTable StructureProject header dataWork breakdown structure (WBS)Resource breakdown structure (RBS)TimeFact/Measures
May 16, 2011
9
MPM Data Warehouse Structure
May 16, 2011 ©2011 Deltek, Inc. All Rights Reserved
10
MPM Data Warehouse StructureProject Tables
May 16, 2011 ©2011 Deltek, Inc. All Rights Reserved
11
MPM Data Warehouse StructureWBS Table
May 16, 2011 ©2011 Deltek, Inc. All Rights Reserved
12
MPM Data Warehouse StructureRBS Tables
May 16, 2011 ©2011 Deltek, Inc. All Rights Reserved
13
MPM Data Warehouse StructureTime
May 16, 2011 ©2011 Deltek, Inc. All Rights Reserved
14
MPM Data Warehouse StructureFact Table
May 16, 2011 ©2011 Deltek, Inc. All Rights Reserved
15 ©2011 Deltek, Inc. All Rights Reserved
Creating an OLAP cube with MPM Data WarehouseDemo
May 16, 2011
16
Creating an OLAP cube with MPM Data Warehouse
May 16, 2011 ©2011 Deltek, Inc. All Rights Reserved
1. Launch Business Intelligence Development Studio (BIDs) and create a new Analysis Service Project
2. Create a new data source and use the wizard to connects to the MPM data warehouse
3. Create a new data source view and use the wizard to add tables from the from the MPM data warehouse
4. Use the cube wizard to create a new cube with Project, RBS, WBS, and time dimension.
17 ©2011 Deltek, Inc. All Rights Reserved
Creating cost reports from the OLAP cube in excelDemo
May 16, 2011
18
Creating cost reports from the OLAP cube in excel
May 16, 2011 ©2011 Deltek, Inc. All Rights Reserved
1. Launch Microsoft Excel and use the data connection wizard to connect to your OLAP cube
2. Create a PivotTable report with cost data
3. Add charts
19 ©2011 Deltek, Inc. All Rights Reserved
Creating cost reports from the OLAP cube with reporting servicesDemo
May 16, 2011
20
Creating cost reports from the OLAP cube with reporting services
May 16, 2011 ©2011 Deltek, Inc. All Rights Reserved
1. Launch Business Intelligence Development Studio (BIDs) and create a new Report Server Project
2. Add a new shared data source using the shared data source wizard
3. Create a cost report
4. Add a chart
21
Questions?
May 16, 2011 ©2011 Deltek, Inc. All Rights Reserved
www.twitter.com/deltek
www.facebook.com/deltekinc
www.linkedin.com/company/163414
www.youtube.com/user/deltekinc