This article walks you through creating a Datarails FP&A Copilot agent in Microsoft 365 Copilot Studio. Once set up, the agent connects to your Datarails Finance OS data via MCP (Model Context Protocol) and lets your finance team run dashboards, extract P&L reports, detect anomalies, compare budget vs. actuals, and more — all from natural language prompts inside Microsoft Teams or M365 Copilot Chat.

Video Walkthrough

Watch the full step-by-step walkthrough (6 minutes):

Prerequisites

• Microsoft 365 Copilot license - Required for Copilot extensibility and agent creation.
• Copilot Studio access - You need access to Microsoft Copilot Studio.
• Admin consent - Your tenant admin must allow custom agents and sideloading.
• Active Datarails account - An active Datarails environment with MCP enabled.
• Datarails MCP access - Make sure you can access Datarails by using the FinanceOS connection. If you are not sure, ask your CSM.    

What the Agent Can Do

Once configured, the Datarails FP&A Copilot agent provides 16 financial analysis capabilities through natural language:

Additional capabilities include: ad-hoc data queries, table exploration, field profiling, environment setup, API field testing, and anomaly report generation.

Step 1: Create a New Agent in Copilot Studio

1. Navigate to https://copilotstudio.microsoft.com and sign in with your Microsoft 365 account.
2. In the left sidebar, click Agents.
   
3. Click + Create blank agent
   .
4. Enter a name:  Datarails FP&A Copilot.
5. In the next screen, add a description: Financial planning and analysis agent powered by Datarails Finance OS.

 Step 2: Connect the Datarails MCP Server

This step connects the agent to your Datarails Finance OS data through the MCP protocol.

1. In the agent editor, go to Tools in the ribbon.
    
2. Click + Add a tool.
   
3. In the "Create new" section, select Model Context Protocol.
   
4. Enter the following:
   • Server name: Datarails Finance OS
   • Server description: your description.
   • Server URL: https://mcp.datarails.com/mcp
5. Under Authentication, select OAuth 2.0.
6. Under type, select Dynamic discovery.
   
7. Click Create.
8. Click on the "Not connected" button, and "Add new connection" to authenticate with your Datarails account.
   
    
9. Authorize access to Datarails.
   
    
10. Click Add and configure.

After connecting, the following 16 MCP tools will automatically appear in the Tools section:

Step 3: Enable Generative Orchestration

Generative orchestration allows the agent to automatically route user requests to the correct MCP tool based on the instructions you provided in Step 2.

1. In the agent editor, go to Settings.
2. Select Generative AI.
3. Ensure Generative orchestration is set to ON.

Why this matters: Without generative orchestration, you would need to manually build Topics with trigger phrases for each capability. With it enabled, the agent reads your instructions and tool descriptions to automatically select the right tool for each user request.

Step 4: Add Knowledge Sources (Optional)

You can give the agent access to company documents such as environment profiles and financial templates stored in SharePoint.

1. In the agent editor, go to Knowledge in the left sidebar.
2. Click + Add knowledge.
3. Select SharePoint and enter the URL to your company's SharePoint site containing Datarails reference materials.
4. Click Add.

This is especially useful for storing and retrieving the environment profile (see "First-Time Environment Setup" below).

Step 5: Test the Agent

1. In the agent editor, click Test agent (bottom-left corner).
2. Try several conversation starters to verify that the agent works correctly.
3. Verify the following during testing:
   • The agent successfully authenticates with Datarails when prompted.
   • It returns real data from your Datarails environment.
   • It uses aggregate_table_data for totals (not execute_query).
   • Data is presented in formatted tables with proper currency and percentage formatting.
   • The agent correctly routes different request types to the appropriate tools.

Please note: you might be required to authenticate again. To do that, press on the link:

and make sure that the connection status is "Connected." 

When finished, click "Retry."

Step 6: Publish the Agent

When you are satisfied with the agent's performance:

1. Click Publish (top-right corner of the agent editor).
2. Choose your deployment channels:
   • Microsoft Teams - The agent appears in the Teams sidebar.
   • Microsoft 365 Copilot - The agent appears in M365 Copilot Chat under Agents.
3. Configure who can access the agent: the entire organization or specific security groups.

First-Time Environment Setup

After the agent is published, each user (or an org admin) should run the environment discovery workflow once. This teaches the agent the structure of your Datarails data.

1. Navigate to the "Channel" section in the top ribbon.
2. Open the agent in M365 Copilot Chat or Microsoft Teams.
   
3. Make sure that the option "Make agent available in Microsoft 365 Copilot" is enabled.
   
4. Go to "Availability options" > "Waiting for approval."
   
5. Make sure that your organization admin receives a notification and approves the agent.  
6. In the top-right corner of the screen, select "Publish."
   
   
    
7. Go back to channels > Microsoft 365 and Microsoft Teams > "See agent in Microsoft 365"
   
8. Click the Set Up Environment conversation starter, or type: Learn my Datarails environment. You will be required to authenticate again. After the authentication, click "retry."
9. The agent will guide you through a multi-phase discovery process:
   • Phase 1: Verify MCP connection
   • Phase 2: Discover and list all available tables
   • Phase 3: Analyze the schema of your main financial table and map fields (Amount, Scenario, Date, Account hierarchy, Department)
   • Phase 4: Map account categories (Revenue, COGS, OpEx)
   • Phase 5: Analyze KPI table (if available)
   • Phase 6: Test each field for aggregation compatibility
   • Phase 7: Present and save the complete environment profile
10. Save the resulting profile to SharePoint for persistent use across sessions, or paste it into the agent instructions under a "My Environment" section.

Best Practices

• Run environment setup first - Always complete the "Learn Environment" workflow before using other capabilities. This ensures the agent knows your table structure and field mappings.
• Save profiles to SharePoint - For team-wide use, save the environment profile JSON to a shared SharePoint folder. The agent's knowledge source will pick it up automatically.
• Start with conversation starters - They are pre-tuned to produce good results and help new users discover what the agent can do.
• Verify critical numbers - While the agent uses unlimited-row aggregation for accuracy, always verify critical financial figures before making business decisions.
• Use natural language - The agent understands finance terminology. Ask questions as you would ask a colleague: "How are we doing vs. budget?" or "What should I know for the board meeting?"

Troubleshooting

Agent says, "I don't have access to Datarails."

Problem: The OAuth token has expired, or the MCP connection was not authenticated.

Solution:

• Go to Tools > Datarails Finance OS > click Sign in to re-authenticate.
• Verify your Datarails MCP credentials (Client ID, Client Secret) are still valid.

The agent uses execute_query for totals instead of aggregate_table_data

Problem: The agent is not following the data integrity rules in the instructions, which can lead to incomplete financial totals.

Solution:

• Review the agent instructions and ensure the critical data integrity rules are present with strong emphasis (e.g., "NEVER use execute_query for totals").
• If the issue persists, create an explicit Topic with trigger phrases like "totals", "sum", "revenue total" that forces the use of aggregate_table_data.

Aggregation fails on a field

Problem: The aggregate_table_data tool returns an error for a specific dimension field.

Solution:

• Try alternative field names. For example, use DR_ACC_L1.5 instead of DR_ACC_L1.
• Run the "Test API Fields" capability to discover which fields are aggregation-compatible.
• Update your environment profile with the working field names.

Tools not showing after connecting to the MCP server

Problem: After adding the MCP connection, no tools appear in the Tools section.

Solution:

• Verify the MCP Server URL is exactly: https://mcp.datarails.com/mcp
• Confirm the OAuth credentials are correct and the server is running.
• Try removing and re-adding the MCP connection.

The agent does not route to the correct capability

Problem: The agent picks the wrong tool or does not understand the user's intent.

Solution:

• Verify Generative orchestration is enabled in Settings > Generative AI.
• For capabilities that are frequently mis-routed, create explicit Topics with specific trigger phrases.
• Review the agent instructions to ensure routing logic is unambiguous.

The environment profile is lost between sessions

Problem: Copilot Studio does not have persistent memory across sessions like other AI platforms.

Solution:

• Recommended: Save the profile as a JSON file to SharePoint and add that SharePoint folder as a Knowledge source.
• Alternative: Paste the profile directly into the agent instructions under a "My Environment" section, then re-publish.
   

Authentication Denied

Problem: When trying to connect to Datarails, you get this result: 
"Authentication Denied: It seems like you are not permitted to use this tool. Please reach out to your CSM for more details."    

Solution: 
Verify with your CSM that the option to connect Datarails to AI platforms is enabled for you. 

Security & Third-Party Responsibility (Microsoft 365)

The Datarails FP&A Copilot agent runs inside Microsoft Copilot Studio and Microsoft 365 Copilot, which are operated by Microsoft — an independent third party that is not controlled by Datarails.

On the Datarails side, the agent authenticates using OAuth 2.0 over HTTPS, access is governed by the same permissions as your Datarails account, and Datarails does not store the financial data returned to the agent.

However, prompts, agent responses, any data rendered in Copilot, and any materials you save to SharePoint (including environment profiles) are processed and may be retained by Microsoft under Microsoft's own terms and data-protection commitments. Your use of Microsoft 365 Copilot is governed by Microsoft's terms and privacy policy, and you are responsible for ensuring that your use complies with your organization's data-handling and compliance obligations.

Before using the agent with sensitive financial data, please review Microsoft's data and privacy documentation:

• Microsoft Privacy Statement → https://privacy.microsoft.com/privacystatement
• Microsoft 365 Copilot — Data, Privacy, and Security → https://learn.microsoft.com/copilot/microsoft-365/microsoft-365-copilot-privacy

Frequently Asked Questions

Do I need a Microsoft 365 Copilot license?
Yes. A Microsoft 365 Copilot license is required for Copilot extensibility, which includes creating and using custom agents in Copilot Studio.

Can multiple users share the same agent?
Yes. Once published, the agent is available to anyone in the organization (or specific security groups, depending on your publishing settings). All users connect to the same Datarails MCP server.

Does the agent work in Microsoft Teams?
Yes. When you publish the agent, you can deploy it to both Microsoft Teams (appears in the sidebar) and M365 Copilot Chat (appears under Agents).

How does the agent connect to my Datarails data?
The agent connects via the Model Context Protocol (MCP). MCP is a standardized protocol that allows AI agents to securely access external tools and data sources. The agent authenticates using OAuth 2.0 with your Datarails credentials.

Is my financial data secure?
On the Datarails side: the agent authenticates via OAuth 2.0 over HTTPS, data is fetched in real time rather than stored by Datarails, and access is governed by the same permissions as your Datarails account. Note, however, that the agent operates within Microsoft 365 Copilot. Prompts, responses, and any data displayed in or saved to Microsoft (including SharePoint knowledge sources) are subject to Microsoft's own terms and privacy policy. Please review Microsoft's privacy documentation (https://privacy.microsoft.com/privacystatement) before using the agent with sensitive financial data.

Can I customize the agent's capabilities?
Yes. You can modify the agent instructions to change its behavior, add or remove conversation starters, create explicit Topics for specialized workflows, and add additional knowledge sources from SharePoint.

What if I also use the Datarails agent in Claude or ChatGPT?
The Copilot agent connects to the same Datarails MCP server used by other AI platforms. Any improvements to the MCP server automatically benefit all connected agents. The environment profile format is also compatible across platforms.

How do I update the agent after initial setup?
Edit the agent in Copilot Studio (instructions, tools, knowledge sources, or topics), then click Publish again. Changes take effect immediately for all users.

Last updated: April 2026

Updated June 07, 2026 11:41