Working in the credit card space for nearly 20 years, and focused on business performance supporting by IT systems and processes, I can say that a well designed DB is paramount for system performance, leading to good reporting, good decisions, and a successful business.
One aspect of the SDLC that occurs more than any CIO would like to admit, is that the due dates for the system (capability) readiness are set in stone without a good vetting of the entire process to actually achieve those business outcomes with the foundational aspects of the systems and processes.
The net effect of setting due dates for launching capabilities is that certain critical but not well understood processes get squeezed in the SDLC, and then the executives scratch their heads on why the overall ROI is not being generated from the system capability.
The two phases that I often see cut the most due to lack of understanding are design and change management.
We will focus on the "facts" of the DB design since we are studying facts and dimensional modeling in this section of the class. Having the right expertise as well as sufficient time to properly design the DB is absolutely critical to the overall system capability performance.
Imagine building a house, and not spending much time on how the hallway connects to the bathroom and then potentially having 2 or 3 floors, but a missing staircase inside, but one outside? The net result would be that one would have to go outside of the house, then climb up the stairs and up to the second floor. This is a similar type of thing that the queries will have to do if they have to make joins that are not efficient and end up with a performance cost.
The design of the facts and dimensions is the critical aspect of this and a fundamental tenet to the overall system performance. Paying close attention to the attributes that one includes in the various tables is key as it can also serve as an audit to the requirements analysis phase that is performed as well.
If one thinks about the end goal of the system and realizes that they will need to have some historical data on the addresses, and chooses Type 1, then that is the wrong design. This can be caught during the design phase.
Also, If there are a number of Yes/No attributes, then one can utilize the junk dimension design to remove some of the attributes that will make the fact table explode in volume and have a simpler more performance oriented design by putting those attributes in a related dimension table.
Another aspect that can improve performance and maintainability is whether to employ a snowflake design with the normalization of the tables.
All of these design decisions effect the queries that will select the data and return it to a report for executives to see or for other program applications to utilize for various system functions.
Another aspect of good DB design and system cleanliness is data quality, as well as master data management. There are a number of great profiling tools in the market today, and many can be automated to perform cleansing, deduping, and other quality checks on the data to ensure efficiency and effectiveness in the system.
In summary a good design through spending adequate time on design of facts, dimensions, and the right schema, as well as having good data quality processes and tools in the system, will enable the business performance metrics to be met more naturally in today's environment.
No comments:
Post a Comment