Power BI is the umbrella term for a set of data visualization, reporting, and analytics tools and services from Microsoft. Power BI can import data from various sources like MS Excel, CSV, JSON, and other online services and organize them to run queries, build reporting dashboards and data visualization.
Power BI has a set of components, which are available for different platforms and needs. These components together are called as Power BI.
Power BI gateway is used to connect on premise data sources to Power BI desktop or Power BI cloud services to get continuous data for reporting and analytics.
Power BI services is a cloud based subscription service.
Related Article: Power BI Services And Benefits
1. Sourcing Data
Power BI can source data from a vast number of file types and online services. You can import the data into Power BI or you can establish a live connection to service to get the data. If we import a file to Power BI, it limits the data sets to 1 GB compressed. If the data set is more than 1 GB then we can use Direct Query.
There are two other options for accessing large data sets.
List of Data Sources.
Excel, Text/CSV, XML, JSON, Folder and SharePoint Folder
SQL Server Database, Access Database, SQL Server Analysis Services Database, Oracle Database, IBM DB2 Database, IBM Informix database (Beta), IBM Netezza (Beta), MySQL Database, PostgreSQL Database, Sybase Database, Teradata Database, SAP HANA Database, SAP Business Warehouse server, Amazon Redshift, Impala, Google BigQuery (Beta), Snowflake
Azure SQL Database, Azure SQL Data Warehouse, Azure Analysis Services database (Beta), Azure Blob Storage, Azure Table Storage, Azure Cosmos DB (Beta), Azure Data Lake Store, Azure HDInsight (HDFS), Azure HDInsight Spark (Beta)
Power BI service, SharePoint Online List, Microsoft Exchange Online, Dynamics 365 (online), Dynamics 365 for Financials (Beta), Common Data Service (Beta), Microsoft Azure Consumption Insights (Beta), Visual Studio Team Services (Beta), Salesforce Objects, Salesforce Reports, Google Analytics, appFigures (Beta), comScore Digital Analytix (Beta), Dynamics 365 for Customer Insights (Beta), Facebook, GitHub (Beta), Kusto (Beta), MailChimp (Beta), Mixpanel (Beta), Planview Enterprise (Beta), Projectplace (Beta), QuickBooks Online (Beta), Smartsheet, SparkPost (Beta), SQL Sentry (Beta), Stripe (Beta), SweetIQ (Beta), Troux (Beta), Twilio (Beta), tyGraph (Beta), Webtrends (Beta), Zendesk (Beta)
Vertica (Beta), Web, SharePoint List, OData Feed, Active Directory, Microsoft Exchange, Hadoop File (HDFS), Spark (Beta), R Script, ODBC, OLE DB, Blank Query
Direct Query and on premise gateway requirement for data sources
Direct Query supports the following data sources:
Amazon Redshift, Azure HDInsight Spark (Beta) , Azure SQL Database, Azure SQL Data Warehouse, Google BigQuery (Beta), IBM Netezza (Beta), Impala (version 2.x), Oracle Database (version 12 and above), SAP Business Warehouse (Beta), SAP HANA , Snowflake, Spark (Beta) (version 0.9 and above), SQL Server, Teradata Database, Vertica (Beta)
The following data sources required an on premise gateway to connect to power bi services
2. Transforming the data
After importing data into power bi, power bi provides a preview window to select columns or entities to choose for visualization. If you need to edit the query, there are plenty of transformation options available to do the task.
3. Reporting and Publishing
After sourcing and editing the data, we are ready to create reports. Reports are visualization of data in form of graphs, charts and pie charts with filters and slicers. There are also lot of custom visualization available.
After creating reports, we can publish them to power bi services. You can also publish them to on premise power bi server.
4. Creating Dashboards
After publishing reports to power BI services, we can create dashboard by pining the individual elements or by pining the live report page. When pining the individual elements, the visual retains the filter setting selected when saving the report. Pinning the live report page allows the dashboard user to interact with the visual by selecting slicers and filters.
Microsoft provides Power BI mobile apps for android, iOS and windows phone device. Apps can be used to view reports and dashboards in power bi site. Reports and dashboards can be shared with others with notes and highlight marks.
Power BI desktop is free from Microsoft that comes with features like
Power BI Pro is priced at 600INR per user per month and comes with features like
Power BI Enterprise is an enterprise agreement and pricing is based on nodes and capacity.
Each Power BI deployment has two components. A web front end cluster and a backend cluster.
Web Front End
Web front end manages initial connection, client authentication and request routing to nearest data centers.
Power BI uses the Azure Traffic Manager (ATM) to direct user traffic to the nearest datacenter, determined by the DNS record of the client attempting to connect, for the authentication process and to download static content and files. Power BI also uses the Azure Content Delivery Network (CDN) to efficiently distribute the necessary static content and files to users based on geographical locale.
The back end manages visualization, user dashboards, datasets, reports, data storage, data connections, data refresh in power BI services. The gateway role acts as a gateway between users and Power BI services.
All data are stored in azure blob storage and user azure storage account and authenticated by azure active directory.
Free Demo for Corporate & Online Trainings.