Power BI Lecture Slides PDF

Power BI Essentials for Beginners Data is the new oil…and Dataleum is the rig!  Day 1 Locations Measured Introduction to Data Analytics and Visualization - Power BI...

Power BI Essentials for Beginners Data is the new oil…and Dataleum is the rig!  Day 1 Locations Measured Introduction to Data Analytics and Visualization - Power BI DATA INFORMATION KNOWLEDGE WISDOM Introduction ▪ Data/Information is the most strategic business asset. It is a collection of facts. ▪ Data analysis is the process of identifying, cleaning, transforming and modelling data (structured or unstructured) in order to get useful insights to support the critical decision- making process. Image credit: Microsoft Overview of Data Analysis ▪ Data analysis is telling a story with data. Five categories of analytics: Descriptive Diagnostic Predictive Prescriptive Cognitive Image credit: Microsoft Data Analysis Cycle Ask: Business Challenge/Objective/Question Prepare: Data generation, collection, storage, and data management Process: Data cleaning/data integrity Analyze: Data exploration, visualization, and analysis Share: Communicating and interpreting results Act: Putting your insights to work to solve the problem What Exactly is Power BI? Introduction to Power BI ▪ Power BI is a suite of self-service data analytics and visualization tools. ▪ It can connect to quite several data sources to: ▪ Power BI is made up of 3 main components: o Pull Data o Clean up and prepare the data o A report authoring tool called Power BI Desktop o Perform rich analysis o An online sharing tool called Power BI Service o Create clear visuals to view outcome of the analysis o And a mobile tool called Power BI Mobile o Also allows sharing of insights with others ▪ To be an expert user, you will need to be very knowledgeable in: Transform o Data Preparation Extract Load o Data Modeling and Data Analysis Expressions (DAX) Source Target o Data Visualization o Business Knowledge Power BI Licensing Model Power BI Desktop - User Interface Tabs & Ribbons Panes Views Canvass Getting Data ▪ A primary goal of Microsoft Power BI is to enable report creators, managers, and stakeholders to connect to any data source necessary to create a unified experience that makes sense to audiences and viewers of the content. ▪ Along with full support for more typical, legacy systems such as an Excel spreadsheet or SQL Server database, Power BI can authenticate and access data across a wide variety of data sources via “Connectors”, including Azure, Oracle, or even external services such as Facebook, Salesforce etc. ▪ Consider doing some calculations in the original data source to improve performance when you are getting data into Power BI. Power Query ▪ Power Query is a business intelligence tool available in Excel and Power BI that allows you to import data from many different sources and then clean, transform and reshape your data for analysis. Power Query Language is M. ▪ Benefits of clean data: ▪ More accurate results ▪ Better organized tables ▪ Simpler data navigation ▪ Human-readable values ▪ Perform over 360 different transformations to data Visualization ▪ Visualizations or “Visuals” are the end-result of any Business Intelligence effort. Ultimately, the aim is to access data and present it to users in a meaningful, compelling, interactive, and insightful way. ▪ Power BI exposes a large number of out-of-the-box visualizations found in common Business Intelligence tools, such as numerous graphs, charts, maps, and filters, while also enabling visualization “extensibility” for creating organization-specific or scenario-specific custom visuals to be created by developers and power users. o Line, bar, pie, stacked o Matrix/pivots o Key performance o Tree maps o Geo and filled maps o Slicers/filters o Custom visualizations Why Visualize Your Data? ▪ Makes your data easy to share and understand. ▪ Tell stories to facts about your data and enable anyone to connect easily to the information you are passing across. ▪ Assists to share insights easily and more effectively. ▪ Increases comprehension, retention and is appealing to the eye. ▪ Interact easily with your data, question your data and make informed decisions quickly. Uses of Some Power BI Visuals ▪ Area Chart: Like the line chart but has the area ▪ Line Charts: Shows the progress of a series with between the axis and the line filled in. respect to time. ▪ Bar/Column Chart: For looking at specific values ▪ Maps (Basic): Used for categorical and quantitative across different categories. information with spatial locations. ▪ Card (multi-row or single): Emphasizes a big value ▪ Maps (Filled): Works as basic maps but works with or a set of values. the intensity of colour. The more intense the colour, ▪ Combo Chart: Combines a line chart and a column the larger the value. chart. ▪ Scatter/Bubble Charts: Displays the relationship ▪ Pie Charts: Shows the relationship of part to a between two (scatter) or three (bubble) whole. quantitative measures. ▪ Doughnut Chart: Like pie charts, they show part-to- ▪ Table: Works with quantitative comparisons among whole relationships. items with many categories. ▪ Funnel Chart: Used for a process with stages and ▪ Waterfall Charts: Helps to show as running total shows the flow from one stage to the next. values are added or subtracted. ▪ Gauge Charts: Displays current status with respect ▪ Slicer: Used for filtering visuals and reports. to goal. ▪ Tree map: Charts consisting of coloured rectangles ▪ KPIs: Displays progress towards a measurable goal. with the size of each rectangle representing value. Demo – Crime Statistics in Nigeria 2017 Q &A  Locations Measured Day 2: Data Modelling & DAX - Power BI DATA INFORMATION KNOWLEDGE WISDOM Modelling ▪ Modelling is the technique of creating logical Demo: Using the provided Dataleum Store data connections and relationships between data sources. Relationships are defined between tables through primary and foreign keys. ▪ Power BI has the concept of directionality to a relationship. This directionality, or cardinality, plays an important role in filtering data between multiple tables. ▪ The processes of managing and adjusting these relationships across data sources is referred to as modelling. Uses the concepts outlined below: Star Schema o Create Calculated Columns o Optimize data models for better visuals Benefits of a good data model: o Create Measures and work with time-based functions o Create Calculated Tables Accurate reports. Faster data exploration. ▪ Facts Table: Store observations or business events Simpler aggregations. ▪ Dimension Table : Describes business events/entities. Easier to maintain. Overview of DAX ▪ Data Analysis Expressions (DAX) is a formula Demo: Using the provided Dataleum Store data expression language used in Analysis Services, Power BI, and Power Pivot in Excel. ▪ DAX formulas include functions, operators, and values to perform advanced calculations and queries on data in related tables and columns in tabular data models. ▪ DAX is a functional language, like Excel Functions ▪ Can be used to generate new columns, new tables or store calculations as Measures https://docs.microsoft.com/en-us/dax/dax-overview New Columns ▪ New columns are also referred to as calculated columns. Useful when a required reporting field is missing from the original data. It can be generated on the fly or using DAX functions ▪ A calculated column is a column that you add to an existing table (in the model designer) and then create a DAX formula that defines the column's values. ▪ When a calculated column contains a valid DAX formula, values are calculated for each row as soon as the formula is entered. Values are then stored in the in-memory data model. Writing Simple DAX Measures ▪ Measures are computations in Power BI, executed and displayed on a visual. Measures are a summarization of data. ▪ Implicit Measures are internally generated while Explicit Measures are manually written (Calculated measures). ▪ Measures use functions, like simple aggregator functions such as SUM, AVERAGE, COUNT ▪ The result of measures can only be displayed inside a visual. Practice Examples: ▪ Total Cost = SUM(Orders[Costs]) ▪ Total Profit = SUM(Orders[Profit]) ▪ Total Revenue = SUM(Orders[Sales]) Formula Guide Calculated Column: Cost Price = Orders[Sales] - Orders[Profit] Profit Margin = DIVIDE(Orders[Profit], Orders[Sales]) Measures: Total Cost = SUM(Orders[Cost Price]) Total Revenue = sum(Orders[Sales]) Calendar = ADDCOLUMNS(CALENDARAUTO(), New Table (Calendar): "Year", YEAR([Date]), Calendar = CALENDARAUTO() "MonthNumber", MONTH([Date]), Year = Year('Calendar'[Date]) OR "Month", FORMAT([Date], "MMMM"), "Day", DAY([Date]), Year = FORMAT('Calendar'[Date], "YYYY") "Quarter", FORMAT([Date], "\QQ")) Month = FORMAT('Calendar'[Date], "MMMM") Month Number = Month('Calendar'[Date]) Quarter = format('Calendar'[Date], "\QQ") Q &A End Thank you for listening.