Optimize Performance: Choose the Best Data Source for Pivot Table

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.

Understanding Syncfusion Pivot Table

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.

Factors to consider when choosing a data source for Pivot Table

Let’s see some of the significant factors that need to be considered when choosing a data source for our Pivot Table:

Data volume and complexity

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.

Performance requirements

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.

Ease of integration

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.

Real-time data access

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.

Security and compliance

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.

Best practices for choosing a data source for Pivot Table

Let’s see some of the best practices for choosing a data source for a Pivot Table:

Evaluate your use case

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.

Test performance and scalability

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.

Ensure data quality

Data quality is paramount for accurate analysis. Choose data sources that allow you to maintain high data quality standards, including validation and error checking.

Plan for data integration

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.

How does the Syncfusion Pivot Table work with different data sources?

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 data: Ideal for small to medium datasets, JSON is lightweight and easy to work with.
• OLAP (Online Analytical Processing): Suitable for large datasets and complex data analysis.
• Relational databases: SQL databases like MySQL, SQL Server, and PostgreSQL provide robust data storage and querying capabilities.
• NoSQL databases: NoSQL databases like MongoDB provide a flexible schema design for unstructured data.
• Web Services: Web Services, such as RESTful APIs and OData services, are helpful for real-time data integration and dynamic updates.
• CSV files: Simple for smaller datasets and quick setups.

JSON data

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.

Advantages

• Ease of use: JSON is easy to read, write, and parse, making it a popular choice for web apps.

• Flexibility: JSON can handle various data structures, including nested objects and arrays.

• Lightweight: JSON is compact, reducing the data transmitted over the network.

Considerations

• Performance: Handling large datasets in JSON can be inefficient and slow.

• Limited Scalability: JSON is not ideal for large or complex datasets.

When to use

• Small to medium 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');

CSV files

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.

Advantages

• Simplicity: Easy to create, read, and manipulate.
• Portability: CSV files are widely supported and easily shareable.
• Quick setup: Ideal for quick setups and small datasets.

Considerations

• Performance: Not suitable for large datasets or complex queries.
• Limited functionality: Lacks advanced features and data types.

When to use

• Small datasets for quick analysis or prototyping.
• Data migration or import/export scenarios.
• Simple apps with minimal data manipulation needs.

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 (Online Analytical Processing) data

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.

Advantages

• Performance: OLAP is designed for fast querying and data analysis, even with large datasets.
• Complex analysis: Supports complex calculations, aggregations, and multi-dimensional data analysis.
• Scalability: OLAP systems can handle massive datasets with ease.

Considerations

• Complexity: Setting up and maintaining an OLAP system can be complex and require specialized knowledge.
• Cost: OLAP solutions can be expensive to implement and maintain.

When to use

• Large datasets with complex analysis requirements.
• Business intelligence and data warehousing apps.
• Scenarios requiring high performance and scalability.

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

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.

Advantages

• Structured data: Data is organized in a tabular format, ideal for Pivot Tables.
• Scalability: Suitable for handling large volumes of data.
• ACID compliance: Ensures data integrity and consistency.

Considerations

• Complexity: Requires knowledge of SQL for data retrieval.
• Performance: This may require optimization for large datasets.

When to use

• Medium to large datasets.
• Apps requiring robust data storage and complex queries.
• Scenarios where data integrity and relationships are essential.

NoSQL databases

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.

Advantages

• Flexibility: Schema-less design allows for easy modification of data structures.
• Scalability: Excellent for handling large-scale, distributed data.

Considerations

• Query complexity: Can be more complex to query than relational databases.
• Consistency: May sacrifice consistency for availability and partition tolerance (CAP theorem).
• Connectivity: We may require specific connectors or APIs for integration.

Web Services or remote data

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.

Advantages

• Real-time data: Enables real-time data integration and dynamic updates.
• Scalability: Can handle large and distributed datasets.
• Flexibility: Easily integrate with various services and APIs.

Considerations

• Complexity: Requires developing and maintaining RESTful APIs.
• Latency: Network latency can impact performance.

When to use

• Apps needing real-time data updates.
• Integrating data from multiple sources or external APIs.
• Scenarios requiring high flexibility and scalability.

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');

References

For more details, refer to the following references:

• Connecting to PostgreSQL in Pivot Table
• Connecting to Microsoft SQL Server in Pivot Table
• Connecting to Oracle in Pivot Table
• Connecting to Elasticsearch in Pivot Table
• Connecting to Snowflake in Pivot Table
• Server-Side Pivot Engine in Pivot Table

Conclusion

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!

Related blogs

• Easily Group Data into Ranges in Web Applications Using Pivot Table
• Optimize Memory Management in JavaScript Pivot Table: Best Practices and Tips
• Easily Perform CRUD Actions in Blazor Pivot Table with SQL Database & Entity Framework
• Efficient Report Management: Save and Load Reports in Vue Pivot Table with SQL Server and Node.js Express Server