3- Connecting Power BI to Systems / Databases
In this part, we will simulate a connection to a live database that business data is input and edited continuously as in a usual business system. We will connect the PostgreSQL (PSQL) database, edit and input data in PSQL, examine the changes in Power BI reports.
If you followed the workshop since the beginning, you must have already installed PostgreSQL and done some database operations in it. It is a good idea to revisit this part briefly before starting today.
If you did not follow the first part and don’t want to use SQL now, you can still follow this part of the workshop. We are not necessarily working on SQL; we are using PSQL as if we have connected to a live system.
As usual, you can download the database tables. There is also a disconnected Power BI file which shows the end state of our work in this part.
Connect to database
Open PBI desktop application, open a new file and go on with getting data.
There is a big list of file types, databases, services, platforms etc. that you can connect and get data in PBI; PSQL is one of them.
Select the PostgreSQL database and click Connect. In the new dialogue write the P-SQL server host and database names. You can see the host name in PSQL, on the left pane under the servers, right-click on the server name where the LM database resides. In our case the server name is Sales, from the dropdown menu select Properties and select Connection from the dialogue; now you see the Host name/address.
You can see the database name in the left pane under the server. Both are case sensitive:
Server Host name : Localhost
Database: Lead_Merchants
Now input them in the PBI dialogue, and select DirectQuery. This means we will stay connected to the database and get updates as soon as we press the refresh button. On the contrary, the Import method will get the data in PBI.
With the direct connection, we establish automation, where we can get updated data as frequent as we want with a click of a button. And because we do not import the huge tables into PBI, we also end up with very small file size. After we finish the workshop, we will import all the tables in order to share the PBI file of the workshop.
Press OK, the Navigator dialogue opens, where you will see all the tables in the database. The tables that are necessary for analysis must be added to PBI; we will select all tables by checking all of them.
Press Load and tables will be connected to PBI.
Examine the tables in Model view, check relationships between tables.
Recognise that there are only Report and Model view buttons on the left side of the PBI window. The Data view button is not displayed as all the tables are a link to a database, and they are read-only. We can still extend the tables in PBI by adding columns, measures or creating new tables from current ones.
We will go on and prepare the Power BI file we have prepared in the fourth part. Go on and follow the steps in Power BI: Data Analysis for Business Intelligence add measures.
We will now go on and manipulate data in our system (PostgreSQL), and examine the output in PBI.
Create a new page, name it as Sales Dashboard put the filled map, resize it to fill the report, put a region filter at the bottom of the page. We will change the country data and examine it in this table.
Now select Europe in the region filter. As you can see Russia and a few other countries that are geographically in Asia are grouped in Europe, in Lead Merchants sales grouping.
Management decides to move Russia and other countries to Asia sales group, so an operator changes the region of Russia. The operator would probably work on a screen where the country definitions are made and adjusted, but we will directly change the database in PSQL.
Enter the code
SELECT * FROM countries WHERE region = 'Europe'
Press F5
We see the list of European countries, including Russia.
We will update the countries table and change the region of Russia to Asia PBSQL.
UPDATE countries SET region = 'Asia' WHERE country = 'Russia'
Go to PBI, click Refresh on the Home menu, and examine the change in Europe.
A more familiar Europe map.
In 2020 some new clients, from South America, opened online accounts with Lead Merchants.
We have to update both countries and customers tables.
These would be defined in designated screens which eventually update the database. We can do these additions by simply loading new data on existing tables or by writing code.
To see both, we will update the countries table with code and then append the new customers list to the customers table in PostgreSQL.
The below code is adding 12 countries in the countries table.
INSERT INTO countries(country_code, country, region, region_id, employee_id)
VALUES ('AR', 'Argentina', 'South America', 'SOAM01', 'MV03'),
('BO', 'Bolivia', 'South America', 'SOAM01', 'MV03'),
('BR', 'Brazil', 'South America', 'SOAM01', 'MV03'),
('CL', 'Chile', 'South America', 'SOAM01', 'MV03'),
('CO', 'Colombia', 'South America', 'SOAM01', 'MV03'),
('EC', 'Ecuador', 'South America', 'SOAM01', 'MV03'),
('PY', 'Paraguay', 'South America', 'SOAM01', 'MV03'),
('PE', 'Peru', 'South America', 'SOAM01', 'MV03'),
('UY', 'Uruguay', 'South America', 'SOAM01', 'MV03'),
('VE', 'Venezuela', 'South America', 'SOAM01', 'MV03'),
('SR', 'Suriname', 'South America', 'SOAM01', 'MV03'),
('GY', 'Guyana', 'South America', 'SOAM01', 'MV03')
RETURNING *
Together with 12 countries, we also added a new region, South America, to our table.
Now refresh the PBI and select South America, the new region, in the region filter. South America is now filled as a region.
Now it is time to define the colour shading in Format Painter to make our choropleth map.
As we do not have any transactions from South American customers in 2019, the region has a whiter shade of blue.
Now we will update the customers table and load new tables from LM 2020 database.
Right-click on the customers table on the left pane of PSQL, import the Customers20. Follow the import procedure explained in the first part.
Create Datetable and Orders20 tables.
Create orders20 table as defined in the previous part.
Now import the datetable and orders20 tables.
Datetable is related to orders (2019) and orders20 tables, and it is necessary to compare columns from separate years.
Set relationships for the new tables.
ALTER TABLE public.orders20 ADD CONSTRAINT "customers_FK" FOREIGN KEY(customer_id) REFERENCES public.customers(id);
ALTER TABLE public.orders20 ADD CONSTRAINT "products_FK" FOREIGN KEY(product_id) REFERENCES public.products(id);
ALTER TABLE public.orders20 ADD CONSTRAINT "date_FK" FOREIGN KEY(order_date) REFERENCES public.datetable(id);
ALTER TABLE public.orders ADD CONSTRAINT "date_FK" FOREIGN KEY(order_date) REFERENCES public.datetable(id);
Refresh the PBI and see the new tables and examine relationships in the model view.
Examining the sales results for 2019 and 2020
We will create a new table for comparison of 2 years results.
The orders_conso table will be created by measures.
First, rename the orders table as orders19. Now public.orders19 and public.orders20 tables, correctly naming the years they involve.
Select any table, from the Table tools menu select New table.
Rename the new table as orders_conso.
Add the below measures to the orders_conso table.
Revenue_19 = SUMX('public orders19', [units_sold]
* RELATED('public products'[price]))
Cost_19 = SUMX('public orders19', [units_sold]
* RELATED('public products'[p_cost]))
Units_19 = SUM('public orders19'[units_sold])
Profit_19 = [Revenue_19] - [Cost_19]
Revenue_20 = SUMX('public orders20', [units_sold]
* RELATED('public products'[price]))
Cost_20 = SUMX('public orders20', [units_sold]
* RELATED('public products'[p_cost]))
Units_20 = SUM('public orders20'[units_sold])
Profit_20 = [Revenue_20] - [Cost_20]
Finally, we must add a date hierarchy to datetable, as PBI did not create the date hierarchy on the tables with the direct connection.
The id column in datetable shows the dates. Click on the three dots on the id column and select New hierarchy.
The id Hierarchy is created under datetable. Now click dots on the year column, select Add hierarchy, id Hierarchy. Do this for month and m_name, in this order. The date hierarchy is ready.
Go to the sales report page, Select Table visual, add the month and m_name fields from date hierarchy, Units_19 and Units20 fields from orders_conso table.
As you can see we have full year’s figures for 2019 and only 8 months until 20 August for 2020. For comparison purposes, we will examine the first-half results.
Add a slicer, click the little arrow on the top right, choose Between and set the slicer to 1 to 6 months.
Now we can compare the first-half results of LM sales.
As seen from the table, there is a significant increase in sales in the first half of 2020. There is also a fluctuation; sales increase sharply until April then start decreasing in May. We will add an Area chart visual to examine this fluctuation.
Add regions by profit table, products by units sold table and revenue by month table to Sales Report.
Add a Clustered column chart to compare the top 3 countries by profit.
Argentina is among the top 3 countries contributing the profit in 2020; where there were no sales in this country in 2019.
Add a multi-row card for units sold, revenue and profit totals of both years.
The visuals you can use in Power BI are much more than you see in the visuals pane. There are other visuals developed by Microsoft and third parties, you can import and use in your reports.
Under the Infographics category add Scroller. The scroller visual is added under VISUALIZATIONS.
Add scroller, select employee name and revenue_20 fields, set filter to Top 5 by revenue.
Add a Slicer for regions and set the type to the dropdown.
The Sales Report is completed.
Rename the Sales Dashboard page as Sales Map 2019. Right-click on the page tab and duplicate this page.
Rename the new page as Sales Map 2020. Change Revenue field to Revenue 20 field. You have to redo the Format Painter Data setting for the year 2020.
Now we have a choropleth map of 2020.
AI
There are 3 AI visuals in Power BI: the Q&A, the Decomposition Tree and the Key Influencers. These come very handy when you work with data that you are not very comfortable with, especially when you newly start the analysis. But after examining data in various tools in this six-part workshop the AI visuals will not look too impressive.
We will use 2 of these visuals very basically.
Q&A
This is a visual where you can ask some questions on the data, get fast answers, sometimes a number or a graph, and then convert this answer to any other visual.
We create a new page in the report and name it Q&A. Put 2 Q&A visuals on this page and resize them to have a full view. As you can see there are already some questions suggested by the visual. We will ask our own questions.
First question: What is the profit by month in the year 2020?
While we are asking the question the tool suggests words to better understand the question. For example profit for the year 2020 is suggested as profit_20 as we have defined it in the measure. It returns the months in a chart from highest to lowest.
The second question: What are the units sold by product category? Again wording improved by suggested words. You can simply select a question suggested by the visual also.
Now you can change these Q&A visuals with any other visual, such as a table or pie chart, so you will have built these visuals without playing with measures and fields.
Decomposition Tree
With this visual, you can examine your data with various breakdowns.
We created another page for this visual: Add a decomposition tree and resize it to fill the page.
Select Revenue20 from orders20 table. A bar appears in the visual representing the revenue. We will have a breakdown of the contributors to this revenue.
Then select the product category. When you select the first field to breakdown, a small + sign appears in the revenue bar.
Click on the plus sign and a small dialogue asks High value or Low value. Select high value and first breakdown appears, revenue by product category.
Select region and then click plus sign then select employee name.
Now the revenue is broken down into very high granularity. We see the revenue by product categories that sum up to the total revenue, then each regions contribution to the revenue of the selected product category, and employees performance for each region.
You can select different product categories or regions on the tree to analyse the granularity, or restart the breakdown and start with country or product etc.
This is a very useful tool for analysis if you get hold of it, in fact, you may want to start your analysis with this tool and learn about the dimensions of the data you want to examine.
The Key influencers is used to analyse the drivers that influence the key metrics. For example, we could analyse the units sold by price and see the effect of price on sales. It seems that this tool is not working well on LM sales data.
There are other AI functionalities embedded in PBI other than these three visuals. But these visuals are useful for end users like us.
We have finalised the analysis. As a final step, we will import the database tables so that this can be a shareable file.
Save the file with a different name. Open Query editor, go to any table, under the applied steps select source. PBI now shows Switch all tables to import mode. Click this button, Close & apply changes and save the file.
In windows explorer, you can see that the size of the imported file is significantly big, 67.798Kb.
Publishing to Power BI Service with a direct connection requires the set-up of an on-premise gateway, which we will not do at this workshop.
Also, when the connection to Power BI Service is set up, real-time streaming can only be done with certain dataset types.
We can still publish the imported file to Power BI Service and go on with publishing and distribution of reports.
This is the end of the six-part workshop on data analysis tools. I hope you have enjoyed working with these tools as much as I have. Please write your comments or ask any questions in the comments section at the bottom of this page or by email.
Stay well.
Go to Automation for Analysis and Reporting.
Go to the Data Analysis workshop.