Merging Hierarchical Dashboard Data

Read how InetSoft saves money and resources with deployment flexibility.

These selections correspond, respectively, to the left outer join, the right outer join, and the full outer join. In addition to the matching rows preserved by the equi-join, the left outer join preserves all the rows in the top table, while the right outer join preserves all the rows in the bottom table. The full outer join preserves all the rows of both tables.

When an outer join is specified, the additional rows included from a table (beyond those selected by the equi-join) do not have matching rows in the other table. Therefore, outer-join tables generally exhibit empty cells corresponding to the unmatched rows.

Follow the steps below to change the join type of a joined table from inner join to left outer join.

1. Create a new Worksheet by clicking on the 'New Worksheet' button.
2. Expand the 'Query' node, and the 'Orders' node.
3. Drag the 'customers' node from the tree to an empty cell in the Worksheet. A new table named 'customers1' is created.
4. Expand the 'DWS' node on the 'Query' tree.
5. Drag the 'NY Customers' node from the tree to an empty cell in the Worksheet. A new table named 'NY Customers1' is created.
6. Select the 'customer_id' column header in table 'customers1', hold down the left mouse button and drag it from the 'customers1' table onto the 'CUSTOMER_ID' column header of the 'NY Customers1' table. This creates a new (inner) joined table called 'Query1'.
7. Right-click on the title row of the 'Query1' table and select 'Preview' from the context menu.
   Notice that only the customers from the state of NY are listed.
8. Close the preview.
9. In the Data Worksheet, click the join connector in table 'Query1' to select it.
10. Right-click the connector and select 'Join Properties' from the context menu. This opens the 'Join Type' dialog box.
11. Select the 'include all values' option for the 'customers1' table and click 'OK'.
12. Preview the table again. Notice that the two tables are now joined using the left outer join.

Data Manipulation Articles on InetSoft

• Data Grid Caching

  InetSoft's StyleBI platform facilitates big data manipulation by accessing and aggregating data from disparate sources like spreadsheets, Hadoop, Apache Spark, MapR, and SAP HANA, eliminating the need for a traditional data warehouse. The software enables users to modify data sets, identify relationships, and interpret data without altering the original sources, while remembering user-defined contextualizations for consistent analysis. Its user-friendly interface supports both easy and advanced data manipulation, incorporating visualizations, visual analysis, and published reporting in real time. Data grid caching technology uses in-memory database reporting to cache data, reducing slow response times and allowing flexible refresh intervals such as every 5 minutes, hourly, or daily to handle large datasets efficiently. This approach ensures actionable insights from vast, growing data stores that traditional tools cannot manage with the same speed and efficiency.

• Group and Aggregate

  InetSoft's BI Tool features a data manipulation layer called Worksheets, which includes tools such as joins, filters, concatenations, grouping, and aggregation for handling Data Blocks. To manipulate a Data Block, users right-click the table's top row and select 'Group and Aggregate' to open the Aggregate dialog box. In the dialog, selections can be made to group fields like 'Order.Date' by month and aggregate fields like 'Product.Total' using sum. Upon clicking 'OK', the specified grouping is applied to the relevant columns, while unspecified columns like 'Category' are hidden. After manipulation, the Data Block can be saved as an asset named "SampleAsset" in the Global scope of the Asset Repository for use in reports and other Worksheets.

• Automate Machine Learning Workflows

  Data mining involves extensive data storage and manipulation, where machine learning serves as a component focused on learning from the data, contrasting with the negative connotation of data mining as "torturing data" until it fits preconceived notions. The presenter emphasizes the importance of empirical results over theory in industry applications, highlighting how data manipulation in data mining can lead to a "drill-till-you-find-something" mentality that is less desirable. In the end-to-end data process, data scientists engage in collecting and preparing data to understand its contents, limitations, and potential, but repetitive aspects like ensuring consistent data streams should be automated to avoid manual repetition. Automation is crucial for productionizing machine learning models, including deploying scoring jobs and embedding business logic, allowing data scientists to hand off tasks to IT with confidence in batch, online, or streaming environments. Robust automation processes, including audit trails for data input, applied processes, and timestamps, are essential for transparency in regulated industries, freeing data scientists to focus on advanced tasks rather than manual data handling.

• Data Worksheet Creation

  InetSoft's BI platform enables data manipulation through reusable data worksheets, allowing users to create, join, and mash up data sources for advanced calculations and what-if scenarios, which can then be shared for dashboard and report creation. In predictive analytics software, data preparation involves defining timeframes, transaction types, and patterns to model and identify fraudulent orders, such as in eCommerce fraud reduction by excluding non-credit card transactions and applying deep learning algorithms. For generating analytics reports, the process starts with creating a prototype report as a skeleton with editable components like crosstabs or charts, setting page limits, and assigning IDs for user modifications via an Ad Hoc Wizard. Successful data mining projects emphasize massaging data with business-driven time windows, such as 18-month purchase inactivity to flag lost customers, balancing predictive modeling with decision-making needs. Superstore analytics dashboards support data manipulation by applying dynamic filters for regions, categories, customer segments, sales amounts, profits, and order dates to drill down and adjust visualizations in real-time.

• Data Mashup for Visualizations

  StyleBI offers a data integration platform that enables real-time access and mashup of multiple data sources, including Big Data, to create seamless dashboards and reports by combining them with traditional sources. Users can manipulate data through a spreadsheet-like drag-and-drop interface for easy creation and modification of reports and dashboards, incorporating sophisticated analytics like what-if analysis. High-speed data manipulation on Big Data is achieved via data grid caching, which combines in-memory database reporting with disk access inspired by Hadoop and MapReduce. InetSoft's patent-pending Data Block™ technology facilitates productive reuse of queries and end-user defined data mashups, enhancing self-service capabilities for data analysis and visualization. In examples, movie production companies and hedge funds use big data manipulation for tasks such as audience segmentation, predictive analytics, sentiment analysis, and quantitative trading strategies to derive insights from vast datasets.

• Data Mashup Engine

  Focus Consulting Group selected InetSoft's Style Scope for its flexibility in data manipulation and visualization to manage and expand their extensive database on firm culture, leadership, and performance diagnostics. The organization sought a tool to integrate data from existing survey platforms like SurveyMonkey, enabling more efficient processes and complex analyses across diagnostics for investment firms. Susan DiBiccari, IT Consultant at Focus, highlighted the tool's drag-and-drop interface for manipulating and combining data into dashboard-ready structures. InetSoft's platform features a powerful data mashup engine that facilitates fast transformation of data from disparate sources, potentially reducing the need for a data warehouse. Additionally, the latest release includes custom data connectors for 73 cloud-based applications, simplifying integrations for business users.

• Data Transformation Tool

  InetSoft's data warehouse reporting tool features a powerful data transformation and mashup tool that enables users to preprocess data by cleaning missing values, transforming variables, and merging sources without building new ETL processes. Data scientists utilize the tool for data exploration through charts, graphs, and pivot tables to identify patterns and relationships, as well as for feature engineering by creating new features from existing data. The tool supports model validation and interpretation by visualizing outputs, metrics, feature importance, and partial dependence plots to evaluate and adjust models. Challenges in data manipulation include data integration complexity from disparate formats, data quality issues affecting accuracy, and handling complex queries and joins that require understanding of data schemas. Additionally, the tool addresses data latency from ETL processes and offers real-time transformation connected to live sources via a drag-and-drop designer and SQL editor for efficient mashups.

• Data Block Technology

  InetSoft's BI platform features patent-pending Data Block™ technology that enables the productive reuse of queries and supports end-user defined data mashups by combining efficient information access with visual analysis technologies. This allows users to perform data manipulation through visual operations in the BI query tool, replacing manual SQL coding with intuitive methods for constructing queries. The platform supports analytics for machine learning models built in Spark and provides access to popular OLAP databases such as Oracle Hyperion Essbase, Oracle OLAP, MS Analysis Server, and SAP NetWeaver BW for data handling. Dashboards and visualizations are created in real-time by dropping data items into visual elements like charts and metrics, revealing intrinsic relationships among the data. Additionally, the tool facilitates data manipulation access for power users in analytic dashboards, enabling self-service customization and interactivity for big data visualization.

• Data Mashup for SQL Reporting

  InetSoft's StyleBI offers advanced SQL reporting services with a professional authoring tool that combines word-processing design with dynamic data manipulation capabilities. Key benefits include the ability to transform and manipulate raw data, embed business logic through scripting, and enable in-report exploration for reshaping information in real time. The patent-pending Data Block™ technology facilitates productive reuse of queries and end-user defined data mashups, allowing efficient integration from multiple sources. This supports self-service analytics in customer service BI solutions, where drag-and-drop interfaces enable real-time data integration and visualization without heavy IT dependency. Overall, these features enhance manageability, agility, and interactive access to data across various service platforms.

• Data Block Technology

  InetSoft's business analyst reporting software features patent-pending Data Block™ technology that enables the productive reuse of queries and supports end-user defined data mashups, facilitating efficient data manipulation for self-service analysis. The software includes a Big Data Manipulation Engine that accesses various sources like spreadsheets and Hadoop, allowing users to change, aggregate, and visualize vast datasets in one centralized place. It provides thorough data analysis reporting capabilities to evaluate and manipulate data, ensuring that insights are clearly articulated through proper reporting mechanisms. InetSoft's visual analysis technologies combined with these manipulation tools empower average business users, IT administrators, and developers to achieve maximum self-service by efficiently accessing and transforming information. Overall, the platform supports multidimensional charting, filtering, and drill-down analysis to handle diverse data sources securely and effectively.

• Data Mashup Tool

  The InetSoft BI tool enables users to create data worksheets for advanced data manipulation, including what-if scenarios and reusable data models that can be shared across dashboards and reports with appropriate permissions. Data mashups are performed by dragging and dropping columns from different data sources or models to define relationships, such as joining order information with returns data based on common fields like order number and product name. The system automatically generates underlying queries, including SQL joins, without requiring users to have technical knowledge, handling aspects like one-to-many relationships and data from multiple databases through a simplified drag-and-drop interface. Users can adjust join properties, such as including all records from one side even if no matches exist in the other, via right-click options and wizard-driven selections, ensuring flexible data integration. These worksheets serve as foundational components for further reporting and dashboard creation, promoting collaborative data manipulation among power users and business experts.

• AbstractTableLens Implementation

  The content discusses manipulating tabular data in reports using InetSoft's Style Intelligence, which provides a simple API for controlling table aspects and convenience classes to minimize coding. The AbstractTableLens class simplifies creating custom table lenses for data sources without adapters by requiring implementation of just three methods: getRowCount(), getColCount(), and getObject(int row, int col), with optional overrides for header row and column counts to handle multi-page or wrapping tables. Users must call the moreRows(0) method before other lens methods to avoid runtime exceptions, and header counts are included in total row and column counts. The AttributeTableLens acts as a wrapper around other table lenses, enabling easy ad hoc settings of visual attributes without tedious implementation, and serves as the base for adapters like Swing JTable and JDBC, as well as table style classes. The DefaultTableLens, derived from AttributeTableLens, offers direct storage and manipulation of table data, allowing users to create tables programmatically, such as by specifying dimensions and setting cell values with setObject().