Revanth Periyasamy
In today’s data-driven business landscape, the ability to access and analyze data from various sources is crucial for making informed decisions. Peliqan is a powerful platform that simplifies this process by providing direct data access to a wide range of data sources, including SaaS business applications, databases, data warehouses, APIs, and files. This comprehensive guide will walk you through the process of connecting to data sources using Peliqan, enabling you to harness the full potential of your data.
Peliqan’s flexible architecture allows you to connect to both cloud-based and on-premises data sources, making it an ideal solution for businesses with diverse data ecosystems. Whether you’re working with traditional relational databases, modern NoSQL databases, or popular SaaS applications, Peliqan has you covered.
The first step in leveraging Peliqan’s data integration capabilities is to add a new connection. Here’s how to do it:
Once you’ve added a connection, Peliqan automatically sets up a data pipeline and begins the synchronization process for SaaS sources or schema discovery for databases. This process typically takes between a few seconds to a few minutes, depending on the size and complexity of your data source.
Peliqan handles different types of data sources in unique ways to optimize performance and data accessibility:
For these types of connections, Peliqan performs schema discovery without replicating the actual data. This means it creates a catalog of all databases, schemas, tables, and columns within the source. When you access data from these sources or execute SQL queries in Peliqan, it uses a direct database connection to fetch the data in real-time.
Key points:
When connecting to SaaS applications, Peliqan sets up an ETL (Extract, Transform, Load) pipeline to replicate your data into either the built-in Peliqan data warehouse or a data warehouse of your choice (such as Snowflake or BigQuery).
Key points:
By providing these powerful connections to databases, data warehouses, and SaaS applications, Peliqan enables you to make the most of your existing data infrastructure while adding powerful data integration, transformation, and analysis capabilities.
Now, let’s explore how this process works for various popular data sources, along with examples of how you can leverage this integration for powerful insights. Let’s take some popular tools + Power BI integrations in each categories,
Finance & Accounting
Marketing & Sales
Xero, a popular cloud-based accounting software, holds valuable financial data that can significantly enhance your Power BI reports. Here’s how to connect Xero to Power BI using Peliqan:
Create a dynamic cash flow analysis dashboard to visualize cash inflows and outflows.
SELECT
i.Date AS TransactionDate,
i.InvoiceNumber,
i.Total AS InvoiceAmount,
p.Amount AS PaymentAmount,
i.Total - COALESCE(p.Amount, 0) AS OutstandingAmount
FROM
Xero.Invoices i
LEFT JOIN
Xero.Payments p ON i.InvoiceID = p.InvoiceID
ORDER BY
i.DateThis SQL query combines invoice and payment data, allowing you to visualize cash inflows and outflows over time in Power BI.
NetSuite, an all-in-one cloud business management suite, offers a comprehensive set of business data. Here’s how to connect NetSuite to Power BI with Peliqan:
Develop a dashboard that combines financial and inventory data from NetSuite. Here’s a sample query in Peliqan:
SELECT
t.TransactionDate,
t.TransactionType,
t.Amount,
i.ItemName,
i.QuantityAvailable,
i.ReorderPoint
FROM
NetSuite.Transactions t
JOIN
NetSuite.Items i ON t.ItemID = i.ItemID
WHERE
t.TransactionDate >= DATE_SUB(CURRENT_DATE, INTERVAL 180 DAY)This SQL query allows you to create visualizations in Power BI that show financial trends alongside inventory levels and reorder points.
QuickBooks, a widely used accounting software, contains crucial financial data for businesses. Here’s how to connect QuickBooks to Power BI using Peliqan:
Assumptions:
Create a detailed profit and loss analysis dashboard. Here’s a sample query in Peliqan:
SELECT
t.TxnDate,
t.AccountType,
t.AccountName,
t.Amount,
c.CustomerName,
p.ProductName
FROM
QuickBooks.Transactions t
LEFT JOIN
QuickBooks.Customers c ON t.CustomerRef = c.CustomerID
LEFT JOIN
QuickBooks.Products p ON t.ProductRef = p.ProductID
WHERE
t.TxnDate >= DATE_SUB(CURRENT_DATE, INTERVAL 365 DAY)
ORDER BY
t.TxnDateThis SQL query allows you to create Power BI visualizations that show revenue streams, expense categories, and profitability over time.
Exact Online, a cloud-based business software, offers comprehensive financial and business management data. Here’s how to connect Exact Online to Power BI using Peliqan:
Assumptions:
Develop a dashboard that provides insights into your company’s financial health. Here’s a sample query in Peliqan:
SELECT
t.TransactionDate,
t.AccountCode,
a.AccountName,
t.Amount,
t.Description,
c.CustomerName,
i.InvoiceNumber
FROM
ExactOnline.Transactions t
JOIN
ExactOnline.Accounts a ON t.AccountCode = a.AccountCode
LEFT JOIN
ExactOnline.Customers c ON t.RelationCode = c.CustomerCode
LEFT JOIN
ExactOnline.Invoices i ON t.TransactionID = i.TransactionID
WHERE
t.TransactionDate >= DATE_SUB(CURRENT_DATE, INTERVAL 180 DAY)This SQL query allows you to create Power BI visualizations that show cash flow, accounts receivable aging, and revenue trends.
Shopify, a leading e-commerce platform, contains valuable data about your online store’s performance. Here’s how to connect Shopify to Power BI with Peliqan:
Assumptions:
Build a comprehensive e-commerce dashboard that tracks sales, product performance, and customer behavior. Here’s a sample query in Peliqan:
SELECT
p.ProductName,
SUM(o.Quantity) as TotalSold,
AVG(o.Price) as AveragePrice,
i.QuantityOnHand,
c.CustomerCity,
c.CustomerCountry
FROM
Shopify.Orders o
JOIN
Shopify.Products p ON o.ProductID = p.ProductID
JOIN
Shopify.Inventory i ON p.ProductID = i.ProductID
JOIN
Shopify.Customers c ON o.CustomerID = c.CustomerID
WHERE
o.OrderDate >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
GROUP BY
p.ProductName, i.QuantityOnHand, c.CustomerCity, c.CustomerCountryThis SQL query allows you to create Power BI visualizations that show top-selling products, inventory levels, and geographical sales distribution.
JIRA, Atlassian’s popular project management and issue tracking tool, contains valuable data about your team’s work and productivity. Here’s how to connect JIRA to Power BI using Peliqan:
Example: Agile Project Management Dashboard
Develop a dashboard that tracks your team’s agile development process. Here’s a sample query in Peliqan:
SELECT
i.IssueKey,
i.Summary,
i.IssueType,
i.Status,
i.StoryPoints,
s.SprintName,
s.StartDate,
s.EndDate,
u.DisplayName as Assignee
FROM
JIRA.Issues i
LEFT JOIN
JIRA.Sprints s ON i.SprintID = s.SprintID
LEFT JOIN
JIRA.Users u ON i.AssigneeID = u.UserID
WHERE
i.UpdatedDate >= DATEADD(day, -90, CURRENT_DATE())
ORDER BY
s.StartDate, i.StoryPoints DESCThis SQL query allows you to create Power BI visualizations that show sprint progress, issue distribution, and team member workload. You can track key agile metrics such as velocity, burndown, and cycle time.
Zendesk, a customer service and engagement platform, contains valuable data about customer interactions. Here’s how to connect Zendesk to Power BI using Peliqan:
Assumptions:
Create a dashboard that tracks key customer support metrics. Here’s a sample query in Peliqan:
SELECT
t.TicketID,
t.Subject,
t.Status,
t.Priority,
t.CreatedAt,
t.ResolvedAt,
DATEDIFF(HOUR, t.CreatedAt, t.ResolvedAt) as ResolutionTime,
a.Name as AssignedAgent,
s.Score as SatisfactionScore
FROM
Zendesk.Tickets t
LEFT JOIN
Zendesk.Agents a ON t.AssignedAgentID = a.AgentID
LEFT JOIN
Zendesk.Satisfaction s ON t.TicketID = s.TicketID
WHERE
t.CreatedAt >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)This SQL query allows you to visualize ticket resolution times, customer satisfaction scores, and agent performance in Power BI.
HubSpot, a leading CRM and marketing automation platform, contains a wealth of customer and marketing data. Here’s how to integrate HubSpot with Power BI using Peliqan:
Create a comprehensive sales and marketing dashboard by combining HubSpot contact and deal data. Here’s a sample query in Peliqan:
SELECT
c.Email,
c.Lifecycle_Stage,
d.DealStage,
d.Amount,
d.CloseDate
FROM
HubSpot.Contacts c
LEFT JOIN
HubSpot.Deals d ON c.ContactID = d.AssociatedContactID
WHERE
d.CloseDate >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)This SQL query allows you to visualize your sales pipeline, lead conversion rates, and deal values in Power BI.
MongoDB, a popular NoSQL database, stores vast amounts of unstructured and semi-structured data. Here’s how to integrate MongoDB with Power BI using Peliqan:
Assumptions:
Analyze user behavior patterns from MongoDB event logs. Here’s a sample query in Peliqan:
SELECT
UserID,
EventType,
COUNT(*) as EventCount,
AVG(DATEDIFF(SECOND, SessionStart, SessionEnd)) as AvgSessionDuration
FROM
MongoDB.EventLogs
WHERE
EventDate >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY
UserID, EventTypeThis SQL query allows you to create visualizations in Power BI that show user engagement metrics and session durations.
LinkedIn, the world’s largest professional network, provides valuable data for B2B marketers and recruiters. Here’s how to connect LinkedIn to Power BI using Peliqan:
Create a dashboard that tracks your LinkedIn marketing efforts. Here’s a sample query in Peliqan:
SELECT
Date,
Followers,
Impressions,
Clicks,
Engagements,
CTR
FROM
LinkedIn.CompanyPageAnalytics
WHERE
Date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
ORDER BY
Date DESCThis SQL query allows you to visualize your LinkedIn follower growth, post engagement rates, and ad performance in Power BI.
Google Sheets is a versatile collaborative spreadsheet tool used by many organizations. Here’s how to connect Google Sheets to Power BI with Peliqan:
SELECT
Date,
Product,
ActualSales,
ForecastSales,
(ActualSales - ForecastSales) as Variance
FROM
GoogleSheets.SalesForecast
WHERE
Date >= CURRENT_DATE()
ORDER BY
Date, ProductThis SQL query allows you to create visualizations in Power BI that compare actual sales against forecasts and track variances over time.
Odoo, an open-source ERP and CRM system, contains comprehensive business data. Here’s how to integrate Odoo with Power BI using Peliqan:
Develop a dashboard that combines inventory levels and sales performance. Here’s a sample query in Peliqan:
SELECT
p.Name as ProductName,
s.Quantity as StockQuantity,
s.ReorderPoint,
w.Name as Warehouse,
SUM(so.Quantity) as SoldQuantity,
SUM(so.PriceTotal) as Revenue
FROM
Odoo.Products p
JOIN
Odoo.Stock s ON p.ProductID = s.ProductID
JOIN
Odoo.Warehouses w ON s.WarehouseID = w.WarehouseID
LEFT JOIN
Odoo.SaleOrderLines so ON p.ProductID = so.ProductID
WHERE
so.Date >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
GROUP BY
p.Name, s.Quantity, s.ReorderPoint, w.NameThis SQL query allows you to create Power BI visualizations that show inventory levels alongside sales performance for each product.
Airtable, a flexible database-spreadsheet hybrid, is used by many teams for project management and data organization. Here’s how to connect Airtable to Power BI using Peliqan:
Create a dashboard that tracks project progress and resource allocation. Here’s a sample query in Peliqan:
SELECT
p.ProjectName,
t.TaskName,
t.Status,
t.AssignedTo,
t.DueDate,
t.EstimatedHours,
t.ActualHours
FROM
Airtable.Projects p
JOIN
Airtable.Tasks t ON p.ProjectID = t.ProjectID
WHERE
t.DueDate >= CURRENT_DATE()
ORDER BY
t.DueDateThis SQL query allows you to create Power BI visualizations that show project timelines, task status, and resource allocation.
Notion, an all-in-one workspace for notes, documents, and databases, contains valuable organizational data. Here’s how to integrate Notion with Power BI using Peliqan:
Assumptions:
Develop a dashboard that tracks document usage and team collaboration. Here’s a sample query in Peliqan:
SELECT
PageTitle,
Creator,
LastEditedDate,
ViewCount,
CommentCount,
Tags
FROM
Notion.Pages
WHERE
LastEditedDate >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
ORDER BY
ViewCount DESCThis SQL query allows you to create Power BI visualizations that show popular documents, active contributors, and trending topics in your Notion workspace.
Facebook, the world’s largest social network, provides rich data for marketers and businesses. Here’s how to connect Facebook to Power BI using Peliqan:
Assumptions:
Create a dashboard that tracks your Facebook marketing efforts. Here’s a sample query in Peliqan:
SELECT
Date,
PostID,
PostType,
Reach,
Impressions,
Likes,
Comments,
Shares
FROM
Facebook.PostInsights
WHERE
Date >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
ORDER BY
Date DESC, Reach DESCThis SQL query allows you to create Power BI visualizations that show post engagement, audience reach, and content performance over time.
OneDrive, Microsoft’s cloud storage service, often contains important business files and data. Here’s how to connect OneDrive to Power BI using Peliqan:
Assumptions:
Develop a dashboard that tracks file usage and team collaboration. Here’s a sample query in Peliqan:
SELECT
FileName,
FileType,
CreatedBy,
LastModifiedDate,
LastModifiedBy,
AccessCount,
SharedWithCount
FROM
OneDrive.Files
WHERE
LastModifiedDate >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
ORDER BY
AccessCount DESCThis SQL query allows you to create Power BI visualizations that show popular files, active contributors, and collaboration patterns in your OneDrive.
AFAS, a comprehensive business software suite popular in the Benelux region, contains a wealth of business data. Here’s how to connect AFAS to Power BI using Peliqan:
Assumptions:
Create a dashboard that combines financial and HR data. Here’s a sample query in Peliqan:
SELECT
f.AccountCode,
f.Description,
f.Amount as ActualAmount,
b.Amount as BudgetedAmount,
e.Department,
COUNT(e.EmployeeID) as EmployeeCount
FROM
AFAS.FinancialTransactions f
JOIN
AFAS.Budget b ON f.AccountCode = b.AccountCode
JOIN
AFAS.Employees e ON f.Department = e.Department
WHERE
f.TransactionDate >= DATE_SUB(CURRENT_DATE, INTERVAL 90 DAY)
GROUP BY
f.AccountCode, f.Description, f.Amount, b.Amount, e.DepartmentThis SQL query allows you to create Power BI visualizations that show financial performance alongside workforce metrics.
Snowflake, a cloud-based data warehousing platform, offers powerful analytics capabilities. Here’s how to connect Snowflake to Power BI using Peliqan:
Create a comprehensive analytics dashboard using your Snowflake data warehouse. Here’s a sample query in Peliqan:
SELECT
s.SaleDate,
p.ProductName,
p.Category,
s.QuantitySold,
s.SaleAmount,
c.CustomerName,
c.Region
FROM
Snowflake.Sales s
JOIN
Snowflake.Products p ON s.ProductID = p.ProductID
JOIN
Snowflake.Customers c ON s.CustomerID = c.CustomerID
WHERE
s.SaleDate >= DATEADD(day, -365, CURRENT_DATE())This SQL query allows you to create Power BI visualizations that show sales trends, product performance, and customer segmentation across regions, leveraging Snowflake’s powerful data processing capabilities.
Amazon Redshift, a cloud data warehouse, often stores large volumes of analytical data. Here’s how to connect Redshift to Power BI using Peliqan:
Create a dashboard that handles large-scale data analysis from your Redshift data warehouse. Here’s a sample query in Peliqan:
SELECT
c.CustomerID,
c.CustomerName,
o.OrderDate,
p.ProductName,
o.Quantity,
o.TotalAmount,
l.City,
l.Country
FROM
Redshift.Customers c
JOIN
Redshift.Orders o ON c.CustomerID = o.CustomerID
JOIN
Redshift.Products p ON o.ProductID = p.ProductID
JOIN
Redshift.Locations l ON c.LocationID = l.LocationID
WHERE
o.OrderDate >= DATE_SUB(CURRENT_DATE, INTERVAL 365 DAY)This SQL query allows you to create Power BI visualizations that show customer behavior patterns, product performance, and geographical sales distribution on a large scale.
Workday, a cloud-based system for finance, HR, and planning, contains critical business data. Here’s how to connect Workday to Power BI using Peliqan:
Develop a comprehensive dashboard that combines workforce analytics with financial performance metrics. Here’s a sample query in Peliqan:
SELECT
e.EmployeeID,
e.FullName,
e.Department,
e.JobTitle,
p.PerformanceRating,
c.CompensationAmount,
f.BudgetAmount,
f.ActualSpend
FROM
Workday.Employees e
JOIN
Workday.PerformanceReviews p ON e.EmployeeID = p.EmployeeID
JOIN
Workday.Compensation c ON e.EmployeeID = c.EmployeeID
JOIN
Workday.FinancialData f ON e.Department = f.Department
WHERE
p.ReviewDate >= DATE_SUB(CURRENT_DATE, INTERVAL 365 DAY)This SQL query allows you to create Power BI visualizations that show employee performance metrics alongside department budgets and actual spend, providing a holistic view of your organization’s human capital and financial health.
By leveraging Peliqan’s powerful connectors and data transformation capabilities, you can easily integrate data from a wide variety of sources into Power BI. This enables you to create comprehensive, data-driven reports and dashboards that provide a 360-degree view of your business.
This standardized approach simplifies the data integration process, allowing you to focus on deriving insights from your data rather than struggling with complex connection setups.
Remember, the key to successful data integration is not just connecting the sources, but also properly transforming and modeling the data to create meaningful relationships and metrics. Peliqan simplifies this process by allowing you to use SQL to prepare your data before it reaches Power BI, reducing the data preparation workload within Power BI itself.
As you explore these various data connections, consider how each source can contribute to a more complete picture of your business performance. Whether you’re analyzing financial data from QuickBooks, tracking employee performance with Workday, managing customer relationships with HubSpot, or handling large-scale data analysis with Redshift, Peliqan and Power BI together provide a powerful toolkit for turning your data into actionable insights.
By mastering the art of connecting diverse data sources to Power BI through Peliqan, you’ll be well-equipped to drive data-informed decision-making across your organization, unlocking new insights and opportunities for growth. Start exploring the possibilities today, and transform the way your organization leverages its data for success!
Revanth Periyasamy is a process-driven marketing leader with over 5+ years of full-funnel expertise. As Peliqan's Senior Marketing Manager, he spearheads martech, demand generation, product marketing, SEO, and branding initiatives. With a data-driven mindset and hands-on approach, Revanth consistently drives exceptional results.