TL;DR: Syncfusion Pivot Table connects to multiple data sources, making it a versatile tool for data analysis. Selecting the right data source is crucial for performance. This guide explores different options and offers tips on choosing the best one.
Syncfusion Pivot Table is a powerful tool for data analysis and visualization. One of its standout features is the ability to connect to various data sources, making it a versatile choice for businesses and developers. Choosing the right data source for your Pivot Table is crucial for ensuring optimal performance and usability.
Let’s explore the different data sources you can connect to Syncfusion Pivot Table, their benefits, and how to choose the one that best suits your needs.
Before diving into data sources, it’s essential to understand what the Syncfusion Pivot Table is and how it works. The Syncfusion Pivot Table is a component for ASP.NET MVC, ASP.NET Core, TypeScript, JavaScript, Angular, React, Vue, and Blazor platforms. It allows you to create interactive tables for data analysis, offering features like data filtering, sorting, grouping, and aggregation.
Let’s see some of the significant factors that need to be considered when choosing a data source for our Pivot Table:
The size of your data can affect performance. Large datasets might require more powerful databases or optimized storage solutions. For example, JSON or CSV files might be sufficient for small to medium datasets. For larger datasets, consider using Relational and NoSQL databases or OLAP systems. OLAP systems provide the necessary capabilities for complex data analysis and multi-dimensional data. For more straightforward analysis, JSON or CSV files can be practical.
Consider the performance requirements of your app. Databases generally offer better performance for large-scale data operations compared to file-based data sources. However, JSON and CSV files might suffice for quick and straightforward analysis.
Evaluate how easily the data source can be integrated with Syncfusion Pivot Table. Relational databases and JSON/XML files typically have well-established integration methods, while some NoSQL databases and RESTful APIs might require additional configuration.
If your app requires real-time data access, use RESTful APIs or databases with real-time capabilities. File-based data sources like Excel and CSV are static and require manual updates.
Ensure your chosen data source complies with your organization’s security and privacy requirements. Databases often provide robust security features, whereas file-based data sources might require additional measures to secure sensitive information.
Let’s see some of the best practices for choosing a data source for a Pivot Table:
Understand your app’s specific needs and choose a data source that aligns with those needs. Consider the data type, required operations, and user expectations. Consider NoSQL databases for web apps that require high availability and scalability. Relational databases like SQL Servers are a good choice for traditional business apps that use structured data.
Conduct performance testing to ensure the chosen data source can handle the expected load. Also, consider future scalability needs and choose a solution that can grow with your app.
Data quality is paramount for accurate analysis. Choose data sources that allow you to maintain high data quality standards, including validation and error checking.
Develop a clear plan for integrating your data source with Syncfusion Pivot Table. Consider using middleware or ETL (Extract, Transform, Load) tools to streamline the integration process. JSON and CSV data are convenient for quick setups and ease of use. Relational databases and OLAP systems require more setup and maintenance but offer greater capabilities.
The flexible and robust data binding capabilities facilitate Syncfusion Pivot Table’s ability to work with various data sources. It supports multiple data sources, including:
JSON (JavaScript Object Notation) is a lightweight data interchange format. Syncfusion Pivot Table can easily consume JSON data, which is particularly useful for web apps where data is often fetched in JSON format from APIs.
Flexibility: JSON can handle various data structures, including nested objects and arrays.
Lightweight: JSON is compact, reducing the data transmitted over the network.
Performance: Handling large datasets in JSON can be inefficient and slow.
Limited Scalability: JSON is not ideal for large or complex datasets.
Apps where ease of use and quick setup are priorities.
Prototyping and testing.
Here is a code example for integrating JSON data with our Pivot Table.
Binding JSON data via local
var localData = [
{ Product: 'Bike', Country: 'USA', Sales: 100 },
{ Product: 'Car', Country: 'Canada', Sales: 200 }
];
var pivotTableObj = new ej.pivotview.PivotView({
dataSourceSettings: {
dataSource: localData,
rows: [{ name: 'Product' }],
columns: [{ name: 'Country' }],
values: [{ name: 'Sales' }],
filters: []
}
});
pivotTableObj.appendTo('#PivotTable');
Binding JSON data via remote (external) link
var pivotGridObj = new ej.pivotview.PivotView({
dataSourceSettings: {
url: 'https://cdn.syncfusion.com/data/sales-analysis.json',
expandAll: false,
rows: [
{ name: 'EnerType', caption: 'Energy Type' }
],
columns: [
{ name: 'EneSource', caption: 'Energy Source' }
],
values: [
{ name: 'PowUnits', caption: 'Units (GWh)' },
{ name: 'ProCost', caption: 'Cost (MM)' }
],
filters: []
}
});
pivotGridObj.appendTo('#PivotTable');
Comma-separated values (CSV) files are a standard format for exporting and importing data. Syncfusion Pivot Table can parse CSV files and use them as a data source, making it convenient to analyze data from spreadsheets or other tabular data sources.
Here is a code example for integrating CSV data with Pivot Table.
Binding CSV data via local
var csvdata =
'Region,Country,Item Type,Sales Channel,Total Revenue,Total Cost,Total Profit\r\nMiddle East and North Africa,Libya,Cosmetics,Offline,3692591.20,2224085.18,1468506.02\r\nNorth America,Canada,Vegetables,Online,464953.08,274426.74,190526.34\r\nMiddle East and North Africa,Libya,Baby Food,Offline,387259.76,241840.14,145419.62\r\nAsia,Japan,Cereal,Offline,683335.40,389039.42,294295.98';
var pivotObj = new ej.pivotview.PivotView({
dataSourceSettings: {
dataSource: getCSVData(),
type: 'CSV',
expandAll: false,
formatSettings: [
{ name: 'Total Cost', format: 'C0' },
{ name: 'Total Revenue', format: 'C0' },
{ name: 'Total Profit', format: 'C0' },
],
drilledMembers: [{ name: 'Item Type', items: ['Baby Food'] }],
rows: [{ name: 'Country' }, { name: 'Region' }],
columns: [{ name: 'Sales Channel' }, { name: 'Item Type' }],
values: [
{ name: 'Total Profit' },
{ name: 'Total Cost' },
{ name: 'Total Revenue' },
],
filters: [],
},
height: 290,
width: '100%',
});
pivotObj.appendTo('#PivotTable');
function getCSVData() {
var dataSource = [];
var jsonObject = csvdata.split(/\r?\n|\r/);
for (var i = 0; i < jsonObject.length; i++) {
dataSource.push(jsonObject[i].split(','));
}
return dataSource;
}
Binding CSV data via remote (external) link
var pivotObj = new ej.pivotview.PivotView({
dataSourceSettings: {
url: 'https://bi.syncfusion.com/productservice/api/sales',
type: 'CSV',
expandAll: false,
enableSorting: true,
formatSettings: [{ name: 'Total Cost', format: 'C0' }, { name: 'Total Revenue', format: 'C0' }, { name: 'Total Profit', format: 'C0' }],
drilledMembers: [{ name: 'Item Type', items: ['Baby Food'] }],
rows: [
{ name: 'Region' },
{ name: 'Country' }
],
columns: [
{ name: 'Item Type' },
{ name: 'Sales Channel' }
],
values: [
{ name: 'Total Cost' },
{ name: 'Total Revenue' },
{ name: 'Total Profit' }
],
filters: []
},
height: 300,
width: '100%'
});
pivotObj.appendTo('#PivotTable');
OLAP cubes allow for complex data analysis and are commonly used in business intelligence apps. Syncfusion Pivot Table supports OLAP data sources, enabling multi-dimensional data analysis with rich, hierarchical data structures. OLAP data sources often provide faster query responses due to pre-aggregated data.
Here is a code example for integrating the OLAP data with the Pivot Table.
var pivotTableObj = new ej.pivotview.PivotView({
dataSourceSettings: {
catalog: 'Adventure Works DW 2008 SE',
cube: 'Adventure Works',
providerType: 'SSAS',
enableSorting: true,
url: 'https://bi.syncfusion.com/olap/msmdpump.dll',
localeIdentifier: 1033,
rows: [{ name: '[Customer].[Customer Geography]'],
columns: [{ name: '[Product].[Product Categories]'},
{ name: '[Measures]'}],
values: [{ name: '[Measures].[Customer Count]'},
{ name: '[Measures].[Internet Sales Amount]']
}
});
pivotTableObj.appendTo('#PivotTable');
Relational databases are one of the most common data sources for our Pivot Table. They store data in tables, making it easy to retrieve and manipulate data using SQL queries. Popular relational databases include MySQL, PostgreSQL, SQL Server, and Oracle.
NoSQL databases such as MongoDB offer a flexible schema and are designed to handle unstructured data, making them a good choice for certain types of data analysis.
Data can be fetched from remote servers using web services or APIs. Syncfusion Pivot Table can connect to remote data sources via RESTful services, making it ideal for apps that analyze data from various endpoints. It involves retrieving data from remote servers or databases.
Here’s a code example for connecting an API service to our Pivot Table.
var data = new ej.data.DataManager({
url: 'https://bi.syncfusion.com/northwindservice/api/orders',
adaptor: new ej.data.WebApiAdaptor(),
crossDomain: true,
});
data.defaultQuery = new ej.data.Query().take(8);
var pivotTableObj = new ej.pivotview.PivotView({
dataSourceSettings: {
dataSource: data,
expandAll: true,
filters: [],
columns: [{ name: 'ProductName' }],
rows: [{ name: 'ShipCountry' }, { name: 'ShipCity' }],
formatSettings: [{ name: 'UnitPrice', format: 'C0' }],
values: [{ name: 'Quantity' }, { name: 'UnitPrice' }],
},
height: 350,
width: '100%',
gridSettings: { columnWidth: 120 },
});
pivotTableObj.appendTo('#PivotTable');
For more details, refer to the following references:
Thanks for reading! In this blog, we’ve explored the various options, benefits, and best practices for choosing the right data source for the Syncfusion Pivot Table, ensuring optimal performance and usability.
If you’re already a Syncfusion user, the latest version of Essential Studio is available on the License and Downloads page. We offer our new users a 30-day free trial to explore all our components’ features and capabilities.
If you need further assistance, contact us via our support forum, support portal, or feedback portal. We’re always here to help you!
The above is the detailed content of Optimize Performance: Choose the Best Data Source for Pivot Table. For more information, please follow other related articles on the PHP Chinese website!
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
