Integrating the Dataverse

  At first glance, the value of nothing is not significant.   In the world around us, we literally walk past “nothing” all the time and don’t even notice it.      But at times, “nothing” is just as important as something.   For example, if you were walking on a city street and someone removed a manhole cover.   That “nothing” would certainly have a huge impact if you fell into the manhole. Another way “nothing” can impact you is that it allows others to make assumptions.     Have you ever been in a conversation with a friend when they asked you a question you did not want to answer?   Instead of telling them, “I would prefer not to comment,” did you just leave your comment unsaid, just hanging there in the air?   You were hoping they would infer that you did not want to comment, but is that what happened?   You see, when you did not specifically answer the question, you allowed your friend to answer for you, and that is whe...

When designing your data model, especially in data warehousing or analytical systems, you might opt for identity columns as primary keys. This is a common and often advantageous practice due to the inherent benefits of identity columns. However, relying solely on these generated keys can create a disconnect with how the business views and interacts with data. This is where the data principle of utilizing auxiliary keys becomes crucial. Identity Columns: The Physical Key to Optimization Before we dive into how the principle of using auxiliary keys works, we have to define what we would use as a primary key.  In the world of data warehousing, it is common practice to use identity columns as the primary key of every table.  These auto-incrementing integer columns offer several advantages in database design, but as we will see, they do have flaws.  Some advantages are: Compact Storage: Identity columns are generally small, fixed-size data types (like integers), which makes t...

Did you have a sibling growing up?  Maybe an older brother or sister who did not like sharing?  Or maybe there was a neighborhood kid who was not good about being a team player?  Do you remember that frustration when all you wanted to have was a chance with the football, but because it was not yours, you could never touch it? Do you still get that angry, anxious feeling when you open your data exploration tool of choice, knowing that there are secrets hidden in that data that you have no idea about?  Does the customer on the sales order always have to be the party that pays the invoice?  Should the bill-to address be the ship-to address?  What impact do the shipping terms have when we consider a late payment? Wouldn't it be nice if that type of information were kept somewhere, besides in the head of the person who has worked with the data the longest?  How mad would you be if I told you there has been a place for this data for years, but it just was no...

In my last blog, I highlighted five data principles that I have used in the past to create a solid data strategy.  The idea behind a principle is that it is flexible to adjust to changing needs but rigid enough to maintain order.   A good example of this is the golden rule, "do unto others as you would have them do to you".  When you apply this principle, you are free to do whatever you want, as long as it is something you want done to yourself.  It is a very flexible principle that can have serious consequences if broken.   One crucial principle that forms a solid foundation for your data initiatives is  naming standards . It might seem like a simple concept, but consistent naming conventions throughout your data environment can significantly impact efficiency, governance, and understanding for everyone involved.  With the right naming standards, looking into the data environment as a DBA, developer, or user, there will be no question about ...