Service Overview

This service facilitates seamless integration between PostgreSQL databases and Power BI, granting you exclusive read-only access to materialized views within the PostgreSQL environment. This connection empowers users to generate and manipulate Business Intelligence (BI) reports directly in Power BI, leveraging the rich dataset stored in PostgreSQL.

Setup Instructions

Whitelist Your PowerBI Server IP Address: Initiate the service by providing your machine’s IP address to get whitelisted, enabling secure access to the database environment.
Connection Details Provision: Get PostgreSQL connection details, including the host, database name, user, and password, to facilitate a straightforward setup.
Power BI Connection: Use Power BI to connect with PostgreSQL using the provided credentials. Detailed instructions for connecting Power BI to PostgreSQL databases can be found on Microsoft Learn.

Security Protocols

IP Address Whitelisting: Access to the database is meticulously controlled through IP address whitelisting, ensuring that only authorized users can connect to the database.
Read-Only Permissions: To maintain data integrity and prevent unauthorized modifications, provided users will have read-only access to the database views.

Features and Capabilities

Real-Time Data Insights: You can query materialized views for the latest data, supporting dynamic BI reporting and real-time insights within Power BI.
Data Visualization and Reporting: Power BI's advanced data visualization tools allow you to interpret their data through various graphs and charts, facilitating the creation of comprehensive reports and dashboards. More on Power BI's capabilities and how to leverage them can be explored through EasyInsights.

Detailed Connection Guide

Pre-Connection Checklist

Ensure the PostgreSQL NpgSQL provider is installed on your machine for Power BI Desktop versions before the December 2019 release. After the December 2019 release, NpgSQL 4.0.10 is bundled with Power BI Desktop, eliminating the need for separate installations.

Establishing Connection to PostgreSQL

Open Power BI Desktop and navigate to 'Get Data' to initiate the process.
Select 'PostgreSQL database' from the data source options and proceed by clicking 'Connect'.
Fill in the server and database name in the dialog box that appears. Choose between 'Import' and 'DirectQuery' modes based on your reporting needs.
When prompted, input your PostgreSQL credentials, define the authentication scope, and select 'Connect'.
Utilize the Navigator window to select the database information you wish to load or transform via the Power Query Editor.

For comprehensive guidance on connecting to PostgreSQL databases using Power BI, including troubleshooting tips and advanced configuration options, refer to Microsoft's official documentation.

This documentation aims to provide you with all the necessary information to successfully integrate their PostgreSQL databases with Power BI, enhancing their data analysis and reporting capabilities.

Data Tables for BI Consumption

This section outlines the key data tables from Mosaic's PostgreSQL database that are integral to generating dynamic and insightful reports in Power BI. These tables cover various aspects of project management, financials, resource planning, and operational metrics. Understanding the structure and purpose of these tables will enable you to craft detailed and meaningful BI reports.

Resource Management Tables

Resource and HR Management Tables

Members

Each table within Mosaic's database provides the comprehensive data needed for effective project management, financial analysis, and operational oversight. By accessing these tables through Power BI, you can leverage the powerful data visualization and analysis tools available in Power BI to create detailed reports and dashboards that drive strategic decision-making and operational efficiency.