W3SKILLSET

Requirement Document for FID Connectivity Report 1 – Power BI

1. Introduction

1.1 Purpose

The purpose of this document is to define the requirements for the FID Connectivity Report in Power BI. This report provides insights into financial opportunities, deal statuses, fund summaries, and diverse segment commitments to help stakeholders make data-driven decisions.

1.2 Scope

This report is used to track and analyze financial opportunities across different lines of business (LOB), fund activities, and diverse segment investments. It includes data visualizations for trends, deal summaries, and fund commitments.

2. Functional Requirements

2.1 Report Overview

The Power BI report should include the following key sections:

  • Total Opportunity: Displays the total count of opportunities.
  • Opportunities by LOB: Breakdown of opportunities across various business lines.
  • Status of Opportunities: Classification of opportunities into Passed, Closed, Active, or Lost.
  • Most Active Funds: Ranking of funds based on the number of deals.
  • LTM Deals Per Month: Historical trend of deals closed over the last 12 months.
  • Deal Summary: Detailed summary of individual deals including fund, banker, status, and location.
  • Diverse Segments Summary: Overview of fund commitments with segmentation by diverse ownership (e.g., Women-led, Minority-led).

2.2 Data Sources

The report pulls data from multiple sources such as:

  • Internal financial databases.
  • CRM systems tracking deal statuses.
  • Historical fund commitment records.

2.3 Data Visualizations

  • Bar Charts: To represent opportunity distribution by LOB.
  • Pie Charts: To showcase the proportion of opportunities by status.
  • Tables: To provide detailed deal summaries with key financial metrics.
  • Trend Line Graphs: To display the LTM deal trends.

2.4 Key Metrics

The report should provide insights into:

  • Total number of opportunities.
  • Breakdown of opportunities by LOB.
  • Status-wise classification of opportunities.
  • Most active funds and their rankings.
  • Long-term deal trends by month.
  • Fund commitment details with diverse segment classification.

3. Non-Functional Requirements

3.1 Performance

  • The Power BI report should load within 5 seconds for each view.
  • The dataset should be optimized for efficient query execution.

3.2 Security

  • Access control should be implemented to restrict financial data visibility.
  • Role-based permissions should be defined for viewing/editing the report.

3.3 Scalability

  • The report should be capable of handling growing financial data without performance degradation.

3.4 Usability

  • The report should be intuitive and interactive, allowing users to filter data dynamically.
  • Drill-down capabilities should be available for more detailed insights.

4. Assumptions & Constraints

4.1 Assumptions

  • Data sources are accurate and regularly updated.
  • Users have Power BI access to view the report.
  • Filters and interactive elements will be enabled for deeper analysis.

4.2 Constraints

  • Real-time data refresh might not be feasible; updates may be scheduled periodically.
  • Report performance may depend on the complexity of data queries.
FID Connectivity Report_1Download

1. Data Extraction (SQL Queries)

The data required for the Power BI report will be extracted from databases using SQL. The key tables include:

  • Opportunities Table (stores financial opportunities)
  • Deals Table (tracks deal statuses)
  • Fund Summary Table (contains fund-related information)
  • LOB Table (maps lines of business)
  • Diverse Segments Table (records fund commitments with diversity details)

1.1 SQL Queries for Data Extraction

(A) Extracting Opportunities Data

sqlCopyEditSELECT 
    OpportunityID, LOB, Status, CreatedDate, ClosedDate, Amount, FundID
FROM 
    Opportunities
WHERE 
    CreatedDate >= DATEADD(YEAR, -5, GETDATE()) -- Last 5 years of data

(B) Extracting Fund Summary

sqlCopyEditSELECT 
    FundID, FundName, CommitmentAmount, PrimaryLocation, Segment
FROM 
    FundSummary

(C) Extracting Deal Status Data

sqlCopyEditSELECT 
    DealID, FundID, Status, DealDate, Company, Product, ReferredTo, Amount
FROM 
    Deals

(D) Extracting LOB Details

sqlCopyEditSELECT 
    LOBID, LOBName, BusinessUnit
FROM 
    LOB

These queries fetch the necessary data, which will be loaded into Power BI.

2. Data Transformation (Power Query – M Language)

Once the raw data is extracted using SQL, it will be cleaned and transformed in Power Query.

2.1 Cleaning and Formatting Data

  • Remove null or duplicate values
  • Convert data types (dates, numbers, text)
  • Standardize column names

Example Power Query (M Language) code for removing nulls & duplicates:

mCopyEditlet
    Source = Sql.Database("ServerName", "DatabaseName"),
    Opportunities = Source{[Schema="dbo", Item="Opportunities"]}[Data],
    RemoveNulls = Table.SelectRows(Opportunities, each ([OpportunityID] <> null)),
    RemoveDuplicates = Table.Distinct(RemoveNulls)
in
    RemoveDuplicates

2.2 Creating Relationships in Power Query

  • Merge tables on common keys (e.g., FundID, LOBID)
  • Create calculated columns if needed

Example Merging Deals with Opportunities:

mCopyEditlet
    MergedTable = Table.NestedJoin(Opportunities, "FundID", Deals, "FundID", "DealsData", JoinKind.LeftOuter),
    ExpandedTable = Table.ExpandTableColumn(MergedTable, "DealsData", {"DealDate", "Amount", "Status"})
in
    ExpandedTable

3. Data Modeling & Measures (DAX Expressions)

After transforming data in Power Query, we define relationships, calculated columns, and measures in DAX.

3.1 Creating Key Measures in DAX

(A) Total Opportunities

DAXCopyEditTotal_Opportunities = COUNT(Opportunities[OpportunityID])

(B) Opportunities by Status

DAXCopyEditOpportunities_By_Status = CALCULATE(COUNT(Opportunities[OpportunityID]), GROUPBY(Opportunities, Opportunities[Status]))

(C) Most Active Funds

DAXCopyEditMost_Active_Funds = 
TOPN(
    5, 
    SUMMARIZE(FundSummary, FundSummary[FundName], "Total Deals", COUNT(Deals[DealID])), 
    [Total Deals], DESC
)

(D) Long-Term Deal Trends (LTM Deals Per Month)

DAXCopyEditLTM_Deals = 
CALCULATE(
    COUNT(Deals[DealID]), 
    FILTER(
        Deals, 
        Deals[DealDate] >= TODAY() - 365
    )
)

(E) YTD Opportunities

DAXCopyEditYTD_Opportunities = TOTALYTD(COUNT(Opportunities[OpportunityID]), Opportunities[CreatedDate])

4. Report Visualization in Power BI

4.1 Setting Up the Dashboard

Using Power BI visuals, the following sections will be created:

  • Bar Chart: Opportunities by LOB
  • Pie Chart: Opportunity Status Distribution
  • Table: Fund Summary with Commitment Details
  • Line Chart: LTM Deals Trend

4.2 Interactivity & Filters

  • Date Filters: Select opportunities for a specific time period
  • LOB Filters: View opportunities for selected LOB
  • Drill-through: Click on a fund to see its deal details

5. Deployment & Optimization

5.1 Data Refresh Schedule

  • Data will be refreshed daily from SQL Server
  • Incremental refresh will be enabled for performance optimization

5.2 Performance Optimization

  • Use aggregations and indexed SQL tables to speed up queries
  • Reduce Power Query steps to minimize load times
  • Optimize DAX calculations by avoiding unnecessary filters

6. Conclusion

By following the above methodology, the FID Connectivity Report will be successfully developed in Power BI with:

  • Efficient SQL data extraction
  • Optimized Power Query transformations
  • Robust DAX calculations
  • Interactive visualizations

Sample data files in CSV format for the key tables used in the Power BI report. These files will include:

  1. Opportunities.csv – Contains financial opportunities data
  2. Deals.csv – Includes deal tracking information
  3. FundSummary.csv – Tracks fund commitments and diversity details
  4. LOB.csv – Maps lines of business
OpportunitiesDownload
DealsDownload
FundSummaryDownload
LOBDownload

Leave a comment

Your email address will not be published. Required fields are marked *