Power BI

What is it?

• a

Mainly Comprises of Three Steps

• Data Integration: The first step is to extract and integrate the data from heterogeneous data sources. After integration, the data is converted into a standard format and stored in a common area called the staging area.

• Data Processing: Once the data is assembled and integrated, it requires some cleaning up. Raw data is not so useful therefore, a few transformation and cleaning operations are performed on the data to remove redundant values, etc. After the data is transformed, it is stored in data warehouses.

• Data Presentation: Now that the data is transformed and cleaned, it is visually presented on the Power BI desktop as reports, dashboards, or scorecards. These reports can be shared via mobile apps or web to various business users

various Power BI versions?

• Power BI Desktop:

• Power BI Premium: for larger organizations with a dedicated storage capacity for each user. 50GB storage capacity can be hosted along with 100TB storage on the cloud as a whole. It costs $4995 per month.

• Power BI Pro: With the pro version, you get full access to the Power BI dashboard, creation of reports, along with unlimited sharing and viewing of reports. Storage limit of 10GB per user.

Few key terms

• Dataset

• Dashboard

  • are feature of power bi service only, not available in power bi desktop

  • Dashboard is a single page, often called a canvas, tells story through visualizations.

  • It's limited to one page, a dashboard contains only the highlights of that story

• Tiles

• Report

• Visualization

• DAX

Major Components of Power BI

• Power Query: data mash-up and transformation, use to extract data from various databases and to delete a chunk of data from various sources

  • Power query is a function that filters transforms, and combines the data extracted from various sources. It helps to import data from databases, files, etc. and append data

• Power View: The view provides an interactive display of various data sources to extract metadata for proper data analysis

• Power Bi Desktop: Power Desktop is an aggregated tool of Power Query, Power View, and Power Pivot. Create advanced queries, models, and reports using the desktop tool

• Power Bi Website:

• Power Pivot:

  • Power Pivot enables you to import millions of rows from heterogeneous sources of data into a single excel sheet

  • Uses a functional language "Data Analysis Expression" (DAX) to perform the calculations.

  • Creates a relationship between various tables to be viewed as pivot tables.

• Power Map: 3D Geospatial Data Visualization

• Power Q&A: Natural Language Question and Answer

• Power Bi Mobile Apps:

Data Storage in Power Bi

• The data doesn’t get stored in Power BI. Power BI will only store the metadata of the data tables involved and not the actual data.

• Uploading data on Power BI means consuming the memory space of your Power BI desktop.

  • If data is on the website, it consumes the space of the Power BI cloud machine

  • Even though it is the fastest method (storage in power Bi), the maximum size of the file to be uploaded cannot exceed 1 GB until and unless you have Power BI premium (then you have 50 GB at the expense)

• Two sources of stored data:

  • Azure Blob Storage: When users upload the data, it gets stored here.

  • Azure SQL Database: All the metadata and system artifacts are stored here.

• They are stored as either fact tables or dimensional tables

  • Fact Tables

  • Dimensional Tables

Different Connectivity Modes

All the database connections it can make

• File: Excel, Text/CSV, XML, PDF, JSON, Folder, SharePoint.

• Database: SQL Server database, Access database, Oracle database, SAP HANA database, IBM, MySQL, Teradata, Impala, Amazon Redshift, Google BigQuery, etc.

• Power BI: Power BI datasets, Power BI dataflows.

• Azure: Azure SQL, Azure SQL Data Warehouse, Azure Analysis Services, Azure Data Lake, Azure Cosmos DB, etc.

• Online Services: Salesforce, Azure DevOps, Google Analytics, Adobe Analytics, Dynamics 365, Facebook, GitHub, etc.

• Others: Python script, R script, Web, Spark, Hadoop File (HDFS), ODBC, OLE DB, Active Directory, etc.

Available formats?

1. Power BI desktop: For the desktop version

   • can connect to 80+ data sources

   • connects multiple data sources

   • transforms data

   • shape and model data

   • creates visualization and reports

   • publish to power bi service

   • access to power query editor

2. Power BI services:

   • Cloud based service (SaaS)

   • access to some data sources

   • create visualizations and reports

   • create paginated reports

   • create dashboards

   
3. Power Bi Report Builder:

   • Creation of paginated reports in the power bi service

Available Views

• Data View (Curating, Exploring, and Viewing Tables)

• Model View (shows all tables with relationship)

• Report View (number of reports, provide visualizations)

Various Refresh Options Available

• Package / OneDrive Refresh

• Data / Model Refresh

• Tile Refresh

• Visual Container Refresh

Types of Visualizations in Power Bi

• Visualization is a graphical representation of data

• We can use visualizations to create reports and dashboards

• The kinds of visualizations available in Power BI are

  • Bar charts

  • Column charts

  • Line chart

  • Area chart

  • Stacked area chart

  • Ribbon chart

  • Waterfall chart

  • Scatter chart

  • Pie chart

  • Donut chart

  • Treemap chart

  • Map

  • Funnel chart

  • Gauge chart

  • Cards

  • KPI

  • Slicer

  • Table

  • Matrix

  • R script visual

  • Python visual

References

• Interviewbit questions and answers
• Simplilearn questions and answers
• Intellipad questions and answers
• Youtube lectures: https://www.youtube.com/watch?v=bSAus3Ixhk8&list=PLoyECfvEFOjZGHV6eB_VOJPQVsCwUE3fO&index=6