MICROSOFT EXCEL BUSINESS INTELLIGENCE

Business Intelligence in a business is to help corporate executives, business managers and other operational workers make better and more informed business decisions. Companies also use BI to cut costs, identify new business opportunities, andspotinefficientbusinessprocessesripeforre-engineering.BIasadisciplineand asatechnology-drivenprocessismadeupofseveralrelatedactivitieswhichinclude, Data mining, analytical processing, Querying and Reporting. The potential benefits of business intelligence include Accelerating and improving decision making, optimizing internal business processes, increasing operational efficiency, driving new revenues, gaining competitive advantages over business rivals, identifying market trends and Spotting business problems that need to beaddressed 

This course makes use of microsoft Excel which is the universal language of business and accountants, and most decision-makers in small to mid-sized businesses use the king of spreadsheets in some way for reporting. In fact, research analyst Gartner has conceded that “Excel is still the number one reporting tool”. 

ThiscoursecoverstheExcelDashboardandReports,pivottable,PowerBIpublisher for Excel and Excel Power BI tools (power query, PowerPivot, powerview, powermap) which will help in providing faster data insights, data analysis, data mining, data modelling, financial reporting, improving day-to-day decision-making capacities and businessperformance. 

This course is very hands-on with Excel, and offers all the advice and software practice utilizing these advanced tools which include building queries to extract, transform, and load data, and also to create elegant data models with PowerPivot. This involves analyzing multiple data sets, working with cross-sectional data, merging different sets to create a data model. 

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

• Extract and transformdata 
• Import data into Power Pivot from differentsources 
• Set up and manipulate Pivot Tables and Pivot Charts with PowerPivot 
• Describe, set up and work with Tables, Table Relationships, Calculated Columns andMeasures 
• Understand the complex DAX functions and use them in your dataanalysis 
• Work with relational data and perform dynamicaggregation. 
• Create a report with real-world data: PowerPivot, Power Query, powerview and Power Map 
• Calculated items andKPIs 
• Extract meaningful insight from a datamodel 
• Dynamic filters with slicers andtimelines 
• Automate reoccurring tasks 
• Work with the ‘M’language 
• Localizing data & geographic plots across theglobe 
• Make a killermap 

Module 1: Introducing Power BI for Excel You Will Learn: 

• What Is Power BI forExcel? 
• Power BI for Excel KeyFeatures 
• Microsoft’s Self-Service Power BIModel 

Module 2: BI Data Query Course Content 

Power Query is the tool use in BI data Query, allows you to extract data from multiplesourcesandperformdatacleansingoperationsonthatdata.Itallowsyouto clean, reshape, and combine your data with ease, no matter where it comes from be it Excel workbook, Text or CSV file, XML file, JSON file, SQL Server database, Access database, webetc 

You Will Learn: 

• What is a BI Data Query? 
• Activating Power Query inExcel 
• Power QueryInterface 
• Business Intelligence DataSources 
• Data SourceTypes 
• Planning a BI DataQuery 
• Query EditorTransformations 
• Merging & Appending Queries 
• Calculated Columns 
• Grouping and SummarizingData 
• Unpivoting Data 
• Dealing with MalformedData 
• Finalizing Queries 

Module 3: Creating Data Models 

Excel can analyze mountains of data, but you might be working too hard if you're not utilizing the Data Model feature to corral it. This feature lets you integrate data from multiple tables by creating relationships based on a common column. The model works behind the scenes and simplifies 

You Will Learn: 

• Creating a Data Model from ExcelData 
• Creating a Data Model from Access DatabaseData 
• Creating a Hybrid Data Model 

Module 4: PowerPivot for Excel 

Power Pivot has been developed to help companies analyze and understand the growingquantitiesofdatathatarecreatedonadailybasis.PowerPivotwillimprove your time to create reports, develop data analysis and gain insight into your your data by at least 80%. Create relationships between tables of data without Vlookups, Create powerful Key Metric and Key Performance Indicator calculations, Manage and work with data models of millions of records of data and Create powerful new dashboards andreports. 

You Will Learn: 

• What isPowerPivot? 
• How PowerPivot differs from aPivotTable 
• Activating Power Pivot inExcel 
• PowerPivot Components 
• Loading Data into PowerPivot 
• What is a PowerPivotReport? 
• Data-Driven Storytelling 
• Understanding PowerPivotData 
• Understanding Data Terminology 
• Understanding Tables 
• Understanding Relationships 
• Diagnosing Data Models 
• Data Model Schemas 
• Create Reports using PowerPivotData 
• Making Changes to PowerPivotData 
• Creating Pivot Tables with PowerPivot Data 
• Creating PowerPivotPivotCharts 
• Creating Slicers 
• Graphing Data 
• Named Sets andKPIs 

Module 5: Creating PowerPivot Functions 

Data Analysis Expressions (DAX) is the native formula and query language for MicrosoftPowerPivot,PowerBIDesktopandSQLServerAnalysisServices(SSAS) Tabularmodels.DAXincludessomeofthefunctionsthatareusedinExcelformulas with additional functions that are designed to work with relational data and perform dynamicaggregation. 

You Will Learn: 

• Calculated Columns 
• What isDAX? 
• Understanding DAXFunctions 
• Common DAXFunctions 
• Dates and DateFunctions 

Module 6: Power View for Excel 

Power View is an interactive data exploration, visualization, and presentation experience that encourages intuitive ad-hoc reporting. 

You Will Learn: 

• What Is Power View? 
• Activating Power Pivot inExcel 
• Creating Power ViewReports 
• Power View InterfaceComponents 
• Understanding TableVisualizations 
• Chart Visualizations 
• Power View Tiles, Slicers, &Multiples 

Module 7: Power Map for Excel 

ExcelPowerMapisanextensionthatletsyouplotyourExcel-basedgeospatialdata on amap. 

You Will Learn: 

• What Is Power Map? 
• Preparing a Power Map Tour 
• Power Map Designer 
• Creating a Power Map Tour 
• Power Map FormattingTools 

Module 8: Power BI publisher for Excel 

With the Power BI Publisher Excel, you take snapshots of your important insights in Excel and Pin them in Power BI Dashboards. It helps to easing the Transition from Excel to PowerBI 

You Will Learn: 

• Installing power bi Publisher forExcel 
• Activating power biPublisher 
• Pin a range to adashboard 
• Pin a Chart to adashboard 
• Manage pinnedelements 
• Connect to data in PowerBI 

This course is suited to those with an interest in Excel, data analysis, data science, business intelligence, market analytics, reporting sales, analytics, Fraud and Audit and is well suited for Accountants, statisticians, Auditors, Internal Auditors, Economists, enterprise managers, data analysts, Business Analysts, Finance staff, Banking staff, financial market staffs, consultants, Management and those involved with Compliance and Risk assessment and BI. 

Prerequisites: 

1. Each participate is expected to have access to a system during the training as this is a practical class and this would aid active participation and understanding. 
2. Prior knowledge of excel is compulsory as this is not a beginner non- intermediate excelclass. 

N:B:Allexercises andprojectfilesusedonthecourse willbeavailableforclassuse and takehome.