SunDucks coffee has $1000$ coffee shops in multiple states. They sell coffee, snacks, and related products. On an average, each store has $1500$ orders per day, and each order has $2$ order line items per order. Their current dimensional model for Sales process is shown below. $($Dimensional attributes not shown$).$Sunducks Coffee has installed an IoT Environment scanning system to collect data on store and surrounding environment and to explore the impact of the environment on sales. The IoT Environment scanning system collects the following data and the data is recorded once per second, $24$hrs a day. The management wants this IoT data to be included in the data warehouse so the impact of the environment variables on sales can be analyzed.

$1.$ Queue Length $($Average que length at register, range $1-50)$

$2.$ Indoor Temperature $($Indoor temperature, range $-50\mathrm{.}00$ to $150\mathrm{.}00$ F$,2$ decimal point precision$)$

$3.$ Outdoor Temperature $($Outside temperature, range $-50\mathrm{.}00$ to $150\mathrm{.}00$ F$,2$ decimal point precision$)$

$4.$ Time Of Day $($HH:MM:SS$)$

$5.$ Humidity $($Relative$,$ percentage, $0\mathrm{.}00$ to $100\mathrm{.}00,2$ decimal point precision$)$

$6.$ Cloud Cover $($Percentage$,0\mathrm{.}00$ to $100\mathrm{.}00,2$ decimal point precision$)$

$7.$ Weather condition $(10$ categories$)$

$8.$ Pollen $(1$ to $10$ rating$)$

$9.$ Traffic Conditions $($Score varying from $-10,000$ to $+10,000)$

$10.$ Seating Availability $($Percentage $0$ to $100\%,0$ decimal point precision$)$

$11.$ Foot Traffic Score $($Score $0$ to $10000)$

$12.$ Indoor Noise level $(0\mathrm{.}00$ to $140\mathrm{.}00,2$ decimal point precision$)$

$13.$ Outdoor Noise level $(0\mathrm{.}00$ to $140\mathrm{.}00,2$ decimal point precision$)$

Describe how this data can be included in the data warehouse for meaningful analysis. Include in your solution the schema for the appropriate dimension and$/$or fact table$($s$)$ that model the above data along with the attributes, data type and range $($acceptable values$)$ of the attribute. Include in your solution, the expected size of the fact and$/$or dimension tables for $1$ year of transactions.

Benchmark testing of the database infrastructure for join performance of dimension tables with the fact table shows that response for tables size under $1000$ are very quick. There is a linear increase in response time from $100$ to $10,000,$ but still within acceptable limits$.$ Join performance degrades substantially beyond $10,000$ rows.

$2.$ Schema $($New Fact AND$/$OR Dimension tables$)$ for capturing IoT data.

$3.$ Attribute descriptions including data type and range.

$4.$ Estimate of new fact and dimension table$($s$)$ size for $365$ days transactions. Show calculations. Include calculations to show number of rows and size per row.

$5.$ Describe two sample reports $($Specify Columns, Rows, Filters and Cell values$)$ that can be created using the new schema that addresses the following questions.

a$.$ How do sales vary across stores with varying cloud cover during the hours of $8$:$00$am $4$:$00$pm and Months of May $$ Sep.

b$.$ How do sales vary across products with varying temperatures during the hours of $12$:$00$pm $5$:$00$pm during the months of March $$ Sep.

$6.$ Raw data is the data that comes from the IoT devices and gets staged in the staging area as is$.$ Show the estimates for size of raw IoT data for $1$ year.