Create Materialized View

PAT

info

Follow the steps mentioned below.
Total time taken for this task: 5 Minutes.
Pre-requisites: Setup Forecasting Job

A materialized view contains a precomputed result set, based on an SQL query over one or more base tables. You can issue SELECT statements to query a materialized view, in the same way that you can query other tables or views in the database. When you create a materialized view, Amazon Redshift runs the user-specified SQL statement to gather the data from the base table or tables and stores the result set.

To create a materialized view follow the below steps:

Goto home -> Datasets -> Views -> ➕ Create View
Enter the required information and click submit.
Make sure to change <your-userid> before clicking submit.

View Name: mv_retail_sales_<your_userid>
Description: Union of sales table and sales prediction
View Type: Materialized
Domain: workshop
Target Location: Redshift
Keywords:
Data Classifications:
Auto Refresh: Yes
Sql To Create View:  Copy below SQL

CREATE MATERIALIZED VIEW workshop.mv_retail_sales_<your_userid>
as
select
 Month_Id as sales_Month_Id
,Store_No as sales_Store_No
,Store_Name as sales_Store_Name
,Store_Address as sales_Store_Address
,Region as sales_Region
,food_or_non_food as sales_food_or_non_food
,Main_Category_No as sales_Main_Category_No
,Main_Category_Name as sales_Main_Category_Name
,Sales_in_NSP as sales_Sales_in_NSP
,COGS_in_NNBP as sales_COGS_in_NNBP
,Gross_Profit as sales_Gross_Profit
,split_part(Gross_Profit_pct, '%',1) as sales_Gross_Profit_pct
,TO_DATE(sales.timestamp,'YYYY-MM-DD HH24:MI:SS') as sales_date
,0 as p10
,0 as p90
from workshop.retail_sales_transformed_<your-userid> sales

union all

Select
0 as sales_Month_Id
,cast(Store_No as integer) as sales_Store_No
,Store_Name as sales_Store_Name
,NULL as sales_Store_Address
,Region as sales_Region
,food_or_non_food as sales_food_or_non_food
,cast(Main_Category_No as integer) as sales_Main_Category_No
,Main_Category_Name as sales_Main_Category_Name
,cast(p50 as decimal(10,2)) as sales_Sales_in_NSP
,cast(COGS_in_NNBP as decimal (10,2))as sales_COGS_in_NNBP
,0.0 as sales_Gross_Profit
,split_part(Gross_Profit_pct, '%',1) as sales_Gross_Profit_pct
,to_date(split_part(r_predict.date, 'T',1),'YYYY-MM-DD HH24:MI:SS')  as sales_date
,cast(p10 as decimal(10,2))
,cast(p90 as decimal(10,2))
from workshop.retail_item_per_store_forecast_<your_userid> r_predict

Amorphic logo

Once the view is created, `View Status` must change to `create_complete`. In case of errors, check error message and correct SQL by editing ✏️ the view . If SQL cannot be updated, delete view and re-create it with updated SQL.

Congratulations!!!

You are now ready to create a dashboard. Proceed to 'Create Quicksight Dashboard' task.

info

Once the view is created, View Status must change to create_complete. In case of errors, check error message and correct SQL by editing ✏️ the view . If SQL cannot be updated, delete view and re-create it with updated SQL.​

Congratulations!!!

Once the view is created, `View Status` must change to `create_complete`. In case of errors, check error message and correct SQL by editing ✏️ the view . If SQL cannot be updated, delete view and re-create it with updated SQL.