MS Power BI with Gateway

This time I thought of exploring MS Power BI a bit, which is coming up very well and has played an important role in putting Microsoft in the Leader's position in recent times.

Before getting into our Topic, just a small introduction about Power BI and prerequisites for using the Power BI Gateway.

Power BI is a suite of business analytics tools that deliver insights throughout the organization. In short, "Quickly go from Data to Insight, and from Insight to Action".

Prerequisites for using Power BI Gateway:

1. Power BI Desktop is installed

2. Access to https://powerbi.microsoft.com

3. Power BI Gateway is installed (here we have used 'Personal Gateway')

Once we have all our ingredients in place, it is time to start cooking...

In my Database, I have 5 tables as shown in the screenshot below. But I am going to use only 3 tables i.e. SKILL_D, EMPLOYEE_D and SKILL_MATRIX_F.

NOTE:: Keeping in mind the Naming Convention of the tables in Database. Anything_D means it is a Dimension Table and Anything_F means it is a Fact Table.

Now, in the MS Power BI Desktop, I click on 'Get Data' to connect to the Data Source.

MS Power BI supports data from various Data Source for e.g. File, SQL Server, Oracle, Teradata etc). For my purpose I am selecting "Oracle DB".

I am using my local Database "XE" and importing the above mentioned tables. The screenshot is given below:

And importing  SKILL_D, EMPLOYEE_D and SKILL_MATRIX_D tables / data

In the Power BI Desktop, you have 3 views :

1. Report View - Used to create reports

2. Data View - view the data, edit columns, add measures etc

3. Relationship View - Create the relationship between the tables

Before creating a Report in Power BI, make sure to check the Relationship View to make sure that the relationship between the tables is properly established. In the below screenshot, we can see that the relationship is established perfectly by default.

Once the Relationship View is confirmed, you can view the data in the Data View; just to make sure that the Data is getting populated correctly. I got the below screen on my system.

After making sure everything is fine, I add a new measure 'DISTINCTCOUNT(EMPLOYEE_SID)' by right clicking on the Table in the 'Data View'. Remember to use the EMPLOYEE_SID Column from SKILL_MATRIX_F Table.

One thing you will notice that building measures / columns using DAX Functions is real easy and intuitive.

Once the expression is complete, I rename this column as "# of Employee".

Similarly I rename the "SKILL_DESC" Column to "Skill".

Next, I go to the 'Report View'. There are different types of charts which we can use as per our requirement, and creating them is fairly easy. In this case, I add a Pie chart, and put "Skill" in the Legend and "# of Employee" in the Values.

Next I add a Table, which will be useful for me show the change of data once loaded through the Power BI Gateway. I save this report as 'Power BI Sample 3'.

Now, it is time to publish the report online so that they are accessible from https://powerbi.microsoft.com, for which you need to have the access Since I have the access and I am already Logged-in, so I just have to click on 'Publish' button in the "Home" tab of the Menu Bar at the extreme right.

Once the report is Published online, you will get a confirmation of the same and will be asked to be directed to the report online.

Once published, I can go to 'My Workspace' in Power BI online and can view the reports. I already have 2 earlier reports also.

Click on the report name "Power BI Sample 3" to view the report.

Let's create a Dashboard now to place this report in the Dashboard. I name it as 'Sample Dashboard - Gateway'.

I go back to the report and click on 'Pin Live Page'.

It asks if I want to pin it to an 'Existing dashboard' or 'New Dashboard'. I mark the 'Existing  Dashboard' and select the 'Sample Dashboard - Gateway' from the Drop-down.

Once added we can access the report through the dashboard.

This brings me to end of my first goal to create and publish a Power BI report.

Next I will try to refresh the data using 'Power BI Gateway'.

For that I go to my 'Start Menu' and start the 'Power BI Gateway - Personal', installed in my machine.

Once it is started, you will get a screenshot like below:

Next, we have to check it with our report 'Power BI Sample 3'. For that I go to My Workspace ⟹ Datasets tab, click on the 3 dots under the Action Column and then select 'Settings' option.

In the Settings I see Gateway connection is fine, but there is some issue with the Data source credential. Let's investigate that.

I just have to change the Authentication method to 'Basic'.

And that resolves the error. So now I am good to go.

It is time to change the data, I add a new employee 'Kunal'.

And let's add 3 new rows in the 'SKILL_MATRIX_F' table for this new employee.

After making the changes and saving them, I need to refresh the data manually in Power BI Online to make the changes show in report. First, I will refresh it manually by clicking the refresh button in the Datasets page. Refer the screenshot for more details:

And when I visit the dashboard page, I see that 3 new rows have been added for this new employee.

Now, let's try to schedule the refresh of data. For that I add 2 more rows in the SKILL_MATRIX_F table, like given below:

Now, I go back to the Settings page once again and make the Scheduled refresh on, and set the refresh time as 3:00 PM daily. We can change the Frequency, Time Zone, Time etc if required.

And now let's leave it for some times. Past 3 PM when I check it I see Last Refresh has happened at 3:14 PM.

To validate the data change, I revisit the dashboard.

And happy to find that now this employee has got 5 skills. Which means our scheduled refresh is working perfectly...... 👍.