simcorp.com
Mitigate risk
|
Reduce cost|
Enable growthFor all your reporting and analytics tasks, you
need a central data repository regardless of
source. SimCorp’s Data Warehouse Manager
gives you a comprehensive, industry-standard
investment data warehouse model along with
all of the tools you need to facilitate timely,
automated, and accurate reporting. The
Data Warehouse Manager gives you access
to high quality data at your fingertips, and
helps you answer important questions without
assistance of your IT department.
Data
Why a
Data Warehouse?
Data warehouses are typically implemented to consolidate data from disparate systems into a single
database, giving a ‘single version of the truth’ and exposure to crucial business information via
common reporting or business-intelligence tools. New regulatory reporting requirements are forcing
many financial institutions to implement data warehouses.
What SimCorp Clients Gain
• Implementation costs reduced by up to 80% • Implementation time reduced by 75% • Reduced risk of failure
• The proven market standard for SimCorp Dimension clients
• Reduced operational cost and risk (automated upgrades & enhancements) • A solution owned by business with minimal
IT footprint With SimCorp Dimension at the core of your enterprise
architecture, your organization has made a decision towards an integrated platform, rather than having different operating systems storing information separately. Your company already has all investments in a single book of records and perhaps even uses SimCorp Dimension as the master for security master and market data master, with or without a data-scrubbing engine in front.
However, SimCorp Dimension is rarely the only operating system in investment management firms, so some data integration is still required. The need to present business information to end users in a user-friendly format is becoming increasingly important as the need for self-service business intelligence is becoming the industry standard.
Key benefits
for your business
A key benefit for business users is easy access to data, enabling them to deliver reports and perform in-depth analysis for decision-support. SimCorp’s Data Warehouse Manager provides an industry standard solution and tools to extend and operate the data warehouse within your business. The Data Warehouse Manager integrates data from disparate systems, and provides easy access for end-users to information for business analytics, reporting and decision-support.
With SimCorp Dimension as the core operating system, you probably already store around 80 percent of your operating data in SimCorp Dimension’s database, structured however, in a data model that serves transactional processing rather than reporting and analytics.
The common perception in the market is that a data ware-house project on average takes two yearsi and involves 10-15
full time employees. Yet such projects are still prone to high failure rates.ii Market intelligence also shows that the annual
maintenance cost of a production data warehouse is between 40 and 60 percent of the initial costiii simply to keep up with
market trends and the evolution of source systems.
i M. Demarest.´(1997) ‘The politics of data warehousing’. http://www.noumenal.com/marc/dwpoly.html
ii Adelman, S (2014): ‘Measuring Data Warehouse Return on Investment - A Whitepaper’, http://cms2.dama-phoenix.org/wp-content/uploads/2013/07/ PhxDAMADay2012_Measuringhttp://cms2.dama-phoenix.org/wp-content/uploads/2013/07/PhxDAMADay2012_Measuring-Data-WarehouseROI.pdfData-WarehouseROI.pdf
The Investment Management Warehouse covers the most relevant areas of SimCorp Dimension from a reporting and analytics perspective and consists of the following major components:
The SimCorp Business Information Model
Ensures that the business terminology is consistent between SimCorp Dimension and other SimCorp components, including the IMW.The SimCorp IMW Multidimensional Data Model
The core and all subject areas included in the IMW overtime are available as multidimensional data models, ready for business intelligence tools and OLAP cubes of the customers’ choice. Since Release 5.6, the IMW model has more than 90 tables covering more than 700 fields from SimCorp Dimension.Predefined installable database schema
Helps clients achieve a fast implementation of the Data Warehouse.
Predefined ETL jobs
Loads data from SimCorp Dimension to the Data Warehouse, and since Release 5.6, includes more than 130 data extracts.
Our 10 guiding
design principles
In order to follow Data Warehouse Manager’s best practices while leveraging the most from the tight integration with SimCorp Dimension, our solution is carefully designed according to the 10 guiding principles below:1. Business-friendly, multidimensional solution engineered for intuitive usability and based on Ralph Kimball’s design guidelines
2. Deliver data to the business in a reporting-friendly format where data can be used as it is, without further transformation needed when general industry best practices apply
3. Presented to business users in business terminology based on the same vocabulary as in SimCorp Dimension
4. Incorporate the accumulated investment management knowledge of SimCorp 5. Support data warehouse requirements of
investment management firms
6. Analytical and reporting components to support business intelligence tools and OLAP technologies 7. Capture and promote data and metadata, enabling
detailed lineage back to SimCorp Dimension including the Internal Keys
8. Ready-to-use data that is out-of-the-box and based on extracts from SimCorp Dimension that can be easily reconciled against SimCorp Dimension 9. Customer-specific extensions in SimCorp Dimension
(such as free codes etc.) and integration of data residing outside of SimCorp Dimension
10. Automatic upgrades with SimCorp Dimension to ensure a stable operation
Investment
Management
Warehouse
Reporting tools
SimCorp Dimension is integrated with SAP Crystal Reports for operational reporting, and can be used on data warehouse content. SimCorp Coric is the preferred client reporting system, available for client communications and web reporting including self-service options.
Business Analytics
Any third party business intelligence or reporting tool, which can access Oracle, will work with the SimCorp Dimension solution.
Data
Analytics
The analytics layer is where end users log on using a reporting or analytic tool of choice to produce
reports or gain insight for decision support in their daily work. These users may or may not be users
of SimCorp Dimension, but could also be operational analysts or senior management. According to
business needs, a number of different options are available.
Data Mart Layer
Multidimensional Data Marts
The Data Warehouse Toolbox may be used to build data marts based on sub-sets of the central base tables
in the Data Warehouse Manager. SimCorp provides foundation packages of ETL and tables for sample
data marts. SimCorp strongly advises against granting users direct access to the base data warehouse,
but instead publish data to consumers through data marts. According to business needs, a number of
different options are available.
Custom data marts should be created along these lines: • Subsetting the base data warehouse tables to provide
targeted, local, simpler data models for specific business purposes and often only include ‘current’ versions of versioned facts
• Governing access control and authorizations in easy and fail-safe ways
• Optimize the query performance according to business needs using relational tables with relevant indexing and/or cube technologies (OLAP)
Reporting Data Marts
The standard data warehouse solution includes some fact tables designed for reporting purposes. The variations include:
• Fact tables with high-level aggregations of non-additive measures in the risk and performance area
• Fact tables that feed calculation results (e.g. Fund Figures) in a form, which may be transposed into custom data marts for reporting purposes
Data Mart Storage
Since SimCorp does not supply specific data mart solutions, there are no restrictions on the choice of platform for the data mart layer. This may be on Oracle, SQL Server or any other technology. However, the SimCorp Data Warehouse Toolbox currently only supports loading to Oracle tables, so the loading to cubes on a MS SQL Server for analysis services must be specified in the MS SQL Server tools. SimCorp has expertise to support in this area.
Data
Warehouse
Toolbox
• Pre-built data model for investment managers • Industry standards and full
instrument coverage • Maintained by SimCorp
– works across upgrades • An ETL tool for the business
• Consolidate data from SimCorp Dimension with other data sources • Use the SimCorp data dictionary to select
the right data and include meta-data and lineage
• Apply data quality screens and schedule data warehouse load plans
Investment
Management
Warehouse
Data Warehouse Tool Box
• A set of tools to create, run and manage ETL (Extract, Transform, Load) processes
• Consolidates data with other sources
• Uses the data dictionary to select the correct data and include metadata and lineage
• Applies data quality rules and specifies data warehouse load plans
Investment Management Warehouse (SimCorp IMW)
• A market standard data warehouse solution and datamodel
• Multi-dimensional model following the Kimball metho- dology designed with end users in mind
• Full financial instrument coverage that follows market trends
• Maintained by SimCorp and works across upgrades The solution is tightly integrated with SimCorp Dimension and as a business user familiar with SimCorp’s reporting and connectivity tools, you can use the Data Warehouse Manager to.
• Consolidate data from external sources
• Build marts and reports based on specialized data models • Specify data loads
• Turn disparate data into business value • Control and monitor data quality
Solution overview
Using the
Data Warehouse Toolbox
Data model extensions and ETL jobs
are designed, scheduled,
and monitored from within SimCorp Dimension. This reduces the
need to hire external specialists unfamiliar with the new tools.
Additionally, there is little need to involve a database administrator
for reporting queries, empowering business users to manage and
find the data they need. These users are free to schedule data loads
as they see fit, ensuring that the right data is delivered at the right
time. The workflow also facilitates custom processes, such as data
quality-breach thresholds or ad-hoc manual checks.
The Data Warehouse Tool Box is based on proven technologies
Data Dictionary
The Data Warehouse Manager incorporates a simple Data Dictionary, documenting the source tables and fields (in SimCorp Dimension) covered by the data warehouse. It also includes cross-references between source tables and fields and destination tables and fields and the extraction setups and extraction definitions used in the ETL processes. The content of the data dictionary is maintained by SimCorp.
Data from External Sources
The SimCorp Data Warehouse Toolbox enables users to load data from other systems using underlying Oracle technology with a user interface inside SimCorp Dimension.
You can use the following data sources in the Data Ware-house Toolbox:
• Databases (via Oracle external tables)
• CSV and other structured files via the External File Definitions window
• XML, Web services, message queues, middleware via the Communication Server
Preservation of History
The Investment Management Warehouse is designed for detailed preservation of history. There are several levels of support for preservation of history:
• Surrogate key handling by way of mapping to the business keys is always enforced and in use
• Time series-style fact modelling of changes over time for prices, ratings etc.
• Support for slowly changing dimensions (type 2) by way of specification of version-creation criteria, which is applied to the SimCorp Dimension extracts, and of surrogate key usage in the staging processes for selected dimensions • Designed for usage of “Update or append” to allow for
updates of the data warehouse fact tables, if so desired
Versioned storage structures for selected facts, such as daily positions and transaction lifecycle. Most fact tables are loaded using simple append strategies, but for some facts a concept of versioning has been applied to view certain measures as they were at a specific point in time. The versioning property has been implemented using ‘versions’ on the fact table records and a corresponding ‘version’ dimension.
User-defined data models, which is certainly available in SimCorp Dimension (in the form of free codes, tree node names, formula and extra fields etc.) is handled by adding custom extension tables to the DWH data model using the Data Warehouse Toolbox.
Please note that non-linear, non-additive measures and key ratios (such as TWR and VaR) can only be represented with difficulty in multi-dimensional analytical tools (such as OLAP-tools). For reporting environments, this can be dealt with in flat, aggregated data structures, but not for user- driven, interactive business analytics where SimCorp Dimension’s internal analytic facilities are recommended.
“ Selecting SimCorp’s data
warehouse solution has been
a shortcut for Jyske Invest
to getting a modern and
up-to-date data warehouse and
professionalizing our reporting
capabilities.“
Finn Beck
Ready,
Set, Grow
Data Warehouse Manager
belongs to SimCorp’s portfolio of
integrated front-to-back solutions for business process automation
in investment management. Efficient workflows seamlessly
integrate your organization and provide accurate and up-to-date
information when you need it, empowering SimCorp’s flexible and
scalable solutions allow you to capitalize on opportunities as they
arise and swiftly adapt to changes in business requirements.
Leading investment management firms worldwide rely on SimCorp
solutions to provide optimal business conditions and secure
competitive advantage. Get ready to grow with SimCorp.
Learn more about Data Warehouse Manager at
simcorp.com
software and services to the world’s leading investment managers, asset managers, fund managers, fund admini-strators, pension funds, insurance funds, and wealth managers. Based on its world-class software platforms, SimCorp Dimension and SimCorp Coric, SimCorp provides global financial organizations with the tools they need to mitigate risk, reduce cost, and enable growth. Listed on the NASDAQ OMX Copenhagen, SimCorp is a global company, regionally covering all of Europe, North America, and Asia Pacific. For more information, please visit www.simcorp.com.
Legal Notice
The contents of this publication are for general information and illustrative purposes only and are used at the reader’s own risk. SimCorp uses all reasonable endeavors to ensure the accuracy of the information. However, SimCorp does not guarantee or warrant the accuracy, completeness, factual correctness, or reliability of any information in this publication and does not accept liability for errors, omissions, inaccuracies, or typographical errors. The views and opinions expressed in this publication are not necessarily those of SimCorp. © 2015 SimCorp A/S. All rights reserved. Without limiting rights under copyright, no part of this document
