) and follow the instructions for your operating system.
* Once you have Spark installed, you can start using PySpark by importing the library in your Python script:
```python
from pyspark import SparkContext, SparkConf
```
* The next step is to create a SparkConf and SparkContext. The SparkConf allows you to configure various settings for your Spark application, while the SparkContext is the entry point to the Spark cluster and the main object you will use to interact with it.
```python
conf = SparkConf().setAppName("MyApp").setMaster("local")
sc = SparkContext(conf=conf)
```
* With the SparkContext, you can now create RDDs (Resilient Distributed Datasets), which are the basic data structure in Spark. You can create RDDs from a variety of data sources, such as local files, HDFS, or even other RDDs.
```python
rdd = sc.textFile("path/to/file.txt")
```
* RDDs support two types of operations: transformations and actions. Transformations are operations that create a new RDD from an existing one, such as map, filter, and groupByKey. Actions are operations that return a value or write data to an external storage, such as count, collect, and saveAsTextFile.
```python
# Transformation
rdd2 = rdd.filter(lambda x: "error" in x)
# Action
print(rdd2.count())
```
* PySpark also supports DataFrame and SQL API which is similar to pandas and SQL.
```python
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("MyApp").getOrCreate()
df = spark.read.csv("path/to/file.csv", header=True, inferSchema=True)
df.show()
```
* Finally, remember to stop the SparkContext when you are done:
```python
sc.stop()
```
This is a brief summary of PySpark basics, but it is just the tip of the iceberg. There are many more features and capabilities of PySpark, such as machine learning libraries, streaming, and graph processing.
{% hint style="info" %}
But if you are writing PySpark code in Databricks then you can ignore the above code.
{% endhint %}
In the next chapter we will learn about using PySpark in Databricks.
# Databricks Introduction
In this page we will look at a brief introduction about Databricks
**Databricks** is an Apache Spark-based analytics platform that allows you to easily process big data and build machine learning models. It was founded by the creators of Apache Spark and provides a collaborative, **cloud-based platform** for data engineering, machine learning, and analytics.
Databricks provides a **web-based notebook interface** that allows you to easily process large datasets using Spark and provides **built-in integration** with popular data storage systems such as Amazon S3 and Azure Data Lake Storage.
Additionally, Databricks provides a variety of features to help you optimize the performance of your Spark jobs, such as **automatic cluster management** and **dynamic allocation of resources**. It also provides a wide range of **visualization tools** and **machine learning libraries** that can be used to analyze and gain insights from your data.
Overall, Databricks is a **powerful and user-friendly platform** that makes it **easy to process big data** and **build machine learning models** in a **collaborative, cloud-based environment**.
### Why using Databricks for PySpark is better than using PySpark with local installation?
1. Scalability: Databricks allows you to easily scale your Spark clusters up or down as needed, without the need for manual configuration or setup. This makes it easy to process large datasets and handle increased traffic.
2. Collaboration: Databricks provides a web-based notebook interface that allows multiple users to collaborate on a project in real-time. This feature makes it easy for data scientists, engineers, and analysts to share and collaborate on code and results, improving the overall productivity of a team.
3. Integration: Databricks provides built-in integration with popular data storage systems such as Amazon S3 and Azure Data Lake Storage, making it easy to load and process large datasets.
4. Monitoring: Databricks provides a wide range of tools and metrics to monitor the performance of Spark jobs, allowing you to identify and diagnose any performance bottlenecks or issues.
5. Automation: Databricks provides a wide range of automation features such as automatic cluster management, dynamic allocation of resources, and auto-terminating idle clusters.
6. Security: Databricks provides a wide range of security features such as end-to-end encryption, network isolation, and role-based access control to ensure that your data is secure and protected.
7. High-Availability: Databricks runs on the cloud infrastructure which is automatically replicated across multiple availability zones and can scale horizontally to handle increased traffic, making it highly available and fault tolerant.
> In summary, using Databricks for PySpark is more efficient, productive, and secure than using PySpark with a local installation.
# PySpark in Databricks
In this tutorial, we will see how we can get started with Databricks
Databricks offers 2 platforms
* Databricks Commercial platform (Paid)\
* **Databricks Community Edition (Free)**\
In this tutorial, we will be focusing on the **Databrick Community Edition.**
In order to get started you will need to follow the following steps:
**Step 1:** Go to the Databricks Community Platform website () and click on the "Sign Up" button to create a new account.
**Step 2:** Fill in the required information to create an account, such as your name, email address, and password.
**Step 3:** Verify your email address by clicking on the link sent to your email.
**Step 4:** Once your account is created, you will be redirected to the Databricks Community Platform dashboard.
**Step 5:** To start working with Databricks, you will need to create a new workspace. Click on the "Workspaces" button on the left sidebar, and then click on the "Create Workspace" button.
**Step 6:** Give your workspace a name and select the "Community Edition" plan.
**Step 7:** Once your workspace is created, you will be taken to the workspace dashboard.
**Step 8:** Now you can create a new cluster by clicking on the "Clusters" button on the left sidebar and then clicking on the "Create Cluster" button.
**Step 9:** Give your cluster a name, select the appropriate settings and click on the "Create Cluster" button.
**Step 10:** Once the cluster is created, you can create a new notebook by clicking on the "Workspaces" button on the left sidebar, then the "Create" button and then selecting "Notebook".
**Step 11:** Give your notebook a name and select the language (Python or Scala) and attach the created cluster to the notebook.
**Step 12:** You can now start writing and running code in your notebook using PySpark or Scala.
Please note that the community edition of Databricks has some limitations such as the number of hours the cluster can run and certain features may not be available.
That's it! You are now ready to start working with the Databricks Community Platform. Remember that you can always refer to the Databricks documentation for more information and help on specific features and functionality. Additionally, you can always reach out to Databricks community support for any questions or issues you may encounter.
It's important to note that, as a community edition user, you may not have access to all the features that are available on the paid version, but it's still a great way to get started and learn about the platform. The community edition should be enough for small-scale data processing and experimentation.
### A basic example
* To begin working with PySpark in Databricks, you'll first need to create a new notebook and attach it to a Spark cluster. This can be done by clicking on the "New Notebook" button in the Databricks workspace and selecting "Python" as the language.
* Once you have a new notebook open, you can start working with PySpark by importing the necessary libraries and modules. The most commonly used library is the `pyspark` library, which provides the main interface for working with PySpark.
* Databricks notebooks has PySpark session preloaded with the name `"spark"`
* To read data from a file (e.g. CSV), you can use the `spark.read` method to read the file, and then convert it to a DataFrame:
```python
df = spark.read.csv("path_to_file.csv", header = True, inferSchema = True)
```
* Once you have a DataFrame, you can perform various transformations and operations on it, such as selecting columns, filtering rows, and grouping data. For example:
```python
df.select("column1", "column2")
df.filter(df["column1"] > 10)
df.groupBy("column1").mean()
```
* To write the dataframe to a file you can use the following command:
```python
df.write.parquet("path_to_file.parquet")
```
* Finally, you can use the `show()` method to display the results of your analysis, for example:
```python
df.show()
```
These are the basic commands that you need to get started with PySpark in Databricks. There are many more functions and options available, and it's always good to check the documentation for the latest updates and options.
*
# Reading Data with PySpark
In this tutorial we will see how we can read data in PySpark
* To read data in PySpark in Databricks, you will first need to create a SparkSession. You can create a SparkSession by running the following command:
```python
from pyspark.sql import SparkSession
spark = SparkSession.builder.appName("MyApp").getOrCreate()
```
{% hint style="info" %}
The above step can be skipped in Databricks. Because Databricks notebooks have the Spark Session already preloaded.
{% endhint %}
* Once you have a SparkSession, you can use the `spark.read` method to read data from various sources. Databricks supports various file formats such as CSV, JSON, Parquet, and many more. You can read a CSV file from a DBFS (Databricks File System) by running the following command:
```python
df = spark.read.csv("/path/to/file.csv", header=True, inferSchema=True)
```
* Databricks also supports reading data from external sources such as Amazon S3, Azure Blob Storage, and more. You just need to provide the appropriate path for the file.
```python
df = spark.read.csv("s3a://path/to/file.csv", header=True, inferSchema=True)
```
* Once you have read the data, you can perform various operations on the dataframe such as filtering, aggregation and join. Databricks also provides a built-in visualization tool called display which you can use to plot the dataframe.
```python
display(df.filter(df.column_name == 'value').groupby('column_name').agg(avg('column_name')))
```
* You can also save the dataframe back to DBFS or external storage like S3, Azure Blob etc.
```python
df.write.parquet("/path/to/save/data.parquet")
```
* To read data from other sources such as Hive or JDBC, you can use the `spark.read` method with the appropriate options.
```python
df = spark.read.format("jdbc").options(url="jdbc:postgresql:dbserver", dbtable="schema.tablename", user="username", password="password").load()
```
* Always make sure that you have the required credentials to access the data and the required libraries are installed.
These are some basic notes for reading data using PySpark in Databricks. There are many more options and configurations available for reading data, depending on the specific data source and use case.
# PySpark Transformation Methods
In this tutorial, we will try to explore some of the common data transformation methods in PySpark
* Data transformations are operations that create a new DataFrame from an existing one. PySpark provides a variety of data transformation methods that can be used to manipulate data in a DataFrame.
* One of the most commonly used transformation methods is `select()` which allows you to select specific columns from a DataFrame. You can use the `select()` method to select one or more columns by name or index.
```python
df.select("column1","column2")
```
* Another commonly used transformation method is `filter()` which allows you to filter rows based on a certain condition. You can use the `filter()` method to filter rows by passing a column name and a condition.
```python
df.filter(df.column_name > 10)
```
* You can also use `groupBy()` to group the dataframe by one or more columns and perform various aggregate functions such as count, sum, avg, etc. on the grouped dataframe
```python
df.groupBy("column1","column2").agg(avg("column3"),max("column4"))
```
* `join()` method allows you to join two DataFrames based on a column. It supports various types of joins such as inner join, outer join, left join, and right join.
```python
df1.join(df2, "column_name", "inner")
```
* `drop()` method allows you to drop one or more columns from a DataFrame.
```python
df.drop("column1","column2")
```
* `withColumn()` method allows you to add or replace a column in a DataFrame.
```python
df.withColumn("new_column", df.column1 + df.column2)
```
* `sort()` method allows you to sort the dataframe by one or more columns in ascending or descending order.
```python
df.sort(df.column1.desc())
```
* `distinct()`method removes duplicate rows from a DataFrame.
```python
df.distinct()
```
* `limit()` method allows you to limit the number of rows in a DataFrame.
```python
df.limit(10)
```
* `repartition()` method allows you to change the number of partitions in a DataFrame. This can be useful when you want to increase or decrease the parallelism of a DataFrame.
```python
df.repartition(10)
```
* `coalesce()` method allows you to decrease the number of partitions in a DataFrame.
```python
df.coalesce(5)
```
* `cast()` method allows you to change the data type of a column.
```python
from pyspark.sql.functions import col
df = df.withColumn("column1", col("column1").cast("int"))
```
* `replace()` method allows you to replace specific values in a DataFrame.
```python
df = df.replace(["value1", "value2"], ["new_value1", "new_value2"], "column_name")
```
* `dropna()` method allows you to drop rows with null values from a DataFrame.
```python
df.dropna()
```
* `fillna()` method allows you to fill null values with a specific value.
```python
df.fillna(0, "column_name")
```
These are some of the commonly used data transformation methods in PySpark. There are many more methods and options available depending on the specific use case. It's always good to check the [documentation](https://docs.databricks.com/) for the latest updates and options.
# Handling Duplicate Data
In this tutorial, we will see some common methods for how we can handle duplicate data.
* Removing duplicate rows based on all columns:
```python
df = df.distinct()
```
* Removing duplicate rows based on specific columns:
```python
df = df.dropDuplicates(["column1", "column2"])
```
* Removing duplicate rows based on specific columns and considering only the first occurrence:
```python
df = df.dropDuplicates(["column1", "column2"], keep='first')
```
* Removing duplicate rows based on specific columns and considering only the last occurrence:
```python
df = df.dropDuplicates(["column1", "column2"], keep='last')
```
* Removing duplicate rows based on specific columns and considering only the first occurrence for each group of duplicates:
```python
df = df.dropDuplicates(["column1", "column2"], keep=False)
```
It's worth noting that `dropDuplicates()` is an alias for `distinct()` so you can use either of these function depending on your preference and it's always good to check the [documentation](https://docs.databricks.com/) for the latest updates and options.
# PySpark Action Methods
In this tutorial we will try to look at some of the common action methods in PySpark
* Action methods are operations that return a value or produce a side effect. They are used to retrieve or collect data from a DataFrame.
* One of the most commonly used action methods is `count()` which returns the number of rows in a DataFrame.
```python
df.count()
```
* Another commonly used action method is `show()` which displays the first n rows of a DataFrame. By default, it shows 20 rows, but you can specify a different number of rows.
```python
df.show(n=10)
```
* `collect()` method is used to retrieve all the rows in a DataFrame as an array of Row objects. It should be used with caution as it can cause the driver to run out of memory if the DataFrame is too large.
```python
df.collect()
```
* `first()` method is used to retrieve the first row in a DataFrame.
```python
df.first()
```
* `take()` method is used to retrieve the first n rows of a DataFrame.
```python
df.take(n=5)
```
* `foreach()` method is used to apply a function to each element of a DataFrame.
def my_function(row):
print(row)
df.foreach(my_function)
* `foreachPartition()` method is used to apply a function to each partition of a DataFrame.
```python
def my_function(iterator):
for row in iterator:
print(row)
df.foreachPartition(my_function)
```
* `toPandas()` method is used to convert a DataFrame to a pandas DataFrame. It should be used with caution as it can cause the driver to run out of memory if the DataFrame is too large.
```python
df.toPandas()
```
These are some of the commonly used action methods in PySpark. There are many more methods and options available depending on the specific use case. It's always good to check the [documentation](https://docs.databricks.com/) for the latest updates and options.
# PySpark Native Functions
In this tutorial we will explore some common PySpark native functions
PySpark provides a variety of built-in functions that can be used to perform operations on columns in a DataFrame. These functions are part of the pyspark.sql.functions module and can be imported as follows:
```python
from pyspark.sql.functions import *
```
Some examples of commonly used functions include:
* `sum()` function: It is used to calculate the sum of a column.
```python
df.agg(sum("column1"))
```
* `avg()` function: It is used to calculate the average of a column.
```python
df.agg(avg("column1"))
```
* `min()` function: It is used to calculate the minimum value of a column.
```python
df.agg(min("column1"))
```
* `max()` function: It is used to calculate the maximum value of a column.
```python
df.agg(max("column1"))
```
* `concat()` function: It is used to concatenate two or more columns
```python
df.select(concat(col("column1"), col("column2")))
```
These functions can be used with the `select()` and `agg()` methods to perform operations on DataFrame columns.
```python
df.select(sum("column1").alias("sum_column1"))
```
You can also use these functions in the `filter()` method to filter the dataframe based on a certain condition
```python
df.filter(col("column1") > 10)
```
These functions can also be used with the `withColumn()` method to add a new column to a DataFrame.
```python
df.withColumn("new_column", col("column1") + col("column2"))
```
You can also use the `when()` and `otherwise()` functions to create a new column based on a certain condition.
```python
from pyspark.sql.functions import when
df.withColumn("new_column", when(col("column1") > 10, "high").otherwise("low"))
```
You can also use the `ifnull()` and `nullif()` functions to handle missing values.
```python
from pyspark.sql.functions import ifnull, nullif
df.select(ifnull("column1", 0))
df.select(nullif("column1", 0))
```
These are just some examples of the built-in functions provided by PySpark. There are many more functions available and it's always good to check the documentation for the latest updates and options.
It's always good to check the [documentation](https://docs.databricks.com/) for the latest updates and options. Also, when you are working with Databricks, always make sure that you have the required libraries installed.
# Partitioning
In this tutorial we will learn about Partitioning strategy in PySpark
In PySpark and Databricks, partitioning is the process of dividing a large dataset into smaller, manageable chunks called partitions. The goal of partitioning is to improve the performance and scalability of Spark by distributing data processing across multiple nodes in a cluster. This way, the data can be processed in parallel, which speeds up the processing time.
In PySpark, there are two main types of partitioning:
1. Hash Partitioning: In hash partitioning, the data is divided into partitions based on a hash function. The hash function takes a column of the data and maps its values to a specific partition. This way, data with the same value will be grouped into the same partition.
2. Range Partitioning: In range partitioning, the data is divided into partitions based on a range of values for a specific column. The range is determined based on the distribution of the data in the column.
Databricks provides several functions for partitioning data, including `repartition()` and `coalesce()`. The `repartition()` function can be used to specify the number of partitions for a DataFrame or RDD. The `coalesce()` function can be used to reduce the number of partitions for a DataFrame or RDD.
It's important to note that partitioning can impact the performance of Spark, both positively and negatively. To optimize performance, it's important to understand the data being processed and to choose the appropriate partitioning strategy.
`partitionBy` is a method available in PySpark for defining the partitioning strategy when writing data to a file system, such as HDFS or S3.
Here's an example of how you might use `partitionBy` when writing a PySpark DataFrame to a partitioned parquet file:
```lua
df.write.partitionBy("column_name").parquet("/path/to/parquet/file")
```
In this example, the data in the DataFrame `df` will be partitioned by the values in the column `column_name`. This means that data with the same value in the `column_name` column will be written to the same partition, which can help optimize read performance when querying the data in the future.
You can also specify multiple columns for partitioning:
```lua
df.write.partitionBy("column_name_1", "column_name_2").parquet("/path/to/parquet/file")
```
This will partition the data in the DataFrame `df` by both `column_name_1` and `column_name_2`.
`repartition()` is a method in PySpark that is used to change the number of partitions of a Spark DataFrame or RDD. It is used to increase or decrease the parallelism of your Spark job.
Here's an example of how you might use `repartition()` to increase the number of partitions in a PySpark DataFrame:
```bash
codedf = df.repartition(100)
```
In this example, the number of partitions in the DataFrame `df` is increased to 100. This can help improve the parallelism of your Spark job, which can result in faster processing times.
It's important to note that repartitioning can be an expensive operation as it involves shuffling the data across the nodes in your cluster. Therefore, it's important to carefully consider the trade-offs between the number of partitions and the cost of shuffling data.
Here's another example that shows how you might use `repartition()` based on the values in a column:
```bash
df = df.repartition(100, "column_name")
```
In this example, the number of partitions in the DataFrame `df` is increased to 100 and the data is partitioned based on the values in the column `column_name`. This can help improve the performance of operations that involve filtering or aggregating data based on the values in this column.
`coalesce()` is a method in PySpark that is used to reduce the number of partitions in a Spark DataFrame or RDD. Unlike `repartition()`, `coalesce()` does not shuffle the data and is therefore more efficient for reducing the number of partitions.
Here's an example of how you might use `coalesce()` to reduce the number of partitions in a PySpark DataFrame:
```bash
df = df.coalesce(10)
```
In this example, the number of partitions in the DataFrame `df` is reduced to 10. This can help reduce the overhead of parallel processing and improve the efficiency of your Spark job.
Here's another example that shows how you might use `coalesce()` to combine multiple partitions into a single partition:
```bash
df = df.coalesce(1)
```
In this example, the number of partitions in the DataFrame `df` is reduced to 1. This can be useful for operations that require all the data to be processed by a single node, such as writing the data to disk or printing the data to the console.
It's important to note that `coalesce()` can only combine adjacent partitions and can only reduce the number of partitions. It cannot increase the number of partitions. If you need to increase the number of partitions, you should use `repartition()`.
# Bucketing
In this tutorial we learn about Bucketing strategy in PySpark
Bucketing is a feature in PySpark that enables you to group similar data into separate "buckets" to improve query performance. This is achieved by organizing the data into fixed-size hash-based buckets based on one or more columns in your DataFrame. Each bucket is stored as a separate file in the underlying file system. When you query the data, Spark can access the data in parallel from the individual files instead of having to scan the entire data set, which can improve query performance significantly.
Fixed-size hash-based buckets refer to a bucketing technique in which data is divided into a fixed number of buckets based on a hash of the values in a specific column or columns. In this approach, each data value is hashed and the hash value is used to determine which bucket the data should belong to. The number of buckets is fixed, so each bucket has roughly the same number of data points.
This technique is useful when we want to divide data into a small, fixed number of buckets, while still keeping related data together. For example, in a data analysis use case, we may want to divide a large dataset into a small number of buckets based on specific values of a certain column, such as user IDs or timestamps. With fixed-size hash-based bucketing, we can quickly and efficiently retrieve data for specific buckets and process the data within those buckets.
Here's an example of how you might create buckets in a PySpark DataFrame:
```python
from pyspark.sql.functions import bucket, expr
df = df.write.bucketBy(10, "column1").sortBy("column2").saveAsTable("table_name")
```
In this example, we're grouping the data into 10 buckets based on the values in the "column1" column. The data within each bucket is then sorted by the values in the "column2" column.
It's important to note that bucketing is most effective when the data within each bucket is roughly the same size. To ensure this, you should choose the number of buckets carefully and consider the distribution of the data in the columns that you're using for bucketing.
{% hint style="info" %}
Additionally, bucketing is only supported for tables that are stored as Parquet files and can only be used in combination with sorting.
{% endhint %}
# Partitioning vs Bucketing
In this tutorial we will try to understand the difference between Partitioning and Bucketing
Partitioning and bucketing in PySpark refer to two different techniques for organizing data in a DataFrame.
**Partitioning:** Partitioning is the process of dividing a large dataset into smaller and more manageable parts called partitions. Each partition contains a subset of the data and can be processed in parallel, improving the performance of operations like filtering, aggregation, and join. In PySpark, we can use the `repartition()` or `coalesce()` functions to change the number of partitions.
**Bucketing:** Bucketing is a form of partitioning that groups similar data together in a single partition. Unlike regular partitioning, bucketing is based on the value of the data rather than the size of the dataset. In PySpark, we can use the `bucketBy()` function to create bucketing columns, which can then be used to efficiently retrieve and process related data.
To sum up, partitioning helps with performance by dividing data into smaller parts, while bucketing helps with data organization by grouping related data together.
`partitionBy` and `bucketBy` are two different features in PySpark used for organizing data in a DataFrame.
#### partitionBy vs bucketBy
`partitionBy` is used to partition a DataFrame into multiple chunks based on the values in one or more columns. Each partition is then stored as a separate file in the underlying file system. Partitioning is used to improve query performance by allowing Spark to access the data in parallel from multiple files instead of having to scan the entire data set. Here's an example of how you might use `partitionBy` in PySpark:
```lua
df.write.partitionBy("column1", "column2").parquet("/path/to/data")
```
In this example, we're partitioning the data into separate files based on the values in the "column1" and "column2" columns. Each file contains all of the data for a specific combination of values in these two columns.
`bucketBy`, on the other hand, is used to create fixed-size hash-based buckets based on the values in one or more columns. Each bucket is stored as a separate file in the underlying file system. Bucketing is used to improve query performance by reducing the number of files that need to be scanned during a query. Here's an example of how you might use `bucketBy` in PySpark:
```lua
df.write.bucketBy(10, "column1").sortBy("column2").parquet("/path/to/data")
```
In this example, we're grouping the data into 10 buckets based on the values in the "column1" column. The data within each bucket is then sorted by the values in the "column2" column.
In summary, `partitionBy` is used to partition the data into separate files based on the values in one or more columns, while `bucketBy` is used to create fixed-size hash-based buckets based on the values in one or more columns. Both are used to improve query performance, but they achieve this in different ways.
# Spark Streaming
A to-the-point Instruction/Guide for any Spark enthusiast
## What to expect
Here we will be explaining everything, including setting up your spark development environment, the theoretical & practical concepts related to spark, and big data.
We will also do some fun projects along the way.
It is one-stop documentation to learn and practice the Spark framework. So, just sit back and follow along.
### Installation
{% tabs %}
{% tab title="Windows user" %}
####
#### 1. Install Java 8
Check if Java 8 is already installed on your system or not
```bash
java -version
```
If Java is installed, it will respond with the following output:
If not then you need to install Java 8
To install Java 8, visit the following link and click on the download button
{% embed url="" %}
{% hint style="warning" %}
Spark needs Java 8 to work. So installing any other version won't work well. Please use the above link only.
{% endhint %}
Once downloaded, double-click on the file and complete the installation.
After installation is complete, open a new command prompt and check for the java version as follows:
```bash
java -version
```
**2. Install Python 3**
First, check if python 3 is already installed.
```bash
python --version
```
The above command should display a result like this:
If the python version is not showing up and you get an error, please if double-check if python is properly installed and the python path is added to environment variables.
To install the latest python version visit the following link and download the installer.
{% embed url="" %}
Once downloaded, run the installer.
During the installation make sure you check the following option to add the python path to the environment variables, as shown below
> *Make sure that python is installed with "**all users**" option*
Now open a new command prompt and run the following command to check the python version.
```bash
python --version
```
The output should print the python version
**3. Install Spark**
Use the following command to install spark.
```bash
pip install pyspark
```
If the above command does not work then you can use the manual installation steps as mentioned below.
### Spark Manual Installation (only if pip step 3 doesn't work)
**1: Download Setup**
Open the following link
{% embed url="" %}
Under the Download Apache Spark heading, there are two drop-down menus. Use the current non-preview version.
In **Choose a Spark release drop-down** menu **select 3.0.3 (Jun 23, 2021)**. In the second drop-down **Choose a package type**, leave the selection **Pre-built for Apache Hadoop 2.7**.
Click the spark-3.0.3-bin-hadoop2.7.tgz link.
A page with a list of mirror links loads where you can see different servers to download from. Pick any from the list and save the file.
#### 2: Install Apache Spark
Create a new folder named Spark in the root of your C: drive. From a command line, enter the following:
```bash
cd \
mkdir Spark
```
In Explorer, locate the Spark file you downloaded.
Right-click the file and extract it to C:\Spark using the tool you have on your system (e.g., 7-Zip).
#### 3: Add winutils.exe File
Navigate to this URL
Select the folder that matches the hadoop version with your spark download
Then, Inside the subsequent bin folder, locate winutils.exe, and click it.
Find the **Download** button on the right side to download the file.
Create new folders **Hadoop** and **bin** on C: using Windows Explorer or the Command Prompt.
Copy the winutils.exe file from the Downloads folder to **C:\hadoop\bin.**
#### 4: Configure Environment Variables
Click **Start** and type *environment*.
Select the result labeled ***Edit the system environment variables***.
A System Properties dialog box appears. In the lower-right corner, click **Environment Variables** and then click **New** in the next window.
For *Variable Name* type ***SPARK\_HOME***.
For *Variable Value* type **C:\Spark\spark-3.0.3-bin-hadoop2.7** and click OK. If you changed the folder path, use that one instead.
In the top box, click the **Path** entry, then click **Edit**. Be careful with editing the system path. Avoid deleting any entries already on the list.
You should see a box with entries on the left. On the right, click **New**.
The system highlights a new line. Enter the path to the Spark folder **%SPARK\_HOME%\bin**.
Repeat this process for Hadoop and Java.
* For Hadoop, the variable name is **HADOOP\_HOME** and for the value use the path of the folder you created earlier: **C:\hadoop.** Add **C:\hadoop\bin** to the **Path variable** field, but we recommend using **%HADOOP\_HOME%\bin**.
* For Java, the variable name is **JAVA\_HOME** and for the value use the path to your Java JDK directory (in our case it’s **C:\Program Files\Java\jdk1.8.0\_251**).
Click **OK** to close all open windows.
#### 5: Launch Spark
Open a new command prompt window using the right-click and **Run as administrator**:
To start Spark, enter:
```bash
spark-shell
```
If you set the **environment path** correctly, you can type **`spark-shell`** to launch Spark.
The system should display several lines indicating the status of the application. You may get a Java pop-up. Select **Allow access** to continue.
Finally, the Spark logo appears, and the prompt displays the **Scala shell**.
Open a web browser and navigate to ****.
You can replace **localhost** with the name of your system.
You should see an Apache Spark shell Web UI. The example below shows the *Executors* page.
To exit Spark and close the Scala shell, press **`ctrl-d`** in the command prompt window.
{% endtab %}
{% tab title="Linux user" %}
{% endtab %}
{% endtabs %}
# Installation Issues
This page addresses some common errors and issues that you might have while installing Spark on your system.
### Common issues/errors:
If you are getting the following error while installing Spark **3.2** try installing Spark **3.0.3**
Alternate steps to resolve the error:
**OPTION 1**
Open a spark cluster manually using the command:
```bash
spark-class org.apache.spark.deploy.master.Master
```
Your output should be something like the image below:
This means that your UI is set at localhost:8080 and you have opened the master at localhost:7077.
So now the only thing that's left to do is open a 2nd cmd and execute the command:
```bash
spark-shell --master spark://localhost:7077
```
Your output should be something like the image below:
**OPTION 2**
Install an older spark version preferably **Spark 3.0.3**
# Jupyter Notebook Setup
In this guide you will see how to setup jupyter notebook as a default editor for PySpark
After completing the Spark installation in order to set jupyter notebook as the default editor we need to the following steps:
### Step 1 (install jupyter notebook)
Open CMD and use the following command to install jupyter notebook
```bash
pip install jupyter
```
### Step 2 (set environment variables)
create the following environment variables:
**PYSPARK\_PYTHON** = {path of your python.exe}
**PYSPARK\_DRIVER*****\_*****PYTHON** = jupyter
**PYSPARK\_DRIVER\_PYTHON\_OPTS** = 'notebook'
That's it!
*You can verify if your setup was successful by typing the following command on cmd*
```bash
pyspark
```
*This should open jupyter notebook in your default browser.*
# Azure Data Factory
# Smart Contract Guide
A simple guide for developing and deploying smart-contracts for Blockchain Beginners inspired by the OpenZeppelin Guide
{% hint style="info" %}
This guide is based on the [OpenZeppelin's Official Smart Contact Guide](https://docs.openzeppelin.com/learn/)
{% endhint %}
#### In this guide we will go through the following:
1. [Setting up a Node project](https://docs.openzeppelin.com/learn/setting-up-a-node-project)
2. [Developing smart contracts](https://docs.openzeppelin.com/learn/developing-smart-contracts)
3. [Deploying and interacting](https://docs.openzeppelin.com/learn/deploying-and-interacting)
4. [Writing automated tests](https://docs.openzeppelin.com/learn/writing-automated-tests)
5. [Connecting to public test networks](https://docs.openzeppelin.com/learn/connecting-to-public-test-networks)
6. [Upgrading smart contracts](https://docs.openzeppelin.com/learn/upgrading-smart-contracts)
7. [Preparing for mainnet](https://docs.openzeppelin.com/learn/preparing-for-mainnet)
# Setting up a Node project
Setting up a Node project for smart-contracts
To start a new project, create a directory for it:
```bash
mkdir learn_sc && cd learn_sc
```
Then we will initialize our node project inside the newly created folder:
```bash
npm init -y
```
This will create a `package.json` file, which will evolve as your project grows, such as when installing dependencies with `npm install`
{% hint style="info" %}
JavaScript and npm are some of the most used software tools in the world: if you’re ever in doubt, you’ll find plenty of information about them online.
{% endhint %}
#### Using npx
There are two broads type of packages stored in the npm registry: *libraries* and *executables*. Installed libraries are used like any other piece of JavaScript code, but executables are special.
A third binary was included when installing node: [npx](https://blog.npmjs.org/post/162869356040/introducing-npx-an-npm-package-runner). This is used to run executables installed locally in your project.
For our local blockchain development, we will need to install certain packages/libraries so that we can create a local blockchain network within our local systems. For this we can either **Truffle** or **Hardhat.**
{% hint style="info" %}
Whilst [Truffle](https://www.trufflesuite.com/truffle) and [Hardhat](https://hardhat.org/) can be installed globally we recommend installing them locally in each project so that you can control the version on a project-by-project basis.
{% endhint %}
In this guide, we will be going with using Hardhat.
#### Tracking with Version Control
Before you get coding, you should add [version control software](https://en.wikipedia.org/wiki/Version_control) to your project to track changes.
By far, the most used tool is [Git](https://git-scm.com/), often in conjunction with [GitHub](https://github.com/) for hosting purposes. Indeed, you will find the full source code and history of all OpenZeppelin software in our [GitHub repository](https://github.com/OpenZeppelin).
{% hint style="info" %}
If you’ve never used Git before, a good starting place is the [Git Handbook](https://guides.github.com/introduction/git-handbook/).
{% endhint %}
{% hint style="warning" %}
Don’t commit secrets such as mnemonics, private keys and API keys to version control! Make sure you [`.gitignore`](https://git-scm.com/docs/gitignore) files with secrets.
{% endhint %}
# Developing smart contracts
This guide will let you get started writing Solidity contracts
In this guide we will be going over the following:
* Setting up a Solidity Project
* Compiling Solidity Source Code
* Adding More Contracts
* Using OpenZeppelin Contracts
### About Solidity
We won’t be covering language concepts such as syntax or keywords in this guide. For that, you’ll need to check out the following curated content, which features great learning resources for both newcomers and experienced developers:
* For a general overview of how Ethereum and smart contracts work, the official website hosts a [Learn about Ethereum](https://ethereum.org/learn/) section with lots of beginner-friendly content.
* If you’re new to the language, the [official Solidity documentation](https://solidity.readthedocs.io/en/latest/introduction-to-smart-contracts.html) is a good resource to have handy. Take a look at their [security recommendations](https://solidity.readthedocs.io/en/latest/security-considerations.html), which nicely go over the differences between blockchains and traditional software platforms.
* Consensys' [best practices](https://consensys.github.io/smart-contract-best-practices/) are quite extensive, and include both [proven patterns](https://consensys.github.io/smart-contract-best-practices/development-recommendations/) to learn from and [known pitfalls](https://consensys.github.io/smart-contract-best-practices/attacks/) to avoid.
* The [Ethernaut](https://ethernaut.openzeppelin.com/) web-based game will have you look for subtle vulnerabilities in smart contracts as you advance through levels of increasing difficulty.
With that out of the way, let’s get started!
### Setting up a Project
The first step is to install a development tool.
The most popular development framework for Ethereum is [Hardhat](https://hardhat.org/), and we cover its most common use with [ethers.js](https://docs.ethers.io/). The next most popular is [Truffle](https://www.trufflesuite.com/truffle) which uses [web3.js](https://web3js.readthedocs.io/). Each has its strengths and it is useful to be comfortable using all of them.
In this guide, we will show how to develop, test and deploy smart contracts using hardhat
To get started with Hardhat we will install it in our project directory.
```bash
npm install --save-dev hardhat
```
Once installed, we can run `npx hardhat`. This will create a Hardhat config file (`hardhat.config.js`) in our project directory.
```bash
npx hardhat
```
```
888 888 888 888 888
888 888 888 888 888
888 888 888 888 888
8888888888 8888b. 888d888 .d88888 88888b. 8888b. 888888
888 888 "88b 888P" d88" 888 888 "88b "88b 888
888 888 .d888888 888 888 888 888 888 .d888888 888
888 888 888 888 888 Y88b 888 888 888 888 888 Y88b.
888 888 "Y888888 888 "Y88888 888 888 "Y888888 "Y888
Welcome to Hardhat v2.2.1
✔ What do you want to do? · Create an empty hardhat.config.js
Config file created
```
### First contract
We store our Solidity source files (`.sol`) in a `contracts` directory. This is equivalent to the `src` directory you may be familiar with from other languages.
We can now write our first simple smart contract, called `Box`: it will let people store a value that can be later retrieved.
We will save this file as `contracts/Box.sol`. Each `.sol` file should have the code for a single contract, and be named after it.
{% code title="Box.sol" %}
```solidity
// contracts/Box.sol
// SPDX-License-Identifier: MIT
pragma solidity ^0.8.0;
contract Box {
uint256 private _value;
// Emitted when the stored value changes
event ValueChanged(uint256 value);
// Stores a new value in the contract
function store(uint256 value) public {
_value = value;
emit ValueChanged(value);
}
// Reads the last stored value
function retrieve() public view returns (uint256) {
return _value;
}
}
```
{% endcode %}
### Compiling Solidity
The Ethereum Virtual Machine (EVM) cannot execute Solidity code directly: we first need to compile it into EVM bytecode.
Our `Box.sol` contract uses Solidity 0.8 so we need to first [configure Hardhat to use an appropriate solc version](https://hardhat.org/config/#solidity-configuration).
We specify a Solidity 0.8 solc version in our `hardhat.config.js`
```
/**
* @type import('hardhat/config').HardhatUserConfig
*/
module.exports = {
solidity: "0.8.4",
};
```
# SQL Interview Questions
In this document, you can find the list of some commonly asked SQL interview questions.
Before starting with questions it's important that you revise some important topics related to SQL before the interview. For an SQL interview, you can prepare for the following topics.
{% hint style="warning" %}
If not all you should be confident in 80% of these topics and familiar with the concept of others.
{% endhint %}
### Here are some important topics to study before an SQL interview:
1. **SQL basics:** Make sure you have a solid understanding of SQL syntax and the basic commands (SELECT, FROM, WHERE, ORDER BY, GROUP BY, etc.) and can write simple queries.
2. **Joins:** Be familiar with different types of joins (INNER, LEFT, RIGHT, FULL OUTER) and how to use them to combine data from multiple tables.
3. **Aggregate functions:** Know how to use aggregate functions (SUM, COUNT, AVG, MAX, MIN) to calculate values based on groups of rows.
4. **Subqueries:** Be able to use subqueries to retrieve data from other tables based on a condition.
5. **Indexes:** Understand how indexes work and how to create and use them to improve query performance.
6. **Constraints:** Be familiar with different types of constraints (PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK) and how to use them to enforce data integrity.
7. **Views:** Understand how views work and how to create and use them to simplify complex queries.
8. **Stored procedures and functions:** Be able to create and use stored procedures and functions to encapsulate business logic.
9. **Transactions:** Understand how transactions work and how to use them to ensure data consistency and integrity.
10. **Window functions:** Be able to use window functions to perform complex calculations based on groups of rows.
11. **Data types:** Understand different data types (numeric, string, date/time, etc.) and how to use them in queries.
12. **Normalization:** Be familiar with the concept of database normalization and the different levels of normalization.
13. **Performance tuning:** Understand how to optimize query performance by using indexes, avoiding subqueries, and other techniques.
14. **Database design:** Be familiar with principles of database design, including entity-relationship modeling, data modeling, and schema design.
15. **Recent updates and features:** Be aware of any recent updates and features in the SQL language, and be able to discuss how they might impact your work.
It's also important to practice writing SQL queries on your own and to review and analyze sample queries and data sets. By mastering these topics and practicing your skills, you can feel confident and well-prepared for your SQL interview.
### Below are some commonly asked interview questions from basics to intermediate.
1. **What is SQL?** \
SQL (Structured Query Language) is a programming language used to manage and manipulate data in relational databases.
2. **What are the types of SQL statements?** \
There are several types of SQL statements, including SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, and ALTER.
3. **What is the difference between a primary key and a foreign key?** \
A primary key is a column or a set of columns that uniquely identifies each row in a table, while a foreign key is a column or a set of columns that refers to the primary key of another table.
4. **What is a join in SQL?** \
A join is a SQL operation used to combine data from two or more tables based on a related column between them. There are different types of joins, such as inner join, left join, right join, and full outer join.
5. **What is a subquery in SQL?** \
A subquery is a query nested inside another query. It can be used to retrieve data from one or more tables and use that data in the main query.
6. **What is the difference between a view and a table in SQL?** \
A view is a virtual table based on the result of a SQL statement, while a table is a physical structure that stores data. Views do not store data and are generally used to simplify complex queries.
7. **What is normalization in SQL?** \
Normalization is a process of organizing data in a database to reduce redundancy and improve data integrity. There are different levels of normalization, such as first normal form (1NF), second normal form (2NF), and third normal form (3NF).
8. **What is an index in SQL?** \
An index is a data structure used to improve the performance of queries by providing quick access to specific rows in a table. It is created on one or more columns in a table.
9. **What is a trigger in SQL?** \
A trigger is a SQL code that is automatically executed in response to a specific event, such as inserting, updating, or deleting data in a table.
10. **What is the difference between a stored procedure and a function in SQL?** \
A stored procedure is a precompiled block of SQL code that can be executed multiple times, while a function is a set of SQL statements that returns a single value. Functions can be used in SQL queries, while stored procedures cannot.
11. **Write a query to find the total number of customers in a database**
```sql
SELECT COUNT(*) FROM customers;
```
12. **Write a query to find the names of all customers who have placed an order in the past month.**
```sql
SELECT DISTINCT c.name
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date > DATEADD(month, -1, GETDATE());
```
13. **Write a query to find the top 5 best-selling products.**
```sql
SELECT p.name, SUM(oi.quantity) AS total_sold
FROM products p
JOIN order_items oi ON p.id = oi.product_id
GROUP BY p.name
ORDER BY total_sold DESC
LIMIT 5;
```
14. **Write a query to find the average order value for each customer.**
```sql
SELECT c.name, AVG(o.total) AS average_order_value
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;
```
15. **Write a query to find the number of orders placed by each customer in the past year.**
```sql
SELECT c.name, COUNT(*) AS total_orders
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.order_date > DATEADD(year, -1, GETDATE())
GROUP BY c.name;
```
16. **Write a query to find the top-selling product for each year.**
```sql
SELECT YEAR(order_date) AS year,
product_name,
total_sales
FROM (
SELECT YEAR(order_date),
product_name,
SUM(quantity * price) AS total_sales,
ROW_NUMBER() OVER (PARTITION BY YEAR(order_date) ORDER BY SUM(quantity * price) DESC) AS rn
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
GROUP BY YEAR(order_date), product_name
) q
WHERE rn = 1;
```
17. **Write a query to find the running total of sales for each month.**
```sql
SELECT order_date,
SUM(total_sales) OVER (ORDER BY order_date) AS running_total
FROM (
SELECT DATE_TRUNC('month', order_date) AS order_date,
SUM(quantity * price) AS total_sales
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
GROUP BY DATE_TRUNC('month', order_date)
) q;
```
18. **Write a query to find the difference in sales between each month and the previous month.**
```sql
SELECT current_month,
previous_month,
current_month_sales - previous_month_sales AS sales_difference
FROM (
SELECT DATE_TRUNC('month', order_date) AS current_month,
SUM(quantity * price) AS current_month_sales,
LAG(DATE_TRUNC('month', order_date)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS previous_month,
LAG(SUM(quantity * price)) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS previous_month_sales
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
GROUP BY DATE_TRUNC('month', order_date)
) q
WHERE previous_month IS NOT NULL;
```
19. **Write a query to find the top-selling product for each customer.**
```sql
SELECT customer_id,
product_name,
total_sales
FROM (
SELECT customer_id,
product_name,
SUM(quantity * price) AS total_sales,
ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY SUM(quantity * price) DESC) AS rn
FROM orders o
JOIN order_details od ON o.order_id = od.order_id
JOIN products p ON od.product_id = p.product_id
GROUP BY customer_id, product_name
) q
WHERE rn = 1;
```
20. **Write a query to find and remove duplicates from a table.**
```sql
-- To find the duplicates
SELECT column1, column2, COUNT(*) AS count
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;
-- To remove the duplicates
DELETE FROM table_name
WHERE id NOT IN (
SELECT MAX(id)
FROM table_name
GROUP BY column1, column2
);
```
21. **Write a query to find and merge duplicates in a table.**
```sql
-- To find the duplicates
SELECT column1, column2, COUNT(*) AS count
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;
-- To merge the duplicates
UPDATE table_name
SET column1 = new_value
WHERE id NOT IN (
SELECT MAX(id)
FROM table_name
GROUP BY column1, column2
);
DELETE FROM table_name
WHERE id NOT IN (
SELECT MAX(id)
FROM table_name
GROUP BY column1, column2
);
```
22. **Write a query to find rows that have similar but not exact values in a column.**
```sql
SELECT column_name,
COUNT(DISTINCT column_name) AS count
FROM table_name
GROUP BY SOUNDEX(column_name)
HAVING COUNT(DISTINCT column_name) > 1;
```
23. **Write a query to find and replace duplicates in a table.**
```sql
-- To find the duplicates
SELECT column1, column2, COUNT(*) AS count
FROM table_name
GROUP BY column1, column2
HAVING COUNT(*) > 1;
-- To replace the duplicates
UPDATE table_name
SET column1 = new_value
WHERE id NOT IN (
SELECT MAX(id)
FROM table_name
GROUP BY column1, column2
);
DELETE FROM table_name
WHERE id NOT IN (
SELECT MAX(id)
FROM table_name
GROUP BY column1, column2
);
```
# Power BI Interview Questions
This document will try to highlight some of the most common interview questions asked in Power BI
1. **How does Power Query work in Power BI?**\
Power Query is a powerful data transformation and preparation tool in Power BI that allows users to connect, shape, and transform data from various sources. It provides an intuitive and visual interface for performing data cleaning, merging, filtering, and other transformation tasks.
Here's how Power Query works in Power BI:
* Data Source Connection: Power Query enables you to connect to a wide range of data sources such as databases, files, web services, and more. You can establish connections to these sources within Power BI using the "Get Data" option.
* Data Transformation: Once connected, Power Query provides a series of intuitive transformations that allow you to shape and clean your data. You can perform tasks such as removing columns, changing data types, filtering rows, merging data from multiple sources, and creating calculated columns.
* Query Editor: The transformations are performed in the Query Editor, which is a visual interface where you can preview and modify the data before loading it into your Power BI model. The Query Editor provides a wide range of functions and operations for transforming and cleaning data, and you can apply these transformations step-by-step or by using predefined transformations.
* Applied Steps: As you perform transformations in the Query Editor, Power Query records each step as an "Applied Step." These steps form a sequence of operations that can be reviewed, modified, or rearranged to achieve the desired data transformation.
* Data Load: Once you have completed the data transformations, you can load the data into Power BI for visualization and analysis. Power Query automatically creates a data model based on the transformed data, which can be further enhanced by defining relationships, hierarchies, and measures.
* Refreshing Data: Power Query enables you to set up scheduled refreshes to keep your data up to date. This is particularly useful when dealing with dynamic data sources that regularly change.
Overall, Power Query simplifies the data preparation process in Power BI, allowing users to connect to various data sources, apply transformations, and load clean and structured data for analysis and visualization.
2. **Can you describe the process of data modeling in Power BI?**
Data modeling in Power BI involves organizing and structuring the data within your Power BI model to create relationships between tables and define measures and calculations. Here's an overview of the process:
* Import or Connect to Data: Start by importing or connecting to the relevant data sources in Power BI using the "Get Data" option. This could include databases, Excel files, CSV files, or other sources.
* Create Tables: Once the data is imported, you need to create tables in Power BI based on the data sources. Each table represents a distinct entity or category and should contain related fields or columns.
* Define Relationships: Establish relationships between the tables based on common fields. Power BI uses these relationships to perform data aggregation and slicing across multiple tables. You can define relationships by dragging and dropping fields between tables in the "Relationship" view or by using the Manage Relationships dialog box.
* Create Calculated Columns: Calculated columns allow you to create new columns in a table by defining custom calculations based on existing columns. These calculations can involve mathematical operations, string manipulations, logical expressions, or even referencing other tables. Calculated columns are computed during the data loading process and can be used for analysis and visualization.
* Write Measures: Measures are calculations that perform aggregations, such as sum, average, count, or distinct count, over the data. Measures are typically used in visualizations to display summary information or perform calculations based on user interactions. You can write measures using DAX (Data Analysis Expressions) language within the Power BI desktop.
* Enhance the Model: As you build your data model, you can enhance it by defining hierarchies, which provide drill-down capabilities, and by adding additional metadata, such as data categories, descriptions, and formatting.
* Test and Validate: It's important to test and validate your data model to ensure it behaves as expected. Verify that relationships are correctly established, measures provide accurate results, and calculations are working as intended.
* Visualize and Analyze: Once the data model is built and validated, you can start creating visualizations and reports using the tables, relationships, calculated columns, and measures. Power BI provides a wide range of visualization options to represent your data in meaningful ways.
3. **What is DAX (Data Analysis Expressions) and how is it used in Power BI?**\
DAX, which stands for Data Analysis Expressions, is a formula language used in Power BI and other Microsoft products like Power Pivot, Analysis Services, and Power Automate. It is specifically designed for data modeling and analysis tasks. DAX allows you to create custom calculations, perform aggregations, and define measures within your Power BI models. Here's an overview of DAX and its usage in Power BI:
* Formula Language: DAX is a formula language that resembles Excel formulas but provides additional capabilities for working with relational data. It includes a rich set of functions and operators that enable you to perform calculations, manipulate data, and create advanced expressions.
* Calculated Columns: In Power BI, you can use DAX to create calculated columns within tables. Calculated columns are computed during the data loading process and add new columns to your tables based on custom formulas. These columns can perform calculations using values from other columns or even reference values from related tables.
* Measures: Measures are one of the key components of DAX in Power BI. Measures allow you to perform aggregations and calculations on your data, such as sum, average, count, or distinct count. Measures are typically used in visualizations to display summary information or perform calculations based on user interactions. DAX measures are defined using functions like SUM, AVERAGE, COUNT, and more.
* Context and Filtering: DAX leverages the concept of context and filtering to calculate results dynamically based on the current context or user selections. DAX expressions can be influenced by filters applied to the data model, slicers, or interactions within visualizations. This allows for dynamic and interactive analysis of data.
* Time Intelligence: DAX provides specific functions and patterns for handling time-related calculations and analysis. These functions help with tasks such as year-to-date calculations, comparing values across different time periods, and working with calendar tables.
* Advanced Calculations: DAX supports advanced calculations and data modeling techniques such as creating calculated tables, defining hierarchies, handling relationships, and working with parent-child hierarchies.
* Performance Optimization: DAX allows for performance optimization techniques like using calculated tables instead of calculated columns, utilizing calculated measures instead of adding unnecessary columns to your tables, and leveraging DAX functions that optimize query performance.
By leveraging DAX in Power BI, you can create complex calculations, perform advanced analysis, and customize your data models to meet specific business requirements. It provides a powerful toolset for data modeling, analysis, and visualization within the Power BI ecosystem.
4. **How do you create relationships between tables in Power BI?**\
We can create relationships between tables using the following steps:
1. Open Power BI Desktop: Launch Power BI Desktop, and open or create a new report.
2. Import or Connect to Data: Import or connect to the data sources that contain the tables you want to relate. You can use the "Get Data" option to import data from various sources such as databases, files, or web services.
3. Create Tables: Once the data is imported, you need to create tables in Power BI based on the imported data. Each table should represent a distinct entity or category and contain related fields or columns.
4. Identify Common Fields: Look for fields (columns) that exist in multiple tables and can be used to establish relationships. These common fields act as keys to connect the tables.
5. Manage Relationships: In the Power BI Desktop, click on the "Modeling" tab in the ribbon, and then select "Manage Relationships." Alternatively, you can right-click on the field of one table and choose "Manage Relationships."
6. Define Relationships: In the "Manage Relationships" dialog box, click on the "New" button. Select the primary table (the table that contains the primary key or unique identifier) and the related table (the table that contains the foreign key).
7. Specify Relationship Type: Choose the relationship type based on the cardinality between the tables. The relationship types include "One-to-One," "One-to-Many," or "Many-to-Many."
8. Set Cross Filter Direction: Select the cross-filter direction based on how you want the relationship to filter data. You can choose "Both directions," "Single," or "Automatic" (which lets Power BI decide based on the data model).
9. Choose the Fields: In the dialog box, select the corresponding fields in each table that establish the relationship. Ensure that the data types of the fields match.
10. Validate and Create the Relationship: Click on the "OK" button to validate and create the relationship between the tables.
11. Check Relationship Icons: After creating the relationship, you will see visual indicators (icons) in the fields involved in the relationship. The icons indicate the relationship type and help you visually identify related fields.
12. Test and Modify: Test the relationships by creating visualizations that involve multiple tables. Ensure that the data is correctly filtered and aggregated based on the relationships. If necessary, you can modify or delete relationships using the "Manage Relationships" dialog box.
Remember to save your Power BI report to preserve the defined relationships. Creating relationships between tables is crucial for performing data analysis across multiple tables, enabling Power BI to generate accurate results and interactive insights based on the related data.
5. **What are calculated columns and measures in Power BI? What is the difference between them?**\
In Power BI, calculated columns and measures are two distinct components used for data analysis and calculations within tables. Here's an explanation of calculated columns and measures, as well as the differences between them:
Calculated Columns:
* Calculated columns are additional columns created within a table based on custom formulas or expressions.
* They are computed during the data loading process and become part of the table's structure.
* Calculated columns allow you to perform calculations using values from other columns within the same table.
* Calculated columns are useful for creating new data points or adding additional context to the data.
* Once created, calculated columns become part of the table's schema and can be used in various visualizations and calculations.
* However, it's important to note that calculated columns can impact performance, especially when dealing with large datasets.
Measures:
* Measures, also known as calculated measures, are calculations performed on aggregated data.
* Measures are typically used in visualizations to provide summary information or perform calculations based on user interactions.
* They are defined using the Data Analysis Expressions (DAX) language, which allows for complex calculations and functions.
* Measures perform calculations dynamically based on the context of the visualizations and user selections.
* Unlike calculated columns, measures do not create additional columns within the table structure.
* Measures can aggregate data across tables, leveraging relationships, and provide context-aware results.
* Measures are particularly useful for performing aggregations such as sum, average, count, or distinct count.
Differences between Calculated Columns and Measures:
* Purpose: Calculated columns are used for creating new columns with values derived from existing columns within the same table. Measures, on the other hand, perform calculations on aggregated data and provide summarized results for visualizations.
* Computation: Calculated columns are computed during the data loading process, while measures are calculated on the fly based on the visual context and user interactions.
* Storage: Calculated columns become part of the table structure and occupy storage space. Measures, however, do not create additional columns and do not impact the table structure or storage.
* Performance: Calculated columns can impact performance, especially with large datasets, as they are computed and stored in memory. Measures are dynamic calculations that leverage aggregations, resulting in better performance.
* Usage: Calculated columns are used as data points or additional context within the same table. Measures are used in visualizations to provide aggregated results and perform calculations across tables.
In summary, calculated columns are used to create new columns within a table, whereas measures are calculations performed on aggregated data for visualizations. Each has its own purpose and usage, and understanding the differences between them is important for effective data analysis in Power BI.
6. **How can you enhance the performance of Power BI reports and dashboards?**\
To enhance the performance of Power BI reports and dashboards, consider implementing the following best practices:
* Optimizing DAX used in calculated columns and measures.
* Limiting the number of visuals on each page of the report to a minimum
* As a rule of thumb, one should not use more than 7-10 visuals on a single page
* Data Modeling:
* Optimize data models by minimizing the number of columns and rows to only what's necessary for analysis.
* Use calculated measures instead of calculated columns wherever possible, as measures are computed on the fly and consume less memory.
* Use calculated tables sparingly and only when necessary, as they can increase the data model size and processing time.
* Avoid unnecessary relationships and ensure relationships are appropriately defined and optimized.
* Avoiding unwanted many-to-many and bi-directional relationships.
* Data Source:
* Use query folding to push data transformations and filtering operations to the data source, reducing data loading times.
* Filter data at the source whenever possible to reduce the amount of data loaded into Power BI.
* Data Refresh:
* Optimize data refresh schedules based on the frequency and freshness requirements of the data.
* Utilize incremental refresh to load only the incremental changes instead of refreshing the entire dataset.
* Query Optimization:
* Use Power Query Editor to clean and transform data efficiently, avoiding unnecessary or computationally expensive operations.
* Leverage query folding to delegate filtering and transformations to the data source.
* Remove any unused or unnecessary steps in Power Query to reduce data loading and processing time.
* Report Design:
* Minimize the number of visuals on each page and limit the amount of data displayed in each visual.
* Use visual-level filters and slicers to reduce the amount of data rendered in visuals.
* Avoid using too many visuals with high cardinality data (e.g., large tables with many rows) on a single page.
* Utilize drill-through and drill-down functionalities to provide detailed information on demand instead of displaying all details upfront.
* Use summarized tables or aggregates for large datasets to speed up visual rendering.
* Visualization Optimization:
* Optimize visuals by reducing unnecessary customizations and effects that may impact performance.
* Limit the number of data points displayed in charts and graphs, especially for line charts or scatter plots.
* Use appropriate visualizations that convey the desired information effectively while maintaining performance.
* Use of Filters:
* Apply filters at the appropriate level, such as page, visual, or report level, to reduce the amount of data processed and rendered.
* Avoid using unnecessary or redundant filters that do not contribute to the analysis.
* Monitor Performance:
* Utilize the Performance Analyzer tool in Power BI to identify performance bottlenecks and optimize queries, visuals, and data models accordingly.
* By disabling unwanted visual interactions
By following these performance optimization techniques, you can ensure that your Power BI reports and dashboards load quickly, respond smoothly to user interactions, and provide an optimal user experience.
7. **Can you explain the concept of Power BI gateways and their purpose?**\
Power BI gateways are an integral part of the Power BI ecosystem and play a crucial role in connecting on-premises data sources to Power BI services. Let's delve into the concept of Power BI gateways and their purpose:
* On-Premises Data Connectivity: Power BI gateways enable Power BI services to securely connect and access data from on-premises data sources, such as databases, files, and local servers. These data sources typically reside within the organization's private network and are not directly accessible from the cloud-based Power BI service.
* Data Refresh and Direct Query: Power BI gateways facilitate scheduled data refresh and real-time data access through Direct Query for on-premises data sources. They establish a connection between the Power BI service and the on-premises data, ensuring that the data in Power BI reports and dashboards remains up-to-date and reflects the latest changes from the on-premises sources.
* Gateway Modes: Power BI gateways offer two modes: the Personal mode and the Enterprise mode.
* Personal mode: This mode is suitable for individual users who need to connect to on-premises data sources for their personal reports. It is simple to install and manage but does not provide centralized control and administration.
* Enterprise mode: This mode is designed for organizations with multiple users and data sources. It offers centralized administration, scalability, and better control over data source access and refresh schedules.
* Data Security and Encryption: Power BI gateways prioritize data security by establishing secure connections between on-premises data sources and Power BI services. Data transmitted through the gateway is encrypted to protect sensitive information.
* High Availability and Load Balancing: Enterprise mode gateways support high availability and load balancing features. You can configure multiple gateway instances to distribute the load across them, ensuring better performance and redundancy in case of gateway failures.
* Gateway Cluster: In the Enterprise mode, multiple gateways can be grouped together to form a gateway cluster. This allows for enhanced scalability, load balancing, and failover capabilities.
* Cloud Data Sources: In addition to on-premises data sources, Power BI gateways also support connecting to cloud-based data sources such as Azure SQL Database, Azure Data Lake Storage, and more. This enables seamless integration of both on-premises and cloud data sources within Power BI reports and dashboards.
* Power Platform Integration: Power BI gateways are part of the Microsoft Power Platform, which includes Power Apps, Power Automate (previously known as Microsoft Flow), and Power Virtual Agents. This integration enables data sharing and connectivity across the Power Platform services, allowing for comprehensive data-driven solutions.
In summary, Power BI gateways serve as a bridge between the on-premises data sources and the cloud-based Power BI services. They facilitate secure data connectivity, enable data refresh, and ensure real-time access to on-premises data. Power BI gateways play a vital role in integrating on-premises and cloud data sources to deliver comprehensive and up-to-date insights through Power BI reports and dashboards.
8. **How do you publish and share reports and dashboards in Power BI?**\
Publishing and sharing reports and dashboards in Power BI involves the following steps:
* Prepare Your Report: Before publishing, ensure that your report is complete and ready for sharing. Create visuals, add filters, and organize the report layout as desired.
* Save the Report: Save the Power BI report file (.pbix) on your local machine or network drive.
* Sign in to Power BI: Open Power BI Desktop or navigate to the Power BI service (app.powerbi.com) in your web browser. Sign in using your Power BI account credentials.
* Publish to Power BI Service:
* Power BI Desktop: In Power BI Desktop, click on the "Publish" button in the Home tab of the ribbon. Choose "Publish to Power BI" to upload the report to your Power BI workspace.
* Power BI Service: If you are using the Power BI service directly, click on the "Upload" button on the home page or workspace. Select the .pbix file from your local machine and upload it.
* Select Destination Workspace: Choose the workspace where you want to publish the report. You can select an existing workspace or create a new one.
* Configure Settings (Optional): Specify any additional settings, such as the report's visibility, permissions, and data refresh schedule. You can also set up row-level security, sharing options, and other advanced configurations.
* Publish the Report: Click on the "Publish" button to upload the report to Power BI. The report will be published to the selected workspace.
* Share the Report and Dashboard:
* Within Power BI Service: Once the report is published, you can share it with others by selecting the report in the workspace, clicking on the "Share" button, and specifying the recipients or groups with whom you want to share the report. You can grant them view or edit access based on their needs.
* Embedding: You can embed Power BI reports or dashboards in other applications or websites using the Power BI embedded feature. This allows users to access and interact with the report within the context of the application.
* Collaborate and Collaborate: Collaborate with other users by granting them access to the report and dashboard, allowing them to view, explore, and interact with the data. You can also assign specific roles and permissions to control user access and editing capabilities.
* Schedule Data Refresh (if applicable): If your report relies on data from a data source that requires regular updates, configure the data refresh schedule to ensure the report reflects the latest data.
* Monitor and Manage: Monitor usage, access, and performance of your published reports and dashboards using the Power BI service's administration and monitoring features. You can manage permissions, revoke access, or make updates as needed.
By following these steps, you can publish your Power BI report to the Power BI service, share it with others, collaborate on data analysis, and ensure that the insights are accessible to the intended audience.
9. **Have you worked with Power BI Embedded? If so, can you explain how it is used?**\
Power BI Embedded is a feature of Power BI that allows you to embed Power BI reports, dashboards, and visualizations into your own custom applications or websites. It enables you to integrate interactive data visualizations seamlessly within your application's user interface, providing users with data-driven insights without requiring them to leave your application.
Here's how Power BI Embedded is typically used:
1. Application Integration: Power BI Embedded allows you to embed Power BI reports and dashboards directly into your application or website. This integration enables you to provide data visualization capabilities to your application users without them needing a separate Power BI account or leaving your application's context.
2. Embedded Analytics: By integrating Power BI reports and dashboards, you can empower your users with interactive and visually appealing data visualizations. They can explore data, apply filters, drill down into details, and gain insights directly within your application.
3. Customization: Power BI Embedded provides extensive customization options to ensure the embedded reports align with your application's branding and user experience. You can customize the appearance, layout, and interactive elements to match your application's look and feel.
4. Security and Authentication: Power BI Embedded supports authentication mechanisms to ensure secure access to embedded reports. You can implement authentication protocols such as Azure Active Directory (Azure AD) to control user access and permissions within your application.
5. Scalability: Power BI Embedded offers scalable infrastructure, allowing your application to handle varying user loads and data volumes. It can handle simultaneous requests and provide responsive and performant embedded reports.
6. Licensing and Pricing: Power BI Embedded has its own licensing model separate from Power BI Pro or Premium. It offers different pricing tiers based on usage, including per-user or capacity-based options. You can choose the appropriate licensing model based on your application's requirements and expected user base.
7. Management and Monitoring: Power BI Embedded provides management and monitoring capabilities to track usage, performance, and health of embedded reports. You can monitor the embedded analytics usage, data refreshes, and troubleshoot any issues using the Power BI service admin portal.
It's important to note that working with Power BI Embedded typically requires development skills and knowledge of web development technologies such as JavaScript, APIs, and authentication mechanisms. Additionally, understanding the Power BI Embedded documentation and SDKs is crucial for successful integration.
By utilizing Power BI Embedded, you can enrich your applications with interactive data visualizations, empower users with data insights, and provide a seamless user experience within your application environment.
10. **How can you apply filters and slicers in Power BI reports?**\
In Power BI reports, we can apply filters and slicers to interactively control the data displayed in your visuals. Filters and slicers allow users to focus on specific subsets of data and perform targeted analysis. Here's how you can apply filters and slicers in Power BI reports:
* Visual-Level Filters:
* Select the visual (e.g., chart, table) to which you want to apply the filter.
* Locate the "Visualizations" pane on the right side of the Power BI Desktop or the top of the Power BI service.
* Expand the "Filters" section in the "Visualizations" pane.
* Drag and drop the desired field from the data model into the "Filters" section.
* Configure the filter settings, such as selecting specific values, applying relative date filtering, or setting a top N filter.
* Page-Level Filters:
* Open the "Filters" pane in the Power BI Desktop or the Power BI service.
* Drag and drop the desired field from the data model into the "Filters" pane.
* Configure the filter settings, such as selecting specific values, applying relative date filtering, or setting a top N filter.
* The page-level filter will be applied to all visuals on the current report page.
* Report-Level Filters:
* Open the "Filters" pane in the Power BI Desktop or the Power BI service.
* Switch to the "Report level" tab in the "Filters" pane.
* Drag and drop the desired field from the data model into the "Filters" pane.
* Configure the filter settings, such as selecting specific values, applying relative date filtering, or setting a top N filter.
* The report-level filter will be applied to all visuals across all report pages.
* Slicers:
* Slicers are visual elements that allow users to select values to filter the entire report or a specific page.
* To add a slicer, locate the "Visualizations" pane in Power BI Desktop or the Power BI service.
* Click on the "Slicer" icon in the "Visualizations" pane.
* Drag and drop the desired field from the data model into the slicer.
* Customize the slicer's appearance, such as changing the layout, style, or slicer type (e.g., dropdown, list, or checkbox).
* Interacting with Filters and Slicers:
* Users can interact with applied filters and slicers to select or deselect values, which will dynamically update the displayed data in the visuals.
* You can use single-select slicers, multi-select slicers, or custom slicer functionalities based on your requirements.
* Filters and slicers can also be used in combination to provide more refined data filtering options.
* Cross-Filtering and Highlighting:
* Power BI supports cross-filtering, where applying a filter or slicer to one visual dynamically filters other related visuals based on the selected values.
* Visuals can also be configured to highlight specific data points based on the applied filters or slicers, allowing users to focus on specific insights.
Remember to save and refresh your report after applying filters and slicers to ensure that the changes are reflected in the published report. By leveraging filters and slicers effectively, you can provide interactive data exploration capabilities and empower users to analyze data based on their specific criteria and preferences.
11. **Have you used Power BI's Q\&A feature? How does it work?**\
Power BI's Q\&A (Question and Answer) feature allows users to query their data by typing natural language questions and receiving visualizations and insights in response. Here's how it works:
* Enable Q\&A: To use the Q\&A feature, ensure that it is enabled for the Power BI report or dataset. Q\&A needs to be enabled during the report development phase.
* Type Natural Language Questions: In the Power BI service or the Q\&A Explorer in Power BI Desktop, locate the Q\&A search box. Type your question in plain, conversational language. For example, you can ask questions like "Total sales by region" or "Show me a bar chart of revenue by product category."
* Automatic Query Generation: Power BI uses natural language processing (NLP) and advanced algorithms to understand the query and generate a corresponding query based on the available data model and relationships. It translates the natural language question into a structured query language (DAX) query.
* Visual and Verbal Response: Power BI Q\&A provides both visual and verbal responses to your queries. It presents visualizations in the form of charts, tables, or other relevant visual types, along with verbal responses in the form of textual insights or summaries.
* Interact and Refine Results: After receiving the initial response, you can further interact with the visualizations. You can apply filters, change chart types, drill down into details, or refine the question to get more specific insights.
* Synonyms and Clarifications: Power BI's Q\&A feature supports synonyms and clarifications to improve the accuracy of the responses. Synonyms allow you to specify alternative names or phrases for specific data elements, while clarifications let you provide additional context or constraints to refine the query interpretation.
* Natural Language Modeling: Power BI's Q\&A feature employs natural language modeling to learn from user interactions. Over time, it improves its understanding of queries and provides more accurate and relevant responses based on user feedback.
* Data Preparation for Q\&A: To optimize the Q\&A experience, it's essential to prepare the data model and metadata appropriately. This includes defining relationships between tables, creating user-friendly field names and descriptions, and specifying synonyms and clarifications for better query comprehension.
* Q\&A Settings and Customization: Power BI provides settings and options to customize the Q\&A experience. Administrators can control which visuals and data sources are available for Q\&A, set up synonyms, and manage query suggestions.
* Natural Language Generation: In addition to Q\&A, Power BI also supports natural language generation (NLG), which enables the automatic generation of textual narratives based on data insights. NLG can be used to create textual summaries or explanations of visualizations to further enhance the communication of data-driven insights.
Power BI's Q\&A feature bridges the gap between data exploration and natural language queries, making it easier for users to derive insights from their data without needing to write complex queries or know the underlying data structure. It enables a more intuitive and interactive experience for users to interact with their data and gain meaningful insights.
12. **Can you describe the process of creating and using bookmarks in Power BI?**\
Bookmarks in Power BI allow you to capture the current state of a report page, including filters, slicer selections, visual interactions, and other settings. You can then create and apply bookmarks to navigate between different report views or save specific report states for future reference. Here's an overview of the process of creating and using bookmarks in Power BI:
* Creating Bookmarks:
* Open the Power BI report in Power BI Desktop.
* Navigate to the report page where you want to create a bookmark.
* Arrange the visuals, apply filters, slicers, or any other desired interactions to represent the desired report state.
* Go to the "View" tab in the ribbon, then click on the "Bookmarks" pane to open it.
* Click on the "Add" button in the Bookmarks pane to create a new bookmark.
* Give the bookmark a descriptive name to indicate the intended state or view it represents.
* Choose the options you want to include in the bookmark, such as the current page, visuals, filters, and display settings.
* Click "OK" to save the bookmark.
* Using Bookmarks:
* With bookmarks created, you can now use them to navigate between different report states or views.
* In Power BI Desktop, you can click on a bookmark in the Bookmarks pane to apply it. This instantly changes the report to the saved state defined by the bookmark, including filters, slicers, and visual interactions.
* In the Power BI service, you can find the bookmarks in the Bookmarks pane on the right side of the report viewer. Clicking on a bookmark applies it to the report, just like in Power BI Desktop.
* Bookmark Interactions and Options:
* Bookmarks can be set up to interact with other visuals. For example, you can configure a bookmark to show or hide specific visuals when applied.
* You can choose to include or exclude visuals, filters, slicers, drillthrough states, and other settings in a bookmark. This allows you to define precisely what gets captured and applied when using a bookmark.
* Bookmarks can also be used to reset or clear selections by specifying the desired state as the bookmark.
* Applying Multiple Bookmarks:
* You can create and apply multiple bookmarks within a report. This allows users to switch between different report views quickly.
* Bookmarks can be applied sequentially, giving users a step-by-step walkthrough experience within a report.
* Bookmarks can also be used in combination with buttons or other navigation elements to create interactive dashboards or guided analytics experiences.
* Bookmarks in Power BI Service:
* When you publish the report to the Power BI service, bookmarks are available for users to apply and interact with.
* Users can view and apply bookmarks in the Power BI service by opening the report, navigating to the desired report page, and using the Bookmarks pane.
By utilizing bookmarks in Power BI, you can provide users with predefined report views, guided navigation, interactive storytelling, and customized user experiences within your reports. Bookmarks enhance the interactivity and flexibility of your reports by capturing and applying specific report states.
13. **How can you schedule data refresh in Power BI? What are the considerations for refreshing data?**\
To schedule data refresh in Power BI, you can follow these steps:
* Power BI Service:
* Open the Power BI service (app.powerbi.com) and sign in to your account.
* Navigate to the workspace containing the dataset you want to refresh.
* Open the dataset by clicking on it.
* In the dataset view, click on the "Schedule Refresh" option in the toolbar.
* Configure the refresh settings by specifying the frequency, time zone, and credentials for the data source.
* Save the refresh schedule.
* Power BI Desktop:
* Open the Power BI Desktop application.
* Open the report that uses the dataset you want to schedule for refresh.
* In the Home tab, click on the "Transform data" button to open Power Query Editor.
* In the Power Query Editor, click on "Manage Parameters" in the Home tab.
* Configure the parameters, such as connection details and credentials, required for refreshing the dataset.
* Close the Power Query Editor and save the changes to the report.
* Publish the report to the Power BI service.
* Once the report is published, follow the steps mentioned in the Power BI Service section to schedule the data refresh.
Considerations for data refresh in Power BI:
* Data Source Support: Ensure that the data source you're using supports scheduled refresh in Power BI. Commonly supported data sources include SQL databases, Excel files, SharePoint lists, OData feeds, and cloud-based services like Azure SQL Database, Azure Data Lake, etc. Some data sources may require additional configuration or data gateway setup.
* Credentials and Authentication: Power BI requires valid credentials to access and refresh data from the data source. Ensure that the credentials used for data refresh have the necessary permissions to retrieve and update the data.
* Data Volume and Performance: Consider the size of your dataset and the performance implications of refreshing the data. Large datasets or frequent refresh intervals may impact the performance of your data source, especially if it involves complex queries or extensive data transformations.
* Refresh Frequency: Determine the appropriate refresh frequency based on the data's freshness requirements. You can choose options like daily, weekly, or even more frequent intervals, depending on how frequently your data changes.
* Scheduled Refresh Limitations: Be aware of the limitations and restrictions on scheduled data refresh in Power BI, such as the maximum number of refreshes per day, maximum duration of a refresh, or restrictions on certain data sources.
* Data Gateway Configuration: If your data source is on-premises or behind a firewall, you need to set up a data gateway to enable scheduled refresh. The data gateway facilitates the secure connection between the Power BI service and your on-premises data source.
* Error Handling and Monitoring: Configure notifications or alerts to receive notifications in case of any refresh failures or errors. Regularly monitor the refresh history and ensure that the scheduled refreshes are executing successfully.
By setting up scheduled data refresh in Power BI, you can ensure that your reports and dashboards always reflect the most up-to-date data, providing users with accurate and timely insights.
14. What are Power BI gateways and when do we need to use them?\
Power BI gateways are software components that enable connectivity between Power BI and on-premises data sources. They act as intermediaries, allowing Power BI to securely access and refresh data from on-premises or private network data sources. Here's an overview of Power BI gateways and when you need to use them:
* Types of Power BI Gateways:
* On-premises Data Gateway: This gateway is used to connect Power BI to on-premises data sources such as SQL Server, Oracle, SharePoint, and file shares. It enables data transfer between the Power BI service and on-premises data sources by establishing a secure connection.
* Power BI Personal Gateway: This gateway is primarily used for individual users and allows direct connectivity from Power BI Desktop to on-premises data sources without the need for a data gateway cluster.
* Use Cases for Power BI Gateways:
* Direct Query: If you have large or frequently changing datasets in on-premises data sources, using Direct Query mode in Power BI allows you to retrieve real-time data without importing it into the Power BI service. To establish this connection, you need to use the On-premises Data Gateway.
* Scheduled Data Refresh: When you want to refresh data from on-premises data sources on a scheduled basis, Power BI gateways are required. Gateways enable the Power BI service to connect to the data source, retrieve updated data, and refresh the dataset automatically.
* Live Connection: Power BI allows you to create live connections to on-premises Analysis Services models or Azure Analysis Services. In such cases, you need to configure the appropriate gateway to establish the connection and enable interactive exploration of data.
* Gateway Installation and Configuration:
* Install the appropriate gateway software on a machine that has access to the on-premises data sources. The machine should meet the specified requirements and have network connectivity to the Power BI service.
* Configure the gateway by providing necessary information such as connection details, credentials, and data source settings.
* Register the gateway with the Power BI service, linking it to your Power BI account and the specific workspace or dataset that requires access to on-premises data.
* Security and Data Privacy:
* Power BI gateways ensure secure data transfer between the Power BI service and on-premises data sources. The communication is encrypted to protect sensitive data.
* Gateways respect the data source's existing security mechanisms, such as authentication methods or role-based access control, ensuring that only authorized users can access the data.
* Data Refresh and Monitoring:
* After configuring the gateway, you can schedule and monitor data refreshes from on-premises data sources in the Power BI service. You can set up notifications for refresh failures and monitor the refresh history for troubleshooting purposes.
By using Power BI gateways, you can establish secure connections between Power BI and on-premises data sources, enabling real-time data access, scheduled data refresh, and live connections. They are essential when your data resides in on-premises or private network environments and ensures that Power BI reports and dashboards are always up-to-date with the latest data from these sources.
15. **Can you explain the difference between Power BI Desktop, Power BI Service, and Power BI Report Server?**\
the key differences between Power BI Desktop, Power BI Service, and Power BI Report Server:
* Power BI Desktop:
* Power BI Desktop is a Windows application that you install on your local machine.
* It is used for creating, designing, and authoring Power BI reports and dashboards.
* Power BI Desktop provides a robust set of data modeling, transformation, visualization, and analytics capabilities.
* It allows you to connect to various data sources, perform data transformations using Power Query, create data models using Power Pivot, and build interactive visualizations using a wide range of chart types, tables, and custom visuals.
* Power BI Desktop is primarily used by report authors and developers during the report creation and testing phase.
* You can publish Power BI Desktop reports to the Power BI Service or Power BI Report Server for sharing and collaboration.
* Power BI Service:
* Power BI Service, also known as Power BI online or Power BI cloud, is a cloud-based platform for sharing, collaborating, and consuming Power BI reports and dashboards.
* It is a web-based application that you access through a browser, allowing you to view and interact with reports and dashboards created in Power BI Desktop.
* Power BI Service provides features such as sharing reports with colleagues, setting up data refresh schedules, creating and managing dashboards, creating content packs for sharing, and collaboration through features like comments and data-driven alerts.
* Power BI Service supports automatic cloud-based data refresh, allowing you to keep your reports up to date with the latest data.
* It also offers additional capabilities like natural language Q\&A, embedding reports in other applications, and advanced sharing and security options.
* Power BI Service is designed for end-users, business analysts, and stakeholders who consume and interact with the reports and dashboards.
* Power BI Report Server:
* Power BI Report Server is an on-premises reporting solution that allows you to host Power BI reports and dashboards within your organization's network infrastructure.
* It is a standalone product separate from the cloud-based Power BI Service.
* Power BI Report Server enables you to publish and share Power BI reports securely within your organization, without the need for data to leave your network.
* It provides a web portal for viewing and interacting with reports, similar to the Power BI Service.
* Power BI Report Server also supports scheduled data refresh, supports Active Directory authentication, and provides granular control over access and permissions.
* Power BI Report Server is suitable for organizations with strict data privacy and security requirements or those that prefer to keep their data on-premises.
In summary, Power BI Desktop is used for report authoring and development, Power BI Service is the cloud-based platform for sharing and collaboration, and Power BI Report Server is an on-premises solution for hosting Power BI reports within the organization's network. The three components complement each other and provide flexibility for different deployment scenarios and user requirements.
16. [**How do you handle security and access control in Power BI?**](#user-content-fn-1)[^1]\
Power BI provides several security and access control mechanisms to ensure the confidentiality, integrity, and availability of your data and reports. Here are some key aspects of security and access control in Power BI:
* Power BI Workspace:
* Workspaces in Power BI serve as containers for organizing and managing content, including reports, dashboards, datasets, and dataflows.
* You can create workspaces and assign permissions to control access to the content within them.
* Workspace access can be managed at the individual user level or through security groups, allowing you to define who can view, edit, or share content within a workspace.
* App Workspaces:
* App Workspaces provide a dedicated space for collaborative report development and sharing within a team or department.
* You can assign different roles to users within an App Workspace, such as members, contributors, or admins, to control their capabilities and access levels.
* App Workspaces also allow you to define row-level security (RLS) rules, which enable you to restrict data access based on user roles or attributes.
* Sharing and Embedding:
* Power BI offers various sharing options to control how reports and dashboards are shared with others.
* You can share reports and dashboards with individuals or groups within your organization, or with external users through secure sharing methods like Azure B2B.
* Additionally, Power BI provides embedding capabilities, allowing you to embed reports and dashboards in other applications or websites while maintaining security controls.
* Row-Level Security (RLS):
* RLS enables you to restrict data access at the row level based on user roles or attributes.
* By defining RLS rules, you can ensure that users only see the data they are authorized to access, even when viewing the same report or dashboard.
* RLS can be configured based on roles defined in Power BI or synchronized with roles from an external source like Azure Active Directory.
* Data Source Security:
* Power BI respects the underlying security mechanisms of the data sources it connects to, such as SQL Server or Analysis Services.
* Users accessing Power BI reports and dashboards will only see the data they have permissions to access in the data source itself.
* It is essential to ensure that the data sources have appropriate security measures in place to control access to the data.
* Azure Active Directory Integration:
* Power BI integrates tightly with Azure Active Directory (Azure AD), allowing you to leverage its identity and access management capabilities.
* You can use Azure AD for user authentication, enforce multi-factor authentication (MFA), and manage user access through groups and role assignments.
* Auditing and Monitoring:
* Power BI provides auditing and monitoring capabilities to track user activities, access patterns, and changes to content.
* Auditing logs can be used to analyze and investigate security incidents, compliance, and governance requirements.
It's important to carefully plan and implement security and access control measures in Power BI to safeguard sensitive data and ensure that users only have access to the information they are authorized to see. By leveraging the various security features and best practices provided by Power BI, you can establish a robust security framework for your Power BI deployment.
17. **What is row level-security, how to create it and when do we use it?**\
Row-level security (RLS) is a security feature in Power BI that allows you to control data access at the row level based on user roles or attributes. It enables you to restrict the data that users can see within a report or dashboard, ensuring that each user only sees the subset of data that is relevant to them.
To create row-level security in Power BI, follow these steps:
* Define Roles:
* In Power BI Desktop, go to the "Modeling" tab and click on "Manage Roles."
* Click on "Create" to define a new role.
* Assign a name to the role and specify any necessary filters or rules that define the data the role should have access to.
* You can create multiple roles with different filters or rules to accommodate different access requirements.
* Apply Roles to Tables:
* In the "Manage Roles" window, select a role, and then select the tables to which the role applies.
* Define filters or rules specific to each table to restrict the data that the role can access.
* Repeat this step for each role and table combination as needed.
* Publish to Power BI Service:
* After defining the roles and applying them to tables in Power BI Desktop, publish the report to the Power BI service.
* In the Power BI service, navigate to the dataset associated with the report.
* Open the "Security" tab for the dataset and assign users or groups to the appropriate roles.
*When to use row-level security:*\
Let's consider a realistic scenario where row-level security can be applied in Power BI:
Scenario: Sales Dashboard with Regional Data Access
Assume you work for a multinational company that has a centralized sales dashboard in Power BI. The company operates in multiple regions, and each region has its sales team responsible for specific territories. To ensure data security and privacy, you need to implement row-level security.
Here's how you can set up row-level security for this scenario:
* Define Roles:
* Create roles for each region, such as North America, Europe, Asia-Pacific, and so on.
* Assign appropriate names to each role, indicating the corresponding region.
* Apply Roles to Tables:
* In Power BI Desktop, go to the "Modeling" tab and click on "Manage Roles."
* Select a role (e.g., North America) and apply it to the sales-related tables in your data model.
* Define a filter or rule that restricts the data to the specific region. For example, you can set a filter on the "Region" column to include only the corresponding region for each role.
* Publish to Power BI Service:
* Publish the report to the Power BI service, ensuring the dataset is associated with the report.
* In the Power BI service, navigate to the dataset and open the "Security" tab.
* Assign users or groups to the appropriate roles based on their region. For example, assign users from the North America region to the North America role.
Result:
* Users assigned to the North America role will only see sales data related to the North America region in the sales dashboard, while users assigned to other roles will have access to the data specific to their respective regions.
* This ensures that each regional sales team can access and analyze data relevant to their territories without exposing data from other regions.
By implementing row-level security in this scenario, you maintain data privacy and confidentiality, ensuring that sensitive sales information is accessible only to authorized users based on their assigned roles.
[^1]:
# Exercise 0
Function in SQL
* Create a database named `practice`.
* Run the below queries to generate a table.
```
CREATE TABLE SalesData (
SaleID INT PRIMARY KEY,
SaleDate DATE,
ProductName NVARCHAR(50),
Quantity INT,
UnitPrice DECIMAL(10, 2),
Discount DECIMAL(5, 2),
Region NVARCHAR(50),
Salesperson NVARCHAR(50)
);
INSERT INTO SalesData (SaleID, SaleDate, ProductName, Quantity, UnitPrice, Discount, Region, Salesperson)
VALUES
(1, '2024-01-01', 'Laptop', 2, 1500.00, 0.10, 'North', 'Alice'),
(2, '2024-01-05', 'Mouse', 5, 25.00, 0.05, 'South', 'Bob'),
(3, '2024-01-10', 'Keyboard', 3, 50.00, 0.15, 'East', 'Charlie'),
(4, '2024-01-15', 'Monitor', 1, 300.00, 0.20, 'West', 'Alice'),
(5, '2024-01-20', 'Laptop', 1, 1500.00, 0.00, 'North', 'Eve'),
(6, '2024-01-25', 'Mouse', 10, 25.00, 0.10, 'South', 'Bob'),
(7, '2024-01-30', 'Keyboard', 7, 50.00, 0.00, 'East', 'Charlie'),
(8, '2024-02-01', 'Monitor', 2, 300.00, 0.05, 'West', 'Alice'),
(9, '2024-02-05', 'Laptop', 3, 1500.00, 0.15, 'North', 'Eve'),
(10, '2024-02-10', 'Mouse', 4, 25.00, 0.10, 'South', 'Bob');
```
Write SQL Queries to solve for the following:
1. Calculate the total revenue `(Quantity * UnitPrice)` for all sales.
2. Find the average discount given across all products.
3. Determine the maximum quantity sold in a single transaction.
4. Count the number of sales transactions in the North region.
5. Write a SELECT query to extract the first three characters of each product name.
6. Use a SELECT query to convert all salesperson names to uppercase.
7. Write a SELECT query to replace "Laptop" with "Notebook" in the product names.
8. Use a SELECT query to extract the month from each sale date.
9. Write a SELECT query to calculate the number of days between the earliest and latest sale dates.
10. Use a SELECT query to calculate the year-to-date total revenue.
11. Write a SELECT query to round the UnitPrice to the nearest whole number for all products.
12. Use a SELECT query to calculate the square of the quantity sold for each transaction.
13. Use a SELECT query to categorize sales as "High Value" if the revenue exceeds $1000, otherwise "Low Value."
14. Write a SELECT query to determine if any sales had a discount greater than 10%.
15. Write a SELECT query to calculate the average discount, ignoring any NULL values (simulate NULLs if needed).
16. Use a SELECT query to convert the SaleDate into a formatted string like YYYY-MM-DD and display it.
17. Write a SELECT query to cast the UnitPrice as an integer and display it.
# Exercise 1
1. Create a Database named `IMDB`
2. Download the SQL file from [this link](https://arbrecreations-my.sharepoint.com/:u:/g/personal/mrinmais_arbre_in/EUF1TWUESkhIruCqyTw8NpoBB_9Ld2FFJm2JK76OydCOqQ?e=POeYlp) and run the code present in it inside the `IMDB` database.\
(Make sure that the tables are created inside `IMDB` database only)
3. Write an SQL statement to display all the records from the `movies` table
4. Write an SQL statement to display the count of the rows in the `movies` table.
5. Write an SQL statement to display all the `genre` values in the `genre` table in capital letters.
6. Write an SQL statement to display all the records in the genre table that has an `space` in the genre value.
7. Write an SQL statement to display all the records in the movies table in the movie names starting with `##` and also `---`
8. Write an SQL statement to update all the movie names where the name starts with `##` or also `---` and remove the `##` and `---`
9. Write an SQL statement to delete all the records in the `genre` table where the `genre` value is **Invalid Genre**
10. Write an SQL statement to delete all the records where `movie_id` is **32217** and the `genre` is **Sport**
11. Write an SQL statement to display all the records where domestic earning is greater than **100000** and worldwide earning is less than **9000000**
# Exercise 2
1. Create a Database named `northwind`.
2. Download the SQL file from [this link](https://github.com/microsoft/sql-server-samples/blob/master/samples/databases/northwind-pubs/instnwnd.sql) and run the code present in it inside the `northwind` database.\
(Make sure that the tables are created inside `northwind` database only)
3. Write an SQL query to display the `ProductID`, `ProductName`, `UnitPrice` from `Products` table in decreasing order of the `UnitPrice`.
4. Write an SQL query to display all the **distinct** `CategoryName` in `Categories` table.
5. Write an SQL query to **count** the total number of **unique** `OrderId` in `Order Details` table.
6. Write an SQL query to display the `OrderID`, `Employee Full Name` (example: Mr. John Doe), `Customer Company Name` and `OrderDate` in such a way that the Latest Order is displayed at the top.
7. Write an SQL query to display the **most expensive** `UnitPrice` in `Products` table for each `CategoryName`. Also, arrange it in ascending order of `CategoryName`.
8. Write an SQL query to display the `OrderID`, `CustomerName`, `OrderDate` and the `total sales amount` for each OrderId and Customer.\
*(Total Sales Amount = (Quantity \* UnitPrice) - ((Quantity \* UnitPrice) \* Discount)*
9. Write an SQL query to display the `CustomerName` and the `total sales amount` for each OrderId and Customer.\
*(Total Sales Amount = (Quantity \* UnitPrice) - ((Quantity \* UnitPrice) \* Discount)*
10. Write an SQL query to display the `SupplierName` and the `total sales amount` for each Supplier.\
*(Total Sales Amount = (Quantity \* UnitPrice) - ((Quantity \* UnitPrice) \* Discount)*
11. Write an SQL query to display the `ShipperName` and the `Total number of orders` placed for each Shipper.
12. Write an SQL query to display the `CustomerName` and the `Total number of orders` placed for each Customer.
13. Write an SQL query to display the `ProductName` and the `Total number of orders` placed for each Product.
14. Write an SQL query to display the `ProductName` that has the most number of Orders Placed.
15. Write an SQL query to display the total Discount given out.
16. Write an SQL query to display `OrderId`, `CustomerName`, `OrderDate`, `OrderMonth` and `OrderYear` in increasing order of OrderDate.\
*(Hint: use date time functions in SQL)*
17. Write an SQL query to display `Total Sales including discounts` for each `Month` and `Year`.
18. Write an SQL query to find the `month` with the most `number of orders` placed.
19. Write an SQL query to find the `year` with the most `number of orders` placed.
20. Write an SQL query to display the `TotalSales` for each `year`.
# Exercise 3
{% hint style="info" %}
You can ignore **Question 1** and **Question 2** if you already have Northwind database loaded.
{% endhint %}
1. Create a Database named `northwind`.
2. Download the SQL file from [this link](https://github.com/microsoft/sql-server-samples/blob/master/samples/databases/northwind-pubs/instnwnd.sql) and run the code present in it inside the `northwind` database.\
(Make sure that the tables are created inside `northwind` database only)
3. Select the contact name, customer id, and company name of all Customers in London
4. Select all available columns in the Suppliers tables that have a FAX number.
5. Select a list of customers id’s from the Orders table with required dates between Jan 1, 1997 and Jan 1, 1998 and with freight under 100 units.
6. Select a list of company names and contact names of all the Owners from the Customer table from Mexico, Sweden and Germany.
7. Count the number of discontinued products in the Products table.
8. Select a list of category names and descriptions of all categories beginning with 'Co' from the Categories table.
9. Select all the company names, city, country and postal code from the Suppliers table with the word 'rue' in their address. The list should be ordered alphabetically by company name.
10. Select the product id and the total quantities ordered for each product id in the Order Details table.
11. Select the customer name and customer address of all customers with orders that shipped using Speedy Express.
12. Select a list of Suppliers containing company name, contact name, contact title and region description.
13. Select all product names from the Products table that are condiments.
14. Select a list of customer names who have no orders in the Orders table.
15. Insert a new shipper named 'Amazon' to the Shippers table using SQL.
16. Change the company name from 'Amazon' to 'Amazon Prime Shipping' in the Shippers table using SQL.
17. Select a complete list of company names from the Shippers table. Include freight totals rounded to the nearest whole number for each shipper from the Orders table for those shippers with orders.
18. Select all employee first and last names from the Employees table by combining the 2 columns aliased as 'DisplayName'. The combined format should be 'LastName, FirstName'.
19. Select a list of products from the Products table along with the total units in stock for each product. Give the computed column a name using the alias, 'TotalUnits'. Include only products with TotalUnits greater than 100.
# Ultimate SQL Server Cheat Sheet
This ultimate SQL Server cheat sheet is designed to be a quick reference guide for database administrators, developers, and data analysts. It covers fundamental commands, concepts, and best-practices.
### Getting Started
#### **Connecting to SQL Server**
* Connect using SQL Server Management Studio (SSMS):
* Open SSMS
* Enter server name
* Choose authentication mode (Windows/SQL Server)
* Click 'Connect'
### Sample Data
* **Table: Employees**
```sql
-- create table
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY IDENTITY(1,1),
Name VARCHAR(100) NOT NULL,
Age INT,
Department VARCHAR(50),
Salary DECIMAL(10,2),
Email VARCHAR(100) NULL
);
-- insert sample data
INSERT INTO Employees (Name, Age, Department, Salary, Email)
VALUES
('John Doe', 30, 'IT', 60000.00, 'john.doe@example.com'),
('Jane Smith', 28, 'HR', 55000.00, 'jane.smith@example.com'),
('Alice Johnson', 35, 'Finance', 75000.00, 'alice.johnson@example.com'),
('Bob Williams', 40, 'IT', 80000.00, 'bob.williams@example.com'),
('Charlie Brown', 27, 'Marketing', 50000.00, 'charlie.brown@example.com');
```
* **Table: Departments**
```sql
-- create table
CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY IDENTITY(1,1),
DepartmentName VARCHAR(50) NOT NULL
);
-- insert sample data
INSERT INTO Departments (DepartmentName)
VALUES
('IT'),
('HR'),
('Finance'),
('Marketing'),
('Operations');
```
* **Table: Employee\_Audit**
```sql
-- create table
CREATE TABLE Employee_Audit (
AuditID INT PRIMARY KEY IDENTITY(1,1),
EmployeeID INT,
ChangeDate DATETIME DEFAULT GETDATE(),
ChangeDescription VARCHAR(255)
);
-- insert sample data
INSERT INTO Employee_Audit (EmployeeID, ChangeDescription)
VALUES
(1, 'Salary updated to 65000'),
(2, 'Department changed to Finance'),
(3, 'New email added: alice.johnson@example.com'),
(4, 'Employee promoted to Senior IT Engineer'),
(5, 'Salary increased to 55000');
```
### **Basic Commands**
```sql
-- Create Database
CREATE DATABASE MyDatabase;
-- Use Database
USE MyDatabase;
-- Drop Database
DROP DATABASE MyDatabase;
-- Create Table
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY IDENTITY(1,1),
Name VARCHAR(100) NOT NULL,
Age INT,
Department VARCHAR(50),
Salary DECIMAL(10,2)
);
-- Drop Table
DROP TABLE Employees;
-- Alter Table
ALTER TABLE Employees ADD Email VARCHAR(100);
ALTER TABLE Employees DROP COLUMN Email;
ALTER TABLE Employees ALTER COLUMN Salary DECIMAL(12,2);
```
### CRUD Operations
```sql
-- Insert Data
INSERT INTO Employees (Name, Age, Department, Salary)
VALUES ('John Doe', 30, 'IT', 60000.00);
-- Update Data
UPDATE Employees SET Salary = 65000.00 WHERE EmployeeID = 1;
-- Delete Data
DELETE FROM Employees WHERE EmployeeID = 1;
-- Select Data
SELECT * FROM Employees;
SELECT Name, Salary FROM Employees WHERE Department = 'IT';
```
### Filtering & Sorting
```sql
-- WHERE Clause
SELECT * FROM Employees WHERE Age > 30;
-- ORDER BY Clause
SELECT * FROM Employees ORDER BY Salary DESC;
-- DISTINCT Clause
SELECT DISTINCT Department FROM Employees;
-- TOP Clause (Return top N rows)
SELECT TOP 5 * FROM Employees ORDER BY Salary DESC;
```
### Joins
```sql
-- INNER JOIN
SELECT e.Name, d.DepartmentName
FROM Employees e
INNER JOIN Departments d ON e.Department = d.DepartmentID;
-- LEFT JOIN
SELECT e.Name, d.DepartmentName
FROM Employees e
LEFT JOIN Departments d ON e.Department = d.DepartmentID;
-- RIGHT JOIN
SELECT e.Name, d.DepartmentName
FROM Employees e
RIGHT JOIN Departments d ON e.Department = d.DepartmentID;
-- FULL JOIN
SELECT e.Name, d.DepartmentName
FROM Employees e
FULL JOIN Departments d ON e.Department = d.DepartmentID;
```
### Aggregations & Grouping
```sql
-- COUNT, SUM, AVG, MIN, MAX
SELECT COUNT(*) AS TotalEmployees FROM Employees;
SELECT AVG(Salary) AS AvgSalary FROM Employees;
SELECT MIN(Salary) AS MinSalary, MAX(Salary) AS MaxSalary FROM Employees;
-- GROUP BY
SELECT Department, COUNT(*) AS EmployeeCount
FROM Employees
GROUP BY Department;
-- HAVING Clause
SELECT Department, AVG(Salary) AS AvgSalary
FROM Employees
GROUP BY Department
HAVING AVG(Salary) > 50000;
```
### Subqueries
```sql
-- Subquery in WHERE
SELECT Name FROM Employees WHERE Salary > (SELECT AVG(Salary) FROM Employees);
-- Subquery in FROM
SELECT * FROM (SELECT Name, Salary FROM Employees) AS TempTable;
```
### Common Table Expressions (CTEs)
```sql
WITH EmployeeCTE AS (
SELECT Name, Salary FROM Employees WHERE Salary > 50000
)
SELECT * FROM EmployeeCTE;
```
### Window Functions
```sql
-- ROW_NUMBER, RANK, DENSE_RANK, NTILE
SELECT Name, Salary,
ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum,
RANK() OVER (ORDER BY Salary DESC) AS RankNum,
DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRankNum
FROM Employees;
-- Running Total
SELECT Name, Salary,
SUM(Salary) OVER (ORDER BY Salary ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS RunningTotal
FROM Employees;
```
### Indexing
```sql
-- Create Index
CREATE INDEX idx_EmployeeName ON Employees(Name);
-- Unique Index
CREATE UNIQUE INDEX idx_Unique_EmployeeEmail ON Employees(Email);
-- Drop Index
DROP INDEX idx_EmployeeName ON Employees;
```
### Stored Procedures & Functions
```sql
-- Stored Procedure
CREATE PROCEDURE GetEmployees
AS
BEGIN
SELECT * FROM Employees;
END;
EXEC GetEmployees;
-- Function
CREATE FUNCTION GetAverageSalary()
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @AvgSalary DECIMAL(10,2);
SELECT @AvgSalary = AVG(Salary) FROM Employees;
RETURN @AvgSalary;
END;
SELECT dbo.GetAverageSalary();
```
### Transactions
```sql
-- Begin Transaction
BEGIN TRANSACTION;
-- Execute Queries
UPDATE Employees SET Salary = 70000 WHERE EmployeeID = 1;
-- Commit Transaction
COMMIT;
-- Rollback Transaction
ROLLBACK;
```
### Triggers
```sql
-- Create Trigger
CREATE TRIGGER trg_AfterInsert ON Employees
AFTER INSERT
AS
BEGIN
PRINT 'New Employee Inserted';
END;
-- Drop Trigger
DROP TRIGGER trg_AfterInsert;
```
### Error Handling
```sql
BEGIN TRY
UPDATE Employees SET Salary = -5000 WHERE EmployeeID = 1;
END TRY
BEGIN CATCH
PRINT 'An error occurred';
END CATCH;
```
### Security & User Management
```sql
-- Create User
CREATE LOGIN MyUser WITH PASSWORD = 'StrongPassword!';
CREATE USER MyUser FOR LOGIN MyUser;
-- Grant Permissions
GRANT SELECT, INSERT, UPDATE ON Employees TO MyUser;
-- Revoke Permissions
REVOKE DELETE ON Employees FROM MyUser;
-- Drop User
DROP USER MyUser;
```
### Performance Tuning
```sql
-- Execution Plan
SET SHOWPLAN_ALL ON;
SELECT * FROM Employees;
SET SHOWPLAN_ALL OFF;
-- Analyze Query Performance
DBCC FREEPROCCACHE; -- Clears query cache
DBCC DROPCLEANBUFFERS; -- Clears data cache
```
#### Project Title: **Skytrax Airline Reviews Analysis Pipeline**
{% embed url="
#### Table of Contents
1. **Project Overview**
* [x] 1.1 Background
* [x] 1.2 Objective
* [x] 1.3 Key Components
2. **Data Collection and Storage**
* [x] 2.1 Web Scraping from Skytrax Reviews
* [x] 2.2 Azure SQL Database Integration
3. **Data Processing and Analysis**
* [x] 3.1 Data Extraction with Pyspark
* [x] 3.2 Exploratory Data Analysis
* [x] 3.3 Advanced Analytics
4. **Results and Insights**
* [x] 4.1 Extracted Insights
* [x] 4.2 Visualization of Findings
* [x] 4.3 Business Implications
5. **Conclusion**
* [x] 5.1 Project Impact
* [x] 5.2 Lessons Learned
* [x] 5.3 Future Enhancements.
**Section 1: Project Overview**
**1.1 Background**
In today's rapidly evolving airline industry, customer feedback plays a pivotal role in shaping business strategies. This project centers around harnessing the power of data by collecting and analyzing airline reviews from Skytrax, a leading source of passenger opinions and reviews for airlines worldwide.
**1.2 Objective**
The primary objective of our project is to extract meaningful insights from daily airline reviews, enabling airlines to make data-driven decisions to enhance customer satisfaction, optimize services, and stay competitive in the market.
**1.3 Key Components**
The Skytrax Airline Reviews Analysis Pipeline comprises four crucial stages: data collection, data storage, data processing, and insights generation. Each stage is meticulously designed to ensure the accuracy, integrity, and reliability of the extracted information.