When working on a multidimensional design, every fact table within scope should be handled with care. In an ideal world, each low-level fact table represents the metrics related to a business event. The meaning of a fact table, ideally, should be evident based on the table name and the composition of the fact table's primary key. Deciding on a primary key for a fact table is an important choice.
Posted April 09, 2019
Clarity of vision is absolutely the most important part of database design. The data architect must understand the shape and patterns of the data being modeled. This lucidity arises when the designer understands the subject area, the goals of the target database, the nature of the data sources involved, and the internal lifecycle of the database objects in scope.
Posted March 04, 2019
In dimensional modeling, business events are typically designated as facts while descriptive information elements are dimensions. However, events (or information about them) occasionally serve as dimensions as well as facts. A good data architect must watch their p's and q's and be certain when it is appropriate for a fact to also serve as a dimension—or when the dual function is not appropriate.
Posted February 08, 2019
More harm than good has been done to software development by letting the planning dates drive the work instead of having the work drive the dates. This planning-date-driven approach causes more stress, more bad decisions, more rework, more failed projects than all other causes combined.
Posted January 02, 2019
Data mart builders must understand what they are working to accomplish. The DBMS is not going to magically guide them to a solution. The builder is responsible for knowing how dimensional techniques work, why they work, and what options may exist within the dimensional framework.
Posted December 04, 2018
While relational database management systems are still the workaday workhorse, we are now adding into the mix document, columnar, and graph datastores, and their variants. Each datastore has something at which it excels, and other things it may not. Similarly, the rules followed in composing data structures, based on the platforms selected, also vary greatly.
Posted November 01, 2018
There has always been a need to tightly control some data items, such as passwords and Social Security numbers. Today, with the rise of concerns over personally identifiable information (PII), the General Data Protection Regulation (GDPR), and other legal mandates, a much larger group of data elements must be controlled. These legal data governance issues may need to guide our hands as we establish database designs.
Posted October 10, 2018
In establishing a staging or landing area for a data lake, a data hub, or a quaint data warehouse environment, structures need to be established that will mimic source data in support of two very basic queries. The first is: "What does the current source dataset look like?" And the second: "What change activity has occurred against the source since the last time it was interrogated?"
Posted September 04, 2018
In the big data world of today, issues abound. People discuss structured data versus unstructured data; graph versus JSON versus columnar data stores; even batch processing versus streaming. Differences between each of these kinds of things are important. How they are used can help direct how best to store content for use. Therefore, deep understanding of usage is critical in determining the flavor of data persistence employed.
Posted August 08, 2018
When we hear the term "think outside the box," how often do we really examine what that phrase truly means? First, one needs a box. And it is on this issue where most folks fail. Before one can consider what is "outside the box," one must clearly understand what exactly is meant by "inside the box." People often consider random approaches the same as being "outside the box." However, just different is not enough.
Posted July 02, 2018
Under usual circumstances, the one-to-many or many-to-many relationship, alone, drives the pattern used within the database model. Certainly, the logical database model should represent the proper business semantics of the situation. But on the physical side, there may exist extenuating circumstances that would cause a data modeler to consider including an associative table construct for a one-to-many relationship.
Posted June 01, 2018
Agile approaches to projects have been touted by many organizations across the globe. IT shops frustrated over expensive projects falling well shy of their goals have been desperate for change. These anxious organizations race into trying Agile as a solution to their woes.
Posted May 08, 2018
For data architects, it is not unusual to use a data modeling tool to reverse-engineer existing solutions' databases. The reverse-engineering could occur for a functional reason, such as gathering information to evaluate a replacement option, or to comprehend a solution, seeking to work out what data should be extracted for a downstream business intelligence need.
Posted April 12, 2018
Many organizations talk about data governance, but rather than establishing an ongoing governance process that is involved with every project, the governance is viewed as a one-time task to be slogged through, over with and done. Such myopic approaches will only lead to failure. Data governance is like life, it is the journey, not a destination.Proper data governance brings commonality to an organization; it leads the journey to a single version of the truth. A single version of the truth does not mean everyone must kowtow to a single metric, but it does mean distinct calculations unique to dissimilar sub-groups have different specific names at the corporate level, even if those naming differences are subtle.
Posted March 07, 2018
Far too often, business users seem consumed by the systems they handle. This makes them unable to define the necessary business processes or needs of the organization. All these users can do is describe what their off-the-shelf packages provide for them. In fact, most users take great pride in their "understanding" of the current system.
Posted February 01, 2018
Don't let the term "unstructured data" confuse you. Structure exists, somehow and somewhere, within unstructured source data. It is that subtle, possibly even encrypted, structure that contains the gems of knowledge that an enterprise seeks. Sometimes those gems only shine when extracted and combined with other little gems from other data sources.
Posted January 02, 2018
No, we weren't born in a crossfire hurricane, nor schooled with a strap right across our backs; we have other crises, mixed priorities, and resourcing deficiencies to cope with. But it's all right now. In fact, it's a gas. There is little choice for those who wish to survive in the IT trade. Either one copes with an ever-changing landscape, or one moves on to another industry. Technology shops across the nation, if not across the world, are in themidst of a crisis. The only problem is that this crisis has gone on for a couple of decades or more.
Posted December 01, 2017
Leadership in IT can be very dynamic. At one time or another it seems anyone may be impressed into a leadership role. The data people (DBAs, data architects, data modelers), often end up considered as unofficial leaders (largely because they are the smarter ones on the team). A leader, whether official or unofficial, has some responsibilities.
Posted November 01, 2017
Operational systems are where data is born. These systems either force people to enter their details or acquire the same from trusted sources. Names, addresses, merchandise selections, and credit card numbers are consumed. The operational solutions interact with users and compatriot applications to give birth to their raison d'etre, be it purchase orders, payroll checks, or any of the thousands of other documents and transactions.
Posted October 18, 2017
Every few years we hear of one new idea or product that will surely bring death to the relational database, or death to the data warehouse, or death to something. It appears that many prefer to see death or at least they greatly enjoy planning for it. Often these finalities never seem to arrive.
Posted September 07, 2017
There is an old joke that a tourist in New York City is lost while sightseeing and notices a musician leaving a taxi; he walks up to the musician and asks, "How do you get to Carnegie Hall?" The musician responds, "Practice, practice, practice." Obviously, such jokes are doomed to obscurity as people now will simply use the navigation app on their phone. However, the advice itself, in its own way, will always be relevant—practice, practice, practice.
Posted August 09, 2017
For everyone normalizing their designs, just remember: There ain't no such thing as a partial foreign key. When a project pretends that partial foreign keys are a valid concept, the end result is a database model that either lies to viewers, or does not give them the necessary information to readily join tables together.
Posted July 05, 2017
Updating fact rows inside a star schema set of tables is never a best practice. Even so, some organizations travel down this path.
Posted June 01, 2017
Choices are pervasive when designing a database. The data modeler must progress through a series of issues: What ideas are important? Which objects stand out? Which concepts can take a back seat? Adding to all those decisions comes understanding the target structures one is shooting at. A normalized design may lean one way, while a dimensional design may lean another.
Posted May 05, 2017
It often seems that working around things is a full-time task in every area of information technology. When workarounds are conceived and deployed, people are not always in agreement.
Posted April 07, 2017
Many businesses seem to believe that dimension tables and reference tables are indistinguishable objects. Apparently, the only difference worthy of note seems to be altering the object's name from "something reference," or "something xref," to "something dimension." As these organizations build multidimensional data marts, they often place a view on top of their reference table and feel good about how quickly and efficiently they complete their data mart.
Posted March 02, 2017
At one of my very first jobs in the service industry, I had a boss who had a variation on an old adage. He would say, "The customer is not always right. In fact many times the customer is dead wrong; but the customer is always the customer." In a similar way, in data modeling, business users and internal IT users, make the world go around. If they don't use the models that are designed, or worse, if they cannot relate to the designed data models, then what value has been delivered?
Posted February 08, 2017
There was a time when what you saw was what you got. Building up the components of a business intelligence area was very straight-forward. A staging area was a staging area; an operational data store was an operational data store. But like buying a pitcher of beer for $2, or gas for less than a dollar per gallon, those days are gone. The dynamics have changed, things are more federated, and IT must accept more than one standard tool.
Posted January 03, 2017
Terms such as "active," "inactive," and "canceled" may seem mundane and inconsequential, and when folks hear the term,"valid values," their eyes glaze and expectations of interest diminish. But exciting or not, reference values and an understanding of them are important to every organization.
Posted December 01, 2016
Why is there such pervasive abuse and misuse of database views today? Views are a helpful tool in building a business intelligence environment, yet many organizations establish practices that not only rob views of their full usefulness but present patterns that actually confuse issues instead.
Posted November 02, 2016
One symptom of an organization in the middle of a knowledge vacuum is evidenced by SQL that often includes what appears to be extravagant usage of the GROUP BY clause. Writing GROUP BYs here, there, and everywhere becomes a little SQL development dance step, a jitterbug to bypass the issue—moving but not really getting anywhere. Why do these kinds of circumstances exist? Well, maybe the only expert on the involved system has retired and no one else has picked up the torch, so no one is willing to touch the code.
Posted October 07, 2016
Programming is a literal sport. Code does exactly what it is configured to do, no compromises. When the definition of a task is fuzzy, it is up to the developer to do what they believe is correct. Does the code reflect what is desired? That answer is left open to interpretation. Sadly, developers may not have a clear understanding, and even the users requesting the solution may not be sure. The results can be very painful for an organization. Expectations may not align with the delivered solutions. Users will blame IT; IT will blame users.
Posted September 02, 2016
As one works through the normal forms, be it a journey to the placid shores of third normal, or the more arid climes of Boyce-Codd normal form, or even the cloudy peaks of fourth normal and beyond—and before one starts thinking about normalizing the design—the database designer has covered a lot of ground work already. Before thinking of normalizing, one needs to have conceptualized the relations that might be within the solution's scope.
Posted August 04, 2016
In the data warehousing arena, development databases often get short shrift. Frequently, this situation arises because development databases are considered too much work to be done properly. So, instead of embracing the problem and following through on the necessary work, it is ignored or done poorly. One could almost say that ignoring the development database has become the standard practice.
Posted July 12, 2016
Many of the NoSQL tools out there, such as MongoDB, Couchbase, Hadoop, and others, purport to be leading a revolution and breaking the bonds of servitude to the restrictive, inflexible, established, relational market. They claim users need more, users need better … and they are there to help. Of course, when speaking about those relational flaws, the comments always focus on problematic aspects of a DBMS' physical implementation.
Posted June 09, 2016
Not too long ago, IT embraced the pattern language concepts of Christopher Alexander. Being an architect, of the more traditional variety, his ideas were based on creating spaces in which people felt good, even if they didn't comprehend exactly why. Architected spaces need to express multiple qualities that include being alive, whole, comforting, free, exact, egoless, and eternal. The more those qualities were embodied, the better people responded to the desirability of the space.
Posted May 04, 2016
The traditional information engineering approach advocates the placement of as much business logic as possible inside the database management system (DBMS). But, more recently, under the umbrella of service-oriented architecture (SOA), folks are arguing for placement of that business logic in a layer of code outside the DBMS. Occasionally, those who favor locating business logic outside the DBMS have even gone so far as to say that this logic "naturally" belongs in a non-DBMS-supported layer.
Posted March 31, 2016
When it comes to operational, third normal form approaches, too many database models today have very low levels of business veracity. This is because current IT trends work against incorporating too much truth inside the entity-relationship diagram. Instead, development teams center on the finished product and excessively worry about describing a solution rather than describing the actual organization.
Posted March 03, 2016
Many people claim to understand the basics of good data and database hygiene. Often, these same people claim it is all very simple and very obvious. However, when peering into existing code and databases, it doesn't seem that good practices are as obvious as people say. "A2" as the name of a column may sound ridiculous, but it has happened.
Posted February 10, 2016
Data modelers must look at the big picture of an organization's data ecosystem to ensure additions and changes fit in properly. Simultaneously, each data modeler must be focused on the minute details, adhering to naming standards, domain rules, data type practices, still remaining ever vigilant for instilling consistency across everything they do. And while focused on all of the above, their efforts must culminate in a practical model that serves the individual project's requirements while also being implementable, maintainable, and extensible.
Posted January 07, 2016
Our data models, in reflecting a specific business, must accurately portray the essence of each business. The unique reality within each organization drives the shape of every data model. The logical meaning of each data element originates with what is actually done and how it is accomplished within that particular organization.
Posted December 02, 2015
Many neophyte data modelers have trouble distinguishing between logical and physical data models. These novices likely cannot explain why each model exists, or the differences expressed between them. Sadly, such confusion also exists in the realm of the experienced data modeler. Not to say an experienced data modeler can't express the difference between a logical and a physical data model; but across a group of experienced data modelers one would not get a consistent answer.
Posted November 09, 2015
The Agile methodology is great for getting turgid development teams to start working faster and more coherently. With Agile, which focuses on more rapid, incremental deliverables and cross-departmental collaboration, the bureaucratic plaque is flushed from the information technology groups' arteries. But there is a dark side to Agile approaches.
Posted October 07, 2015
Data modelers face a choice when encountering multiple variations of a data item. Designers must focus on the longer term appropriateness of their decisions when choosing how their designs will play out; and going vertical or horizontal does have an impact over time.
Posted September 09, 2015
There are fundamental differences in priorities and approaches in building operational solutions and in constructing analytical supporting solutions. However, these differences can produce a gap in understanding that occasionally results in minor battles and skirmishes between these groups along the path of getting work done. A critical area of conflict is access to production data. When building an operationally focused solution such as a website, the application is supreme.
Posted August 10, 2015
Data governance is a touchy subject. Many organizations have tried to build a practice…and failed. Other organizations feel too daunted to even try. IT in particular has a very conflicted relationship with data governance.
Posted July 08, 2015
Petabytes, and more, of data are meaningless without guides to what it all is. Future data users need to know where it came from, how it was filtered, and how it was transformed. Without some functional level of governance we are headed toward a "datapocalypse"—having lots of content but little of value.
Posted June 09, 2015
The objects and relationships identified within any database design need to match with the very subjective realities considered true within a given organization. An optimal data model must incorporate the semantics of the business and provide for a structural arrangement supporting the necessary corporate instrumentality. And that model should do so, even if in the details things within the model are contrary to what other organizations may consider to be the nature of their reality.
Posted May 14, 2015
How does an organization acknowledge that data is important? An organization does so by enabling and supporting efforts for gathering and persisting information about the organization's data resources.
Posted April 06, 2015
In information services, providing a solution that removes a user's pain can be a great boost to one's psyche. Being the hero, wins trust, and may even enhance job security. However, organizationally, too many sideline heroics create chaos. The day after the crisis has passed, things are left in a less than wonderful state.
Posted March 12, 2015