In the first part of this series, we explored what a master data dictionary (MDD) is, its need, the important aspects to consider when building one, and the stakeholders involved.

In this second and final part, we’ll delve into a few suggestions for the structure of the dictionary and discuss how to choose the right tool to build it in.

Structure of the master data dictionary

Broadly, an MDD can be organized in two ways:

1. By data source: This is the method we use at Magento Business Intelligence. Each metric is categorized under the primary data source it is coming from. For example, all support metrics are under Zendesk (our support tool), all account level metrics are under Salesforce, and all time-tracking metrics are under Toggl.
2. By business function: This method should be used when metrics are often joined across multiple data sources. For example, if this method was used at Magento Business Intelligence, the categories would be “Support Metrics”, “Account Level Metrics” and “Operational Efficiency”.

Under both of the two above methods, the metrics can either be simply listed out with their respective SQL queries, or organized as a “View”. At Magento Business Intelligence, we use a combination of both methods:

1. Simple, listed–out metrics: This is the simplest method to organize metrics, but can involve some work for a user when combining several metrics to create a report. For example, let’s say we’re trying to analyze support metrics, and we want to build a report that calculates the number of new support requests along with their assignment and resolution times on a weekly basis. To do so using this method, we would need to find the individual queries for “new support requests”, “assignment time” and “resolution time” and combine them as a single query when creating the report.
2. Creating views: This is a method that is very useful when using a SQL based tool such as Mode Analytics. At Magento Business Intelligence, we use Mode for a large part of our internal reporting. It has a feature called “Definitions” that allows the creation of run-time “views”, which are pre-defined SQL queries that can be referenced like tables in SQL queries. By creating views for each data source, the unnecessary underlying logic can be abstracted from the end-user. For example, the view for “Support Metrics” can contain the columns “Assignment Time”, “Resolution Time”, “Number of responses”, “Time Between Responses” and others. When using this view, the end-user will only need to SELECT the required columns from the view instead of using the underlying SQL logic to calculate these columns. This accomplishes the primary objective of master data dictionaries – maintaining consistency in definitions across organizations.

Choosing the right tool

The choice of tool depends on the structure being maintained in the master data dictionary. At Magento Business Intelligence, we use a combination of our own tool, Magento Business Intelligence (formerly RJMetrics), Wiki, and Mode Analytics to maintain our master data dictionary. Below are the benefits of using each of these tools:

1. Magento Business Intelligence (RJMetrics): We recommend using this tool when the end user is non-technical and not familiar with SQL. Indeed, this is the primary use case of Magento Business Intelligence. It allows the creation of “metrics”, which are pre-defined queries that can be “dragged-and-dropped” to reports. The user need not know the underlying logic of the metric.
2. Wiki: When using the “simple listed out metrics” method of structuring the master data dictionary, a tool like a wiki article can be quite easy and accessible. The “contents” section of the wiki can be linked to the metrics listed in the document. Each metric can contain the required definition, explained in either simple English, SQL queries or a combination of both. An end-user accessing the wiki will need to click on the desired hyperlink at the top of the document to navigate to the required metric’s definition.
3. Mode Analytics: Mode makes it very easy to store SQL “views” using the “Definitions” feature and share reports across organizations. While it is not the ideal tool for non-technical business users to build reports in, it can be very efficient for technical users to collaborate and build reports in. It also has the capability to embed reports outside the platform, which opens up new use cases as well. At Magento Business Intelligence, we use Mode Analytics in conjunction with Stitch, the data consolidation tool. Stitch pipes our data from the different data sources to an Amazon Redshift cluster, from which Mode Analytics can read it.

Finally, the choices you make regarding the structure and tool for your master data dictionary depend on several other variables. It is possible that none of the above options are optimal for your use case. In this case, we would love to hear about how your situation is different and what you have done to build out a master data dictionary! Please feel free to reach out to us in the comments section below.