How to Design Data Marts

Data marts are the business user interface of your data warehouse. I have tried to explain how to design an enterprise data warehouse in my first article. This article will give some best practice tips for designing useful data marts for your business teams. The question was in my previous article:

"What kind of data marts do you need? "

Your business teams' needs can differ from each other. They may need their own data format where, for example, the finance department has requirements other than the sales or marketing department. 

Security requirements must also take into consideration. Different departments should not reach or see others' data. You may not want to prefer other departments to see all the financial data on your systems. 

On the other hand, there will exist shared data between departments, to keep the single point of truth, the data mart design should also have some common structures on your system.

Data marts are focused on mostly fast reading of the data. The data should sometimes be kept in summary formats. So your data marts may contain some aggregated structures or calculated metrics based on your business team need. 

Keeping history is very important for your trend analysis and your past situation of your company. However, keeping all the transaction data may cost you a lot. So you can create some end-of-month or maybe end-of-week structures in your data marts.  

So we can consolidate the questions above as the following lines:

• What do business teams need? Do they have different requirements and need different formats of data?
• Is security of data a concern for your company? 
• Does the data mart have common structures like dimensions or facts?
• Do business teams need aggregated data?
• Do business teams need new calculated metrics? 
• What kind of historical data is needed?
• Should there be a life cycle of a data mart?

Common Structures

Not every data may not be specific to a business department. For example, your customer data is unique on the company base. So the customer data can be kept in a single format and deliver to each department from the same data source.  

Common structures also give you the ability of the easy management of system. In terms of the ETL process, you do not have to manage, schedule separately your data marts. Common structures give also the central management of the ETL processes.

To design the common structures you can group them in two categories:

1. Dimensions: You can keep all of them in one schema or user. Knowing that all of them are your dimension tables. 
2. Facts: Facts include your big master tables, end of month structures, transaction tables. 

Security

You can prevent all user to query your common structures and create database roles for each of the business teams defining each team their own policy with their database role. 

The business terms of the data marts can have views for security reasons. You can filter your data both in columns and rows in your views. But for the performance issues, you should always take into consideration not to define complex queries in your views otherwise the reading performance of your reports may decrease.

Aggregated and Calculated Data

Data marts are designed for the high performance of reading data. According to your business reporting requirements, you should sometimes need to create aggregated structures. For example, if you billions of usage data you can keep a simple version of the sum of the usage data. Sometimes calculating new metrics based on the existing data is needed. Like the cost of the product, or the revenue of a customer or rule-based segmentation of your customers. Many data sources need to be consolidated in this kind of calculation. At this point, calculated data can be generated for your data marts in daily, weekly, or monthly basis as required.

Keeping Historical Data

It is sometimes necessary to keep data in some date basely structures like End of Month, End of Week or partitioned transaction data. The depth of the data can be 12-month, 24-month or more as according your trend and data analysis requirements. This depth should be decided on the data characteristics.

Data Mart Types for Business Needs

Everyone in a company does not relate to all the data that the company has. Salespeople usually deal with the order status; which orders completed, which are currently in progress. Potential customers are also in the focus of the sales department. So you can design a data mart that has order data, customer data, etc. On the other side, the finance department may deal with payments, invoice amounts, and other money stuff.

Life Cycle of a Data Mart

Should there be a life cycle for a data mart? The answer is sometimes "yes" and sometimes "no". If you design a data mart for a small team, sometime in the future, your company organization could be changed and the team might not exist in your company any further. In such cases, you may not want that data mart anymore. However, you can still have the data in your underlying enterprise data warehouse.