MDM (Master Data Management) Business Scenario

Introduction

Master Data Management (MDM), the process of creating and maintaining master data. As per definition Master Data Management (MDM) is a set of coordinated processes, policies, tools and technologies used to create and maintain accurate master data. Master data describes key area of business like customers, products, employee, suppliers, etc. It is stored in multiple, disconnect systems/databases. Unmanaged master data is inaccurate as well as incomplete. Unmanaged master data may lead to poor business decision because of discrepancies.

MDM is required in Relational Model as well as in Dimensional Model (OLAP, BI Analytical data model). In the Relational Model following are example of MDM:

·         Customers, suppliers, employees, and sales representatives

·         Products, equipment, assets, and stores

·         Contracts, licenses, and bills of material

·         Company locations  and customer geographic divisions

Dimension data (Product, Customer, Employee, Department, Geographical regions, etc.) should also be considered as example in Dimensional model because master data can be used for both models.

Master data is a very important part of data. It is crucial and it must be known and correct. We can’t think of master data without quality. Master data typically changes with a much slower rate than transactional data. Master data follows classical CRUD cycle: Create Read, Update and Destroy.

Advantages

Advantage of MDM is categorized below based on area:

Sales

1.       Single shared view of customer may lead to overall customer satisfaction, service and retention.

2.       Sales people will be able to effectively up sell and cross-sell to customers because they get access to an accurate customer data.

Products

1.       Centralized governance of corporate data will immediately launch new product.

2.       MDM will facilitate the new product introduction to market faster. New product launch can be shorter and more agile.

3.       Product data will be shared by supplier to customer on timely basis.

4.       Increase visibility between suppliers and products.

Cost & Operations

1.       MDM in procurement can stop duplication of products.

2.       MDM can produce the same effect for the supplier part master, enabling companies to better understand at a macro level from whom they are buying.

3.       Expanded margin and lower costs.

Risk Management

1.     MDM can enable enterprise wide view of credit risk exposure. It will also reduce business exposure/risk.

2.     MDM can increase capitalization on market opportunity with new product.

Reporting for executive

1.     Faster, more-effective decision making affecting revenue, costs and agility.

2.     Competitive advantage through process agility, and enterprise wide ability to adopt and adapt end-to-end business processes.

3.     It will enable to manage resource effectively.

MDM Values

As per Gartner research MDM can bring value to the business falls into below three categories:

·         Run the business: Resources consumed and focused on the continuing operation of the business. It includes all nondiscretionary expense as part of the "run the business" cost.

·         Grow the business: Resources consumed and focused on developing and enhancing IT systems in support of business growth (typically, organic growth). Discretionary investments are included in the grow the business" cost.

·         Transform the business: Resources consumed and focused on implementing technology systems that enable the enterprise to enact new business models. This is very much a "venture" category.

Scenarios

Data that needs versioning, auditing, or any other kind of maintaining of history, is typically master data. Finally, we give more attention to data that we reuse repeatedly. Re-usage increases value of data for us. The value of the data can increase because of other factors as well like quality and integration.MDM is useful when we need any kind of cross-system interaction, like doing analysis over data from multiple systems.

Below are the scenarios where MDM can be used effectively:

1.     Customer data integration (CDI) to enable overall customer satisfaction, service and retention as well as increase sale people effectiveness.

2.     Product information management to increase revenue various lead and cycle time reductions.

3.     Procurement-oriented MDM to expanded margin and lower costs.

4.     MDM in risk management can contribute to reduced business exposure and risk.

5.     MDM for executives in a form of report can enhance decision making faster. It can contribute towards more-effective decision making affecting revenue, costs and Agility.

6.      MDM in Business performance/agility as in service-oriented architecture (SOA)/data service environment can give competitive advantage through process agility, and enterprise wide ability to adopt and adapt end-to-end business processes.

7.     Development of a product catalog for supporting selling processes.

8.     Integration of multichannel selling, marketing, and service process unifying product, customer and location master data.

9.      Support for a marketing campaign using multiple customer files for a year-end sales drive.

10.   Unification of identity management (user application logon and security).

11.  Business analytics framework to facilitate faster execution and realization of benefits from mergers and acquisitions.

12.   Alignment of master data across BI, performance management and operational business applications.

13.  Alignment of application development practices with business process management suite BPMS) tools.

14.  Maintenance of product, price/discount, packaging and configuration rules for reference by selling and customer support processes.

15.  Support for field maintenance and warranty services over the lifetime of a product and customer relationship.

16.    Migrating of legacy and multiple ERP systems to a smaller number, or even one, ERP structure over a multiyear effort.

Conclusion

MDM can add value to the business in many different ways, most often indirectly, as an enabler for other business and IT initiatives. MDM enable broader and more complex data integration. It eliminates redundant data management activities and redundant integration activities. It also improves decision making, data quality and simplifies data integration. While creating a clean master list can be a challenge, but it will definitely bring many positive benefits.

Acknowledgement & References:

1.       http://www.solidq.com/ce-en/AboutUS/Documents/DQMDM_SQL2008R2.pdf

2.       Gartner Research Case Studies: Where MDM Adds Value to the Business by (Andrew White, John Radcliffe).

BI on SQL Azure

Introduction

A newly emerged concept ‘cloud computing’ is a phenomenon which is quickly embedding into all sectors of IT industry. Data Warehouse (DW) and Business Intelligence (BI) is no exception and are already touching it. It’s right time to identify the potential of existing DW/BI applications post migration to Cloud. Below we will try to cover DW/BI’s compatibility with cloud. ETL, OLAP cube, physical architecture and reporting tools are the major DW/BI processes to be considered and kept in mind before migration. Below we are trying to map each of these processes with cloud respectively.

·         ETL (Extract, Transform and Load) is mostly used process in DW. Here we generally receive large files in week-ends or out of business hours to be processed and loaded. It may be better to use the resources according to demand/requirement for the ETL instead of fixed capacity.

·         OLAP (Online Analytical Processing) cubes are highly intensive to compute and require stronger processing. High Performance Computing and scalability is the main feature of cloud and it is aligned to OLAP cubes requirements.

·         Massively Parallel Processing, back up valuable data, mirrored data and high availability are  the architecture needs in DW/BI environment.SQL Azure platform have built in  provisions for a architecture, with no physical administrator, high availability and built in fault tolerance.

·         There are lots of reporting products/tools used in DW/BI environment. There isn’t a lot of existing support for all tools in cloud but Microsoft is continuously enhancing and improving SQL Azure as a platform as a service to support reporting and all other BI needs.

What can be migrated to cloud?

Now the question comes what can be migrated to cloud? The existing STAR/Snowflake schema design can be migrated as it is to the cloud. Storage allocations could be a challenge. In order to overcome this challenge, existing database’s space usage should be calculated and same amount of space should be requested on the cloud. The amount of data that can be stored in SQL Azure is unlimited but since there is a limit of 50 GB for size of business database, in-order to migrate data which is more than 50 GB, we need to break it in parts and keep it across multiple databases each of 50 GB. Now if you are thinking how this data which is kept across multiple databases can be accessed. The solution lies in skills to generate queries. Parallel queries can be used to access this data.

Once a highly scalable database infrastructure is setup on SQL Azure platform, we can use traditional bcp tool or bcp utility of SQL 2008 R2 to move data from the existing on-premise (headquarters) DW to SQL Azure. SQL Server 2008 R2 Import and Export Data Wizard can also be used. SQL Server Migration Assistant (SSMA for Access v4.2) supports migrating your schema and data from Microsoft Access to SQL Azure.

Once we load DW data on cloud then we need a continuous process to load day to day operational data from various sources on cloud. We could build an ETL framework between office (on premise) and cloud. On premise ETL framework (SSIS packages) should be used to sync data as cloud doesn’t support Business Intelligence Development Studio for now. There could be various choices to sync data and one with minimum cost should always be preferred.

·         SQL Azure to SQL Azure synchronization

·         Office Head Quarter (on-premise) to cloud

·         Cloud to Office Head Quarter (on-premise)

·         Bi-directional

Alternatively, Windows Azure Appfabric Integration can be used as an ETL platform. Of course it is not a good choice but alternate option to use Appfabric integration instead of SSIS packages.

At this time there is no direct support for OLAP and CUBE processing on SQL Azure; alternatively with the HPC (High Performance Computing) attributes using multiple Worker roles, manually aggregation of the data can be achieved. It is interesting to see how OLAP cube processing can fit on cloud? Grid computing attribute of cloud can be used in processing of OLAP cubes. Grid computing is a type of parallel and distributed system that enables the sharing, selection, and aggregation of geographically distributed "autonomous" resources dynamically at run time depending on the work load and availability requirements. We could implement Grid Computing in a cloud computing environment and it is a valid to say that "every cloud is a grid". With appropriate support from Cloud Computing Platform and with work load management, a Cloud platform can be effectively used as a Grid to effectively process the OLAP. It is not so worthy as lots of task similar to BI engine needs to be created. It is just an idea that HPC is an important feature of cloud and same can be utilized to process OLAP cube.

SQL Server Reporting Services tool is supported by cloud but it is in early stage. Limited CTP of the new SQL Azure Reporting is kicked off late last year and it is getting a good response. We can build as well as use these reports in cloud with a small limitation that SQL Azure Reporting currently can connect only to SQL Azure database. I believe SQL Server Reporting Services tool should have a great feature on SQL Azure. However, SQL Azure is supporting Excel and Power pivot which makes it very useful and powerful.

Suitability

SQL Azure is suitable for small and medium size (SMB) companies. It can also be suitable for other applications/scenarios which are listed below:

·         It can be used for Proof of concepts (POCs) for simple applications.

·         New Start up firms can use SQL Azure to avoid initial capital investments on hardware and software licenses.

·         Educational and Training institute can use SQL Azure to reduce their infrastructure cost.

·         Generally in DW data comes from multiple sources and there might be a need to share some data across customer, partners, vendors and employee. SQL Azure suits best here. The consolidated data should be pushed in SQL Azure and then share across customer, partners, vendors and employee.

·         It can also used by the organizations which use their application on quarterly/half yearly/yearly interval. CBSE/ICSE and state boards declare their result annually, IT computation and declaration are done once in a year. Here SQL Azure should be the best choice.

Limitation

A limitation of SQL Azure Database that cannot be missed to mention is that it doesn’t support all of the features and data types that are found in SQL Server. Analysis Services, Replication, and Service Broker, of Business Intelligence Development Studio (BIDS) are the services that are not currently provided on the Windows Azure platform.

Conclusion

·         On Premises ETL solution can be used on SQL Azure.

·         There is very limited support for OLAP as of now. All BI components of Business Intelligence Development Studio (BIDS) should be supported by cloud including usage based aggregations, portioning etc. These value added features are desired and we expect Microsoft should implement them.

·         SQL Azure should support all features which are available in normal database version.

·         Elasticity, Scalability and Reliability supports physical architecture required for DW/BI.

·         SSRS, Excel and Power Pivot reports are supported on SQL Azure.

·         SQL Azure can be used by small and medium level organization.

·         It can also be used for high volume of data by overcoming the size limitation of SQL Azure database.

·         As per current scenario Microsoft Cloud Platform seems to be positioned as one of the leading platforms for DW/BI because it demonstrates great potential in early stage. We expect a great future of SQL Azure for DW/BI if Microsoft promises to enhance it continuously to overcome its limitation.