GP Technical Airlift Notes - Day 2 - What, more SharePoint?

Configuring Business Portal

Eric Gjerdevig, MCT, MCP - egjerdevig@summitgroupsoftware.com

Joe Tews, MCT, MCP - jtews@summitgroupsoftware.com

Why Business Portal as opposed to just building a Sharepoint site?

• Sharepoint doesn't have an understanding of multiple companies
• Sharepoint doesn't understand what an invoice is, what a check is - what an employee is.
• Business Portal has pre-defined roles that provide group level security.

What is needed to run BP in an Extranet?

• ISA Server
• SSL
• Recommended - Multiple web servers / farms for internal / external access. Allows different functionality to be applied to each - more features for internal, limited for external.

Adding Users Manually

• BP Role (MBF - Microsoft Business Framework)
• Site Setting / Users
• Pick a Task / Add new user
• Assign Roles
• Sharepoint Security
• Open Sharepoint Security
• Add user to BP Internal User (ALWAYS!) at a minimum
• Assign to any other groups (that correspond with the roles assigned above)
• Web Services Rights
• Use Dynamics Security Console and add the user to the appropriate Web Services security role based on the roles assigned during the BP role assignment

Adding Users (the smart way)

• Open the Security Sync Tool
• Provide a name for the sync rule (MBF Admin to Sharepoint Admin)
• Select Source (Business Portal - MBF), Company (all), Role (administrator)
• Choose the same for the destination - Destination (Web Services)

Example:

Rule 1 -  MBF Sales Manager => Sharepoint Sales Manager

Rule 2 - MBF Sls Mgr => Business Framework (Web Services) Sls Mgr

Rule 3 - MBF Sls Mgr => ??????

!! - Rules are stored as an XML document that can be re-used.

Why use separate rule sets rather than multiple set.

If adding users in multiple locations, can cause security risks where users accidentally get added to groups they shouldn't be when added at a single location. Create mapping file for MBF => Business Framework and a separate mapping for Business Framework => MBF

Even after all the synchronization above, users need to be connected to their Back Office ID (employee record) in Advanced Roles

Implementation Methodology

There is a HUGE difference between installing Business Portal and implementing Business Portal.

Discovering what the customer needs added to and also what needs to be removed from BP is as important as installing it. Customization turns BP from a product that is feature rich but never used to a business critical tool.

Creating a new page

• Site Actions / Create / Business Portal Web Part Page
• Copy page URL in order to add to Navigation
• Site Settings / Navigation
• Add URL to page into site navigation
• Browse to page
• Add Business Portal web part (primary publishing list - 1)
  • Configure - create new query
• Add BP web part (Rich List Result Viewer - 2)
  • Configure - create new query
• Add publishing information to 1
• Add subscription information to 2
• Edit object 2, subscribe to object 1 to establish the link

 

Bring BI to life with MS Dynamics GP 10

Christina Phillips (Geyer), MCT, MCBSP - christinag@theknastergroup.com

Excel Report Builder, SQL Reporting Services, Excel Links in GP

Business Intelligence - The right information, to the right people, at the right time in the right format.

Data (86,000 line spreadsheet) vs Information (Pie Chart summary)

BI Infrastructure Hierarchy

• Infrastructure: SQL Server
• IT Tools: SQL IS / AS / RS
• End User Tools: Sharepoint / Excel
• Performance Management: Scorcarding, Analytics and Planning, Business Scorecard Manager, ProClarity, PerformancePoint Server

BI with Dynamics GP

• Office
  • Excel Reports and Data Connections
    • Data Connections access data from GP via Excel
    • ~295 available
    • Put DC on network share so that users can get to them
  • Excel Report Builder (Bundled with SmartList Builder)
• SQL Server
  • SQL Reporting Services Wizard
  • SQL 2008 Report Designer
• MOSS
  • Dashboards

After June 2 - Smartlist builder pricing will go up.

Setting up Excel Reports

• Establish a shared location for reports
• Set permissions on folder
• GP / Tools / Setup / Reporting Tools Setup
• Enter paths, enable and deploy Data Connections for all companies
  • System level reports are deployed when running the utility
  • User level - nothing gets deployed automatically. Store your custom reports there. Use %username% wildcard to specify user-id named folder
• Run Deployment
• Do the same for the Reports Library
• Deploys ODC files and generic Excel Reports
• Launch Excel Reports (Admin / Other Reports / Excel Reports)
• Excel accesses data directly - NOT an export of static data
• Set security (in SQL) by adding to rpt_... roles in SQL

Build a New Report

• Launch Excel
• Data tab
• Existing Connections
• Browse to ODC location, choose one
• Build report
• Refresh the data connection

Excel Report Builder

• Bundled with Smartlist Builder (Feature Pack 2)
• Analyze real-time data
• Connect to any GP or SQL table
• Add calculated fields, restrictions
• Publish to file, sharepoint or reports library

Building an Excel Report through Report Builder

• Tools / Smartlist Builder / Excel Reports Builder
• Build report
• Pull in 1st table
• Pull in additional tables (if needed)
• Establish relationship between the tables if needed
• Expand field options to clean up data returned (such as positive / negate $$ amounts for credits or 'friendly names' for document types)
• Add any calculated field (such as Days Past Due)
  • Use DateDiff function
• Add summary page if desired
• Publish

SQL Reporting Services

Available to anyone who has full SQL

Over  60 Reports available

Source files for existing reports available on PartnerSource

Deployment of reports on takes several minutes

All SQL Reports can be exported (XML, CSV, PDF, Excel, Tiff, Word)

Make sure to run the reporting tools setup for SQL reporting services in Dynamics GP (See above for Excel Report publishing)

SQL Report Layout and Design

Model - set of parameters used in a report (tables, links, etc)

Components:

• Report Designer
• Report Builder
• Model Designer
• Report Manager

Planning a report

1. Review existing reports
2. Plan the repot layout
3. Determine tables
4. Determine data source
5. Determine SQL statement or stored procedure (stored proc may be more efficient)
6. Identify grouping
7. Determine filtering, sorting
8. Determine calculated fields, aggregate fields
9. Create and save report
10. Deploy the report, set security

Creating a new SQL Report

• Open Report Designer
• New Report
• Set Data Source by clicking link on report
• Build query in query designer
• Query brings in fields available to the report
• Insert report object (table, matrix, chart, gauge, list)
• Format and publish the report

New Features in SQL 2008 Reporting Svcs

• Enhanced report designer
• Tablix data format (multiple groupings on the same row)
• Report output to word
• Integrated mode with MOSS
• Enhanced charts and gauges

Best Practices for Designing Dashboards

• Dashboard audience
  • Who, what, how
• Plan components
  • Libraries, lists
  • Business Data Catalog types
  • Key Performance Indicators
  • Excel Reports, SQL Reports
• Capitalize on the efficiencies of MOSS

Ending Session - Preview of GP 11

• GP 10 Feature Pack
• Rapid Install, Data Migration and Configuration
• BI (PPS Integration)
• Excel Report Builder
• Project and Field Service Features
• GP 11 TAP
• Live Customers
• Longest GP Beta period ever
• ISV drops throughout
• v10 was focused around the technology platforms. Now that this has been accomplished, version 11 will be highly focused on internal functionality. Release date is planned for 2010.
• Vendor Approval and action based Workflow steps, not just approval only
• New hierarchy feature in Office 14
• Vendor Portal !
• Contact - Chad Sogge (Security conversation)

Q & A

Now that Project Green is dead, what comes after 2013?

Releases are going to continue, integration of other products will continue, particularly products in the next 2 years. Plan on GP v12.

 

What is being done to scale GP to compete with such as PeopleSoft?

PeopleSoft is a hot market. PS customers are jumping ship and there are huge opportunities out there.

Focusing on payroll and HR to match up with PS functionality. Recent benchmarks of 50,000 payroll checks and 20,000 W2 forms. Continuing to expand SQL clustering capabilities.

140 customers over a $$ Billion in revenue

Many customers are moving up-market, many partners are following. This feature pack is focused back at the small market, next focus will be up-market.

 

Will capability be added to Dexterity for some of the new reporting features?

We don't know yet, don't have an answer as to timeline.

 

Will there be a release of source code for DEX?

Probably not going to happen.

 

Portal products coming up. Customer and Vendor Portals. What is the direction on convergence/ divergence of portal products?

Sharepoint is the first place we look at for new development. Homepage in Dex will have the same components in 11.

Graphing and data presentation will be componentized where it can be built once and deployed across many locations - homepage, bp and other portals.

There will not be a single drop and replace of the interface to take advantage of new functionality. This will happen over time.

There are 40,000 customers on GP. We will not take away big blocks of functionality. Implementing BP and Sharepoint today will not be a wasted effort when the next version arrives. It will be added and extended, not replaced.

 

Any plans for supporting products on 64-bit platforms?

GP 11 is planned to be 64-bit compatible, NOT OPTIMIZED for 64 for ALL components (includes BP?)

GP Technical Airlift Notes - Day 1 - GP11 ?

Keynote tidbits

Direction

• Product will be increasingly 'role-based', mapping the product to actual business processes.
• Product will be integrated more tightly with Office product line.
• Product design will be handled through Visual Studio. Standardized customization means increased supportability.

Add and extend:

• Role based => Enhanced user experience
• Portals and worflow => process-centric design
• BI => ad hoc, self service informationg athering
• web service => simplified / standardized accessibility

Direction

(A) Check out "Statement of Direction"

MS will spend $7B in R&D this year

GP looking to increase value to customers who subscribe to Enhancements by releasing Feature Packs between product releases. Next Feature Pack will be a step-in to GP 11, which will be built around Office 14.

16,000 QuickBooks customers last year moved from QuickBooks Pro to QuickBooks Enterprise. They should be GP customers. GP will begin aggressively marketing to QB customers.

New VPC is out.

Feature Pack Features

• Excel report builder - Additional data sources available. Preview columns before export. Add restrictions and calculations. Summarize a Excel Report for multiple companies into a single workbook.
• Forcasting enhancements
• Reporting
  • Personal BI - Report Writer, Excel Reports, Smartlists
  • Team BI - Excel Services, SQL Reporting Services, Sharepoint
  • Org BI - PerformancePoint Server, FRx
  • (A) - Check on PerformancePoint

Scope

(A) Check out Pinnacle Award winner stories to find out what cutting edge solutions are being done.

Demos

Upgrade to GP from QuickBooks. Old vs New

!! - GP 10 Feature pack includes rapid migration tool which allows you to bring in ALL data (including transactions !) in one single export / import routine.

During migration, all master records can be exported to Excel and vetted before importing into GP.

(20 minute upgrade)

Start a new company from scratch

Demo of Rapid Configuration Tool

Using Excel templates, allows for extremely rapid initial setup. End of setup, brings up list of all errors which link directly to cells in original Excel documents

Shipping 13 templates out of box for verticals.

Rapid Config tool hits ~78 windows and over 1300 Fields during setup.

(20 minute setup)

Fun Moments

Pissed off ISV barely controls his fury that his product feature will be built into GP 11 as a native feature - almost blowing his NDA.

MOSS Implementation

Eric Gjerdevig - egjerdevig@summitgroupsoftware.com

Joe Tews - jtews@summitgroupsoftware.com

Business Data Catalog - Map to back-end data that allows that data to be presented in Sharepoint as a list.

Method 1 - Business Data Catalog:

• Imported into SSP in MOSS Central Admin
• After Import, create new site. Add new web part Business Data List / Business Data Related List
• Modify to select source for data for each.
• After data resolves, Edit, Connections, Get data from...

Method 2 - Excel Web Access:

• MOSS Central Admin / Operations / Services on Server / enable Excel Calc Services
• Open SSP page
• Edit Excel Services Settings
• Fill in unattended service account
• Back to SSP / Enter trusted file location
• Upload Excel doc to MOSS library. Open document and publish to excel services (Office key / publish)
• Select what items get published
• Save to publish
• Open page, add Excel Web Access Web Part
• Select workbook created

KPI's

Search

Sources:

• Internal MOSS sites
• External websites
• File Shares
• Line of Business applications
• Exchange public folders

Workflow

Approval process that defines how documents move through an organization.

• Consistent processes
• Automatic notification
• Accessible through MOSS / Outlook
• Reporting

Participant Roles:

• Administrate
• Manage
• Originate
• Approve
• Delegate
• Carbon Copy

Customizing Workflow

Christina Phillips - christinag@theknastergroup.com

Tim Oines - tim.oines@microsoft.com

General Notes

• Requires MOSS
• MDW MOSS Site
• Documents originate in GP (NOT like requisition mgt in Business Portal where documents originate outside of the system)
• Approvals via GP, Outlook or MOSS
• Flexible approval structure
  • Multiple approvers
  • Routing based on document criteria
• Workflow SDK now available on partnersource
  • Integration Guide
  • Web Service Reference
  • 2 Samples (new + extending)
• Creates SQL role DYNWORKFLOWGRP to allow security via MOSS site. However, authentication happens via WSS Timer Service account

Terms

• Workflow Contract - Definition of the workflow. What components are available.
• Workflow Contract Registration - registration of assemblies with the GAC
• Workflow events - events that occur which need to be acted upon
• Dynamics Security Store - stores web services permissions for operations and tasks using auth mgr tool
• Form Controller - responds to gp client events, manages communications with web services, adds message bars, controls and history bar to dynamics window
• Form Factory - .NET code that runs before dexterity windows are opened to determined whether information is affected by a workflow

Out of the Box Workflows (6)

• Batch Approvals
  • GL
  • Payables M
  • Rreceivables Mgt
• Sales Order Processing
  • Credit Limit Override
  • Quote Approval
• Purchase Order Processing
  • Purchase Order Approval

Beyond Out of the Box

• Create a new workflow
  • Payables Check Approval
  • Sales Credit Memo Approval
• Extend existing
  • Add custom fields to standard workflow
  • Integrate 3rd party products

Building a new workflow

1. Make app dictionary changes
2. Install data and config changes
3. Add workflow columns to database
4. Add a web service
5. Create a client workflow assembly
6. Create a server workflow assembly
7. Create a document viewer
8. Update the Dynamics Security Service
9. Update Microsoft SQL Server Security
10. Deploy the Server Workflow Assembly
11. Deploy the Web Service
12. Deploy the Client Workflow Assembly

Business Intelligence

• Jennifer Harwood, Microsoft
• Brian Meier, Microsoft
• Gary Tronson, Microsoft

Excel Report Builder

• Users create or modify data connections and refreshable excel based reports
• Combine data from multiple companies into a single workbook
• Allow publishing to MOSS or other network locations

Setup:

• Install Smartlist Builder
• Tools / Smartlist Builder / Refresh Cache
• Grant Security to tables, views. Tools / Smartlist Builder / Security
  • Update cache every time new views are created and assign security, otherwise they will not be exposed to SLB
  • Tools / Setup / System / Security Tasks / SLB window
• Open Excel Report Builder
• Begin building your report
• If you publish the report, grant security to the report wherever you publish it.
• If you can see the report, but get errors about access - grant access for the data connection in SQL. SQL Mgt Studio / Security / Logins / Make sure the user's Windows account has rights to the databases.

SQL Reporting Services (Jennifer)

Partnersource / Product Releases / Download reports that can be opened in Visual Studio for exploration or modification

Make sure to rename reports if modifying existing reports so that they are not overwritten by service packs or upgrades

Model - a diagram of data that includes tables and views which is used as the foundation of the report. automatically creates aggregates of data (date becomes day, week, month, etc.)

Rest of presentation canceled due to trying to run the demo via Terminal Services over a wifi connection that was anemic.

Integrating BI into Dynamics GP

Demo - create a list in purchasing using Business Data as the source to pull in vendor list and purchase order list. list tracks purchasing disputes. then build a kpi that tracks the number of disputes to show red/yellow/green.