Power BI - Data Analytics, Modeling & Advanced Reporting

Besides all technical reasons, It is everywhere - Power BI is a leader in Gartner's Magic Quadrant for Analytics and Business Intelligence Platforms.

There is a reason it's called "Power" BI - An incredibly powerful tool for both modeling and visualizing data. It has the same base as Power Query.

As an Analyst, you would find Microsoft Power BI to be a fantastic tool that can handle most of the analytics pipeline. It goes from ingesting and modeling data to presenting it as dashboards and sharing the dashboards with the people who care about them.

PERFORMING CALCULATIONS USING DAX FUNCTIONS

• Table-Valued Functions
• Date and Time Functions
• Filter Functions
• Information Functions
• Logical Functions
• Mathematical and Trigonometric Functions
• Statistical Functions
• Text Functions
• Time Intelligence Functions

CREATING ADVANCED DAX MEASURES WITH ADVANCED DAX FUNCTIONS

• Calculate()
• All()
• Filter()
• IF()
• Switch()
• SumX()

What is Power Query?

• It’s a Powerful data analysis and data modeling tool available in Excel as part of Office 365 Subscription.
• A way to handle massive volumes of data in Excel
• A way to add any formulas to pivot, and it adjusts!
• A way to turn workbooks into self­maintaining web applications
• A much more agile and cost effective approach to Business Intelligence
• Something that turns workbooks into industrial strength Apps

The easiest way to create a data model in Power BI and Power Pivot is to gather the data "as is" from a data source, define relationships, and then start designing reports. But this approach raises several issues when you have too many tables, or different levels of granularity.

Benefits of a proper data model with power pivot includes:

• Faster queries
• Simpler DAX code
• Easier maintenance

You need to perform data transformations in the Query Editor that cannot be done by simply using the User Interface.

To perform complex operations on data in the Power Query Editor

MS-SQL is a recommended skill while working with Power BI. You can say it is one of the basic building block if you are looking forward to work full-time with BI.

It offers you more flexibility to implement complication logic which otherwise requires multiple steps inside Power BI model. Also, if you're using direct query mode then you need to have a strong knowledge on SQL as almost all processing happens at the source systems else you would restrict yourself to only limited modeling and designing visualizations.

Important things MS-SQL can help you with

• Create an optimal data model for space and speed
• Manage partitions the right way
• Design an efficient and trustable security model
• Define an ideal processing strategy
• Automate deployment and management of the data model

Why to have MS-SQL as Data warehouse?

While power query could do the major transformations - required for the reporting purpose, it is not advisable and recommended to access the production database for reporting requirements. ETL data from multiple data sources into your data warehouse and then load it to your BI via T-SQL is the best practice.

The whole purpose of this section is to arrive at the best possible BI reporting solution for existing business problems instead of just performing a task.

• To build a light-weight solution for a huge dataset.
• Improvise the Data refresh times and reduce associated cost.
• Use the right mix of available technologies to work with huge datasets - for reporting purpose.