Assured Guaranty

Assured Guaranty (www.assuredguaranty.com)

Our lead developer worked with Assured Guaranty for four years.

Achievements Included (* recommended):

  1. Delivering the first successful AG BI department project in record time. This was due to the significant BI experience and fully understanding the subject matter for the solution.
  2. Designed & integrated the General Ledger & new Sub Ledger for financial reporting & consolidation to SQL Server Analysis Services (SSAS multi-dimensional & Tabular) cubes. This included:
    1. Multiple international reporting accounting consolidations of 175 companies [US & UK GAAP, Statutory, Tax, Management, Trial Balance and US SEC Taxonomy *
    2. Financial reporting consolidations and the alignment of projection * and pipeline * systems for Projected Balance Sheet and Income Statements * and retrospective comparison of data
    3. Real-time financial accounting cube used for fast-close month-end reporting
    4. Alignment of off-balance sheet risk data with financial data *
    5. The alignment of data improves accounting information, understanding, preparation, avoids data duplication (one version of the truth) and removes duplicate ETL processing, and reconciliations
    6. Evolved a new financial reporting path approach to leverage screen real estate
    7. Evolved departmental and reporting methodology for noise-free reporting and user experience
    8. Balance activity optimization
    9. Resolved and programmed sub-ledger cumulative balances for the financial data mart (FDM)
    10. Financial disclosure reporting
    11. Centralized company and dimensional keys across all environments *
    12. SSAS high volume cube performance optimization
    13. Budgeting, planning, and forecasting structures inclusive
    14. Accounting, presentation & precision reporting formats
    15. Drill-through to journals and transactions, i.e., a level not normally achieved in cubes.
    16. Roll-forward reporting
    17. Relational querying using cube Actions to allow for member history and projections.
    18. Cube query and view optimization and education
    19. Period variance & percentage analysis
    20. Reconciliation control totals between ledger, sub-ledger, projection system (runoff) * and financial reporting control checks
    21. User PivotTable and freeform reporting templates
    22. Graphical considerations & integrations including using Microsoft Power BI & Excel
    23. Ernst Young, Accounting & IT interaction & coordination
    24. SOX considerations
    25. Prototyping and concept demonstrations
    26. Design considerations with Enterprise use in mind
    27. ERP source table identification for Peoplesoft and Aptitude
    28. ETL process logic, considerations, design, and evolvement
    29. Surrogate key standardization
    30. Fact table dimension member constraints
    31. SSDT project integration of relational objects, queries, and functions
  3. Redesigned the SSAS Insurance Exposure cube to improve performance. Factors included:
    1. High data volumes over 100m records/month
    2. Various approaches considered and tested to achieve optimal performance for high volumes
    3. Inter group transaction elimination audit trail evolvement missing in original solution
    4. Simplified the group policy elimination methodology for improved performance and understanding
    5. Dimension & cube MDX calculations optimized to reduce unnecessary overcalculation resulting in material performance gains.
    6. Many-to-many dimensions leveraged for business complexity
  4. SSAS Cube XMLA processing & performance optimized for the large volumes of data. Changes included
    1. Correct MDX order and structure to optimize functional requirements and performance.
    2. Improved partition creation and processing.
  5. Optimized cube SSRS reporting approach and MDX to achieve fast query performance.
    1. Some large reports required an optimal MDX & querying approach instead of the dynamic columns
  6. Managing, mentoring & training staff internationally to facilitated knowledge transfer, support & continuity
  7. Documentation for SSAS cube technology, Power BI, Excel PivotTables, PowerPivot, and data usage.
  8. Worked within the business intelligence team to coordinate, define functional & business requirements for Agile sprints, prioritize, collaborate with other departments to implement reporting projects.
  9. Key Recommendations Include:
    1. Alignment of Off-Balance Sheet Risk Data with the Actual Financial Reporting Data Mart (FDM)
    2. Centralized company and other dimensional keys across all cube data, i.e., Operational Data Mart (ODM) Projections to FDM Financial Consolidations and Reporting
    3. Realignment of insurance exposure data to
      • Adopt standard accounting approaches for better internal understanding and communication,
      • Improve scenario maintenance, comparison, and variance analysis,
      • Reduce data volumes by removing duplicate data,
      • Reduce ETL requirements,
      • Improve regulatory reporting,
      • Improve cube processing and performance,
      • Add deltas for exposure data.
    4. Integrate the Financial Projection and Pipeline data with the Actual Ledger Financial Data (FDM) for
      • Projected Balance Sheet, Income Statements and Cashflows evolvement,
      • Leverage existing business dimensional logic and understanding,
      • Easier retrospective data comparison,
      • Automated reconciliations,
      • Reduced reconciliations, support, programs, and maintenance,
      • Elimination of database object duplication and maintenance by promoting conformity.
    5. US and international regulatory disclosure reporting integration for
      • streamlined reporting,
      • improved audit trails,
      • reduced risk by exposing regulatory statutes and information,
      • improved internal education, understanding and knowledge transfer,
      • improved reporting and integration to regulatory authorities.
    6. Centralize data capture into existing cube structures where applicable to improve
      • Data integrity and centralization
      • Accountability with user audit trail.
    7. Stratified cube security
    8. Increased visualization reporting using Microsoft’s Power BI
    9. Specific benefits by adopting some of the recommendations above and not covered include
      • Material cost savings,
      • Improved business process and understanding, and
      • Reduced reporting risk,

Core Applications: Microsoft SQL Server, SSAS (Multidimensional & Tabular), SSIS, SSRS, TFS, Excel (PivotTables & PowerPivot), Power BI, Peoplesoft, Aptitude & Octopus.

Scroll to Top