UseCases/SalesForecast_SAS_Vantage/Sales_Forecasting_SAS_Vantage_PY_SQL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "52b30d72-7e86-4745-b1cb-b01c4eff0444",
   "metadata": {},
   "source": [
    "<header>\n",
    "   <p  style='font-size:36px;font-family:Arial; color:#F0F0F0; background-color: #00233c; padding-left: 20pt; padding-top: 20pt;padding-bottom: 10pt; padding-right: 20pt;'>\n",
    "       Sales Forecasting :- SAS and Vantage Comparison\n",
    "  <br>\n",
    "       <img id=\"teradata-logo\" src=\"https://storage.googleapis.com/clearscape_analytics_demo_data/DEMO_Logo/teradata.svg\" alt=\"Teradata\" style=\"width: 125px; height: auto; margin-top: 20pt;\">\n",
    "    </p>\n",
    "</header>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d8dbcd04-9235-4d08-a266-df244652c356",
   "metadata": {},
   "source": [
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>Introduction</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "This demo walks through how a typical SAS user would use sales data to build a simple sales forecasting model and then will showcase how we can achieve the same using Vantage InDB Analytics</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Customers are finding their analytical environments difficult to manage and are looking for ways to make these environments more streamlined while adapting to more contemporary technologies. Our open source analytical ecosystem can be leveraged to simplify and apply more governance to the data flows in your analytical environment, enabling you to increase efficiency of computation, reduce cost of ownership and take advantage of any analytical tool of choice.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Benefits our customers have received:\n",
    "    <img src = 'images/SASIntro.png' width = '400' align='right' padding='padding:50px'>\n",
    "\n",
    "<li style = 'font-size:16px;font-family:Arial'>For large data extract the time reduced from 16 hours 28 minutes to 28 secs</li>\n",
    "    <br>\n",
    "<li style = 'font-size:16px;font-family:Arial'>Reduced analytic processing from 30 days to a single day.</li>\n",
    "    <br>\n",
    "<li style = 'font-size:16px;font-family:Arial'>Eliminated over 75% of redundant data in SAS environments and Reduced data latency.</li> <br>\n",
    "<li style = 'font-size:16px;font-family:Arial'>Pushed analytic processing into Teradata Database which helped eliminate 6 TB of redundant data and Improved quality of analytics.</li>\n",
    "\n",
    "</p>\n",
    "\n",
    "<br>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> This overview shows how to undertake an analytical model foundation using ClearScape Analytics that uses data from a variety of sources. Teradata Vantage™ enables enterprises to automate and post timely model outputs for use in downstream business processes.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In this demo we will show \n",
    "    <div class=\"alert alert-block alert-warning\">  \n",
    "<p style = 'font-size:16px;font-family:Arial;color:#000000'><i>the SAS code in tan colored box and will not be executed.</i>    \n",
    "</div>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>And follow that with </p> \n",
    "    <code style = 'font-size:16px;'><i>Vantage code that we will run in executable cells</i></code>\n",
    "</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8c67d1a8-01a6-4988-8601-0416386fcc22",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>1. Connect to Vantage</b></p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e8693562-17dd-4cd5-b678-e035f52a4883",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the section, we import the required libraries and set environment variables and environment paths (if required).</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fcb84292-3858-4fc6-a43b-f5d651ebeca2",
   "metadata": {},
   "outputs": [],
   "source": [
    "import warnings\n",
    "warnings.filterwarnings('ignore')\n",
    "import time\n",
    "from teradataml import * \n",
    "\n",
    "import getpass\n",
    "\n",
    "display.max_rows=5"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a4a14c5e-6a6c-4862-b9dc-ea074360759e",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>You will be prompted to provide the password. Enter your password, press the Enter key, then use down arrow to go to next cell. Begin running steps with Shift + Enter keys.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ff384f3a-0402-45a2-9fce-46131ccb7ac7",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../startup.ipynb\n",
    "eng = create_context(host = 'host.docker.internal', username='demo_user', password = password)\n",
    "print(eng)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8d4e68c0-657a-448e-a0f8-0840127cadd6",
   "metadata": {},
   "outputs": [],
   "source": [
    "%%capture\n",
    "execute_sql('''SET query_band='DEMO=Sales_Forecasting_SAS_Vantage_PY_SQL.ipynb;' UPDATE FOR SESSION; ''')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "59d9fbdd-78c1-4208-8328-ab2b1e856fee",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>2. Getting Data for This Demo</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We have provided data for this demo on cloud storage. You can either run the demo using foreign tables to access the data without any storage on your environment or download the data to local storage, which may yield faster execution. Still, there could be considerations of available storage. Two statements are in the following cell, and one of them is commented out. You may switch between the modes by changing the comment string.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9058df61-cb6f-422f-92b6-d49957948b4f",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call get_data('DEMO_SlsForecast_SAS_cloud');\"\n",
    " # Takes about 45 seconds\n",
    "#%run -i ../run_procedure.py \"call get_data('DEMO_SlsForecast_SAS_local');\"\n",
    " # Takes about 70 seconds"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ed3b29f5-f929-4b6a-8367-d9db4f839115",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Next is an optional step – if you want to see status of databases/tables created and space used.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b3e1d9f1-3e71-45d8-b03a-6c91ef991704",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call space_report();\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e85ff555-1abe-4cba-8450-08a1870d1b20",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>3. Explore the dataset</b></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Transfer and explore data in SAS </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>If we were to execute SAS code the first step would be to import all the data from Vantage to SAS. The first data step will create a new dataset named \"sales_temp_data_1\" in the \"work\" library. It will select all the data from the table \"sales_ts_data_1_54M\" located in the \"Teradata\" (we are calling it through our libname connection) teralib.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'> We replicate the same process with the second data step. We fetch all the data from the table \"sales_ts_data_2_54M\" and store it in \"sales_temp_data_2\" in the \"work\" library. After running the second data step we’ll now see store_id, day of sale, transaction_id, product sku id, and transaction quantity and transaction weight within the SAS library.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "755932f9-985a-41df-87b4-b515e621edd0",
   "metadata": {},
   "source": [
    "<code>\n",
    "\n",
    "<div class=\"alert alert-block alert-warning\">  \n",
    "<p style = 'font-size:18px;font-family:Arial;color:#000000'><b>Equivalent SAS Code</b>    \n",
    "<p style = 'font-size:16px;color:#000000'> \n",
    "/* Setting up a libname for the connection with Teradata Database */\n",
    "libname teralib teradata server=barbera user=tahaw pw=tahaw database=tahaw;\n",
    "options SASTRACE=',,,ds' SASTRACELOC=SASLOG nostsuffix;\n",
    "<p style = 'font-size:16px;color:#000000'>\n",
    "/* The first Data step is to fetch all the rows from the Teradata table and create an SAS dataset in the work library */\n",
    "%let start_time = %sysfunc(datetime());\n",
    "data work.sales_temp_data_1;\n",
    "set TERALIB.sales_ts_data_1_54M;\n",
    "run;\n",
    "<p style = 'font-size:16px;color:#000000'>\n",
    "/* The second Data step is to fetch all the rows from the Teradata table for the remaining attributes */\n",
    "data work.sales_temp_data_2;\n",
    "set TERALIB.sales_ts_data_2_54M;\n",
    "run;\n",
    "    </p>\n",
    "</div>\n",
    "</code>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "82f1feb4-03a9-4be3-959b-c8dabf36aa2b",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Explore data in Vantage </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As the data is already in Vantage the data transfer step is <b>NOT</b> required. So, we will explore the data in both the tables</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19399ecf-6736-4de1-8eec-16bd68e2705a",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_qty = DataFrame(in_schema('DEMO_SlsForecast_SAS','Store_sales_Qty'))\n",
    "df_qty"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "058bac14-b998-42f9-80d6-ee777964e7cd",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This data set contain store_id, day of sale, transaction_id, product sku id, and transaction quantity and transaction weight.</p> \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b8fabe90-1af0-41a3-875b-33fed14a0f9c",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_amt = DataFrame(in_schema('DEMO_SlsForecast_SAS','Store_Sales_Amt'))\n",
    "df_amt"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f8b6b727-2afc-4e51-a8ed-df0a1fc289bb",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>This data set contain store_id, day of sale, transaction_id, product sku id and transaction amount.</p> \n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4016eb12-8a99-41d2-88cc-a3e1c77979e7",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>4. Aggregating the Data</b></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Aggregate data in SAS</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As a next step in SAS, we need to prepare the data into a single ADS to forecast sales for this particular store and aggregate the total sales by each store product and transaction id on a particular day.</p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Proc SQL is used in SAS, which uses SAS’s native SQL processing capabilities. Apply the sum on the transaction amount and group by on store_id, day_of_sale, product_sku_id, transaction_id. After it executes, running it will create the dataset sales_aggregated_data_1 in the work library. This new dataset has around 52.5 million records.\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Aggregation is applied on the second data set, sales_temp_2 to do a sum on transaction quantity and weight and group by on store_id, day_of_sale, product_sku_id, transaction_id. After execution, it will create a new dataset named sales_aggregated_data_2 in the work library. This new dataset has around 52.5 million records .</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "51519c26-f19e-438a-a975-675629c8c61f",
   "metadata": {},
   "source": [
    "<code>\n",
    "\n",
    "<div class=\"alert alert-block alert-warning\">  \n",
    "<p style = 'font-size:18px;font-family:Arial;color:#000000'><b>Equivalent SAS Code</b>    \n",
    "<p style = 'font-size:16px;color:#000000'> \n",
    "/* Aggregating the amount to calculate the total sales by each store and product on a particular day */\n",
    "proc sql;\n",
    "  create table work.sales_aggregated_data_1 as\n",
    "  select \n",
    "    store_id, day_of_sale, product_sku_id, transaction_id,\n",
    "    sum(transaction_amount) as total_sales\n",
    "  from work.sales_temp_data_1\n",
    "  group by store_id, day_of_sale, product_sku_id, transaction_id;\n",
    "quit;\n",
    "<p style = 'font-size:16px;color:#000000'> \n",
    "/* Aggregating the weight and quantity to calculate the total weight \n",
    "and total quantity by each store and product on a particular day */\n",
    "proc sql;\n",
    "  create table work.sales_aggregated_data_2 as\n",
    "  select \n",
    "    store_id, day_of_sale, product_sku_id, transaction_id,\n",
    "    sum(transaction_quantity) as total_quantity,\n",
    "    sum(transaction_weight) as total_weight\n",
    "  from work.sales_temp_data_2\n",
    "  group by store_id, day_of_sale, product_sku_id, transaction_id;\n",
    "quit;\n",
    "    </p>\n",
    "    </div>\n",
    "</code>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b0b0a948-3920-493f-92d8-ef8bba173470",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Aggregate data in Vantage </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>First we sum the amount using group by store_id, day_of_sale, product_sku_id, transaction_id.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a45fd027-8b72-43d7-aa42-922f41cd0cf9",
   "metadata": {},
   "outputs": [],
   "source": [
    "query='''create multiset table sales_aggregated_data_amt as\n",
    "(select \n",
    "    store_id, day_of_sale, product_sku_id, transaction_id,\n",
    "    sum(transaction_amount) as total_sales\n",
    "from DEMO_SlsForecast_SAS.Store_Sales_Amt\n",
    "group by store_id, day_of_sale, product_sku_id, transaction_id)with data Primary index(transaction_id);'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('sales_aggregated_data_amt')\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a90c188a-3717-4d2b-8688-9ca290f66d45",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Then, we sum the quantity and weight using group by store_id, day_of_sale, product_sku_id, transaction_id.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "ed612393-f324-42a4-898f-09abce4d9f82",
   "metadata": {},
   "outputs": [],
   "source": [
    "query='''create multiset table sales_aggregated_data_qty as\n",
    "(select \n",
    "    store_id, day_of_sale, product_sku_id, transaction_id,\n",
    "    sum(transaction_quantity) as total_quantity,\n",
    "    sum(transaction_weight) as total_weight\n",
    "from DEMO_SlsForecast_SAS.Store_Sales_Qty\n",
    "group by store_id, day_of_sale, product_sku_id, transaction_id)with data Primary index(transaction_id);'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('sales_aggregated_data_qty')\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e00cb3c8-dc6d-4879-87df-3970525a2cbc",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>5. Merging the Data</b></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Merging data in SAS</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>As a next step, to prepare the data into a single ADS to forecast sales for this particular store, aggregation is done of the total sales by each store product and transaction id on a particular day.\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>These 2 aggregated datasets are merged to have a single table that contains store_id, day_of_sale, product_sku_id, transaction_id, transaction_amount, transaction quantity, and transaction weight. Merge in SAS is used for the two datasets and define the join columns such as store_id, day_of_sale, product_sku_id and transaction_id. After merging the dataset will have all the required columns in one dataset. This dataset contains around 52.5 million rows.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9dad7f7c-14e7-4e17-b234-73c681171613",
   "metadata": {},
   "source": [
    "<code>\n",
    "\n",
    "<div class=\"alert alert-block alert-warning\">  \n",
    "<p style = 'font-size:18px;font-family:Arial;color:#000000'><b>Equivalent SAS Code</b>    \n",
    "<p style = 'font-size:16px;color:#000000'>   \n",
    "/* Vertically merging two datasets and creating another dataset in work library with all the required attributes */\n",
    "data work.merged_sales_data_c;\n",
    "merge  work.sales_aggregated_data_1\n",
    "       work.sales_aggregated_data_2;\n",
    "       by store_id day_of_sale product_sku_id transaction_id;\n",
    "       run;\n",
    "<p style = 'font-size:16px;color:#000000'> \n",
    "/* Aggregating the amount to calculate total sales in a particular day */\n",
    "proc sql;\n",
    "  create table work.aggregated_data as\n",
    "  select \n",
    "    day_of_sale,\n",
    "    sum(total_sales) as total_sales\n",
    "  from work.merged_sales_data_c\n",
    "  group by day_of_sale;\n",
    "    quit;</p>\n",
    "    </div>\n",
    "    </code>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8fd5af0c-f3e8-4076-9f1f-ddeb5ff58fd9",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Merging data in Vantage </b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We merge the 2 datasets in Vantage.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18630196-8a20-4d60-a2fc-4dcf00d01c5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "query='''create MULTISET table merged_sales_data as (\n",
    "      SELECT A.store_id,A.day_of_sale,A.transaction_id,A.product_sku_id,A.total_sales,B.total_quantity,B.total_weight\n",
    "        FROM (SELECT store_id,day_of_sale,product_sku_id,transaction_id,total_sales\n",
    "            FROM sales_aggregated_data_amt ) AS A\n",
    "        INNER JOIN \n",
    "        (SELECT store_id,day_of_sale,product_sku_id,transaction_id,total_quantity,total_weight\n",
    "            FROM sales_aggregated_data_qty ) AS B\n",
    "        ON A.day_of_sale=B.day_of_sale AND A.product_sku_id=B.product_sku_id AND A.store_id=B.store_id \n",
    "        AND A.transaction_id=B.transaction_id) WITH DATA PRIMARY INDEX(transaction_id);'''\n",
    "\n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('merged_sales_data')\n",
    "    execute_sql(query)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "804af1e4-946f-487e-88d8-988a540a5916",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We do a final aggregation to get the total sales by day in Vantage.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "51af464e-f4dd-44c8-92dd-41c4b66d88fb",
   "metadata": {},
   "outputs": [],
   "source": [
    "query='''create multiset table aggregated_sales_td as\n",
    "  (select \n",
    "    rank() over(partition by day_of_sale order by day_of_sale) as SeriesId, ---Series Id created for using in ARIMA\n",
    "    cast(day_of_sale as timestamp(0)) as day_of_sale,\n",
    "    sum(total_sales) as total_sales\n",
    "  from merged_sales_data \n",
    "  group by day_of_sale) with data;'''\n",
    "    \n",
    "try:\n",
    "    execute_sql(query)\n",
    "except:\n",
    "    db_drop_table('aggregated_sales_td')\n",
    "    execute_sql(query)    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "fe3a0381-b466-449a-b886-da5111faea66",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_agg_sales = DataFrame('aggregated_sales_td')\n",
    "df_agg_sales"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b91596b4-27cb-4fdb-9cc5-1f8dac55a668",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We plot the total sales by day of sales to check the series data.</p> "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9dcdb4df-03ae-437e-b997-18821f1df9c2",
   "metadata": {},
   "outputs": [],
   "source": [
    "figure = Figure(width=1000, height=400, heading=\"Daily Sales\")\n",
    "\n",
    "plot = df_agg_sales.plot(\n",
    "    x=df_agg_sales.day_of_sale,\n",
    "    y=df_agg_sales.total_sales,\n",
    "    xlabel='Day of Sale',\n",
    "    ylabel='Total Sales',\n",
    "    color='carolina blue',\n",
    "    figure=figure\n",
    ")\n",
    "\n",
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "72976837-1678-4978-a8cb-291dca976b90",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the plot we can see that the Sales vary from September 2019 till November 2019 than it is flat till November 2020. The sales again vary from November 2020 till January 2021. After that there is a steep drop in sales and it remains below 1000 from January 2021 and March 2021.</p> "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ef1f649f-f51f-4cdd-9d67-2141369286e6",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>6. Using ARIMA (AutoRegressive Integrated Moving Average) model to forecast Sales</b></p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "ARIMA functions on VANTAGE run in the following order:\n",
    "<br>\n",
    "<li style = 'font-size:16px;font-family:Arial'> Run the ArimaEstimate() function to get the coefficients for the ARIMA model.\n",
    "<li style = 'font-size:16px;font-family:Arial'> [Optional] Run ArimaValidate() function to validate the 'goodness of fit' of the ARIMA model, when \"fit_percentage\" argument value is not 100 in ArimaEstimate() function.\n",
    "<li style = 'font-size:16px;font-family:Arial'>Run the ArimaForecast() function with input from step 1 or step 2 to forecast the future periods beyond the last observed period.</li>\n",
    "</p>\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9f685b90-9a0b-4afb-b603-f423c699b388",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.1 Estimation step</b></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Estimation step in SAS</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The final step is to fit the Arima model. In the first PROC ARIMA block, it identifies the best ARIMA model for the total_sales variable with a maximum lag of 30 using the identify statement and estimates the model with one seasonal difference (q=1). The parameter estimates are saved in the arima_params dataset in the work library.</p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d7ff25ef-e987-44d1-bbcd-a29d5ffe82f0",
   "metadata": {},
   "source": [
    "<code>\n",
    "\n",
    "<div class=\"alert alert-block alert-warning\">  \n",
    "<p style = 'font-size:18px;font-family:Arial;color:#000000'><b>Equivalent SAS Code</b>    \n",
    "<p style = 'font-size:16px;color:#000000'> \n",
    "/* Fit ARIMA model and calculating its coefficients */\n",
    "proc arima data=work.aggregated_data;\n",
    "  identify var=total_sales(1) nlag=30;\n",
    "  estimate q=1 outest=arima_params;\n",
    "    run;</p>\n",
    "    </div>\n",
    "</code>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f3d7b3e-12e4-488e-aff8-e347dd05b800",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Estimation step in Vantage</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The ArimaEstimate() function estimates the coefficients corresponding to an ARIMA (AutoRegressive Integrated Moving Average) model, and to fit a series with an existing ARIMA model. The function can also provide the \"goodness of fit\" and the residuals of the fitting operation. The function generates model layer used as input for the ArimaValidate() and ArimaForecast() functions. This function is for univariate series.</p>\n",
    "<br>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here, the previously estimated parameters, namely P (Auto-Regressive lags), d (differencing order), and Q (Moving Average lags), are required to be passed into the MODEL_ORDER function. For example, the specific values used here are MODEL_ORDER(2, 1, 8).\n",
    "</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Furthermore, the fit percentage is determined to be 100. This fit percentage indicates that the ARIMA model is trained using 100% of the available data.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f6a410cf-c533-47b2-a7e8-c55d37ed293f",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_series_df = TDSeries(data=df_agg_sales,\n",
    "                              id=\"SeriesId\",\n",
    "                              row_index=(\"day_of_sale\"),\n",
    "                              row_index_style= \"TIMECODE\",\n",
    "                              payload_field=\"total_sales\",\n",
    "                              payload_content=\"REAL\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "c0d58e27-8f80-44a2-9444-6528617c5570",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import ArimaEstimate\n",
    "# Execute ArimaEstimate function.\n",
    "arima_est_out = ArimaEstimate(data1=data_series_df,\n",
    "                            nonseasonal_model_order=[2,1,8],\n",
    "                            constant=False,\n",
    "                            algorithm=\"CSS_MLE\",\n",
    "                            coeff_stats=True,\n",
    "                            fit_metrics=True,\n",
    "                            residuals=True,\n",
    "                            fit_percentage=100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "f8e3a0bf-21e2-4bd9-999c-0d04e58a4a7b",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "est_result=arima_est_out.fitresiduals\n",
    "est_result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a5778fb4-ce9b-43a6-987d-6b705d2d94da",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We plot the Actual Value of Sales vs the Calculated Value of the ArimaEstimate function. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "0e26b51d-063a-409a-b65d-ca7ea0449447",
   "metadata": {},
   "outputs": [],
   "source": [
    "figure = Figure(width=1000, height=500, heading=\"Comparison of Actual vs Estimated Sales\")\n",
    "\n",
    "plot = est_result.plot(\n",
    "    x=est_result.ROW_I,\n",
    "    y=[est_result.ACTUAL_VALUE, est_result.CALC_VALUE],\n",
    "    xlabel='Sales Date',\n",
    "    ylabel='Total Sales',\n",
    "    color=['carolina blue','orange'],\n",
    "    figure=figure,\n",
    "    grid_linestyle='-',\n",
    "    grid_linewidth=0.5,\n",
    "    legend=['Actual Value','Calculated Value'],\n",
    "    legend_style='upper right',\n",
    ")\n",
    "\n",
    "plot.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e2b9fefa-21b5-4ae4-81fc-b48ec87e084c",
   "metadata": {},
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>6.2 Forecast step</b></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Forecast step in SAS</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>In the second PROC ARIMA block, it identifies and estimates the same ARIMA model as in the first block but additionally generates forecasts for the next 30 time periods (lead=30) and stores the forecasted values in the forecasted_sales dataset in the work library. Now if run this we can see the results with the log indicating it took around 2 seconds to fit the Arima model on the aggregated data. Once the small aggregated dataset is available for the SAS procedure it executes relatively fast.<p>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4f8f9015-a7c7-43ba-b4f7-79d5fafc3c1b",
   "metadata": {},
   "source": [
    "<code>\n",
    "\n",
    "<div class=\"alert alert-block alert-warning\">  \n",
    "<p style = 'font-size:18px;font-family:Arial;color:#000000'><b>Equivalent SAS Code</b>    \n",
    "<p style = 'font-size:16px;color:#000000'> \n",
    "/* Forecasting future 30 values */\n",
    "proc arima data=work.aggregated_data;\n",
    "  identify var=total_sales(1) nlag=30;\n",
    "  estimate q=1 outest=arima_params;\n",
    "  forecast lead=30 out=forecasted_sales;\n",
    "    run;</p>\n",
    "    </div>    \n",
    "</code>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c3bf76ac-5975-44fa-a561-90e1aaeff070",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Forecast Step in Vantage</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The ArimaForecast() function is used to forecast a user-defined number of periods based on models fitted from the ArimaEstimate() function.</p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Here in the next cell, we use the estimated model to forecast the sales for the subsequent 30 periods, i.e., next 30 days.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b165d17d-5062-468f-945b-22a3484c9c68",
   "metadata": {},
   "outputs": [],
   "source": [
    "from teradataml import ArimaForecast\n",
    "\n",
    "# Create teradataml TDAnalyticResult object over the result attribute of 'arima_estimate_op'\n",
    "data_art_df = TDAnalyticResult(data=arima_est_out.result)\n",
    " \n",
    "arima_forcast_out = ArimaForecast(data=data_art_df, forecast_periods=30)\n",
    "forecast_result=arima_forcast_out.result\n",
    "forecast_result"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bccf436c-d8b8-4ffe-8833-a02375862950",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>We plot the Forecasted Value of Sales for the defined number of periods. </p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "faf5494e-f700-4b0b-a994-7df2b1fd14a1",
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "# import matplotlib.patches as patches\n",
    "import seaborn as sns\n",
    "forecast_result_plot=forecast_result.to_pandas().reset_index()\n",
    "plt.figure(figsize=(20,8))\n",
    "sns.lineplot(data= forecast_result_plot ,x=\"ROW_I\",y=\"FORECAST_VALUE\",ci=None)\n",
    "plt.fill_between(forecast_result_plot.ROW_I, forecast_result_plot.LO_80, forecast_result_plot.HI_80, color='lightblue', alpha=0.5)\n",
    "# plt.legend(['Actual Value', 'Predicted Value'], loc='best', fontsize=16)\n",
    "plt.title('Forecast of Sales ', fontsize=20)\n",
    "plt.ylabel('Sales', fontsize=16)\n",
    "plt.xlabel('Sales Date', fontsize=16)\n",
    "plt.show()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "de2bceea-7ef0-4baf-a80f-913883b3e4f7",
   "metadata": {},
   "source": [
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The dark blue line is the Forecasted Sales for the next 30 days, and the blue lines are the upper and lower confidence interval with an 80% confidence level. As seen in the original Sales graph, the sales have dropped below 1000 for the latest period. Similar sales can be observed in the forecast period, which varies around 1000.</p>\n",
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>7. Conclusion:</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>\n",
    "Moving large amounts of data between Vantage and SAS is usually the main culprit for slow running jobs and complex analytics pipelines amplifying governance issues from orphaned and exposed data in SAS environments. By executing the complete flow inside Vantage using ClearScape Analytics we are reducing the complexity and achieving greater efficiency. </p>\n",
    "\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>One of our customers cited the following benefits:</p>\n",
    "<table style = 'border:1px solid black;width:100%;'>\n",
    "         <tr style = 'font-size:16px;font-family:Arial;border:1px solid black;color:#00233C'>\n",
    "             <th style = 'border:1px solid black;'><b>Past Approach using SAS Alone</b></th>\n",
    "             <th style = 'border:1px solid black;'><b>In-Database Approach</b></th>             \n",
    "         </tr>\n",
    "         <tr style = 'font-size:14px;font-family:Arial;border:1px solid black;color:#00233C'>\n",
    "            <td style = 'border:1px solid black;'>Daily process begins with flat file creation at 6:30am – SLA delivered at ~9:30am.</td>\n",
    "            <td style = 'border:1px solid black;'>Daily process begins at 4:00am with EDW load.</td>\n",
    "         </tr>\n",
    "         <tr style = 'font-size:14px;font-family:Arial;border:1px solid black;color:#00233C'>\n",
    "            <td style = 'border:1px solid black;'>File transferred to SQL Server,<b> limited to ~350K customer records based on  specific criteria.</b></td> \n",
    "            <td style = 'border:1px solid black;'>All operational data loaded directly to EDW.  No flat file or intermediate processing is needed.</td>\n",
    "         </tr>\n",
    "         <tr style = 'font-size:14px;font-family:Arial;border:1px solid black;color:#00233C'>\n",
    "             <td style = 'border:1px solid black;'><b>300 step process</b> to support data mining life cycle.<b>30 MINUTES TO SCORE ~350k customers</b></td> \n",
    "             <td style = 'border:1px solid black;'><b>10 step process</b> - Scoring and customer selection done in-database against  ALL customer rows <b>4 MINUTES TO SCORE ~40M customers</b></td>\n",
    "         </tr> \n",
    "         <tr style = 'font-size:14px;font-family:Arial;border:1px solid black;color:#00233C'>\n",
    "             <td style = 'border:1px solid black;'><b>Runs in ~ 3 HOURS</b></td> \n",
    "             <td style = 'border:1px solid black;'><b>Runs in 12 MINUTES with 114x # of customers.</b></td>\n",
    "         </tr>\n",
    "         <tr style = 'font-size:14px;font-family:Arial;border:1px solid black;color:#00233C'>\n",
    "            <td style = 'border:1px solid black;'><b>Cost for data mart infrastructure and support, plus lost opportunity to process all customers</b></td> \n",
    "            <td style = 'border:1px solid black;'><b>Uses available capacity outside of work hours with far less complexity and support cost. By processing all 40M customers vs 350K subset, increased collections $1M - $3M/month.</b></td>\n",
    "         </tr> \n",
    "         <tr>\n",
    "           \n",
    "</table>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4eeec79c-e27e-4042-8207-fcf25aedbcfb",
   "metadata": {},
   "source": [
    "<hr style=\"height:2px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:20px;font-family:Arial;color:#00233C'><b>8. Cleanup</b></p>\n",
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Work Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>Cleanup work tables to prevent errors next time. This section drops all the tables created during the demonstration.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "63e2ae0b-ab08-44c8-bb3d-bf6485469dce",
   "metadata": {},
   "outputs": [],
   "source": [
    "tables = ['sales_aggregated_data_amt','sales_aggregated_data_qty','merged_sales_data','aggregated_sales_td']\n",
    "\n",
    "# Loop through the list of tables and execute the drop table command for each table\n",
    "for table in tables:\n",
    "    try:\n",
    "        db_drop_table(table_name=table)\n",
    "    except:\n",
    "        pass\n",
    "      "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "e727ed04-1bd5-4a4d-bb7d-96e0245b95d5",
   "metadata": {},
   "source": [
    "<p style = 'font-size:18px;font-family:Arial;color:#00233C'><b>Databases and Tables</b></p>\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'>The following code will clean up tables and databases created above.</p>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "63f3aee2-fd81-4c9e-8910-3e211809aef1",
   "metadata": {},
   "outputs": [],
   "source": [
    "%run -i ../run_procedure.py \"call remove_data('DEMO_SlsForecast_SAS');\" \n",
    "#Takes 45 seconds"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2fd432ea-f220-4339-86f8-3908a21fe7a2",
   "metadata": {},
   "outputs": [],
   "source": [
    "remove_context()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "164a4006-9508-4540-91b0-78dec15ff293",
   "metadata": {
    "tags": []
   },
   "source": [
    "<hr style=\"height:1px;border:none;background-color:#00233C;\">\n",
    "<p style = 'font-size:16px;font-family:Arial;color:#00233C'><b>Links:</b></p>\n",
    "<ul style = 'font-size:16px;font-family:Arial'>\n",
    "    <li>UAF(Unbounded Array Framework) Documentation: <a href = 'https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Teradata-VantageTM-Unbounded-Array-Framework-Time-Series-Reference-17.20/Unbounded-Array-Framework'>https://docs.teradata.com/r/Enterprise_IntelliFlex_VMware/Teradata-VantageTM-Unbounded-Array-Framework-Time-Series-Reference-17.20/Unbounded-Array-Framework</a></li>\n",
    "</ul>"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b8bb040b-9ae0-4310-8605-40d8f01b3975",
   "metadata": {},
   "source": [
    "<footer style=\"padding-bottom:35px; background:#f9f9f9; border-bottom:3px solid #00233C\">\n",
    "    <div style=\"float:left;margin-top:14px\">ClearScape Analytics™</div>\n",
    "    <div style=\"float:right;\">\n",
    "        <div style=\"float:left; margin-top:14px\">\n",
    "            Copyright © Teradata Corporation - 2023,2024. All Rights Reserved\n",
    "        </div>\n",
    "    </div>\n",
    "</footer>"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}