Designing and Implementing Big Data Analytics Solutions

Azure Data Factory can be used to orchestrate the execution of stored procedures. This allows more complex pipelines to be created and extends Azure Data
Factory's ability to leverage the computational power of SQL Data Warehouse.
From scenario:
Relecloud has a Microsoft SQL Server database named DB1 that stores information about the advertisers. DB1 is hosted on a Microsoft Azure virtual machine.
Relecloud identifies the following requirements for DB1:
✑ Data generated by the streaming analytics platform must be stored in DB1.
✑ The advertisers in DB1 must be stored in a table named Table1 and must be refreshed nightly.
Reference: https://docs.microsoft.com/en-us/azure/machine-learning/machine-learning-data-science-move-sql-server-virtual-machine

From scenario: Topics are considered to be trending if they generate many mentions in a specific country during a 15-minute time frame.

Box 1: TimeStamp -
Azure Stream Analytics (ASA) is a cloud service that enables real-time processing over streams of data flowing in from devices, sensors, websites and other live systems. The stream-processing logic in ASA is expressed in a SQL-like query language with some added extensions such as windowing for performing temporal calculations.
ASA is a temporal system, so every event that flows through it has a timestamp. A timestamp is assigned automatically based on the event's arrival time to the input source but you can also access a timestamp in your event payload explicitly using TIMESTAMP BY:
SELECT * FROM SensorReadings TIMESTAMP BY time

Box 2: GROUP BY -
Example: Generate an output event if the temperature is above 75 for a total of 5 seconds
SELECT sensorId, MIN(temp) as temp

FROM SensorReadings -

TIMESTAMP BY time -
GROUP BY sensorId, SlidingWindow(second, 5)

HAVING MIN(temp) > 75 -

Box 3: SlidingWindow -
Windowing is a core requirement for stream processing applications to perform set-based operations like counts or aggregations over events that arrive within a specified period of time. ASA supports three types of windows: Tumbling, Hopping, and Sliding.
With a Sliding Window, the system is asked to logically consider all possible windows of a given length and output events for cases when the content of the window actually changes that is, when an event entered or existed the window.
Reference: https://blogs.technet.microsoft.com/machinelearning/2015/06/01/the-azure-stream-analytics-query-language/

From Scenario: Relecloud plans to implement a data warehouse named DB2.

Box 1: Temporal table -
From Scenario:
Relecloud identifies the following requirements for DB2:
Users must be able to view previous versions of the data in DB2 by using aggregates.
DB2 must be able to store more than 40 TB of data.
A system-versioned temporal table is a new type of user table in SQL Server 2017, designed to keep a full history of data changes and allow easy point in time analysis. A temporal table also contains a reference to another table with a mirrored schema. The system uses this table to automatically store the previous version of the row each time a row in the temporal table gets updated or deleted. This additional table is referred to as the history table, while the main table that stores current (actual) row versions is referred to as the current table or simply as the temporal table.
Reference: https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables

Box 2: Clustered Columnstore -
From Scenario:
The data warehouse is expected to grow at a rate of 1 GB per hour or 8.7 terabytes (TB) per year. The data will be retained for five years for the purpose of long- term trending.
Relecloud identifies the following requirements for DB2:
Data from DB1 must be archived in DB2 for long-term storage.
How SQL Database deletes aged rows?
The cleanup process depends on the index layout of the history table. It is important to notice that only history tables with a clustered index (B-tree or columnstore) can have finite retention policy configured.
Reference: https://docs.microsoft.com/en-us/sql/relational-databases/tables/manage-retention-of-historical-data-in-system-versioned-temporal-tables

Box 1: Security -

Security Policy -
Example: After we have created Predicate function, we have to bind it to the table, using Security Policy. We will be using CREATE SECURITY POLICY command to set the security policy in place.
CREATE SECURITY POLICY DepartmentSecurityPolicy
ADD FILTER PREDICATE dbo.DepartmentPredicateFunction(UserDepartment) ON dbo.Department
WITH(STATE = ON)

Box 2: Filter -
[ FILTER | BLOCK ]
The type of security predicate for the function being bound to the target table. FILTER predicates silently filter the rows that are available to read operations.
BLOCK predicates explicitly block write operations that violate the predicate function.

Box 3: Block -

Box 4: Block -

Box 5: Filter -
Reference:
https://social.technet.microsoft.com/wiki/contents/articles/37813.sql-server-2016-implement-row-level-security-using-predicate-function-and-security-policy.aspx https://docs.microsoft.com/en-us/sql/t-sql/statements/create-security-policy-transact-sql