• Business Intelligence & Data Warehouse concepts
• Introduction to Business Intelligence – what, where and how?
• Need for Business Intelligence / Analytics
• BI /DW Lifecycle
• Real life based exercises to understand BI phases
• Requirements gathering
• Analytics and Approach for Solution Design
• Data Modeling (Dimensional)
• Dimensional Modeling Process and Scenario based model designing
• Real time hands on work to understand Data Visualization
• Analytics Demo
• Real life demo
• Career Opportunities discussion and guidance
• Open discussion
• Steps to be carried out
• Short Term
• Long Term
• Fundamentals and Excel interface
• Introduction to analytics, Organizational structure, Concepts of OLTP, OLAP, Normalization, De-normalization, Primary Key, Dimensions, Facts, Factless fact, Foreign Key, VLookup, Ribbon Tabs, Formatting features, Speak Cells, Quick Access toolbar.
• Charts
• Basic chart, Series, Combination charts, Dynamic chart ranges with Excel tables, Histogram, Treemap chart, Sunburst, Pie and Doughnut charts, Sparklines, Use of dashboard
• Pivoting
• Pivoting – Pivot tables, Slicers and Timelines, Creating Pivot charts
• Excel features / Data quality
• Auto Fill, Flash Fill, Fill Series, Use of formula, Recognizing formula and input cells, Adding note / comment, Creating custom list, Data Validation, Screen Tip and few shortcuts, Assignment, Excel shortcuts for navigation, Copying, Moving and Hiding Worksheets, Freeze panes and split screen, Hide and unhide, Insert and adjust columns and rows, Copying and pasting data
• Data Protection
• Protecting Worksheets and specific ranges, Assignment, Order of precedence, Simple formulae, Excel’s Essential rule, Absolute and Relative referencing.
• Excel functions
• Range names for clarity, Referencing other Workbooks or Worksheets, Circular references, Combine values from two or more cells to one cell, Assignments, Functions like COUNT, COUNTA, COUNTBLANK, COUNTIFS, SUM, AVERAGE, SUMIFS, AVERAGEIFS, MAX and MIN (Include by criteria), Basic DATE functions, Time calculations.
• Data population and error handling
• Handling formulae errors, Different types of errors, IF (then, else) function, VLOOKUP function, Assignment, Sort, Unsort, Subtotal, Filter, Remove blank cells, Fill empty cells, Conditional formatting.
• Automation
• Macro Introduction
• Conclusion
• Project
• RDBMS
• What is RDBMS?
• Basic concepts of RDBMS
• Different data types
• KEYS concepts
• SQL Basic
• Introduction SQL (Structured Query Language)
• Fundamentals of SQL
• SQL Tables, Joins, Variables
• SQL Advance
• SQL Functions, Subqueries, Views, Rules
• Condition based queries using various operator
• Data Modeling
• Different type of joins (Basic, Advance)
• Aggregate Function
• Windows Functions
• Use of CTE (Common table expression) with practice
• Hands on exercise on live projects
• Introduction to BI and need for visualization
• BI life cycle
• What is analysis
• Importance of data visualization to the industry
• Introduction to Power BI
• Installation
• Connecting to the data source
• Elements of report windows
• Creating Visualization
• Creating visualization such as column charts, line charts, pie charts, table, matrix and many more
• Formatting the charts
• Grouping and binning (Creating histogram)
• Filtering the data
• Types of filters (Visualization level, page level, across page)
• Slicers
• Base, Advance and Limiting filters
• Concept of internal (context) filters and internal filters
• Transformation using Dax and modeling
• Data transformation using Dax
• Creating new relationship
• Create new tables in Dax
• Creating Measures and columns in Dax
• Navigation
• Creating dashboard pages
• Using Hierarchies for Drill Down navigation
• Creating drill through across pages
• Data refresh and scheduling
• Advanced transformations
• Data cleansing techniques
• Full Load and Incremental Load
• Standard tasks
• Container tasks
• Data profiling and testing techniques
• Deployment and job scheduling
• Introduction to JIRA tool
• Assign and keep track of the course assignments
