Role of Business Analysts

Role of Business Analysts

• Business analysts (BAs) are crucial for decision-making in today's business environment.
• BAs analyze data and convert it into actionable insights for organizations, aiding in better business decisions.
• They bridge the gap between strategic objectives, operational execution, and risk identification in business processes.
• BAs are focused on analyzing costs, benefits, and ROIs for businesses.
• With digital transformation and emerging technologies, BAs are instrumental in integrating these technologies into business processes.

Key Responsibilities and Skills

• Essential skills include data analytics using Excel, basics of SQL and Python, and data visualization with tools like Tableau.
• Understanding and addressing the impact of GenAI in business analysis is crucial.

Business Analytics Explained

• Business analytics involves deriving business insights from trackable processes and events.
• It starts with collecting data from various sources, like local and cloud storage, and even competitor data.
• Analyzing the collected data is crucial to identify useful information.
• Data is converted into information, often visualized through graphs for better understanding.
• Visualizations help in comprehending data faster, utilizing the human brain's ability to process images 3000 times faster than text.
• Data is essential for business analytics, with approximately 22 lac terabytes of data generated daily.
• The ability to handle and analyze this data has led to the rise of business analytics, data analytics, data science, and AI.
• More data leads to better analysis and solutions in business analytics.

Data Pre-processing

• Data is often raw, containing noise and irrelevant information.
• BAs are responsible for cleaning and pre-processing data by removing noise which includes useless or erroneous data.
• An example of erroneous data is illogical entries
• Clean data is vital for training machine learning algorithms effectively.

The Role of a Business Analyst

• A BA works with data from management and data science teams to achieve business goals.
• They gather and clean data to provide insights that answer questions, and understand trends.
• Key is understanding business requirements, often in sales, to determine needs.
• They possess the ability to sift through and pre-process data.
• After pre-processing, data is fed into machine learning algorithms for rapid processing.
• Results from machine learning are converted into visualizations for easy understanding.
• Insights are presented in a concise manner, understandable by both technical and non-technical stakeholders.

Becoming a Business Analyst: Required Skills

• Programming languages are necessary, with Python being a popular choice.
• Strong communication skills and creative thinking are essential.
• Knowledge of SQL and database query languages is important for working with databases.
• Machine learning skills are crucial for predictions and insights.

Tools for Business Analysis

• Tools for business analysis vary depending on the domain.
• Microsoft Excel is a versatile tool if used effectively.
• Knowledge of working with a notebook is important.
• Tableau is a valuable tool for data visualization.
• Apache Spark is useful for distributed computing when handling large datasets.
• Splunk is useful to efficiently convert data.

Business Analyst vs. Data Analyst

• A business analyst focuses on understanding business needs, identifying solutions, and aligning strategies with goals.
• A data analyst focuses on the technical aspects of extracting, cleaning, and analyzing data to find trends.
• Data analysts provide data-driven insights that BAs use for decision-making.

Critical and Logical Thinking

• Critical and logical thinking skills are vital for analyzing complex problems and making informed decisions.
• Critical thinking helps in questioning assumptions and understanding the underlying causes of issues.
• Logical thinking ensures that proposed solutions are structured and practical.
• These skills help to systematically consider all angles and propose data-driven strategies.

Data Cleaning with Excel

• Data cleaning removes irregularities, misspellings, and typos
• Data cleaning ensures end results align with stakeholders' expectations.
• Microsoft Excel is a dominant platform for data cleaning and analysis.
• Important features of Excel
  • Spreadsheets
  • Tabs
  • Columns
  • Rows
  • headers
• Some data cleaning tasks that can be done in Excel
  • Removing duplicates
  • Identifying irregularities
  • Log maintenance should be employed
  • Sorting data

Data Analysis Issues

• Discrepancies in data can arise, such as a salary appearing incorrect due to a missing space, leading to a significant difference in value
• It's important to correct such errors early in the analysis process to prevent issues later on
• Always verify data corrections with the client to ensure accuracy

Functions in Spreadsheets

• Functions are predefined instructions or formulas that perform specific calculations in a set order
• These can include mathematical operations like addition, subtraction, multiplication, and averaging
• Functions save time and effort by automating calculations that would otherwise need to be done manually

Writing Functions

• Basic syntax of a function starts with an equality operator (=)
• Followed by the type of calculation needed (e.g., SUM, MAX, MIN, AVERAGE)
• Then, define the range of cells on which to perform the calculation inside parentheses

Cell Range

• A cell range specifies the dataset or area to calculate
• e.g., E15:E20 refers to all cells from E15 to E20 inclusive
• Clicking and dragging from the first cell to the last cell can define the range

Major Function Types For Data Analysis

• COUNTIF: Counts the number of cells that meet a specific condition
• LEN: Returns the length of a text string
• LEFT and RIGHT: Return a specific number of characters from the left or right side of a cell
• CONCATENATE: Combines text from multiple cells into a single cell
• TRIM: Removes extra, unwanted spaces from a cell

COUNTIF Function

• Syntax: =COUNTIF(range, criteria)
• Range is the group of cells to evaluate
• Criteria is the condition to meet for counting (e.g., specific text or number)

Example of COUNTIF

• A class has 20 students, and you want to know how many students are named "Abhishek"
• The COUNTIF function can find the number of times the name "Abhishek" appears in the list of names
• Formula: =COUNTIF(student name range, "Abhishek")

Excel Example

• Objective is to count the number of movies in English, French, and Italian from a movie dataset
• Use COUNTIF, specifying the range as the language column and the criteria as "English," "French," or "Italian"

LEN Function

• This function returns the number of characters in a specified text string
• e.g., the formula =LEN("India") would return 5 because the word "India' has 5 characters

LEFT and RIGHT functions

• Function return a specific number of characters from the left and right side of the giving value
• Syntax: =LEFT(cell, number of characters) or =RIGHT(cell, number of characters)
• The "cell" parameter specifies the cell containing the text, and the "number of characters" specifies how many characters to extract

Excel Example For LEN Function

• Using a spreadsheet showing movie titles and languages to use function
• Applying =LEN() gives a character count for each language shown
• =LEN Function counts the spaces in between

Excel Example for LEFT Function

• Extracting PIN codes from a spreadsheet containing state names, counties, and PIN codes
• Apply the LEFT Function specifying that Pin codes only are 6 digits
• Syntax: =LEFT(the cell for the pin codes to extract, 6)

CONCATENATE Function

• Combines text from multiple cells into one
• Excel Syntax: =CONCATENATE(cell1, cell2, etc)
• To add space between combined content insert " "

TRIM Function

• Removes extra spaces from beginning and end of texts
• Excel Syntax: =TRIM(cell)

Sorting Data

• Sorting is rearranging data in a specific order, either ascending or descending
• Can customize rules using the custom sort option

Sorting Example

• Sorting employee data (ID, birthdate, age, city, etc), based on either ascending or descending for the data selected
• Choose the column to sort
• Ascending (Lowest to Highest) or descending (Highest to Lowest)

Sorting With Multiple Criteria

• Sorts data by multiple columns and preference for accuracy
• EX. sort the city name in reverse alphabetical order AND start the birth dates from oldest to newest

FILTER Function

• Narrows the data to specific categories
• Example: To filter based on movie language, select the filter tab and choose on what to filter

VLOOKUP Function

• Searches for a value in a column and returns information from another column in the same row
• Useful for restructuring and finding corresponding data
• Syntax: =VLOOKUP(lookup value, table array, col index number, range lookup)

VLOOKUP Parameters

• Lookup value: The known value to search for
• Table array: The table in which to search
• Col index number: The column number containing the value to return
• Range lookup: TRUE for approximate match, FALSE for exact match

HLOOKUP Function

• Similar to VLOOKUP, but searches horizontally instead of vertically
• Searches for a value in a row and returns information from another row in the same column

HLOOKUP Function

• HLOOKUP searches for a value in the top row of a table, returning a value in the same column from a specified row.
• Unlike VLOOKUP, HLOOKUP is used for horizontal tables.
• HLOOKUP requires a row index number instead of a column index number (VLOOKUP).
• The row index number specifies which row's value to return.
• An exact match is typically required, indicated by a "0" in the function.
• If the lookup value is blank, HLOOKUP may show "#N/A."
• Operates similarly to VLOOKUP but on horizontal datasets, which are less common.
• Example: =HLOOKUP(lookup_value, table_array, row_index_number, [range_lookup])

Conditional Formatting

• Alters the appearance of cells based on specified conditions.
• Used to highlight or differentiate data based on its value.
• Useful for quickly identifying trends, outliers, or specific data points in a dataset.
• Saves time and effort compared to manual data analysis.
• Features:
  • Highlight Cell Rules
  • Top/Bottom Rules
  • Data Bars
  • Color Scales
  • Icon Sets

Highlight Cell Rules

• Highlights cells that meet specific criteria.
• Includes options like: greater than, less than, between, equal to, text that contains, a date occurring, and duplicate values.

Top/Bottom Rules

• Formats cells based on their ranking within a dataset.
• Includes options like: top 10 items, top 10 %, bottom 10 items, bottom 10 %, above average, and below average.

Data Bars

• Adds a visual bar within each cell, with the length representing the value's magnitude relative to other values.
• Provides a quick visual comparison of values within a range.

Color Scales

• Applies a color gradient to a range of cells, with colors representing different value ranges.
• Highlights the distribution and relative magnitude of data.

Icon Sets

• Adds icons to cells based on their values, representing categories or trends.
• Uses symbols like arrows, flags, or indicators to visually represent data.

Conditional Formatting Example

• Objective: Highlight companies with quarterly revenues less than $40,000.
• Steps: Select the dataset, go to "Home" ribbon, click "Conditional Formatting," choose "Highlight Cells Rules," then "Less Than."
• Enter the value (40,000) and choose a formatting style (e.g., light red fill with dark red text).
• Objective: Find the bottom ten companies per quarter.
• Steps: Select the quarter column, click "Conditional Formatting," go to "Top/Bottom Rules," then "Bottom 10 Items."
• Choose formatting style to highlight the lowest values.
• Applying data bars fills the cell according to how large the value is in certain cells
• Color scales give different colors, where the top values give darker green and the lowest give red, and midpoint give yellow
• Icon sets can provide indicators with distinctions
• All the conditional formatting is relative to each other

Data Validation

• Implemented in spreadsheets to control values that can or can't be inputted.
• Assists in data collection and handling to ensure the integrity of data is valid.
• Common data validation methods:
  • Setting text length limits
  • Dropdown lists with predefined options
  • Limiting number ranges (decimal or whole numbers)
  • Date or time constraints, with custom error and input messaging

Example 1: Text Length Limit

• Objective: Limit text input in a "Name" column to a maximum of 20 characters.
• Select the range, go to "Data" tab, click "Data Validation."
• Choose "Text Length" criteria, set minimum and maximum character limits.

Example 2: Dropdown List

• Objective: Create a dropdown list for a "Gender" column with options like "Male" / "Female."
• Select the range, go to "Data Validation," choose "List" as the criteria.
• Enter the source values (Male, Female) separated by commas.

Example 3: Number Range

• Objective: Restrict marks in a "Marks" column to a decimal number between 0 and 100.
• Select range, go to "Data Validation," choose "Decimal" criteria, set minimum and maximum values.

Custom Error Messages

• Data Validation allows putting error alerts to customize the title and error message to inform the user correctly.
• Can add a title such as "invalid input" with the error message being "please input values between zero and 100".
• Input messages can also be added so that users are aware of what the data restrictions are before they input data
• Can make it so that only values between zero and 100, etc

Creating Dropdown List

• Objective: Create a dropdown list.
• List data in Excel, select the cell, implement validation, click on error alert, and select the values, it will show up in the cell.
• When datasets are too large to manually write every name with a comma, link the source to another dataset link.
• However, there is a disadvantage that causes issues when data is deleted from the primary dataset.

Pivot Tables

• Used to sort, count, group, or reorganize stored data.
• Categorize rows/columns into different sections.
• Are able to automatically summarize data; can change rows into columns and vice versa.
• Allow calculations like sum, average, max, min etc.

Creating Pivot Tables

• Column headers become Pivot Table fields.
• Numerical values are the most clear and common option.
• Rows, columns, can switch between any, depending on the data

Example Pivot Table structure

• Objective: Create a table in which we can find what other different product lines that were sold in different months.
• Months on the row headers
• Product Line on the column headers
• Sales of different product lines in different months.
• Grand total is given in the last column and last row.
• To create, click any cell in the spreadsheet, go to Insert, then Pivot Table
• Excel automatically detects the table.
• Choose where the Pivot Table will be, new work sheet or current, etc etc

Pivot Tables Enhancements

• Pivot tables combine data from multiple years (e.g., 2003, 2004, 2005) into single months like January.
• To distinguish between years, the month identifier is removed and replaced with the year.
• Allows for analysis of sales data for specific years (e.g., January 2003).
• Month ID can be placed below the year to distinguish data first by year, then by month.
• Data not available in the dataset appears empty in the pivot table.
• Can display quantity of items sold instead of sales value by dragging "Sum of Sales" back to the field option and selecting "Quantity Ordered".
• Currency formatting can be removed to display quantities as general numbers instead of dollar amounts.

Data Visualization: Representing Analysis

• Presenting analyzed data to internal (bosses, managers) and external (clients) stakeholders using pictorial representations.
• Stakeholders may not review datasets directly due to time constraints, so visualization aids understanding.
• Charts, graphs, and plots are used for pictorial representation, including: pie charts, bar charts, line charts, scatter plots, map charts, and tree charts.
• Ensures that all data analysis is represented visually for clarity.

Pie Charts

• Represents part-to-whole relationships.
• Each slice represents a portion of the whole based on the value in the dataset.
• Larger values get bigger slices.
• Three major components: area, central angle, and arc length.

Bar Charts

• Uses bars of different heights to show different amounts, allowing easy comparison between categories.
• One axis (typically y) represents numerical values, and the other (x) represents categories.
• Can be vertical or horizontal.

Line Charts

• Shows changes over time by connecting data points with lines.
• Illustrates trends and differences between consecutive data points or periods.
• Categories are usually along the horizontal axis, and values along the vertical axis.
• Effective when one parameter is non-numeric (horizontal) and the other is numeric (vertical).

Creating Charts in Excel

• To create pie or bar charts, select the data range and choose the desired chart type from the "Insert" tab.
• Chart styles can be modified, and chart titles can be changed by double-clicking.
• Axis titles (X and Y) can be added via the "Chart Elements" option.

Pivot Charts

• Line charts are created from Pivot tables by selecting Insert, then selecting a chart type.
• In pivot charts, the entire range displays but can be filtered.
• Data can be filtered by month and category to display specific subsets.

Advanced Chart Types: Tree Maps

• Provide a hierarchical view of data, showing proportions within a hierarchy.
• Area size represents value, facilitating comparison of proportions.

Advanced Chart Types: Map Charts

• Display data across different geographies/regions.
• Countries are highlighted based on their values in the dataset.
• Used to represent data across the globe, commonly by organizations involved in logistics, imports, and exports.

Creating Advanced Charts in Excel

• To create tree maps or map charts, select the data and choose the desired chart type from the "Insert" tab's hierarchical or Maps options.
• Layout and styling of charts can be customized via chart styles and formatting options.

Introduction to SQL

• Structured Query Language is used to access and manage data in databases.
• Standardized by the American National Standards Institute (ANSI) in 1986 and the International Organization for Standardization (ISO) in 1987.
• ANSI SQL can be used across different database management systems without significant modification.
• MySQL is used in the tutorial examples.

MySQL Installation

• MySQL can be downloaded from the official website.
• The community (free) version is suitable for students and personal use, while the enterprise edition requires a paid license.
• Choose the version that matches your system configuration.

Data Types in MySQL

• Specifies the type of data an object can hold, such as integers, strings, date/time values, and binary streams.
• Common data types used: strings, numeric values, date/time, and binary.

String Data Types in MySQL

• CHAR: Stores a fixed number of characters, from 1 to 8000 bytes.
• NCHAR: Stores Unicode characters, from 1 to 4000 bytes.
• VARCHAR: Stores variable-length, non-Unicode characters up to a specified length.
• VARCHAR(MAX): Stores up to 2^31 - 1 characters, plus two bytes of overhead.
• NVARCHAR: Stores variable-length Unicode characters.
• NVARCHAR(MAX): Stores up to (2^31 - 1) / 2 - 2 characters, plus two bytes of overhead.