Microsoft Power BI is a potent business intelligence tool used to achieve key components of BI. Users can generate visually appealing reports and dashboards, clean and transform data, and connect to a variety of data sources. Thanks to its user-friendly interface, Power BI can be used by both technical and non-technical users.
We have covered steps on installing and using Power BI in our previous articles: Install Power BI on Windows – EXE Files and Beginner Visualizations with Power BI – EXE Files.
Dashboarding has become an essential element for many enterprises. However, industry norms have started to understand that static dashboards are of little value to them. Hence, there has been a growing demand for live data and dynamic dashboards. This means that the visualizations and insights are constantly changing as the data on the backend changes. This requires Business Intelligence professionals to set pipelines supporting dashboards’ dynamic nature. However, achieving these pipelines can be challenging for many organizations due to several constraints.
Power BI gives you several options to get data according to your preference:
But as mentioned, setting up your BI tool for live data can be difficult, as there are many services that require an organization to pay extra to avail these live data facilities.
We will take a small example of SAP, a renowned name in the field of data warehousing and business operations, charges additional fees for their live data connectors. If from the get data option, we click on more and search for SAP we will get these options:
The issue with SAP is that their live data connectors have additional charges, which is not viable and could be money firms. Even if a company has set up its warehousing and has stored all its data on an SAP server, to get access to live data, it will have to access connectors, which will charge them an additional cost. Hence, to avoid these costs, firms do two things: firstly, they make a dashboard and design it according to their requirements. And now, whenever they want to update the data, they copy these designs and create a new BI file with new data. Since this is a rigorous task, the frequency of these updates is relatively low. Hence, users need to avail themselves of the advantages of such advanced tools properly. Or they’d drop the idea of dashboarding and feel this additional step was redundant and should be avoided.
In both cases, the users are not doing themselves any favors. Due to a lack of awareness, they must get strongly built facilities to help them achieve their operations.
There is another straightforward solution to this problem. That is using Power BI’s built-in source refresher services. While this might sound technically demanding, it is pretty simple in practice.
Suppose we continue with the SAP example. Download your report or dataset in the default format of SAP or whatever data source you have and save it as an Excel file. Save this file as “main_source” on a specified path.
Now on your Power BI click on “Import data from Excel”:
Once you import the file, “main_source”, on your power BI, conduct all data manipulations on your Power BI. Suppose you want to change the column name from “City” to “Source_City”, do these changes on your Power BI and do not make any amendments to your source excel file. What this does is keep the original source of your file in the default format of your downloaded report. Now in order to facilitate live data you can download the required report in the default format as many times as you want. And instead of changing the source each time, copy all data from this new file, and paste it on your “main_source” file. Make sure you just copy paste the data and not the columns, columns names should be kept same.
Let’s walk through an example.
Suppose this is your initial file, which you downloaded:
Now we go to Power BI, “import data from excel”, and import this particular table on our BI tool:
After this we Load the data, and now the path of our source data on power BI is the same as the path of this file. We have used the exact same format as what we got from our source site.
Now to cross check the live data updates, let’s say we want to see the KPI: “sum of house prices”:
We want to change the column name to “House Price” instead of “House Price in $1000s (y)”. We will not make this change on our source excel file but we will simply do this on Power BI:
We do this using the “Table View” on the left sidebar.
Now we can visualize it like this:
Our source excel file still has the original name:
Now suppose we download a file for next month’s prices, and it has different data.
Now copy only the values and paste it on to the original values to see it update and help you visualize the data for two months instead of only one:
Copy from new downloaded table and paste it to old current source file:
Now if we save this file and go back to Power BI, we will see an instant change in the sum of house prices:
Click on More options in the data tab of the table and from there press “Refresh Data”:
As soon as you do this, you will see updated sum:
This is a cost effective and simpler way to incorporate live data in your dashboard without designing exaggerated pipelines.
