Sunday, 7 February 2021

What are the key Features of Power BI Free, Pro and Premium

What are key differences between Power BI Free vs Pro vs Premium
Lets have a quick introduction about Power BI.
What is Power BI :
Power BI is a collection of software services, apps, and connectors that work together to turn your unrelated sources of data into coherent, visually immersive, and interactive insights. Your data source may be an Excel spreadsheet, or a collection of cloud-based and on-premises hybrid data warehouses. Power BI lets you easily connect to your data sources, visualize and discover what's important, and share that with anyone or everyone you want.
The key parts of Power BI :
Power BI consists of several elements that all work together, starting with these three basics:
Power BI Desktop : A Windows desktop application.
Power BI Service : An online SaaS (Software as a Service) service. 
Power BI Mobile :  An app version for Windows, iOS, and Android devices.

These three elements--Power BI Desktop, the service, and the mobile apps--are designed to let you create, share, and consume business insights in the way that serves you and your role most effectively.

Beyond those three, Power BI also features two other elements:
Power BI Report Builder : 
A tool for creating paginated reports to share in the Power BI service. 
Power BI Report Server : 
An on-premises report server where you can publish your Power BI reports, after creating them in Power BI Desktop.
Power BI Report Server is a solution that you deploy behind your firewall and then deliver your reports to the right users in different ways, whether that's viewing them in a web browser, on a mobile device, or as an email. And because Power BI Report Server is compatible with Power BI in the cloud, you can move to the cloud when you're ready.

The flow of work in Power BI :
One common workflow in Power BI begins by connecting to data sources in Power BI Desktop and building a report. You then publish that report from Power BI Desktop to the Power BI service, and share it so business users in the Power BI service and on mobile devices can view and interact with the report.

Another workflow involves paginated reports in the Power BI service. The Enterprise report creators design paginated reports to be printed or shared. They can also share these reports in the Power BI service. They're called paginated because they're formatted to fit well on a page. They're often used for operational reports, or for printing forms such as invoices or transcripts. They display all the data in a table, even if the table spans multiple pages. Power BI Report Builder is the standalone tool for authoring paginated reports.
Now will discuss about the key features and differences between the Power BI Free, Pro and Premium

1) Power BI Free :
Power BI Free enables you to connect to 70+ data sources, publish to the web, and export to excel. There are some limitations to the free version, for e.g. you can’t do peer-to-peer sharing and you can’t create App workspaces. However, the free version is great for doing your own analysis and don’t need to distribute the analysis to other end users. 
You also have the connectivity options like Direct Query, live connection, and the use of the gateway. The same visualizations that are available in Power BI Pro are also available in the Power BI Free.

If an Organization has Power BI Premium capacity-based license, the it allows users with a free license to act on content in workspaces that are assigned to Premium capacity. 
Outside of Premium capacity, a user with a free license can only use the Power BI service to connect to data and create reports and dashboards in My Workspace. They can't share the content with others or publish content to other workspaces.

Power BI Free vs. Pro :

2) Power BI Pro :
Power BI Pro is an individual user license that lets users read and interact with reports and dashboards that others have published to the Power BI service. Users with this license type can share content and collaborate with other Power BI Pro users. Only Power BI Pro users can publish or share content with other users or consume content that's created by others, unless a Power BI Premium capacity hosts that content.

You can sign up for a free 60-day individual trial of Power BI Pro. Select the upgrade dialog that appears in the Power BI service whenever you try to use a Pro feature.

Key features of the Power BI Pro :
The Power BI Pro cost is around $9.99(USD)/user/month. It is also included in the Office365 Enterprise E5. It offers the following key features:
On-Premise Data Gateways :
If your data resides On-Premise (not somewhere in the cloud), you can now connect to this data and analyze it. A common example of this is a self-hosted SQL database.
More Data Storage :
Power BI Pro allows you up to 10 GB per Power BI Pro License. The free version caps you at 1GB per user.
Better Data Refreshes :
In Power BI Pro, you can better schedule(8 times per day) data refreshes so that your team always has the latest version of your data.
Sharing and Collaboration :
It allows you to share your data with individual coworkers, or publish enterprise-wide “content packs” and “apps” with row-level data security. You can neither share with others nor consume shared content with Power BI Free. Everyone that you want to share content with must be assigned a Power BI Pro license, unless you decided to upgrade to Power BI Premium.

Notes :
A Power BI license with free and Pro per-user license only uses a shared and limited capacity to process content. If content is stored in that shared capacity, users who are assigned with a Power BI Pro license can collaborate only with other Power BI Pro users. 
They can consume content shared by other users, publish content to app workspaces, share dashboards, and subscribe to dashboards and reports. When workspaces are in Premium capacity, Pro users may distribute content to users who don't have a Power BI Pro license.
When using Premium Per User licenses, content created by a Premium Per User licensed user can only be shared with other users that have a Premium license, unless that content is specifically put on a workspace hosted on a Premium capacity.

How to Purchase Power BI Pro user licenses :
To purchase and assign licenses to members in the Microsoft 365 admin center, you must be a member of the global administrator or Billing administrator role in Microsoft 365.
After you buy licenses, you can assign them to users in either the Microsoft 365 admin center or the Azure portal.
You may follow these steps to purchase Power BI Pro licenses in the Microsoft 365 admin center:
  • Sign in to the Microsoft 365 admin center.
  • On the navigation menu, select Billing > Purchase services.
  • Search or scroll to find the subscription you want to buy. You'll find Power BI under Other categories that might interest you near the bottom of the page. Select the link to view the Power BI subscriptions available to your organization.
  • Select Power BI Pro.
  • On the Purchase services page, select Buy.
  • Choose Pay monthly or Pay for a full year, according to how you want to pay.
  • Under How many users do you want? enter the number of licenses to buy, then select Check out now to complete the transaction.
  • To verify your purchase, go to Billing > Products & services and look for Power BI Pro.
  • To add more licenses later, locate Power BI Pro on the Products & services page, and then select Add/Remove licenses.
How to Assign licenses in the Azure portal
You may follow these steps to assign Power BI Pro licenses to individual user accounts:
  • Sign in to the Azure portal.
  • Search for and select Azure Active Directory.
  • Under Manage on the Azure Active Directory resource menu, select Licenses.
  • Select All products from the Licenses - Overview resource menu, then select Power BI Pro to display the list of licensed users.
  • From the command bar, select + Assign. On the Assign license page, first choose a user, then select Assignment options to turn on a Power BI Pro license for the selected user account.
3) Power BI Premium :
Power BI Premium is an organizational subscription that provides a different way to store content in a virtual container called a capacity. With Premium capacity, anyone with permissions, whether they're inside or outside your organization, can view content stored in this Premium capacity, without purchasing individual Power BI Pro or Premium per-user licenses.

The Premium is designed from the ground up to address the challenges of large enterprise deployments and demanding workloads. A new service architecture was created to optimize for these enterprise scenarios, including the ability for organizations to serve a large number of “readers/viewers” who simply need to view and explore dashboards and reports in a cost-effective manner and without needing to license each user individually.

The Apps and App Workspaces with a diamond icon indicates that the content is stored in Premium capacity.

Premium capacity enables widespread distribution of content by Pro users without requiring Pro licenses for the recipients who view the content. 
The person who is creating content in the Premium capacity uses a Pro licenses to connect to data sources, model data, and create reports and dashboards that are saved to a workspace in Premium capacity. 

Users without a Pro license can still access a workspace that's in Power BI Premium capacity, as long as they're assigned a role in that workspace. If the workspace owner creates an app based on content in that workspace, users without a Pro license can still view that app in the Premium capacity, as long as they've been given permissions for that app.

Within those workspaces, developers assign roles, like Viewer, Contributor, Member, and the Administrator. These roles determine the extent to which the colleagues can interact with the content.

When a Developer creates and publishes an app, they grant access to entire organizations or individuals. The extent to which you can interact with the app's content depends on specific access permissions given to you. 
For example, you may be given access to view the app, connect to the underlying datasets, make copies of reports, or share the content.

Key features of the Power BI Premium :
Separate Resources:
No more “noisy neighbors”. Power BI Premium gives you your own processing environment, so your Power BI operations can’t be slowed down by other users that aren’t even within your company.
More Storage:
Your company gets up to 100 TB of data storage to share in Power BI Premium.
Larger Datasets:
Power BI Premium allows you to work with datasets up to 50 GB in size.
Free User Access to Shared Content:
If you use Power BI Premium, then free users are able to consume shared reports and dashboards.

Power BI Pro vs Premium : (Note : Subject to Change in Future)
The following are the key comparisons between Power BI Pro and Premium as of now.

Dedicated capacity with Power BI Premium :
The Power BI Premium offering introduces the concept of dedicated capacity, and it is the heart of the offering. Power BI Premium enables dedicated capacity, a set of the resources (memory, processors, hardware) reserved for the exclusive use by you, the customer.
Dedicated capacity nodes: the v-cores :
Of course, different BI workloads will require different compute power. Supporting hundreds of users of few reports and dashboards is very different than enabling 100,000 users to consume many BI applications.
When you decide to purchase Power BI Premium, you will need to provision enough capacity to support your workload. Capacity is provisioned through purchasing capacity nodes. 
The capacity nodes you purchase have a certain number of v-cores (“virtual cores”), memory, and bandwidth that will need to be sufficient to power your BI system. Each node is composed of a number of v-cores: frontend cores and backend cores.

The frontend cores are responsible for the web service, dashboard and report document management, access rights management, scheduling, APIs, uploads and downloads, and generally for everything that relates to the user experience.

The backend cores are responsible for heavy liftings: query processing, cache management , running R servers, data refresh, natural language processing, real-time feeds, and server-side rendering of reports and images. 
With the backend cores, a certain amount of memory is reserved as well. Having sufficient memory becomes especially important when dealing with large data models or with a large number of active datasets.

The backend cores that do the heavy lifting are fully dedicated to you, and they will not be shared by others. The frontend cores are shared with others, and they are computed as your portion in the pool of machines that handles the shared frontend services in Power BI.
At launch, Microsoft will offer three sizes for Premium capacity: P1, P2, and P3. Each comes with a different number of v-cores and memory size:

The Storage is set to 100 TB per capacity node.
The resources and limits of each Premium SKU (and equivalently sized A SKU) are described in the following table :

How much capacity needed?
You should plan on provisioning enough capacity to be able to comfortably handle your peak usage. Make sure to plan for some offline scheduled activities such as data refresh, smart alerting, or broad email distributions. If you have not reserved sufficient capacity, your cores could become overloaded and the responsiveness of the system will suffer.
Microsoft has provided a Capacity Calculator on its website to help you plan your Power BI Premium deployment.
The calculator uses a simple usage model based on Microsoft experience across many different organizations. However, the variability of the usage patterns is very significant and will depend on many factors including the specific data models used, the number of queries and their complexity, the hourly distribution of the usage of the service, the data refresh rates, the use of advanced features like Q&A, R and email distributions, and other usage patterns. As such you must use the calculator with caution and with tempered expectations, estimating capacity is not an exact science and performance cannot be guaranteed.

Sample Calculation :

Please Note:
Images/Content Source: /
Please note that, I have consolidated the multiple related topics in to one single article, only for the sake of user Knowledge purpose only, not for any Decision making. There could be manual/typo errors while preparing this article. Also note that, this information is subject to change in future.

Regards, Tamatam

Saturday, 5 December 2020

How to Replace Nulls with Zero in Pivoted Data in SQL

How to replace Null values to 0 in dynamic Pivot columns in SQL Server
In SQL Server, T-SQL Queries, we may come across with a Scenario, where we need to replace the NULL values with 0, in the dynamic Pivot Columns.
We can handle that Scenario as explained in detail in the following Stored Procedure. In this Procedure we are doing the following steps: 
--Storing the Aggregated data from Source table into a Temp Table
--Pivoting the Data from the Temp table and Inserting into another Temp Table
--Replacing the NULL values with 0 in Pivoted Data.
--Finally UnPivoting the updated Pivoted data and inserting into a Temp Table.
Please note that, you can consider only the steps that are relevant to your Scenario.
USE AnalyticsDB

CREATE PROCEDURE [dbo].[SP_Audit_Procecss_Summary]
DECLARE @vQuery NVARCHAR(4000),@vSQL NVARCHAR(4000), @vColumns NVARCHAR(4000), @vRowCount INT, @Val1 INT, @vDate VARCHAR(255);

--Storing the Source Data in a Temp Table
IF OBJECT_ID('[tempdb].[dbo].[##Temp_Process_Audit]', 'U') IS NOT NULL
   Drop Table [dbo].[##Temp_Process_Audit] ;
   Print ('Temp Table [##Temp_Process_Audit] has Dropped and Re-created')

SELECT * INTO [dbo].[##Temp_Process_Audit]
FROM ( Select [Audit_Date], [Quality_Rule], [Region], [Procecss], 
              SUM ([QR_Fail]) AS Total_QR_Fail
      From [dbo].[Process Audit Details]
      Group By [Audit_Date], [Quality_Rule], [Region], [Procecss]
   ) S1 ORDER BY S1.Audit_Date ;

--SELECT * FROM [dbo].[##Temp_Process_Audit]

--Storing the Distinct [Audit_Date] values in a Temp Table which will used as reference, 
  while we replacing the NULL values in the Pivoted Data.
IF OBJECT_ID('[tempdb].[dbo].[##Temp_Dist_AuditDates]', 'U') IS NOT NULL
   Drop Table [dbo].[##Temp_Dist_AuditDates] ;
   Print ('Temp Table [##Temp_Dist_AuditDates] has Dropped and Re-created')

INTO [dbo].[##Temp_Dist_AuditDates] FROM [dbo].[##Temp_Process_Audit] ;

--SELECT * FROM [dbo].[##Temp_Dist_AuditDates];

-Pivoting the Data using XML Method and Storing into a Table
IF OBJECT_ID('[tempdb].[dbo].[##Temp_Process_Audit_Pivot]', 'U') IS NOT NULL
   Drop Table [dbo].[##Temp_Process_Audit_Pivot] ;
   Print ('Temp Table [##Temp_Process_Audit_Pivot] has Dropped and Re-created')

--Getting the list of Unique [Audit_Date] values which will used as new columns in Pivot
SET @vColumns=STUFF (
    ( SELECT DISTINCT ',' + QUOTENAME(S1.[Audit_Date])
       FROM [dbo].[##Temp_Process_Audit] S1
       FOR XML PATH (''), TYPE).value('.' , 'NVARCHAR(4000)' ),
        1, 1, '');

SET @vQuery=' SELECT [Quality_Rule], [Region], [Procecss], '+@vColumns+'
  INTO [dbo].[##Temp_Process_Audit_Pivot] 
  FROM ( SELECT [Audit_Date], [Quality_Rule], [Region], [Procecss],                                         [Total_QR_Fail] FROM [dbo].[##Temp_Process_Audit] )
                          PIVOTSOURCE  PIVOT ( MAX([Total_QR_Fail]) FOR 
                          [Audit_Date] IN (' +@vColumns+ ') ) Pvt ' ;
EXECUTE (@vQuery);

--SELECT * FROM [dbo].[##Temp_Process_Audit_Pivot] ;

--Replacing NULL values with 0 in the Pivoted Data
SELECT @vRowCount=COUNT(*) FROM [dbo].[##Temp_Dist_AuditDates];
SET @Val1=1;
WHILE @Val1<=@vRowCount
SELECT @vDate=[Audit_Date] FROM [dbo].[##Temp_Dist_AuditDates]
SET @vSQL='UPDATE [dbo].[##Temp_Process_Audit_Pivot]  
                SET [' + @vDate + ']=0 WHERE [' + @vDate + '] IS NULL' ;
SET @Val1=@Val1+1

--SELECT * FROM [dbo].[##Temp_Process_Audit_Pivot] ;

--Finally UnPivoting the updated Pivoted Data
IF OBJECT_ID('[tempdb].[dbo].[##Temp_Process_Audit_UnPivot]', 'U') IS NOT NULL
    Drop Table [dbo].[##Temp_Process_Audit_UnPivot] ;
    Print ('Temp Table [##Temp_Process_Audit_UnPivot] has Dropped and Re-created')

SET @vQuery='SELECT [Audit_Date], [Quality_Rule], [Region], [Procecss],[Total_QR_Fail]
                          INTO [dbo].[##Temp_Process_Audit_UnPivot]  
  FROM [dbo].[##Temp_Process_Audit_Pivot]
UNPIVOT ( [Total_QR_Fail]  FOR [Audit_Date] IN (' +@vColumns+ ') ) UnPvt ' ;
EXECUTE (@vQuery);

--SELECT * FROM [dbo].[##Temp_Process_Audit_UnPivot] ;


If the Pivoted table has 16 rows with 3 Pivoted Columns, when we UnPivot it, we will get 16* 3 =48 rows.

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

How to do Pareto Analysis in Power BI using DAX

How to apply the 80/20 rule to analyze the Data using Power BI DAX
Pareto Analysis uses the Pareto Principle, known as the "80/20 Rule", which was introduced by the Italian economist, Vilfredo Pareto.
Pareto Analysis is a statistical technique in decision-making used for the selection of a limited number of tasks that produce significant overall effect.
The Pareto Principle states that 80 percent of a project's benefit comes from 20 percent of the work. Or, conversely, the 80 percent of problems can be traced back to 20 percent of causes.
This technique is also called the vital few and the trivial many.

We can Improve the Business / Process performance by Identifying and resolving the Vital 20 percent of the reasons that causing problems which impacting our 80 percent of the Business outcomes.

Lets discuss on how we can analyze the data using Pareto Analysis, using below example.
Scenario :
Suppose, We would like analyze, what are the vital reasons that Causing the Sales Orders are getting revised in the Business, which has Impact on our Business Processes(eg. O2C).
In a Business Scenario (e.g.O2C / I2C /P2P Process) , the Orders could be revised, because of the following reasons (just assume that):
--Incorrect/Change in Order Quantity,..
--Incorrect/Change in Shipping/Billing/Delivery Address,..
--Incorrect/Change in Shipping/Billing/Delivery Date...

The data model as follows..

Dim_Revison_Reasons :
This Dimension table contains the reasons behind the Orders Revision.

Fact_Order_Revisios :
This Fact table contains the Orders that are revised. The sample looks as per below.

Method - I :
Now we do the required calculations using DAX, to do the Pareto Analysis. The Calculations are based on the Column "Revison Code" of the same Table, FACT_Order_Revisons.

Sum_Orders = SUM(FACT_Order_Revisons[Order_Value])
Total_Orders = CALCULATE(SUM(FACT_Order_Revisons[Order_Value]), ALLSELECTED(FACT_Order_Revisons))

Cumulative_Sum = VAR vSum_Orders=SUM(FACT_Order_Revisons[Order_Value])
VAR vRevisonSummary=
SUMMARIZE(ALLSELECTED(FACT_Order_Revisons), FACT_Order_Revisons[Revison Code], "Sum_Orders", SUM(FACT_Order_Revisons[Order_Value]))
VAR vCumulative_Sum=SUMX(FILTER(vRevisonSummary,[Sum_Orders]>=vSum_Orders),[Sum_Orders])
RETURN vCumulative_Sum

Cumulative_Ratio = DIVIDE([Cumulative_Sum],[Total_Orders])

The result of the above Calculations is as follows. The Calculations are based on the Column "Revison Code" of the same Table, FACT_Order_Revisons.

Now we can generate a Pareto Chart, using these Measures on the Line and Stacked Column chart.

Notes :
The Fields/Measure needs to place as per below:

The Secondary Y-Axis needs to enable, if in case it is not. And make sure to choose the Start values as 0 and End value as 1.

Finally, format the Data Colors (Line color; Bars colors using rules) as per below :

Method - II :
In this Method, we will just look at the other ways of doing the above discussed Calculations, when the Column "Revison Code" is from related Dimension table, Dim_Revison_Reasons.

Sum_Orders = SUMX(RELATEDTABLE(FACT_Order_Revisons),'FACT_Order_Revisons'[Order_Value])

TotalOrders = CALCULATE([SumOrders], ALLSELECTED('Dim_Revison_Reasons'))

Rank_Orders = RANKX(ALLSELECTED(Dim_Revison_Reasons),SUMX(RELATEDTABLE (FACT_Order_Revisons),FACT_Order_Revisons[Order_Value]),,DESC)

CumulativeSumX = SUMX(TOPN([Rank_Orders],ALLSELECTED(Dim_Revison_Reasons),[SumOrders]),[SumOrders])

The Cumulative Sum can be calculated using the following expression as well.
CumulativeSum = CALCULATE([SumOrders],TOPN([Rank_Orders],ALLSELECTED(Dim_Revison_Reasons),[SumOrders]))

CumlativeRatioX = DIVIDE([CumulativeSumX],[TotalOrders])

The result of the above Calculations are as follows :
Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

Subscribe to Blog Posts by Email

ExcelKingdom-Popular Posts