Configuring Materialized Report Views

Users of InetSoft's reporting software can easily configure the materialized report views in order to allocate resources for high level report performance. View the information below to learn more about the Style Intelligence solution.

To configure the server to use distributed materialized views, and to configure the individual nodes, follow the steps below:

  1. Shut down the Style Intelligence server.
  2. Add the following properties to the sree.properties file (in the SREE Home directory) on the Style Intelligence server.

    3. comm.this.host={servername} comm.port=6385 comm.{servername}.ips={server IP address} fs.server={servername}

    Replace {servername} with the Style Intelligence server name. Replace {server IP address} with the Style Intelligence server IP number.

For the first data node machine (node 1), configure the node by fol­lowing the steps below:

  1. Create a new directory on node 1 to contain the Style Intelligence jar files (for example, “C:\node1\jar”).
  2. Copy the Style Intelligence jar files into the new jar file directory: “etools.jar” and “bisuite_pro.jar” (or “visual_pro.jar”).
  3. Create a new SREE Home directory on node 1 to contain the materialized view data and configuration information (for example, “C:\node1\sreehome”).
  4. Open a command window on the data node machine, and navigate to the “node1” directory. Then type the following command:

    5. java -Dsree.home=./sreehome -classpath ./jar/bisuite_pro.jar;./jar/etools.jar;. inetsoft.report.composition.mv.dgui.DistributePropertyDialog

    This opens the 'Required Property' dialog box.

  5. In the 'Required Property' dialog box, enter the {nodename} and {node IP address} in the 'Host Name' and 'Host IP' fields, respectively. Enter the {servername} and {server IP address} in the 'Server Name' and 'Server IP' fields, respectively. Enter the “comm.port” value (from sree.properties above) into the 'Port' field.
  6. Click 'OK' to close the 'Required Property' dialog box. This populates the 'Data Node Configuration' dialog box. (Leave this dialog box open.)
  7. Repeat the above steps for each data node.
  8. Start up the Style Intelligence server, and open Enterprise Manager.

For the first data node machine (node 1), start the node by following the steps below:

  1. Click the 'Test' button in the 'Data Node Configuration' dialog box. A 'Test successful' response indicates that the data node is able to communicate with the server.
  2. If the test was successful, click 'Connect' in the 'Data Node Configuration' dialog box.
  3. Click the 'Close' button (red X) to exit the 'Data Node Configuration' dialog box. This creates an sree.properties file in the data node's SREE Home directory.

    4. The JVM heap size should be scaled according to the data that you expect the node to process.

  4. In the command window, navigate to the “node1” directory. Then start node 1 by typing the following command:
    5. java -Xmx256M -Dsree.home=./sreehome -classpath ./jar/bisuite_pro.jar;./jar/etools.jar;. inetsoft.report.composition.mv.dgui.StartDataNode

  5. Repeat the above steps for each data node.
  6. In Enterprise Manager on the server, open the 'Repository' > 'Dis­tributed Configuration' page under the Server tab. Verify that the nodes you have started are shown with “alive” status.
{{> ad4}}

If you are using a clustered environment for report generation, and wish to use a distributed materialized view for Viewsheets, follow the steps below:

  1. Prepare the following reporting cluster servers:
    1. A proxy server (proxy1)
    2. A slave server (slave1),
  2. Prepare the following distributed materialized view servers:
    1. A server node (server1)
    2. A data node (data1).

    This allows slave1 and server1 to share the same MV definition and security settings, so that a MV query created by slave1 can be executed on server1.

  3. Set proxy1, slave1, and server1 to share the same SREE Home directory.
  4. In the sree.properties file in the shared SREE Home directory, make the following entries:

    5. fs.server=server1 cluster.servers.hosts=slave1 mv.repository.servlet=http://server1/Viewer

  5. Start the following servers: proxy1, slave1, and server1.

    6. If the fs.nodes property in the sree.properties file in the shared SREE Home directory designates server1, remove that entry.

  6. Start the data1server, and add it to the distributed system.

    7. Do not use the Enterprise Manager on proxy1 or slave1 to create the materialized views manually. If you are scheduling materialized view generation, you can use Enterprise Manager on any server.

  7. Create the materialized views on server1 by using Enterprise Manager on server1.

With these settings in place, Viewsheet that use materialized views will execute on server1.

What Is a Materialized View?

A materialized view is a database object that contains the results of a query, effectively storing a snapshot of data that can be used for faster query performance. Unlike a regular view, which is a virtual table generated dynamically from the underlying tables every time it is accessed, a materialized view stores the query result physically, allowing for quicker access, particularly when dealing with complex queries or large datasets.

Key Characteristics of a Materialized View

  1. Physical Storage:
    • The result of the query that defines the materialized view is stored on disk, making data retrieval faster since it avoids recalculating the query each time.
  2. Refresh Mechanism:
    • Materialized views can be refreshed periodically or on-demand to reflect changes in the underlying tables. This ensures that the data remains up-to-date, though it might not always be as current as data accessed directly from the base tables.
    • Refresh Methods:
      • Complete Refresh: The entire view is recomputed and replaced with the new data.
      • Incremental (Fast) Refresh: Only the changes since the last refresh are applied, making it quicker but more complex to manage.
  3. Use Cases:
    • Performance Optimization: Materialized views are often used to speed up complex queries, particularly those involving joins, aggregations, and calculations.
    • Data Warehousing: They are commonly employed in data warehousing environments where the same complex queries are executed frequently.
    • Reporting: Materialized views can be used in reporting applications where data consistency is less critical, and query speed is a priority.
  4. Consistency vs. Performance Trade-off:
    • While materialized views can significantly improve performance, they do come with a trade-off between data consistency and speed. Depending on the refresh strategy, there might be a lag between the actual data in the base tables and what is seen in the materialized view.

How a Materialized View Works

  • Creation:
    • A materialized view is created using a CREATE MATERIALIZED VIEW statement, which defines the query whose results will be stored.
    • Example: 
      sql
                
      Copy code
      
                
      CREATE MATERIALIZED VIEW sales_summary AS  SELECT region, SUM(sales) AS total_sales  FROM sales  GROUP BY region;
  • Querying:
    • Once created, you can query the materialized view just like a regular table, benefiting from the precomputed results.
  • Refreshing:
    • The materialized view can be refreshed using a REFRESH MATERIALIZED VIEW command, which can be scheduled automatically or triggered manually.
    • Example: 
      sql
                
      Copy code
      
                
      REFRESH MATERIALIZED VIEW sales_summary;
  • Dependency on Base Tables:
    • Materialized views depend on the underlying base tables. If the base tables are modified, the materialized view might become outdated until it is refreshed.

Advantages of Materialized Views

  1. Improved Performance:
    • Reduces the need for recalculating complex queries, significantly speeding up query execution times.
  2. Reduced Database Load:
    • By storing precomputed results, it reduces the workload on the database, as it doesn't need to perform expensive calculations repeatedly.
  3. Efficiency in Data Warehousing:
    • Particularly useful in environments where large volumes of data are processed, and the same queries are run frequently.

Disadvantages of Materialized Views

  1. Maintenance Overhead:
    • Requires management of the refresh process to ensure data remains up-to-date.
  2. Storage Requirements:
    • Consumes additional disk space since the result set of the query is stored physically.
  3. Potential for Stale Data:
    • Depending on the refresh strategy, there might be a delay in reflecting the most current data, leading to potential discrepancies.
Previous: Distributing Materialized Report Views