Excel Reporting, Data Modeling & Analytics with Power Pivot Training in Chennai

  • (126)

It is a 36 hours program (Only on Sundays) focusing on how to work with huge data sets, extend your Pivot table summarization capabilities, advanced calculations, import data from various sources and publish the workbooks as interactive web applications.

Before Power Pivot, this kind of analysis was limited to enterprise BI tools such as SAS and Business Objects.

This Course is ideal for Advanced Excel users, Data Analysts, Reporting & MIS professionals, Business Analysts, Managers & Dashboard makers.

Training Details

  • Course
  • Excel Powerpivots
  • Duration
  • 6 Weeks (18 Hours)
  • Mode of Training
  • At Excelgoodies, Instructor Led
  • Course Certificate
  • Excel Reporting Specialist with Powerpivots
  • Course
  • Excel Powerpivots with MS-SQL
  • Duration
  • 12 Weeks (36 Hours)
  • Mode of Training
  • At Excelgoodies, Instructor Led

What is Power Pivot?

  1. It’s a free Add-in provided by Microsoft for Excel 2010 and above.
  2. A way to handle massive volumes of data in Excel
  3. A way to add any formulas to pivot, and it adjusts!
  4. A way to turn workbooks into self-maintaining web applications
  5. A much more agile and cost effective approach to Business Intelligence
  6. Something that turns workbooks into industrial strength Apps

What you will be able to do post training?

  1. Combine data from different sources
  2. Enjoy fast calculations and analysis
  3. Create visually appealing analytical models
  4. Use DAX to create calculated fields for slicing and dicing data
  5. Work seamlessly via Web browser
  6. Collaborate on analytics solutions

Course Content - Overview

Introduction to PowerPivot PowerPivot Environment Taking Data into PowerPivot Calculated Columns DAX Measures
  1. Table-Valued Functions
  2. Date and Time Functions
  3. Filter Functions
  4. Information Functions
  5. Logical Functions
  6. Mathematical and Trigonometric Functions
  7. Statistical Functions
  8. Text Functions
  9. Time Intelligence Functions
Working with Multiple Tables Disconnected Tables Creating Advanced DAX Measures with Advanced DAX Functions
  1. Calculate()
  2. All()
  3. Filter()
  4. IF()
  5. Switch()
  6. SumX()
CREATING CUSTOM CALENDARS USING ADVANCED FILTER() ADVANCED CALCULATED COLUMNS CREATING ADVANCED DASHBOARDS WITH POWERPIVOT
Introduction
  1. Creating a Database
  2. Understanding Tables and Creating Tables
  3. Inserting, Updating and Deleting Data
  4. Querying Data
  5. Filtering Data
  6. Grouping Data
  7. Ordering Data
  8. Column Aliases
  9. Table Aliases
UNDERSTANDING TRANSACT-SQL DATA TYPES
  1. Dates
  2. Strings
  3. Numeric
  4. BLOBs
  5. Bits
  6. UNIQUEIDENTIFIER
  7. Cursor Variables
  8. Timestamps
DDL INSIGHTS
  1. CREATE TABLE
  2. Dropping Objects
  3. CREATE INDEX
  4. TEMPORARY OBJECTS
  5. Object Naming and Dependencies
DML INSIGHTS
  1. INSERT
  2. UPDATE
  3. DELETE
  4. Detecting DML Errors
SELECT STATEMENTS
  1. Simple SELECTs
  2. Calculated and Derived Fields
  3. SELECT TOP / BOTTOM Records
  4. Derived Tables
  5. Joins
  6. Predicates
  7. Subqueries
  8. Aggregate Functions
  9. GROUP BY and HAVING
  10. UNION
  11. ORDER BY
WORKING WITH VIEWS
  1. ANSI SQL Schema VIEWs
  2. Getting a VIEW's Source Code
  3. Updatable VIEWs
  4. Derived Tables
  5. Dynamic VIEWs
  6. Partitioning Data Using Views
  7. View Restrictions
STORED PROCEDURES
  1. Stored Procedure Advantages
  2. Internals
  3. Creating Stored Procedures, Executing Stored Procedures
  4. Environmental Concerns
  5. Parameters for Procedures
  6. Important Automatic Variables
  7. Flow Control Language
  8. Errors
  9. Nesting
  10. Autostart Procedures
TRIGGERS
  1. AFTER Triggers
  2. The inserted and deleted Special Tables
  3. Identifying the Number of Affected Rows
  4. Identifying the Type of Trigger
  5. Not Firing Triggers for Specific Statements 152
  6. Nesting and Recursion
  7. UPDATE and COLUMNS_UPDATED
  8. INSTEAD OF Triggers
  9. Per-Row Triggers
  10. Used with Views
  11. Automatic Handling of Sequences
  12. Database-Level Triggers
  13. Server-Level Triggers
USER-DEFINED FUNCTIONS
  1. Scalar UDFs
  2. Table-Valued UDFs
  3. Per-Row UDFs
STATISTICAL FUNCTIONS
  1. CASE
  2. Efficiency Concerns
  3. Variance and Standard Deviation
  4. Medians
  5. Returning the Top n Rows
  6. Rankings
  7. Modes
  8. Cumulative and Sliding Aggregates
WORKING WITH DATE AND TIME
  1. Date and Time Data Types
  2. Date and Time Manipulation
  3. Date and Time Functions
  4. Literals
  5. Identifying Weekday
  6. Handling Date-only or Time-only Data Prior to SQL Server 2008
  7. Examples of Date and Time Calculations
  8. Rounding Issues
  9. Date- and Time-Related Querying Problems
  10. Age Problems
  11. Overlaps
  12. Grouping by the Week
  13. Working Days
  14. Generating a Series of Dates
ARRAYS
  1. Arrays as Big Strings
  2. Arrays as Tables
SETS
  1. Unions
  2. Differences
  3. Intersections
  4. Subsets
HIERARCHIES
  1. Simple Hierarchies
  2. Multilevel Hierarchies
  3. Indented lists
TRANSACTIONS
  1. Transactions Defined
  2. How SQL Server Transactions Work
  3. Types of Transactions
  4. Avoiding Transactions Altogether
  5. Automatic Transaction Management
  6. Transaction Isolation Levels
  7. Transaction Commands and Syntax
  8. Debugging Transactions
  9. Optimizing Transactional Code

Why Powerpivots with MS-SQL?

  1. It complements Powerpivots better than any other DB
  2. When business scenarios exceed the capabilities of Powerpivots, data shaping can be done at DB (MS-SQL) level enabling you to surpass any complex reporting situations.
  3. High performance due to its (MS-SQL) inherent capabilities of performing row-wise and cross-row business calculations in fraction of seconds, which, otherwise shall take, minutes together when executed only with Powerpivots

Training Schedule

Session Date Time
Session 1 12-Mar 10.00 AM - 1.00 PM
Session 2 19-Mar 10.00 AM - 1.00 PM
Session 3 26-Mar 10.00 AM - 1.00 PM
Session 4 02-Apr 10.00 AM - 1.00 PM
Session 5 09-Apr 10.00 AM - 1.00 PM
Session 6 16-Apr 10.00 AM - 1.00 PM
Session Date Time
Session 1 12-Mar 10.00 AM - 1.00 PM
Session 2 19-Mar 10.00 AM - 1.00 PM
Session 3 26-Mar 10.00 AM - 1.00 PM
Session 4 02-Apr 10.00 AM - 1.00 PM
Session 5 09-Apr 10.00 AM - 1.00 PM
Session 6 16-Apr 10.00 AM - 1.00 PM
Session 7 23-Apr 10.00 AM - 1.00 PM
Session 8 30-Apr 10.00 AM - 1.00 PM
Session 9 07-May 10.00 AM - 1.00 PM
Session 10 14-May 10.00 AM - 1.00 PM
Session 11 21-May 10.00 AM - 1.00 PM
Session 12 28-May 10.00 AM - 1.00 PM

About Trainer

Mr. Sami, Microsoft Certified Trainer, with his qualifications in Finance and IT brings in 14 years of Industry experience. He has successfully trained 6000+ professionals by now, and the counting is still on.

He has undertaken the renowned VBA assignments with IRS, The World Bank, Tata Chemicals, Buckman Laboratories, Standard Chartered, ING Barings and much more. His nature of going that Extra Mile has got him the startling popularity amongst the Excelgoodies prominent clients.

Contact Us

ExcelGoodies Software Private Limited

56, 1st Floor, North Boag Road,​​​​​​​

T. Nagar, Chennai - 600 017

Tel: +91 9176633248

inquiry_chennai@excelgoodies.com

Connect us on