How to create Views on Amorphic?

info

• Follow the steps mentioned below.
• Total time taken for this task: 10 Minutes.
• Pre-requisites: User registration is completed, logged in to Amorphic and role switched

Create a View

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
Auto Refresh: Yes
Keywords:
Data Classifications:
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_ankamv 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_ankamv r_predict

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.

• There are two types of views. Standard and Materialized.
• When materialized view is selected, refresh can be manual or auto.
• These views can be used for creating dashboards.

tip

Try to create another view with auto-refresh as 'No' and try to manually refresh it.