Connecting Materialized Views in Bold BI - MSSQL, PostgreSQL, Oracle and Google Big Query

You can connect to indexed views in SQL Server and Materialized view in PostgreSQL and Oracle database in Bold BI

MS SQL

Creating an indexed view in SQL Server:

    CREATE VIEW customer_order_totals
    WITH SCHEMABINDING
    AS
        SELECT
            CustomerID,
            SUM(OrderID) AS total_order_amount,
            COUNT_BIG(*) AS count_big
        FROM
            dbo.Orders
        GROUP BY
            CustomerID;
    GO
    
    CREATE UNIQUE CLUSTERED INDEX IX_CustomerOrderTotals
    ON customer_order_totals (CustomerID);

Explanation:

• WITH SCHEMABINDING: This option binds the view to the schema of the underlying tables. It ensures that the underlying tables cannot be modified in a way that would affect the view’s definition or its ability to be indexed.
• CREATE UNIQUE CLUSTERED INDEX: This statement creates a clustered index on the view. Indexed views in SQL Server require a unique clustered index to be defined. In this example, we’re creating a clustered index on the CustomerID column to optimize queries that join or filter by CustomerID.

Connecting in Bold BI:

Indexed views in MS SQL can be found under Views in Bold BI.

PostgreSQL:

Creating a Materialized view in PostgreSQL:

    **-- Create a materialized view to join the employees and departments tables**
    CREATE MATERIALIZED VIEW employee_department_info AS
    SELECT 
        e.employee_id,
        e.first_name,
        e.last_name,
        d.department_name
    FROM
        emp e
    JOIN 
        departments d ON e.department_id = d.department_id;
    
   ** -- Refresh the materialized view to populate it with current data**
    REFRESH MATERIALIZED VIEW employee_department_info;

Explanation:

This materialized view will store the results of the join query between the employees and departments tables, which can be refreshed whenever necessary to update the data. Here, the materialized views store data physically, so they might require more storage space and incur overhead when refreshing

Connecting in Bold BI:

Materialized view can be found under Views in Bold BI.

Oracle:

Creating a Materialized view in Oracle:

    CREATE MATERIALIZED VIEW "MATERIALVIEWSAMPLE"
    AS
    SELECT
        "EMPLOYEES_V"."JOB" AS "JOB",
        "EMPLOYEES_V"."MANAGER_ID" AS "MANAGER_ID",
        "EMPLOYEES_V"."COMMISSION" AS "COMMISSION",
        "EMPLOYEES_V"."EMPLOYEE_NAME" AS "EMPLOYEE_NAME",
        CAST("EMPLOYEES_V"."HIREDATE" AS DATE) AS "HIREDATE",
        "EMPLOYEES_V"."SALARY" AS "SALARY",
        "EMPLOYEES_V"."DEPARTMENT_ID" AS "DEPARTMENT_ID",
        "EMPLOYEES_V"."EMPLOYEE_ID" AS "EMPLOYEE_ID"
    FROM "SYSTEM"."EMPLOYEES_V" "EMPLOYEES_V";
    
    **-- Optional: Create a unique index on the materialized view to improve refresh performance**
    CREATE UNIQUE INDEX "MV_EMPLOYEES_ID" ON "MATERIALVIEWSAMPLE"("EMPLOYEE_ID");
    
    **-- Optional: Create a materialized view log for fast refresh**
    CREATE MATERIALIZED VIEW LOG ON "EMPLOYEES_V"
    WITH ROWID, SEQUENCE ("EMPLOYEE_ID", "HIREDATE", "SALARY", "DEPARTMENT_ID")
    INCLUDING NEW VALUES;
    
   ** -- Optional: Schedule a refresh for the materialized view (you can adjust the refresh **options as needed)
    EXEC DBMS_MVIEW.REFRESH('MATERIALVIEWSAMPLE', 'C', '', TRUE, FALSE, 0);
    
   ** -- Optional: Grant necessary privileges to users or roles**
    GRANT SELECT ON "MATERIALVIEWSAMPLE" TO YOUR_USER_OR_ROLE;

Explanation:

The Materialized view named “MATERIALVIEWSAMPLE” based on the “EMPLOYEES_V” view. It selects specific columns from “EMPLOYEES_V” and casts the “HIREDATE” column to a DATE type. Additionally, it includes optional steps to create a unique index on the materialized view, create a materialized view log on the source table for fast refresh, schedule a refresh for the materialized view, and grant necessary privileges for access. These optional steps aim to improve performance and maintain data consistency.

Connecting in Bold BI:

Materialized view can be found under Tables in Bold BI.

Google Big Query:

Creating an Materialized view in Google Big Query:

Example 1:

CREATE MATERIALIZED VIEW sample.my_view AS
SELECT * FROM `sample.employee_data`;

Example 2:

CREATE MATERIALIZED VIEW sample.my_mat_view
AS
SELECT SUM(salary) AS total_salary
FROM `sample.employee_data`;

Explanation:

Materialized view named my_view in the sample schema. It stores the exact data from the employee_data table at the time of creation, enhancing query performance by precomputing the results.

Connecting in Bold BI:

Materialized views in Google Big Query can be found under Tables in Bold BI.

Additional References

• PostgreSQL Materialized Views
• MySQL Materialized Views
• SQL Server Indexed Views
• Google BigQuery Materialized Views