Business Intelligence infrastructure deep dive in Project ...

Business Intelligence infrastructure deep dive in Project ...

Anaheim, CA | February 2-5, 2014 Marc Boyer Microsoft [email protected] Christine Flora Symnoian, LLC [email protected] m

PC329 Business Intelligence infrastructure deep dive in Project Online and Project Server 2013 Marc Boyer Program manager II Microsoft Christine Flora MCSD, MCITP, MCTS, MCT Symnoian, LLC

[email protected] Intelligence From Project Server 2013 and Project Online Business Intelligence Whats changed for Business Intelligence Single database with different schemas Access to reporting schema only

Existing Reports based on the Reporting tables and views still work The Reporting tables retain their previous names Some OLAP settings have been moved to SharePoint Central Admin IT Pro related, instead of functional administration Reporting Tables Data exposed as OData feeds 35 individual tables available

Projec Accessing Project data Data Sources Reporting Database Multidimensional OLAP oData for Project Online

Data Visualization Microsoft BI tools Excel, Excel Power add ins and Excel services Power BI sku for Office 365 SQL Server Reporting Services Or write your own custom app Discover oData Getting access to your

online data What is OData? HTTP-based Available initially with SharePoint 2010 for list data, expanded in 2013 http://www.odata.org ODATA AtomPu b

JSON XML Projec oData in Project Online Quick overview https://projectonlinedemo329.sharepoint.com/sites/pwa/_api/ProjectData

Run it in your favorite browser and see the entities that are supported Format support Project Online oData stream supports either XML (default) or Json ($format=json).

Use Json when possible Json light support coming soon oData in Project Online Connection Rest query

Entities and metadata 35 entities with associations for easy Excel integration. Hit tables directly by adding appending them to the query /_api/ProjectData/Projects Get the list of properties for each entity https://projectonlinedemo329.sharepoint.com/sites/pwa/_api/ProjectData/$metadata

oData Permissions Sharepoint permission mode: Portfolio Viewers, Portfolio managers, Administrators

Project permission mode Access project Server Reporting Service permission Paging Depends on the tables, 100 (tasks) to 200 (issues) Using Excel Native or PowerQuery add in ? Best practice

Start small Create your report Refine your query Usage Using oData Precision and

performance System Query options Supported options $Select to reduces the number of properties $Filter to scale down the number of entries $Orderby, $Top, and $Skip to arrange the returned fields Unsupported options $expand and $link Excel 2013 uses the Association elements to help create for pivot tables and other models.

Get specific fields only https://projectonlinedemo329.sharepoint.com/sites/pwa/_api/ projectdata/Tasks? $select=TaskName,ProjectName,TaskActualDuration,TaskFinishDate,T askPercentCompleted $select to improve perf This seems obvious, but it is often overlooked by BI users. Look back at your reports and updates the queries to only the get the data you really need. To the top

https://projectonlinedemo329.sharepoint.com/sites/pwa/_api/ projectdata/Tasks? $select=TaskName,ProjectName,TaskActualDuration,TaskFinishDate,T askPercentCompleted&$orderby=TaskPercentCompleted desc&$top=20 $top to reduce the scope When you only care about the top Race to finish Top 3 Filter on some properties https://projectonlinedemo329.sharepoint.com/sites/pwa/_api/ projectdata/Tasks?

$select=TaskName,ProjectName,TaskActualDuration,TaskFinishDate,T askPercentCompleted&$top=20&$filter=TaskActualDuration gt 10 $filter to improve perf $filter out inactive projects, non relevant tasks to reduce the size of your download even more. Logical Operators Equal, Greater than, Or . Filter on a project Get the ProjectID

You can also do this programmatically Use it in your query https://projectonlinedemo329.sharepoint.com/sites/pwa/_api/ projectdata/Projects(guid'b28fc3ef-99bf-e111-9f1e00155d022681')/Tasks Functions Date functions Int month(DateTime P0) - $filter=month( TaskFinishDate) eq 2 String functions bool substringof(string po, string p1)

More functions on www.odata.org Still using with SSRS ? SSIS connectivity to Project Online Project Online Windows Azure OData SQL

SQL SSIS SSRS Grant reporting access in Project Online Its a site collection feature Gear / Sites settings Site Collection features oData, Mobile and

You What is your app great at? Audience / User Activities it will Support Features to include Device Capabilities Consuming Data SharePoint Apps

SharePoint (self) Hosted Provider Hosted Auto Hosted Office Apps Project Task Pane Word Excel PowerPoint Mail/Outlook

Mobile Windows 8 Phone Windows 8.1 Store Apps Other Project Server / Project Online Projec What type of Authentication is

needed? On Prem Domain Joined Non-Domain Joined Online Domain Joined Non-Domain Joined O365 credentials

On Prem Authentication string query = "http://w15-sp/_api/sites/pwa/ ProjectData/Projects"; HttpClientHandler clientHandler = new HttpClientHandler(); clientHandler.UseDefaultCredentials = true; HttpClient client = new HttpClient(clientHandler); var response = await client.GetAsync(query); response.EnsureSuccessStatusCode(); Claims Authentication

Courtesy of: Alex Choroshin Demo Before you go 4 points to remember Conclusion

Easy to use Use Microsoft BI stack Pay attention to your queries

New SSIS connection scenario Other PC14 BI Sessions: Session Introduction to Microsoft Visio: A Swiss Army Knife for PMs Building Stellar Project BI Dashboards with Visio Real World Reports: Business Intelligence in Project Online and Project Server 2013 Reports That Jump Off the Screen with Microsoft Project Desktop

Business Intelligence Infrastructure Deep Dive in Project Online and Project Server 2013 Session Room PC245 204C

PC222 203B PC248 202AB PC232 201B

PC329 204A Time MON 11:15 MON 5:00 TUE 10:30 TUE

1:45 WED 12:00 References Authorization and authentication for apps in SharePoint

2013 http://bit.ly/1eoWqJe OAuth authentication permissions on the fly http://bit.ly/1n9Bghv CodePlex Project Claims Web Service Win8 Project http://bit.ly/1c2Dh9V Where it is explained http://bit.ly/1dpNhdl Kirk Evans //Build 2013 Authentication Session http://bit.ly/1atokCS dev.windows.com

MyPC fill out evaluation s Fill out session evaluations by logging win into & MyPC on your laptop or mobile device. prizes! Evaluation prizes daily! Claim your

prize at the Registration Desk on Level 1. www.msprojectconference.com After the event, over 100 hours of resources; including all of the PPT decks and session videos will be 2014 Microsoft Corporation. All rights reserved. Microsoft, Windows and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries. The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR

STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.

Recently Viewed Presentations

  • Teacher Computer Workshop - Columbus State University

    Teacher Computer Workshop - Columbus State University

    Teacher Computing Workshop Wayne Summers ... State University * Solutions / Resources THE HOUSE STUDENT APP CHALLENGE FIRST LEGO LEAGUE Hackathon Activ8 Summer Camps June 10, 2014 Columbus State University * TSYS School of Computer Science 8:42 min * TSYS...
  • SAFETY ACTS by Saibal C Pal - Taxguru.In

    SAFETY ACTS by Saibal C Pal - Taxguru.In

    Schedule III deals with Wood-working Machinery. Schedule IV deals with Rubber Mills . Schedule V deals with Power Process. ... To ensure this Banks thought it necessary to ensure safety to its depositors by seeing that its net worth is...
  • Wireless Access Point (WAP) - Computer Science | Furman ...

    Wireless Access Point (WAP) - Computer Science | Furman ...

    Data is sent over transmission media in bundles called packets. For computers to communicate, these packets of data must be able to flow between computers. Network navigation devices help make this data flow possible. In simple networks, navigation devices are...
  • 2005 Strategy Topics - unizd.hr

    2005 Strategy Topics - unizd.hr

    The article concerns fish aquaculture research, and probably would not have been examined for water temperature data, given the title "Determination of physical behavior of feed pellets in Mediterranean water".
  • Chapter 3 Balancing Chemical Equations What does mother

    Chapter 3 Balancing Chemical Equations What does mother

    The potassium-based alum has been used to produce crisp cucumber and watermelon-rind pickles as well as maraschino cherries, where the aluminum ions strengthen the fruits' cell-wall pectins. Alum is approved by the U.S. Food and Drug Administration as a food...
  • Accounting and the Business Environment

    Accounting and the Business Environment

    Accounting and the Business Environment. ... Publishes the International Financial Reporting Standards, the international accounting rule book. ... which stands for generally accepted accounting principles. GAAP rests on a conceptual framework. The primary objective of financial reporting is to ...
  • Interactivity in Blackboard - Angelina College

    Interactivity in Blackboard - Angelina College

    The alternative to email is Blackboard's Course Messages. Course Messages are a part of Blackboard, so all communication can be accessed from within Blackboard once logged in. Each class section or master class will have its own unique Course Message...
  • Logging in to Enhanced WebAssign Enhanced WebAssign Allows

    Logging in to Enhanced WebAssign Enhanced WebAssign Allows

    Visit the College Bookstore, or Visit www.CengageBrain.com Choose the format and price options that work best for you. CengageBrain.com offers Textbooks Value Bundles including EWA access Rental options Pay for Enhanced WebAssign access online directly from your course. If You...