Here you will see the information about Power Bi architecture and how to create, publish, and schedule a Power BI dashboard, how to create parameters, and also limitations of Power BI.

Data Sources: In power BI data sources can be broadly classified into two types cloud data sources and On-premise data sources.

• Cloud data sources: Azure SQL Database, Azure SQL Data Warehouse, Azure Blob Storage, Excel files in OneDrive, Online Services as Facebook, Mail Chimp, Salesforce, Google Analytics.
• On-Premise Data sources: premise data sources can be further classified into two types as On-premise databases and On-premise files.

On-premise databases: Oracle, My SQL, Teradata, SAP HANA, SQL Server Analysis Services(SSAS), Sybase, IBM DB2.

On-premise files: Excel, CSV, XML, Text files.

Power BI Authoring PC: This authoring machine should have two components installed they are:

• Power BI Desktop: It is a windows application which can be downloaded from Microsoft’s Power BI portal. By using this application, you can design interactive reports and dashboards. Report files can be saved on local disk with .pbix extension. The reports and datasets can be shared in the Power BI portal.
• Power BI Personal Gateway: Needs to be installed separately on authoring PC along with Power BI Desktop and is a bridge providing quick and secure transfer of data in between the Power BI portal and on-premises data sources. Available only with Power BI Pro Subscription. Azure Service Bus provides security for the data transferred between the gateway and the Power Bi Portal, providing a secure channel between your computer and the Power Bi service. This secure channel means that there is no need for you to open any ports in the computer firewall.

Power BI Portal: It is where users publish their datasets and reports, create dashboards and content packs, and share data with team members of their organization. Users can sign in to the portal using their organization account. Each user will have a personal workspace in the portal named My Workspace, which comprises dashboards, reports, and datasets published by the user.

End Users: Dashboards can be shared via email with multiple stakeholders. Power BI offers a free mobile app for IOS, Android, and Windows devices. Reports and dashboards automatically adjust their sizes to fit the screen of the device, so users need not worry about creating mobile versions of the work. Users can interact with reports and dashboards through filters, slicers, drilling, etc.

Creating a Dashboard

Importing data source

• AdventureWorks database needs to be imported from the Microsoft SQL Server. Click on get data icon in the home bar.

• Go to the database and select the SQL Server database and click on connect.

• Now mention the server name and click on ok.
• Select the AdventureWorks database and check the boxes for the tables that need to be imported and click on load.
• After importing tables will be shown in the fields pane.
• Click on the edit queries icon in the home bar to view the data for all the tables.

• A new edit queries window will open, which will show data for all tables where a lot of transformations can be done on the data using the edit Queries window. Click on close and apply after changes have been applied.

Designing the Dashboard

Below are the requirements that should be achieved while designing a dashboard

Requirement-1: To display overall total sales of the company.

Requriment-2: To create drill down on SalesTerritoryGroup> SalesTerritoryCountry> SalesTerritoryRegion and compare total sales.

Requirement-3: To show sales for product subcategories in the selected product category for both internet and reseller sales.

Requriment-4: Locate countries and their respective sales on a geographical map.

Requirement-5: Create a filter on the sales territory group.

Now let us understand each requirement one by one.

Requirement-1: To display the overall total sales of the company.

• To fulfill this requirement, card visualization can be used.
• Drag and drop the card visualization from the visualization pane.
• Right-click on fact internet sales and select a new measure.
• In the measure window, type the name of the measure as  TotalSalesbyDAX.
• Type in the DAX query as:

TotalSalesbyDAX = SUM(FactInternetSales[SalesAmount]) + SUM(FactResellerSales[SalesAmount])

• Now drag the measure into value field of the card. And do some formatting by using the format pane of card visualization.

Requriment-2: To create drill down on SalesTerritoryGroup> SalesTerritoryCountry> SalesTerritoryRegion and compare total sales.             

• To fulfill the requirement, a bar chart can be used.
• Select the Bar  chart from the visualization panel.
• Expand the table DimSalesTerritory and select the SalesTerritoryGroup column and drop it on the  Axis  section   of the Fields pane.
• To create the hierarchy, drag the SalesTerritoryCountry column and drop it below SalesTerritoryGroup in the Axis section.
• Also, drag the SalesTerritoryRegion column and drop it below SalesTerritoryCountry. This creates the hierarchy.
• In the value section, drag the measure TotalSalesbyDAX.
• Now some formatting is required for the bar chart, so maximize the chart by clicking on the icon.
• Go to format pane and enable the data label property now the vales will appear and change the font color, size, background color of data labels.
• As hierarchy got created, data can be drill down from                            SalesTerritoryGroup> SalesTerritoryCountry> SalesTerritoryRegion. Right-click on the bar and click on drill down or use the drill down arrows, which are appearing at the top right corner.
• On drilling down Europe, total sales with respect to countries in Europe are shown.
• And this creates drill down on SalesTerritoryGroup> SalesTerritoryCountry> SalesTerritoryRegion and compares total sales.

Requirement 3: To show sales for the product subcategories in the selected product category for both internet and reseller sales.

 

Steps to Create a treemap for Internet Sales

• To fulfill this requirement, a treemap can be used.
• Select a treemap from the visualization panel.
• To build the hierarchy and enable drill-down, drag, and drop.
• EnglishProductCategoryName and EnglishProductSubCategoryName columns from DimProductCategory and DimProductSubCategory tables, respectively, on  group  section of the Fields pane.
• Drag and drop SalesAmount column from FactInternetSales table in the  Value  section.
• In this tree, chart drill-down is created from.

EnglishProductCategoryName> EnglishProductSubCategoryName so right-click on bikes and do drill down the sales for different subcategories of bikes can be seen.     

 

Steps to Create a treemap for Reseller Sales

• Repeat the steps 1-2, as mentioned in the above chart.
• Select the SalesAmount  column from the FactResellerSales  table instead of the FactInternetSales table while following the third step.

 

This creates treemaps that show the sales made through the internet and reseller in various product categories.

Requirement 4: Locate countries and their respective sales on a geographical map.

 

Steps to Create a Map Visualization

        To fulfill this requirement, map visualization can be used.

• Select Map from Visualization pane.
• To enable the drill-down feature on Map drag and drop SalesTerritoryGroup and SalesTerritoryCountry columns from the DimSalesTerritory table in the location section of Fields pane.
• In the size section, drag the measure TotalSalesbyDAX.
• Formatting can be done by changing the bubble colors using the data color option and increase the bubble size by using the bubble option.
• As the drill-down feature is enabled from SalesTerritoryGroup> SalesTerritoryCountry. On drilling down, European sales made by the individual country in Europe can be seen.

Requirement 5: Create a filter on the sales territory group.

 

 

 

Steps to Add a Slicer

• To fulfill this requirement, the slicer can be used.
• Select slicer from the Visualizations pane.
• Select the DimSalesTerritory table and drag and drop the column SalesTerritoryGroup on to  Field  section. This adds the data of the SalesTerritoryGroup column in the slicer.
• Go to format pane of the slicer to enable multi selection to disable the single select in selection controls. If the select all option is required, it can be enabled. And select either list or dropdown of slicer by clicking down arrow at the top left corner.
• Slicer on SalesTerritoryGroup is created.
• Done with all the requirements of creating a dashboard.

Publishing a Dashboard

• Click on the publish icon, which is on the top right corner.
• A sign-in dialogue box will appear where the Power Bi account email id needs to be entered.
• Give a password and click on sign in.
• Select my workspace.
• A dialogue box will appear, saying that the report is published. Click on the report name; it will navigate to the Power BI portal.
• Now the report in the portal will appear. Click on the icon pin live page to create a dashboard for the report.
• Give the name of the dashboard and click on pin live.
• The dashboard view can be switched between the web view and phone view.

Scheduling a Dashboard

• Click on subscribe to receive the email.
• A dialogue box will appear, saying that an email will be sent whenever the data is updated. Click on save and close.
• Now data refresh for AdventureWorks needs to be scheduled. Go to AdventureWorks and select a scheduled refresh.
• Install power bi personal gateway if the data source is on-premise and mention the gateway connections and enter the credentials of the data source.
• Data refresh can be done daily or weekly and set time zone and mention at what time the data needs to be refreshed if there is a failure in data refresh a notification mail will be sent. Click on apply .
• As a data refresh is scheduled whenever the refresh happens, the subscription will trigger an email to us.

 

 

Requirement: Create parameter on sales territory group

• Go to edit queries in the home bar.
• Go to DimSalesTerritory table right-click on column SalesTerritoryGroup and select add as a new query.
• Now the column SalesTerritoryGroup will be considered as a new table. Right-click on the column and select remove duplicates as you need distinct SalesTerritoryGroup.
• Now SalesTerritoryGroup Query needs to be passed as parameter.
• Click on manage parameters.
• A parameter dialogue box will appear where the name, description, and data type of parameter need to be mentioned. If the suggested value option is any value, only one value can be passed if it is a list of values multiple values can be passed in the current scenario query should be passed as parameter. A select query is suggested values, and in query option select SalesTerritoryGroup_query and the current value, you can mention any among the list of values.

In the current scenario, Europe is taken.

• Click on Ok. Europe will appear as the current value.
• This parameter needs to be applied to the table DimSalesTerritory table. Go to DimSalesTerritory table click on down arrow of SalesTerritoryGroup column select text filters click on equals.
• Filter rows dialogue box will appear—select parameter.
• Now automatically, the parameter will be mapped. Click on OK.
• Now the query data will be filtered on Europe as Europe is mentioned default parameter.
• Click on close and apply it to the query editor. Export the report to Power BI templet. The report will be saved in .pbit format.
• When the templet file is opened, it prompts for the Sales Territory Group where the value should be selected. North America is selected as a filter and click on load then all the data related to North America will be loaded in the report.
• Data related to North America is loaded.
• The difference between .pbit and .pbix files is that a .pbix file will have the data for all the queries, but a .pbit file has only schemas initially. It doesn’t have any data, and data will be loaded only after selecting the parameter; this is the reason .pbit file has less size when compared to .pbix.

Packages that have functions and cursors can be created in oracle, and when that particular package and function is called, it gives the data. In power BI w oracle packages can be called, which will load the data.

 

Requirement: create an oracle package on the customer table when ‘M’ is passed as an input value. It should show the male customers details as output, and if ‘F’ is passed, it should show the female customers details as output.

The package code looks like below:

CREATE OR REPLACE

PACKAGE customer

AS

CURSOR c_tr_customer (p_gender     string default null)

is

select * from "customer" where "Gender"=(p_gender);

TYPE customer_type IS TABLE OF c_tr_customer%ROWTYPE;

FUNCTION Fn_customer (p_gender     string default null) RETURN customer_type PIPELINED ;

END customer

;

/

 

CREATE OR REPLACE

PACKAGE BODY customer as

FUNCTION Fn_customer (p_gender     string default null) RETURN customer_type  pipelined AS

         v_c_tr_type  customer_type;

         v_count      PLS_INTEGER := 0;

   BEGIN

FOR r IN c_tr_customer(p_gender)

         LOOP

            v_c_tr_type := customer_type(r);

            PIPE ROW(r);

            v_count := v_count + 1;

      END LOOP;

RETURN ;

 

END Fn_customer;

END customer;

/

• When package function is called by passing ‘M’ as input, male customer details were shown as output.
• Click on get data in Power BI go to databases select oracle database and click on connect. Give the server name and write the query in the SQL statement.
• ‘M’ is passed as input male customer details will be loaded.

This is how you can call oracle packages in power BI.

• In Power BI, the size of the table does not increase or decrease relatively along with the data, and when data is more scroll bar should be used to scroll down, and when the data is less, the empty space will pop up in the table .
• .Pbit file can’t be uploaded in the Power BI portal you can only upload .Pbix files where the .Pbit file prompts for a parameter so you cannot upload reports with parameters in the portal.
• There is no visibility property in Power BI, like in SSRS, to hide the visualization depending on the condition given.
• Conditional formatting can’t be applied to fields when the data type is other than integers. The conditional formatting pane has only employee key, so conditional formatting can be applied only in numerical fields.
• Dashboards and reports can only be shared with users who have the same email domains or email domains listed with Office 365 tenant.
• Microsoft Power BI is still in the early days. The tool will be getting updates every month by considering the requests from the users.
• Scheduling of reports in power BI portal is not as flexible as Scheduling SSRS reports using share point.