Too often professionals such as Data Analysts, Data Architects, and Compliance Specialists find themselves at a loss when it comes to being able, on their own, to discover, understand and use the rich Metadata within large enterprise ERP and CRM applications.

It becomes complicated, costly, and time-consuming for these users to pull relevant business-context subsets of data models for Data Warehouses and Business Intelligence systems in support of Analytics, insight and reporting if they are unable to easily leverage metadata to get an understandable view of enterprise software data models and to incorporate data from different packages as needed to compose data queries.

The same is true when it comes to connecting to and extracting metadata from massive enterprise applications to work with Data Modeling and Data Governance tools that help organizations centralize, manage, and share Metadata assets, often as part of plans to support Metadata Glossaries and Data Lineage that may directed at streamlining an understanding of enterprise data assets to all non-technical staff.

While tools for enabling such efforts exist, most are typically useable only by deep application technical specialists within the organization or by consultants focused on this area. Business data pros need to be able to have a Metadata Discovery solution to aid them in surfing and slicing data by themselves, especially as part of the move to Self-Service Analytics.

Safyr Steps Up

Silwood Technology’s Safyr software product for ERP and CRM Metadata Discovery and extraction has been helping to fill Metadata gaps for more than 15 years. Businesses continue to use Safyr to gain control of Data Management projects, accelerate BI project delivery and deliver enterprise-wide Operational Data Warehouses. As an example, Hydro Tasmania, a leader in renewable energy development, found Safyr to be a big help when it needed to intelligently use metadata after implementing SAP ERP to replace a number of legacy business applications; the company needed to use SAP as a source for its Business Objects BI program.

To that end, it had to identify which SAP tables contained the data needed for the BI project in order to create the appropriate queries in SAP’s Data Services tool to extract that data for the SAP Business Objects BI platform. In most packaged databases rich Metadata does exist across series of tables, but those table names are technical rather than business-useful, as is the case with attributes. “And there are no defined relationships between tables either,” said Roland Bullivant, who is responsible for Silwood’s sales and marketing programs.

Not surprisingly, Hydro Tasmania found that there was no easily accessible Metadata or documentation of that data in SAP, so it was going to be a big challenge to pinpoint the data needed to deliver the necessary reports and dashboards. The size and complexity of the SAP system and lack of discovery tools were as much barriers for Hydro Tasmania as they are for other companies. “An SAP data model could be 120,000 tables and you typically don’t need it all, just a small subset,” says Bullivant.

To find and understand the parts of the SAP model needed for the specific business areas Hydro Tasmania was focused on, the company chose Safyr to connect to its SAP system. Within just hours it had extracted all metadata, including customizations, to a repository of its own SAP metadata that included technical and business names for tables and fields and attribute descriptions of relationships between tables. Searching and navigating the scope of the SAP data model let it create subject areas composed of table groupings relevant to a specific business topic, which it could export to Data Modeling tools in different formats. With business-oriented visualizations and exports quickly in place, it became possible to deliver accurate information to create Data Services queries on the SAP data itself.

Safyr has specific connection capabilities for Oracle E-Business Suite as well as to SAP to pull metadata into a repository to support business-accessible search and discovery of information, Bullivant notes. Other large enterprise applications it supports include Peoplesoft ERP, Siebel ERP, JD Edwards EnterpriseOne – which still are in use at many large firms – as well as Microsoft Dynamics AX 2012, and Salesforce and Force.

In fact, the Salesforce Metadata Discovery and extractor capabilities were added a couple of years ago based on customers’ requests. It was a puzzle to Silwood as to why that was necessary, as Salesforce’s data model in its standard format is very small – perhaps 200 to 300 tables, Bullivant explains. The reason customers were experiencing issues is that while the data model on its own is small, users have added lots of their own tables; they purchase solutions like FinancialForce Accounting from Salesforce’s app exchange that add to the size of the data model.

“One customer’s data model was over 1,000 tables because of the other apps they bought, and they were trying to make sense of the data model for reporting and analytics,” says Bullivant.  A large US tech company’s Salesforce data model had climbed to over 3,000 tables, he adds” so they had multiplied the size of the basic data model by 15 times.”

Another challenge customers faced with Salesforce was the result of the CRM platform’s relatively easy development environment This encourages organization’s development teams to use agile development, leading to regular and quick data model changes.

“Trying to keep track of what you do is a struggle unless you have a tool to extract metadata and compare it – the data model today vs. last month or last week,” he says.

Add on top the fact that many organizations have multiple instances of Salesforce, and it’s hard to know if all the data models underneath each instance are the same. That’s key when a business goal is to create some sort of central database for reporting purposes. So Silwood developed a comparison feature for Salesforce to compare the base data model to another.

Onto the Next Opportunity

It’s not optimal when companies that have many data models lying beneath their large enterprise systems can’t point to the provenance of that data – especially in the days of intensive regulation.

GDPR, of course, is one of the compliance requirements that has caused most businesses to get to work to better order the personal data in their possession, with information audits and data readiness and assessments on the table. Though the deadline for GDPR compliance was back in May, Bullivant notes that most companies underestimated the scope of it, especially large businesses with multiple SAP, Siebel, and other systems. Fortunately, there likely will be some lag in enforcing compliance for most businesses.

“If there is no Metadata in the underlying databases to tell you what the attributes mean” as they relate to personal information such as name, address and so on, says Bullivant.

“It can be tricky to figure out where something like birth date appears in systems with 100,000 tables. There’s a huge spectrum of information and they have to know where all that data is captured so they can change systems to be compliant with data subjects’ rights.”

Those rights include everything from changing inaccurate information to having personal data completely erased.

Even if a company has gotten far along on the road to GDPR compliance, a wrench can be thrown in the works if it then acquires another company and suddenly become responsible for its compliance programs, too. “You might want to merge your SAP systems, but you have to think about what impact that has on personal Data Discovery,” he says. The point is that preparing to make changes as needed to systems to support personal Data Discovery isn’t just a one-time effort, but an ongoing compliance process that demands attention to Metadata improvements.

Safyr is addressing this with new starter packs to help companies find all instances of personal data fields fast in solutions including SAP, such as attributes in any table that have the string “birth” in them. It works using a Safyr Subject Area – a grouping of tables and fields that can be overlaid on the metadata extracted from a customized SAP system to make it easy to find which tables and fields are important to GDPR.

Once that’s done you can build a subset of personal data items and then amalgamate those, potentially exporting what is needed to spreadsheet or modeling tools, Bullivant notes. “You can at least start a data catalog because now it’s aggregated and a Data Analyst can come in and start to combine or pull that data together with other sources,” he says.

Think of Safyr as a blueprint, the foundation of a data landscape of an enterprise package, says Bullivant:

“That blueprint can be handed off to any other system. Here’s the data landscape, so if you want to know how to do reporting, there it is. If you need to do compliance for GDPR, there it is. Whether it’s for reporting or governance, we accelerate the delivery of data to satellite systems so that organizations can make use of the huge amount of valuable data in those systems.”

Photo Credit:Triff/Shutterstock.com