Understanding Power BI

Self Service BI is a form of business intelligence wherein the end users can design reports as per analysis, play with data chunk without knowing actual programming and SQL queries, save and deploy the reports without any assistance of IT staff.
Power BI is a self-service business intelligence solution with interactive visualizations capabilities. It offers many business analytics tools to analyse and visualize the data. It can be used to make highly interactive reports and dashboard. Power BI dashboards are updated in real time and are available on different devices. Excel spreadsheets, on-premises data sources, big data, streaming data, and cloud services; all these can be connected to Power BI with ease. Users can easily explore underlying data behind dashboard using interactive tools.
Why Data Visualization?
The human brain processes information, using images/charts/graphs to visualize and relate to large amount of complex data. It is easier to understand and recall information visually   than just table of enormous data. Data visualization is a quick and easy way to convey concepts in a universal way. Using Power BI and data visualization, questions about the data can easily be answered.
Power BI helps in identifying areas that need attention or improvement, clarify factors influencing customer behaviour. It also helps in making data driven decisions and predicting future growth.
Why prefer Power BI over its complements:

  • Integration – Its integration with Office 365 suite adds to its usability by providing compatibility with Excel. It is also compatible with SharePoint which is used in many organisations for their ERP systems.
  • Sharing – PowerBI provides granular set of permission levels for the content to be shared wherein we can selectively display or hide information from certain individuals.
  • Mobile Support – Power BI has native apps for iOS, Android and Windows devices.
  • Desktop Client not required – One can start right off from web browser and their work email address.

Creating Reports in Power BI:

  • Get Data : The first step is to import data for the reports. One has the choice of selecting different data sources be it databases, files, or database solutions in the cloud like Azure SQL Database, Azure SQL Data Warehouse, Spark on Azure HD Insight, and SQL Server Analysis Services using DirectQuery. After selecting the sources and providing valid credentials for them, one can import the data. One can get data using two options import and direct query. When one uses import option, any data changes at source are not reflected in data, until we manually refresh the source but using direct query one can have a live connection with source and changes at source are immediately reflected in power BI. To elaborate, Power BI allows connectivity to the following databases for data retrieval:
  • SQL Server Database
  • Access Database
  • SQL Server Analysis Services Database
  • Oracle Database
  • IBM DB2 Database
  • MySQL Database
  • PostgreSQL Database
  • Sybase Database
  • Teradata Database
  • SAP HANA Database
  • IBM Informix Database (Beta)
  • SAP Business Warehouse
  • Amazon Redshift (Beta)
  • Impala (Beta)
  • Snowflake (Beta)
  • Manage Relationships : Power BI gives the capability to manage complex relationships among data. One can create new relationships and can update the existing ones.
  • Data Modelling : Data type and data format while importing can be managed using data modelling window. One can also create calculated columns and new measures using DAX(Data Analysis Expressions, a formula language ). These calculated columns and measures can also be used to make reports more useful and informative. Data hierarchies can also be created by dragging a field into another field.
  • Designing Reports : Different varieties of visualizations and charts are available in Power BI Desktop. Different types of highly interactive bar charts, column charts, line charts, area charts, scatter chart are available. One can also use map to visualize geographical data. One can manage every visual aspect of the report. Dimension based filters can also be used in reports with ease. Drill down feature can also be used after creating hierarchies.
  • Analytics : The latest release of Power BI has Analytics and Forecasting capabilities built into it. With the help of Analytics pane in Visualization area, one can create many types of dynamic reference lines, select which measure they target and customize their look and feel.

Power BI does not come without its drawbacks though:

  • Sharing – Power BI reports and dashboards can only be shared with users within the organisation or which are listed with one’s Office 365 tenant.
  • Data Cleansing – Power BI takes data cleansing for granted and thus if data is not scrubbed, one might want to look into other solutions for the same.
  • Large Datasets – Large datasets will probably give lot of time-outs or performance issues if data does not reside in SQL Server.

Some of Power BI best practices include:

  • Rename tables and columns to descriptive terms so that they could easily be identified. Also, hide unnecessary columns and columns not needed by user.
  • Power BI does not detect the correct data type for an entire column if it encounters null. So, if the fact table has nulls, make sure to include a key in respective dimension table for null values and use that key instead.
  • Wherever possible, try to use a single column primary key. One can also create a surrogate key for the purpose. This has shown to really help with performance.
  • Providing distinct synonyms to column names and queries helps with NLP (Natural Language Processing) results in Q&As. The word distinct here is important so that it does not end up with unnecessary results in answers.
  • Creating hierarchies should be an integral part of the design phase as it also helps in utilising drill down feature in reports.
  • One should try to pre-identify the measures which will be frequently used in reports and those aggregates should be part of the source query.

understanding-power-BI
Self Service BI is a form of business intelligence wherein the end users can design reports as per analysis, play with data chunk without knowing actual programming and SQL queries, save and deploy the reports without any assistance of IT staff.
Power BI is a self-service business intelligence solution with interactive visualizations capabilities. It offers many business analytics tools to analyse and visualize the data. It can be used to make highly interactive reports and dashboard. Power BI dashboards are updated in real time and are available on different devices. Excel spreadsheets, on-premises data sources, big data, streaming data, and cloud services; all these can be connected to Power BI with ease. Users can easily explore underlying data behind dashboard using interactive tools.
Why Data Visualization?
The human brain processes information, using images/charts/graphs to visualize and relate to large amount of complex data. It is easier to understand and recall information visually   than just table of enormous data. Data visualization is a quick and easy way to convey concepts in a universal way. Using Power BI and data visualization, questions about the data can easily be answered.
Power BI helps in identifying areas that need attention or improvement, clarify factors influencing customer behaviour. It also helps in making data driven decisions and predicting future growth.
Why prefer Power BI over its complements:

  • Integration – Its integration with Office 365 suite adds to its usability by providing compatibility with Excel. It is also compatible with SharePoint which is used in many organisations for their ERP systems.
  • Sharing – PowerBI provides granular set of permission levels for the content to be shared wherein we can selectively display or hide information from certain individuals.
  • Mobile Support – Power BI has native apps for iOS, Android and Windows devices.
  • Desktop Client not required – One can start right off from web browser and their work email address.

Creating Reports in Power BI:

  • Get Data : The first step is to import data for the reports. One has the choice of selecting different data sources be it databases, files, or database solutions in the cloud like Azure SQL Database, Azure SQL Data Warehouse, Spark on Azure HD Insight, and SQL Server Analysis Services using DirectQuery. After selecting the sources and providing valid credentials for them, one can import the data. One can get data using two options import and direct query. When one uses import option, any data changes at source are not reflected in data, until we manually refresh the source but using direct query one can have a live connection with source and changes at source are immediately reflected in power BI. To elaborate, Power BI allows connectivity to the following databases for data retrieval:
  • SQL Server Database
  • Access Database
  • SQL Server Analysis Services Database
  • Oracle Database
  • IBM DB2 Database
  • MySQL Database
  • PostgreSQL Database
  • Sybase Database
  • Teradata Database
  • SAP HANA Database
  • IBM Informix Database (Beta)
  • SAP Business Warehouse
  • Amazon Redshift (Beta)
  • Impala (Beta)
  • Snowflake (Beta)
  • Manage Relationships : Power BI gives the capability to manage complex relationships among data. One can create new relationships and can update the existing ones.
  • Data Modelling : Data type and data format while importing can be managed using data modelling window. One can also create calculated columns and new measures using DAX(Data Analysis Expressions, a formula language ). These calculated columns and measures can also be used to make reports more useful and informative. Data hierarchies can also be created by dragging a field into another field.
  • Designing Reports : Different varieties of visualizations and charts are available in Power BI Desktop. Different types of highly interactive bar charts, column charts, line charts, area charts, scatter chart are available. One can also use map to visualize geographical data. One can manage every visual aspect of the report. Dimension based filters can also be used in reports with ease. Drill down feature can also be used after creating hierarchies.
  • Analytics : The latest release of Power BI has Analytics and Forecasting capabilities built into it. With the help of Analytics pane in Visualization area, one can create many types of dynamic reference lines, select which measure they target and customize their look and feel.

Power BI does not come without its drawbacks though:

  • Sharing – Power BI reports and dashboards can only be shared with users within the organisation or which are listed with one’s Office 365 tenant.
  • Data Cleansing – Power BI takes data cleansing for granted and thus if data is not scrubbed, one might want to look into other solutions for the same.
  • Large Datasets – Large datasets will probably give lot of time-outs or performance issues if data does not reside in SQL Server.

Some of Power BI best practices include:

  • Rename tables and columns to descriptive terms so that they could easily be identified. Also, hide unnecessary columns and columns not needed by user.
  • Power BI does not detect the correct data type for an entire column if it encounters null. So, if the fact table has nulls, make sure to include a key in respective dimension table for null values and use that key instead.
  • Wherever possible, try to use a single column primary key. One can also create a surrogate key for the purpose. This has shown to really help with performance.
  • Providing distinct synonyms to column names and queries helps with NLP (Natural Language Processing) results in Q&As. The word distinct here is important so that it does not end up with unnecessary results in answers.
  • Creating hierarchies should be an integral part of the design phase as it also helps in utilising drill down feature in reports.
  • One should try to pre-identify the measures which will be frequently used in reports and those aggregates should be part of the source query.

[:]