Microsoft SQL Server Integration

  • Updated

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:

  1. Adjust the field mappings for your data.
  2. Set up scheduled jobs (automated data syncs).
  3. Import your data:
    1. Projects become OnePlan Plans
    2. People become OnePlan Resources
    3. Time and cost data flows into Resources and Financial planners. 
  4. Build portfolios from your new plans.
  5. 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

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request