About the Client:
Client provides its customers a way to build, protect and maximize value through their differentiated financial and risk advisory and intelligence. Client’s transaction experience and expertise earns them global recognition as leaders in valuation, corporate finance and restructuring. They also are known for our world-class experts and solutions in corporate risk. Client is headquartered in New York with offices around the world.
Background:
Client was using a legacy system which used to cater to their business reporting requirement. In this scenario business users were always depended on the IT teams to prepare any Adhoc report based on the requirements. Client needed a solution where the business users would be able to create bespoke reports based on their current business requirement without much technical expertise.
Challenge with Legacy system:
- Duplication of Logic: All business logic were duplicated for each business user’s report requirement due to their complex data design.
- Hard-coded business conditions: All the business reports had hard coded business logic due to their legacy system architecture limitations.
- Maintaining multiple versions of tables: Increased load on the production database which impacted report performance.
- Data scalability was an issue with the Legacy system
Duration of the Project
18 months
Tools used
SSIS, SQL Server, SAP BW, BO, Webi Reports
Approach:
- Right Angle Solution (RAS) consultants and Business Analysts spoke to different business groups and conducted working sessions and technical sessions on Use Cases
- Conducted Data profiling and reviewed existing ETL and data mapping documents related to their reporting DB.
- Conducted detailed review sessions with client to understand special conditions embedded within data views.
- Infrastructure Enablement – Additional Hardware Space from Infrastructure team
- Data Architecture – Our approach to manage their data with specific advantages for Analytics, Reporting and Maintenance
- Detailed Analysis of current data management techniques. This allowed us to understand source systems better and helped us determine what we can harness and what needs to be improved.
Solution:
- Data Management Framework that supports the following features was developed.
- A multi staged Data Acquisition and Transformation model was created which ensured that the integrity of the Source Data is maintained. Hence, when the data source changes or changes occur in the source system the report data structures are not impacted.
- Business Transformation were applied at a one location as opposed to creating several flavors of purpose-built data structures. This ensured that validating transformations is only at one stage and not at every view that supported a reporting request.
- Solution architecture supported Master Data Elements and a Master Data Management (MDM) strategy was kept in place. These Master Data Elements are data points such as Customer, Contract, Account and Sub-Account Code etc. which are critical for reporting and analysis. They are applicable across all Subject Areas such as Activity Transactions, General Ledger (GL), Utilization & Invoice Analysis.
- ETL Architecture followed best practices such Extracting only necessary information, Optimizing ETL flow for fast data loads and using control tables where possible to re-use and reduce transformation complexities, establish a robust logging mechanism to tack ETL loads.
- Developed initial data structures to insert data and validate.
- Built Subject area data marts at lowest granular level where data can be Drilled Down and Slice & Dice was possible.
- Data Marts data population is automatically triggered once the client regular ETL data loads are completed.
- Historical data was stored as switch partition data.
- Aggregations are based on client’s natural organization hierarchy.
- Finally, we build a universe on Finance Data Warehouse in SAP BO for the easy use of BO report users.
Outcome:
- Business users can create bespoke webi reports using new DWH universe with much ease within their existing reporting SAP BO tool itself.
- Reporting team was freed from mundane tasks of building reports in crystal designer and can focus on more value-added projects for the business.
- As all the Data mart loads were made incremental, DWH data access for business users is quick.