The beauty of a truly wonderful database design is its ability to serve many masters. And good database designers are able to empathize with those who will use their designs. In business intelligence settings, three perspectives deserve consideration when composing designs.
The first, and most important, perspective is that of the business users. The business users require visibility for all the data elements that they need to utilize. Fortunately, it is often only at the individual column level that business users have their concerns. How those data items are spread across the tables is often of less concern because many reporting tools successfully obscure that level of detail. The good designer must consider the quirks or shortcomings of any used reporting tool, in order to best comprehend the impact of design variations and options. Designers need to be conscious of how the tool exposes the data and what aspects may leave rough edges. For example, if stepping away from a simple star schema creates issues, the data modeler needs to apprehend that circumstance.
The next important perspective concerns the reporting specialists, those who do the reports that the users sometimes want but cannot do themselves because of the complexity of the reports. To assist the ultimate preparation of a particularly obstinate report or scorecard, the designer must understand the requirements of those overly complex reports. The designer should step through the possibilities, from something as simple as a well-placed index through something more demanding like special tables. Sometimes special tables may be necessary for the sole purpose of making reporting tasks easier to accomplish, or to enhance the response time for one or another report.
The third important perspective is that of the ETL developer. These developers move the data from the sources into the structures to be used by the first two groups. In an ideal circumstance, the target structures are simple star schemas and the sources are simple transaction and reference structures. The ETL developer will have an easy time connecting the dots and have no impact at all on a database’s design. But there also will be times when the structures are not straight-forward, or the data volume makes tasks hard. At such times, supporting data structures may need to be created to ease the processing burden. Some organizations may leave these tasks entirely in the hands of the ETL developers, while other businesses must involve the data architect.
Although the database designers do not need to write reports or build ETL processes, having such experience can greatly aid them. Database designers need to empathize with these other groups and understand the design choices that will make those other groups’ burdens heavier or lighter. A designer who does not appreciate the implications of a structural design upon the SQL used to access the structure or upon the performance consequences as data volumes scale up, may inadvertently create awkward structures that are painful to deal with. There is a symbiotic relationship between ETL, reports, and database design; the designers need to appreciate the impact of their decisions on these other groups who depend on the designers’ handiwork.
It is rare for a single design to leave everyone happy. But good designs balance the pros and cons of each interested party to establish an environment where everyone gets something. The more empathetic that data architects are for the plight of those who must work day after day with the architects’ output, the more likely those data architects are to find a design that establishes a satisfactory compromise of what each group needs. And at the same time, surprise people with the wonderful utility of their designs.