Skip to main content

Step by Step publish PowerBI Dashboard with Postgres Data


Hi Friends,

After a month I am back on my Blog with Microsoft Modern Reporting environment PowerBI. Today I will share my post related to Powerbi and How I integrated powerbi with postgres sql database.

Basically, Power BI provides a set of tools to gather, prepare and interactively visualize and share data across your organization, in an optimal, secure way. You can create reports fast and easy, with enterprise-grade quality.  Power BI provides three main tools: data discovery, data preparation and data visualization.

In this tip we will show how Power BI works and how to create a simple report from scratch and the options available for creating reports.

How does Power BI work?
According to this Microsoft documentation, Power BI consists of three parts:
  • Power BI Desktop: to create reports in a friendly user interface.
  • Power BI Service: a cloud SaaS used to publish and maintain reports via dashboards.
  • Mobile Power BI: apps available for consuming reports on mobile devices.

Here is steps How I have integrated PostgreSQL Database with Powerbi.


1. Download - Install PowerBI Desktop

The first step is to download and install PowerBI Desktop tool, this is the report development tool which we can install locally or any where .
·         Download from Microsoft portal. (download url)
·         Installation step is very easy just next - next and finish.

2. Connect postgres  Data
After installation need to connect our database which is Postgres
·         Create proper ODBC-DSN for Database server source. Please ensure you have proper postgres driver have installed on system like (Postgres SQL (x64)).
·         Start control panel -> administrative tool - > odbc datasource. Create a dsn of your database source location. Give a proper meaningful name that will recognize by powerbi.
3. Start flying with postgres Data on PowerBi Desktop
·         Start powerbi desktop and create new .

·         Go to Home->Get-Data and choose "ODBC" from More option. Connect it.
·         You will see list of all DSN created. Choose your own DSN. After that give credential and choose data tables which you want in report.
·         After completing this process data will be export in powerbi desktop.
·         Now start creating a sample report from right pannel visualization. Just drag-drop and configure. In 2-3 minute a report will ready.
·         Now save it .

4. Enable Subscription on PowerBI cloud Service.
PowerBI service is cloud based service where you can publish your created report with data source. After that it will be able to sharable - collaborate any application/subsciption/report/pdf/email or website.
·         Signup of Powerbi Service app with pro subscription (60 Days trail, URL)
5. Sign in Powerbi Service on Powerbi desktop.
After successful sign-up, now sign-in powerbi de
·         After sign-in you will able see name on power-bi desktop.
·         Now choose home->Publish to publish. Complete the powerbi app service credential and publish.

6. Now Back to PowerBI cloud service.
·         Review published report.

·         You can review dashboard, report and dataset/data source, 
7. Configure dataset gateway and schedule refresh.

Gateway creates the connection between Power BI cloud-based data analysis technology and the data source located on-premises. Gateway is an application that can be installed on any servers in the local domain. Gateway is responsible for creating the connection and passing data through.

Gateway comes in two different modes: Personal Mode, and On-premises. The difference between these two is not the paid or licensing plan. Both gateways are free to use. The difference is the way that you want to use the gateway. The personal mode is mainly used for one-person use, not for the team. On-premises gateway, on the other hand, is a choice when you want to work in a collaborative environment. Let’s look at their differences in details.

·         Generate embedding code to publish or share publicly.
8. Download and install personal gateway into local system.
follow installation screen by putting powrbi subscription credentials. Then you will able to see 



Configure schedule refresh or force to refresh from dataset.
Thanks for reading
Plz dont forget to like Facebook Page..
https://www.facebook.com/pages/Sql-DBAcoin/523110684456757

Comments

Popular posts from this blog

mongoDB error : aborting after fassert() failure

What to do when facing errors on mongoDB “aborting after fassert() failure”

I like errors, in mongoDB this is the first error I faced and luckily many times. This error i faced during restoring name-space on local and restarting db system. I am still searching the exact root cause of this issue but i am able to resolve the current problem through below steps.

Remove all relevant namespace files from data-file route path..Now repair mongo instance using mongod process.mongod --repair ////////// execute command from bin folder path Then start server using mongd process, if started server successfully then ..mongod  ////////// execute command from bin folder path Restore last backups as normal process.Now check database by connecting mongo shell. Thanks for reading, 
Please comment your experience if you faced and also share knowledge if you have better steps to resolve...


https://www.facebook.com/pages/Sql-DBAcoin/523110684456757

SQL71562: external references are not supported when creating a package from this platform

Last week I got this error from one of developer who was trying to deploy his project from Testing server to SQL Azure QA server. He was using “Deploy Database to SQL Azure” option from SSMS Tool-Task option.

After connecting to SQL Azure portal when operation started to deployment below errors occurs.

Validation of the schema model for data package failed. Error SQL71562: Error validating element xx.xxx.xx:function .dbo.xxx has an unresolved refrence to object xx.dbo.xxxx external refrences are not supported when creating a package from this platform.



Reason: The reason of the this error was; some functions of project was dependent on master database and only single database was being deploy to SQL Azure. DACFx must block Export when object definitions (views, procedures, etc.) contain external references, as Azure SQL Database does not allow cross-database external references So, this error was coming.

Solution : I suggested him to create those function to locally on local database what…

How to add an article in Transactional Replication

If we have a set-up of Transactional Replication for Data Distribution running and wanting to add new object to replication on other server we can follow below process.
To add an article In Transaction replication with PUSH Subscription