This integration aims to synchronize data between Microsoft SQL Server and OnePlan.
Data that can be loaded from Microsoft SQL Server are:
- Plans - Project names, statuses, and more by running the SQLTableToPlans strategy.
- Resources - People, departments, and more by running the SQLTableToResources strategy.
- Resource Plans - Add resources to cost types within your plans by running the SQLTableToResourcePlanner strategy.
- Financial Plans - Add financial information across multiple cost types by running the SQLTableToFinancialPlanner strategy.
OnePlan can also create custom SQL integrations that fits any scenario or custom logic. Please contact your OnePlan representative for more information.
Common Use Cases
The SQL integration has the following use cases:
Resource & Capacity Planning
Centralized Resource Import
Use Case: Resource records (names, roles, departments, locations, etc.) are loaded from SQL Server into OnePlan.
Value: Maintains a single source of truth for resource data, avoiding manual entry and ensuring consistency across planning tools.
Capacity Planning at Scale
Use Case: Available or "actuals" hours data from SQL Server is synced to OnePlan for accurate capacity modeling across departments or geographies.
Value: Enables strategic staffing decisions based on actual availability, especially helpful for globally distributed teams.
Project & Portfolio Management
Automated Project Data Sync
Use Case: Project (called Plans in OnePlan) records (IDs, names, statuses etc.) are regularly pulled from SQL Server to populate or update portfolios.
Value: Keeps the portfolio view current without needing manual updates; ensures OnePlan reflects most up to date.
Multi-System Project Consolidation
Use Case: Projects managed in multiple systems are aggregated in SQL Server, then synced to OnePlan for a unified view.
Value: Reduces siloed reporting and improves executive decision-making across a diverse project ecosystem.
Strategic Alignment Validation
Use Case: Project metadata from SQL Server is matched to strategic goals in OnePlan.
Value: Helps ensure that project investments are aligned to strategic priorities and makes misaligned work visible.
Financial Planning & Tracking
Budget Import & Synchronization
Use Case: Budget data by project is pulled from SQL Server and loaded into OnePlan.
Value: Enables planning teams to track and forecast spend against budgets without spreadsheet wrangling.
Actuals Tracking
Use Case: Actual financials from ERP or accounting systems stored in SQL Server are loaded into OnePlan.
Value: Facilitates budget vs actual comparisons and real-time financial monitoring at both project and portfolio levels.
Cost-to-Resource Rollups
Use Case: SQL Server provides cost center or role-based cost data that’s mapped to projects in OnePlan.
Value: Allows financial planners to assess true resource costs and optimize budgets accordingly.
Advanced Analytics & Decision Support
Portfolio Dashboards
Use Case: Structured SQL data is brought into OnePlan to power custom dashboards showing KPIs like cost per project, resource utilization, and ROI.
Value: Delivers executive-level insights without needing BI tools or custom reports all within OnePlan.
Key Features
The SQL integration supports the following features:
- Works with on-premises or cloud-based Microsoft SQL Server
- Secure connection options (e.g., IP whitelisting)
- Supports scheduled automatic data imports
- Choose exactly what data to import using filters
- Supports creation and updates for:
- Plans
- Resources
- Resource plan data
- Financial plan data
Review the different OneConnect subscription types to determine which integration subscription fits your company's requirements.
Setup Requirements
Before setting up the integration, make sure:
- The necessary database, tables, and fields are created in your SQL database
- The database is accessible (e.g., firewall rules, credentials, etc.)
- Data is populated in the SQL tables you wish to import.
Setup
To set up the integration, see the Setup SQL Server Integration article for step-by-step instructions.
After Setup: What Happens Next?
Once the integration is installed:
- Adjust the field mappings for your data.
- Set up scheduled jobs (automated data syncs).
- Import your data:
- Projects become OnePlan Plans
- People become OnePlan Resources
- Time and cost data flows into Resources and Financial planners.
- Build portfolios from your new plans.
- Start managing your projects in OnePlan!
Mind the Gap
Saving Data into the SQL Database
This integration only brings data into OnePlan from SQL Server. .
Want to send data from OnePlan to SQL Server instead? There's a separate integration for that - OnePlan OneAnalytics.
Related to