This is a discussion about giving business users the ability to create their own reports, and the benefits of using a metadata layer in your business intelligence platform. Business Objects calls this a Universe, other software have other names for it.
So you built a great data warehousing system, data is flowing in and is well organized, and now you are thinking about delivering this data to the business users. (If you waited until the data warehouse was built to think about information delivery, it is a bit late, this should have come up at the very beginning. Moving on.)
You write reports using SQL queries, and you deliver those reports by email, or maybe have the reports published on a web portal. And you do this for all reports, and those reports are very useful, and the report requests keep coming in. More users from more departments start asking for information, and soon you become a specialist of all the data, all the metrics, all the partnerships, all the details and exceptions of every aspect of the company. You also become the bottleneck, as you cannot keep up with the requests, and business users become impatient.
Sounds familiar? Don't worry, it is quite common, the success of a business intelligence platform can be overwhelming at first.
One way to address this situation is to hire more people to write more reports. It works for a while, but it only delays the problem, and actually makes it worse because it allows the concentration of a lot of business knowledge within a small group of people, who are not the ones making decisions in all those areas. It is inevitable that the business intelligence professionals within your organisation will acquire a lot of business knowledge, but this knowledge needs to be distributed in the respective departments.
A better way to reduce the bottleneck, and at the same time limit the concentration of knowledge, is to give the users the ability to create their own reports, and train them on how to do it effectively. Read this again: give ability to create reports, and train users. If the users are not trained, they will not know how do it. This is just common sense.
So you install a database query tool on the computer of the marketing manager, product development manager, and VP of finance, and they start writing SQL queries against the data warehouse, and you have solved the problem. Right? Wrong. Writing SQL is your job. Your business users need a different tool, something more meaningful than data types, codes, joins and group bys. Your users need a graphical interface.
Most reporting software have a built-in graphical tool to write queries. Business Objects, Microstrategy, Tableau, Cognos, they all offer a way of writing queries using a drag-and-drop interface. But that still requires technical knowledge of the underlying database, with all the complexities and exceptions.
This is where the metadata layer comes in. In short, the metadata layer (also called the business layer, or semantic layer) is a piece of software that sits between the database and the reporting tool, and replaces the details of the database with meaningful business metrics. Dragging the labels "product revenue" and "order date" is meaningful, users of the data will understand it immediately. It standardizes the metrics and names used to describe them, and reduces (and hopefully eliminates) instances of "my data says this" and "my numbers show that". All users see the same information, consistently. Also, the metadata layer allows you to add descriptions to the metrics, or filters, or external data, all without adding complexity for the users.
Another benefit of the metadata layer is that you and your team will also be using it, and it accelerates the creation of reports and analyzes. What, you did not think all the reports would be created by business users from now on, did you? Users will now have a tool to access the data as it relates to their area of expertise, but for company-wide metrics you should retain control of report creation. The metadata layer will make this process easier, too. Plus, as most reporting tools on the market allow you to do, you will still have the ability to bend the metadata rules by writing SQL queries directly against the database, and display it in reports, which the users of the graphical tool will most likely not be able to do. That's the combination of easier work and job security!