Federated Data Mart & UDBA’s

Federated Data Mart & UDBA’s

In many businesses today, users do not find all the data that they need in one datamart or EDW, but distributed and spread across different silos and technology platforms. Some of the challenges faced by businesses are;

·         Required data sets are in different platforms and difficult to access and have to navigate on the thin line of permission issues and security concerns

·         Formats of these data sets makes it difficult be it structured, semi-structured or unstructured data

·         Grain of the data differs, often stored at higher level rather than at atomic level

·         Even partially satisfied data sets lack utility due to its relevance, accuracy and timely availability

·         Data sizes are too large to be accommodated in their own databases

Businesses have built UDBA’s (User defined database) stores just to source these datasets to circumvent the challenges even though they are difficult to maintain and manage. These data stores are not scalable besides their reusability is very negligible other than for whom these are build and as a result there are 100’s of different versions being created to satisfy specific business needs.

The issues and concerns are real, and IT managers are aware of it, and are trying to pitch in with solutions, but with varying success. There are companies which are rewriting their existing legacy EDW’s either with a top down or as bottom up approach so business can get to access the atomic grain data in normalized structures as well as providing data in dimensional model for reporting and analytical needs.

 One such solution is to build Federated data model architecture as a bottom up approach across multiple data mart systems. A successful federated model should share and build very high level of common data content (Core), metadata, and metrics in the Core to be accessible by various marts. For example, combining Digital Marketing information with Customer information can certainly provide proper customer, product/service segmentation information and even reliable demographics assessment. A Financial company for their Auto Finance LOB can, by combining Origination, Servicing, Collection and Recovery stack could potential be able build various risk tier propensity models.

Bringing Core data of Employees, Customers, Products, Services, Marketing data assets in a Federated model would be easily accessible by different data marts of LOB (Line of Businesses) with their specific line of data for their operational and analytical reporting needs. Besides the Core, common data sets among the LOB stacks can also be layered in the Core in different data grains with access privileges for building Mashup BI Analytics at enterprise levels. The key requirements of a Federated Data mart are Shared keys, Global Metadata and capable of running distributed queries

One of the true benefits of this design is to the UDBA’s (User Defined Databases). As indicated earlier UDBA’s are decentralized data stores, build to meet the customized business requirements of a LOB. The proliferation of these data stores in enterprises today is due to lack of access to Core data as a single source platform in atomic grain level and multitude of customized individual group business rules and user routines that needs to be applied to the data sets for their reporting requirements.

These UDBA’s come in different forms, shapes, sizes and complexity. There are ones that pull data via ODBC drivers built into applications like Excel and run complicated macros to produce reporting requirements, while some actually have small databases of their own that extracts data from different sources and apply business transformation logic with user routines, functions, stored procedures, triggers to provide business reporting requirements. To top it all, there are 100’s of versions of specific business requirements and these UDBA’s processes are duplicated, cloned to meet each individual group within the LOB’s.

In a typical Financial institution, an Investment Fund manager managing a portfolio (equities, bonds and other investments) has to comply daily, hundreds of rules and regulations as inscribed in the prospectus as its underlying stock/bonds changes its prices in the stock exchanges along with Corporate Actions (CA) that potentially alters underlying portfolio. The Rules, terms for the rules and metadata that defines the decision of the rules all differ from one portfolio to the other. Fund managers have different data source requirements, but most need standard core data sources like Security master data, Stock prices, CA, Fund Index to name a few.

In the absence of a Common Core data sources and Common denomination of core rules construction engine, each Investment Fund manger would have to build UDBA’s under their desk to manage and comply with SEC requirements.

Conclusion: First identify, define and profess the need of common data sources to IT folks to build them in Core of a Federated Data Mart. Second, commoditize and build a scalable user driven rules engine and routines independent of database platform technology as a persistent semantic layer with Global metadata.

In a nutshell, there are many benefits of building a Federated data mart with flexible Core and UDBA is one right candidate up the sleeve.