BI ANALYST COURSE

BI ANALYST COURSE

Business Intelligence (BI) Analyst Course

While a Data Analyst focuses on data extraction, wrangling, querying, and exploratory analysis, a BI Analyst focuses on business process mapping, strategic KPI design, advanced data modeling, enterprise reporting, row-level security, automated data pipelines, and storytelling for decision-makers.

This 15-day course is structured for students who want to master Power BI, SQL for BI, Data Warehouse Architecture, and Advanced Analytics.

Course Overview

  • Course Title: Business Intelligence (BI) Analyst Specialist Course
  • Total Duration: 30 Hours (2 Hours × 15 Days / Classes)
  • Training Mode: Individual 1-on-1 Training (Face-to-Face or Live Online)
  • Target Audience: Aspiring BI Professionals, Business Analysts, IT Graduates, and Professionals looking to advance from basic data entry to enterprise BI roles.

Course Objectives

By the end of this course, students will be able to:

  • Translate business goals into actionable BI key performance indicators (KPIs).
  • Understand Enterprise Data Warehousing concepts (Star/Snowflake Schemas, SCDs, Fact vs. Dimension tables).
  • Write advanced SQL queries for BI reporting (CTEs, Window Functions, Views, and Aggregations).
  • Master advanced DAX (Time Intelligence, Context Transition, Dynamic Measures, Parameters).
  • Implement Row-Level Security (RLS) and publish/manage workspace apps in Power BI Service.
  • Design executive-level interactive visual dashboards following modern UI/UX principles.
  • Automate scheduled dataset refreshes and data pipelines.

Day-by-Day Course Syllabus

Day 1 — Fundamentals of BI & Requirement Gathering: Setting up the BI Lifecycle

  • Difference between Data Analytics, Business Analytics, and Business Intelligence
  • The BI Lifecycle: Business Problem → Data Source → Data Warehouse → BI Dashboard → Decision
  • Defining Business KPIs (Revenue, CAC, LTV, Churn Rate, Profit Margins, Inventory Turnover)
  • Translating business requirements into a BI Solution Architecture matrix

Practical: Draft a BI Requirements Document (BRD) for an enterprise sales/finance scenario.

Day 2 — Data Warehouse Concepts & Dimensional Modelling: Designing Schemas for BI

  • OLTP (Operational Databases) vs. OLAP (Analytical Databases)
  • Star Schema vs. Snowflake Schema vs. Denormalized Tables
  • Designing Fact Tables (Additive, Semi-additive, Non-additive) and Dimension Tables
  • Slowly Changing Dimensions (SCD Type 1 & Type 2) concepts

Practical: Design a relational Star Schema model on paper/draw.io for a Retail/ERP dataset.

Day 3 — Advanced SQL for BI (Views & Aggregations): Preparing BI Data Layers

  • Creating and managing Database Views (CREATE VIEW) for clean reporting layers
  • Conditional logic in SQL using CASE WHEN statements
  • Handling missing data and nulls (COALESCE, NULLIF) for reporting
  • Aggregate logic for business metrics (MTD, YTD SQL logic)

Practical: Write SQL scripts to create custom BI reporting views from normalized database tables.

Day 4 — Advanced SQL (CTE & Window Functions): Analytical SQL Querying

  • Common Table Expressions (CTEs) using WITH clause for complex queries
  • Ranking Functions: ROW_NUMBER(), RANK(), DENSE_RANK()
  • Value Functions: LEAD(), LAG() for period-over-period comparisons
  • Aggregate Window Functions (OVER (PARTITION BY … ORDER BY …))

Practical: Query monthly month-over-month growth rates and customer rankings using Window Functions.

Day 5 — Advanced Power Query & ETL Processing: Enterprise Data Transformation

  • Advanced M Language concepts in Power Query Editor
  • Parameterizing data connections for dynamic source switching
  • Handling incremental refresh concepts and merge/append performance tuning
  • Unpivoting complex matrix spreadsheets into tabular reporting structures

Practical: Build a parameter-driven ETL pipeline connecting to multiple CSV/Excel/SQL sources.

Day 6 — Enterprise Data Modelling in Power BI: Optimizing the Data Engine

  • Creating robust Date/Calendar Tables in Power BI (DAX CALENDARAUTO & M Code)
  • Managing Active vs. Inactive Relationships using USERELATIONSHIP()
  • Handling Many-to-Many relationships and Bridge Tables
  • Bi-directional cross filtering risks and best practices

Practical: Connect multiple fact tables (Sales vs. Target Budget) to shared dimension tables.

Day 7 — DAX Deep Dive: Evaluation Context: Row Context vs. Filter Context

  • Understanding Row Context and Filter Context in DAX
  • Context Transition (CALCULATE function mechanics)
  • Modifying Filter Context using ALL(), ALLEXCEPT(), ALLSELECTED(), and KEEPFILTERS()

Practical: Build measures calculating regional contribution percentages against global totals.

Day 8 — DAX Time Intelligence Functions: Temporal Business Reporting

  • Standard Time Intelligence: TOTALYTD(), TOTALQTD(), TOTALMTD()
  • Period Comparison: SAMEPERIODLASTYEAR(), DATEADD(), PARALLELPERIOD()
  • Custom Fiscal Calendars and non-standard period logic

Practical: Build a year-over-year (YoY) performance scorecard tracking Variance and Variance %.

Day 9 — Advanced DAX Patterns & Dynamic Analytics: Interactive Metrics

  • Disconnected Tables and Field Parameters for dynamic measure selection
  • Rank calculations in DAX using RANKX()
  • Dynamic grouping using SWITCH(TRUE(), …)
  • Customer Segmentation / ABC Analysis using DAX

Practical: Build a dynamic report switcher where users choose between Revenue, Profit, or Quantity metrics on a single chart.

Day 10 — UI/UX & Executive Dashboard Design: Building Storytelling Reports

  • Dashboard Layout Rules: Golden Triangle, visual hierarchy, grid alignment
  • Color psychology, themes, and accessibility in corporate reporting
  • Navigation mechanisms: Page Bookmarks, Selection Panes, Page Navigation Buttons, and Drill-through pages

Practical: Redesign an unorganized report into a streamlined 3-page Executive BI Portal.

Day 11 — Power BI Service & Cloud Architecture: Collaborative BI Deployment

  • Power BI Service architecture: Workspaces, Apps, Datasets, Reports, and Dashboards
  • Publishing reports and setting up On-Premises Data Gateways
  • Scheduling automated dataset refreshes (Hourly/Daily)
  • Managing App publication and user access permissions

Practical: Publish a report to Power BI Cloud Service, configure gateway refresh, and generate an App.

Day 12 — BI Governance, Security & Row-Level Security (RLS): Data Access Control

  • Defining Static and Dynamic Row-Level Security (RLS) using DAX (USERPRINCIPALNAME())
  • Testing security roles in Power BI Desktop and Power BI Service
  • Export controls, data protection, and sensitivity labels

Practical: Implement dynamic security so Regional Managers only view data for their assigned region.

Day 13 — Introduction to Looker Studio & Alternative BI Tools: Multi-Tool Exposure

  • Introduction to Google Looker Studio (formerly Data Studio) for web-based BI
  • Connecting Google Sheets, BigQuery, and Google Analytics to Looker Studio
  • Building quick web dashboards and automated PDF scheduled email reports

Practical: Build a lightweight marketing/sales dashboard using Google Looker Studio.

Day 14 — Performance Tuning & BI Best Practices: Optimizing Large Scale BI

  • Performance Analyzer in Power BI Desktop
  • Optimizing DAX queries and DAX Studio basics
  • Reducing dataset size (data types, high-cardinality columns, aggregation tables)
  • BI Documentation & Data Dictionaries

Practical: Audit a slow report, identify bottlenecks, and speed up query execution time.

Day 15 — Capstone Enterprise BI Project & Presentation: End-to-End Business Case

Business Case: Complete a full end-to-end BI solution based on a realistic enterprise dataset (e.g., E-commerce/Retail/Supply Chain).

Tasks:

  • Define Business KPIs & Data Warehouse Star Schema.
  • Write SQL Views and CTE queries to construct the analytical data layer.
  • Import to Power BI, model relationships, write Time Intelligence DAX measures, and implement Dynamic RLS.
  • Design a high-impact 3-page Executive Dashboard (Executive Overview, Regional Breakdown, Customer Analytics).
  • Publish to Power BI Service, create a cloud app dashboard, and set up a refresh schedule.

Final Presentation: Students present their BI solution explaining strategic insights and operational business recommendations.

Fees & Enrollment

  • Total Fees: LKR 75,000/=
  • Total Duration: 30 Hrs (2 hours × 15 Classes)
  • Training Mode: Individual Training, your own timetable
  • Live Online Classes or Face to Face Direct Classes with our expert trainers.
  • Medium: සිංහල / தமிழ் / English

Call +94 (0) 722000999 / +94 (0) 755123111 — www.iss.lk