DP-600 Exam Preparation Material | Implementing Analytics Solutions Using Microsoft Fabric

Explanation:
Microsoft Fabric, Ingest data into OneLake and analyze with Azure Databricks
You can do this with a pipeline in a workspace and ingest data into your OneLake in Delta format.
Read and modify a Delta table in OneLake with Azure Databricks.
Note: Power BI dataflows enable you to connect to, transform, combine, and distribute data for downstream analytics. A key element in dataflows is the refresh process, which applies the transformation steps you authored in the dataflows and updates the data in the items themselves.
Scenario:
Technical Requirements
Files loaded by the data engineers to OneLake will be stored in the Parquet format and will meet Delta Lake specifications.
Data will be loaded without transformation in one area of the AnalyticsPOC data store. The data will then be cleansed, merged, and transformed into a dimensional model
The data load process must ensure that the raw and cleansed data is updated completely before populating the dimensional model
Planned change:
The data engineers will create data pipelines to load data to OneLake either hourly or daily depending on the data source. The analytics engineers will create processes to ingest, transform, and load the data to the data store in the AnalyticsPOC workspace daily. Whenever possible, the data engineers will use low-code tools for data ingestion. The choice of which data cleansing and transformation tools to use will be at the data engineers’ discretion.
Reference:
https://learn.microsoft.com/en-us/fabric/onelake/onelake-open-access-quickstart
https://learn.microsoft.com/en-us/power-bi/transform-model/dataflows/dataflows-understand-optimize-refresh

Explanation:
Transact SQL LAST_Value
Transact NTILE
Box 1: ROW_NUMBER() ()
* ROW_NUMBER()
Numbers the output of a result set. More specifically, returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
Syntax: ROW_NUMBER ()
OVER ([ PARTITION BY value_expression , ... [ n ] ] order_by_clause )
Incorrect:
* LAST_VALUE() Incorrect syntax.
Note: LAST_VALUE (Transact-SQL)
Returns the last value in an ordered set of values.
Syntax
LAST_VALUE ([ scalar_expression ])[ IGNORE NULLS | RESPECT NULLS ] OVER ([ partition_by_clause ] order_by_clause [ rows_range_clause ] )
* LAST_Value()
* NTILE()
Incorrect syntax used.
Note: NTILE (Transact-SQL)
Distributes the rows in an ordered partition into a specified number of groups. The groups are numbered, starting at one. For each row, NTILE returns the number of the group to which the row belongs.
NTILE (integer_expression) OVER ([ <partition_by_clause> ] < order_by_clause > )
Box 2: WHERE X = 1
Reference:
https://learn.microsoft.com/en-us/sql/t-sql/functions/row-number-transact-sql
https://learn.microsoft.com/en-us/sql/t-sql/functions/ntile-transact-sql
https://learn.microsoft.com/en-us/sql/t-sql/functions/last-value-transact-sql

Explanation:
Correct Solution: You use the following PySpark expression:
df.summary()
summary(*statistics)[source]
Computes specified statistics for numeric and string columns. Available statistics are: - count - mean - stddev - min - max - arbitrary approximate percentiles specified as a percentage (eg, 75%)
If no statistics are given, this function computes count, mean, stddev, min, approximate quartiles (percentiles at 25%, 50%, and 75%), and max.
Note This function is meant for exploratory data analysis, as we make no guarantee about the backward compatibility of the schema of the resulting DataFrame.
>>> df.summary().show() +-------+------------------+-----+
| stddev|2.1213203435596424| null|
Incorrect:
* df.show()
* df.explain().show()
* df.explain()
explain(extended=False)[source]
Prints the (logical and physical) plans to the console for debugging purpose.
Parameters: extended C boolean, default False. If False, prints only the physical plan.
>>> df.explain()
== Physical Plan ==
Scan ExistingRDD[age#0,name#1]
>>> df.explain(True)
== Parsed Logical Plan ==
...
== Analyzed Logical Plan ==
...
== Optimized Logical Plan ==
...
== Physical Plan ==
Reference: https://spark.apache.org/docs/2.3.0/api/python/pyspark.sql.html

Explanation:
What is Customer Retention Analytics?
Customer retention analytics provide predictive metrics of which customers may churn, allowing businesses to prevent this from happening. Let us understand this by an example, by using customer retention analytics, companies can reduce churn and increase profits, as evidenced by a McKinsey report suggesting that extensive use of customer data analytics can drive profit. Customer retention metrics, including the customer retention rate, are used to measure the likelihood of retaining and attracting customers to a business. This is how data analytics helps in customer retention.
Descriptive Analytics
Descriptive analytics provide you with granular insights based on historical data. This includes tracking past purchases, customer complaints, customer service reviews, and more. In order to implement descriptive customer retention analytics, your cloud engineers would need to make sure all customer data is on-premise and up-to-date and backed up on a regular basis. Because it uses historical data to create retention strategies and personalize customer experiences, all historical data must be accessible for analysis.
Incorrect:
* Predictive Analytics
This works in tandеm with dеscriptivе analytics, which allows you to forеcast the behavior of your customers based on past data. This allows you to prеparе for specific customеr intе ractions and improv е customеr rеtеntion. For еxamplе, you can usе historical transactions to prеdict how likely a customer is to rеnеw their subscription at a music plan. Thе nеxt timе that customеr walks into thе studio, your staff will rеcеivе an alеrt to offеr additional incеntivеs to pеrsuadе thеm to rеnеw.
* Prescriptive Analytics
Prescriptive analytics finds solutions based on insights from descriptive analytics. For example, you can collect data about remedial solutions to improve retention and see how well they performed. Prescriptive analytics forces you to retrospectively evaluate all strategies to improve them. For example, a bank might use Fraud Detection. An algorithm evaluates historical data after making a purchase to see if it matches the typical level of spending. If it detects an anomaly, the bank will be notified and will recommend a course of action, such as cancelling the bank card.
* Diagnostic Analytics
Diagnostic analytics involves the collection and examination of data pertaining to a particular issue or occurrence in order to comprehend the underlying causes. Consider a scenario where a fitness app, GymFit, observes a significant drop in user engagement during a specific period. Unraveling the factors contributing to this decline becomes the focal point of diagnostic analytics. In this context, GymFit delves
into the data to uncover reasons why users might be disengaging, such as changes in workout preferences, dissatisfaction with features, or scheduling conflicts. Through careful analysis, GymFit identifies patterns and root causes behind the drop in user engagement. Armed with this knowledge, the fitness app can then implement targeted improvements, addressing concerns and enhancing the overall user experience to prevent further disengagement and attract new users.
Reference: https://emergingindiagroup.com/data-analytics-for-customer-retention/