MIS = Management Information System. It is the process of collecting, processing, and presenting data so managers can make better decisions. In practice it means: someone asks "what were our sales last month by region?" and you answer with a table, chart or dashboard — not a guess.

The MIS toolkit — what each tool does and when you use it

Pivot Tables — the most important Excel skill

A pivot table summarises thousands of rows into a meaningful table in seconds. Insert → PivotTable. Drag fields: Rows (what you want to group by), Values (what you want to sum/count), Filters (what you want to narrow down), Columns (optional second grouping).

VLOOKUP vs HLOOKUP vs INDEX/MATCH

Essential formulas every MIS analyst must know

Macros and VBA — when to use them

Macros record your clicks and replay them. Use for: formatting reports the same way every time, copying data between sheets on a schedule, sending email alerts. Record with: Developer tab → Record Macro. Edit in VBA: Alt+F11. Simple VBA to loop through rows and colour cells based on value — saves hours of manual formatting.

Data Validation and Named Ranges

Data Validation: Data → Data Validation → List. Prevents wrong data entry — dropdown of valid choices. Named Ranges: Select a range → Name Box (top left) → type a name. Use =SalesData instead of =Sheet1!A2:A500. Makes formulas readable and maintainable.

The 5 clauses you need for 90% of queries

JOINs — combining data from multiple tables

Aggregate functions — the backbone of MIS reporting

Date functions — essential for MIS time-based reporting

Subqueries and CTEs — cleaner complex queries

-- CTE example: find departments above average salary
WITH dept_avg AS (
  SELECT department, AVG(salary) as avg_sal
  FROM employees
  GROUP BY department
)
SELECT * FROM dept_avg
WHERE avg_sal > (SELECT AVG(salary) FROM employees)
ORDER BY avg_sal DESC;

6-step automation framework for any MIS task

Complete automation script — monthly sales report

import pandas as pd
from datetime import datetime

# 1. Read source data
df = pd.read_excel("raw_sales.xlsx")

# 2. Clean - remove rows with no sales amount
df = df.dropna(subset=["Sales Amount"])
df["Date"] = pd.to_datetime(df["Date"])

# 3. Filter current month
this_month = datetime.now().month
df = df[df["Date"].dt.month == this_month]

# 4. Summarise by region
summary = df.groupby("Region").agg(
    Total_Sales=("Sales Amount", "sum"),
    Order_Count=("Order ID", "count"),
    Avg_Order=("Sales Amount", "mean")
).round(2).reset_index()

# 5. Sort by total
summary = summary.sort_values("Total_Sales", ascending=False)

# 6. Export
summary.to_excel(f"report_{datetime.now().strftime('%Y%m')}.xlsx", index=False)
print(f"Report saved: {len(summary)} regions")

Reading from SQL database with Python

import pandas as pd
import pyodbc  # or pymysql, psycopg2 depending on your DB

conn = pyodbc.connect(
    "DRIVER={SQL Server};SERVER=myserver;DATABASE=mydb;Trusted_Connection=yes"
)
query = """
SELECT region, SUM(sales) as total
FROM sales_table
WHERE MONTH(sale_date) = MONTH(GETDATE())
GROUP BY region
"""
df = pd.read_sql(query, conn)
conn.close()
df.to_excel("monthly_by_region.xlsx", index=False)

Key Pandas operations every MIS analyst needs

Topic 1 — What is Power BI?

Power BI is a Business Intelligence (BI) tool developed by Microsoft. It helps you connect, transform, visualise, and share data to make better business decisions. BI means turning raw data into useful information — Power BI handles Data Collection, Data Analysis, Data Visualisation, Reporting, and Better Decision Making in one platform.

Power BI Architecture

Power BI Licensing

Topic 2 — Power BI Desktop Interface

Topic 3 & 10 — Data Sources & Get Data

Power BI can connect to a wide variety of data sources. Use Home → Get Data to connect.

Data Import Modes — critical to understand

Topic 4 & 15 — Power Query Editor (ETL)

Power Query is a data preparation and transformation tool in Power BI. Use it to clean, shape and transform raw data before loading it into the data model. Access via Home → Transform Data.

Power Query Keyboard Shortcuts

Topic 5, 11 & 16 — Data Modeling in Power BI

Data modeling is the process of creating relationships between tables. It helps organise data and make it easier to analyse. A data model is a collection of tables, relationships, and calculations (measures and columns).

Types of Tables

Types of Relationships

Cross Filter Direction

Schema Types

Best Practices for Data Modeling

Use a Star Schema. Keep fact tables at the center. Use meaningful and consistent names. Create relationships using unique key columns. Avoid many-to-many relationships if possible. Hide unnecessary columns from report view. Use a Date Table for time intelligence. Always relate dimensions to facts, not to each other.

Topic 6, 12 & 17 — DAX (Data Analysis Expressions)

DAX is the formula language used in Power BI to create calculations in tables or models. DAX is used to create Measures (dynamic calculations), Calculated Columns (static, stored in model), and Calculated Tables (new tables using DAX).

Basic DAX Syntax

-- Basic syntax: Result = FUNCTION( Column1, Column2, ... )
-- Example:
Total Sales = SUM(Sales[Amount])
Avg Sales = AVERAGE(Sales[Amount])
Total Orders = COUNT(Sales[OrderID])
Unique Customers = DISTINCTCOUNT(Customers[CustomerID])

Essential DAX Functions

CALCULATE — the most important DAX function

-- CALCULATE changes the filter context
-- Example: Sales for 2024 only
Sales 2024 = CALCULATE(
    SUM(Sales[Amount]),
    Sales[Year] = 2024
)

-- Sales YTD using time intelligence
Sales YTD = CALCULATE(
    SUM(Sales[Amount]),
    DATESYTD(Date[Date])
)

Filter Context vs Row Context

Time Intelligence Functions

Topic 18 — Measures vs Calculated Columns

Topic 7 — Power BI Visualisations

Which Visual to Use — Quick Guide

Topic 8 — Filters & Slicers in Power BI

Filters vs Slicers

Topic 9 — Formatting & Design in Power BI

Good formatting makes your report attractive, easy to read and more effective. Use the Format pane to modify each visual.

Topic 14 & 19 — Power BI Dashboards & Publishing

A dashboard is a single-page view of your most important visuals from one or more reports. It is ideal for monitoring KPIs and tracking performance. Dashboards are interactive and update with real-time data.

Dashboard vs Report

Step-by-step: build your first Power BI dashboard

Topic 20 — Power BI Service (Publish, Share & Collaborate)

Workspaces in Power BI Service

Row-Level Security (RLS)

RLS allows you to restrict data access based on user roles. Users see only the data they are allowed to see. Example: North region manager sees only North data, South manager sees only South data. Set up in Model view → Manage Roles → define DAX filter rules per role.

Topic 13 — Navigation & Interactivity

Power BI vs Excel — when to use which

Sample DAX Measures Reference

-- Common measures every Power BI developer needs

Total Sales = SUM(Sales[Amount])
Total Quantity = SUM(Sales[Quantity])
Average Price = AVERAGE(Sales[Price])
Total Orders = COUNTROWS(Sales)
Unique Customers = DISTINCTCOUNT(Customers[CustomerID])

-- Profit Margin %
Profit Margin % = DIVIDE([Total Profit], [Total Sales], 0)

-- Running Total
Running Total = 
CALCULATE(
    SUM(Sales[Amount]),
    FILTER(ALL(Date), Date[Date] <= MAX(Date[Date]))
)

-- Sales vs Last Year
Sales Last Year = 
CALCULATE(
    [Total Sales],
    SAMEPERIODLASTYEAR(Date[Date])
)

-- YTD Sales
Sales YTD = 
TOTALYTD([Total Sales], Date[Date])

-- Sales 2024 only
Sales 2024 = 
CALCULATE(
    [Total Sales],
    YEAR(Date[Date]) = 2024
)

-- Top 10 Customers (Calculated Table)
Top Customers = 
TOPN(10, Customers, [Total Sales], DESC)

What Airflow does in plain English

Airflow is a scheduler for data pipelines. Instead of running your Python script manually every Monday morning, Airflow runs it automatically at 6am, sends you an alert if it fails, and keeps a log of every run. Think of it as Windows Task Scheduler but for data teams — far more powerful and with full visibility.

Key concepts

Simple MIS pipeline in Airflow

from airflow import DAG
from airflow.operators.python import PythonOperator
from datetime import datetime, timedelta

def extract(): 
    # query database, save to CSV
    pass

def transform():
    # pandas cleaning and aggregation
    pass

def load():
    # write to Excel, send email
    pass

with DAG("mis_monthly_report",
         schedule_interval="0 6 1 * *",  # 6am on 1st of every month
         start_date=datetime(2025,1,1),
         catchup=False) as dag:

    t1 = PythonOperator(task_id="extract", python_callable=extract)
    t2 = PythonOperator(task_id="transform", python_callable=transform)
    t3 = PythonOperator(task_id="load", python_callable=load)

    t1 >> t2 >> t3  # defines the order