This program will introduce Business Intelligence (BI), hands-on, to allow you to clean, normalize, and interpret large volumes of data.

You Will Learn How To

In this course, we extensively use MS Excel as an ultimate and readily available BI tool allowing you to develop an exclusive level of expertise and adding immediate value to your job and company.

Important Course Information

You will be able to establish historical relationships, analyze the current situation and predict future strategies. The application of BI is borderless, covering operational, tactical and strategic business decisions. It spans all departments and cascades down to all users who perform data cleansing, reporting, analysis, modeling, integration and automation.

Course Outline

• Introducing Business Intelligence
• The Business Value of BI
• The Challenges of BI
• Making the Business Case for BI
• Barriers to BI
• BI in the Marketplace
• Introducing Business Intelligence
• The Business Value of BI
• The Challenges of BI
• Making the Business Case for BI
• Barriers to BI
• BI in the Marketplace

Data analysis tools and techniques
• Consolidating data from separate files and sheets
• Advanced data validation using lists, dates and custom validation
• Powerful array functions
• Cell management tools: left, right, mid, concatenate, value
• Naming, editing, and managing cells and ranges
• Subtotal, sumif, sumifs, sumproduct, count, countif, countifs
• Looking-up data, texts, and values using vlookup
• The incredible table-tools technique
• Slicing dates into day names, weeks, week numbers, month names, years and quarters
• Text-to-columns and dynamic trimming using trim and len
• Managing texts and numbers using replace, find, and substitute
• Text change functions

Mastering data reporting: The 19 must learn pivot-tables tools
• Creating pivot tables
• Number formatting techniques
• Designing report layout
• Sorting in ascending, descending and more sort options
• Filtering labels and values
• Expanding and collapsing reports
• Summarize data by sum, average, minimum, maximum, count
• Show values as % of total and % of
• Pivot table options
• Inserting formulas
• Data analysis
• Copying pivot tables
• Creating pivot charts
• Dynamic chart labeling
• Mastering the slicer
• Showing report filter pages
• Linking pivot tables and pivot graphs with PowerPoint
• Conditional formatting with pivot tables
• Designing reports using the getpivotdata

BI and SQL Analysis Services Multidimensional Models
• Data Warehouses and Star Schemas
• Course Case Study
• SQL Server Integration Services Introduction to Cubes
• Creating Dimensions with Analysis Services
• Creating and Browsing a Cube with Analysis Services
• Creating a KPI With Analysis Services
• Excel as an Analysis Services Client

Data modeling and integration
• Spinner
• Check box data modeling with if function
• Option button data modeling with if function
• List box data modeling with choose function
• Scenario manager
• Linking excel with text files
• Linking excel with databases (Access)
• Linking excel with SQL
• Linking excel with internet
• Linking excel with excel

The look and feel: Charting and visualization techniques
• Using the camera tool
• Working with formula-driven visualizations
• Using fancy fonts
• Leveraging symbols in formulas
• Working with sparklines
• Creating unconventional style charts

Tips and tricks
• Controlling and protecting your reports, worksheets and workbooks
• Data entry form
• Custom list
• Text-to-speech
• Advanced conditional formatting
• Shortcuts for your daily work
• Self‐Service BI and PowerPivot
• DAX
• Tabular Models
• DAX Time Intelligence Functions