Looking through the database diagram, I found 21 core entities (like Person and Product), plus a few reference data sets (like SalesReason and ShipMethod). ![]() It will also start to surface business-specific terms (which may be different to the colloquial interpretation). This level of diagram is absolutely invaluable when you come to look at reporting, as it surfaces all the things the business are ‘concerned’ with. Specifically capturing the entities that the business use, and the words they use to describe how the entities relate to each other. The next level of detail in the diagrams I make is what I call ‘business conceptual’. We can immediately see that ‘Employee’ is used in many sections, and ‘Product’ is used in at least two. For now, we have a high-level diagram of the business.Įxamining each of these ‘sections’ in turn, we can look more into the business relations of the data within the ‘sections’, and what exists between the ‘sections’. If you’re feeling meshy, each of these ‘sections’ are good candidates to look at creating data products to share to the others – but that’s further on down the line. This sort of diagram is really in the realm of enterprise architecture, identifying potential silos and interoperabilities that need to exist for the business to function. This is a small data vault that doesn’t involve the horrors of a 2000+ table undocumented database – looking at you Oracle Workday, Microsoft Dynamics, SAP S4.įrom this physical diagram, we can see a number of clear business ‘sections’ and can construct a high-level business relation diagram. That’s going to make a pretty complicated data vault, but that’s the nature of a data vault. There are ~90 tables, and a lot of relations between the data. Fortunately, a quick google search for ‘adventureworks oltp schema’ gains you a PDF of the physical database diagram. Unfortunately, AdventureWorks doesn’t have ‘a business’ to go and talk to, so we’re going to have to make the horrid assumption that the database is at least a fairly accurate approximation of the business and it’s processes. Processes change over time, fields or tables are co-opted to mean different things, or the database that was originally bought was designed for operating a hotel not a hospital, so all the patient data is in the table marked ‘guests’. Note that this can be, and quite often is, rather different to how any particular database or relational ruleset views the business data. As data vault is focused on the business, the first step of a data vault is always to talk to the business – find out what the business call the entities that they interact with, find out how they interact with them, get a feel for how it all hangs together in the mind of the business. With the initial inspection out of the way, we can start getting down to the business of molding this data into a data vault. Looks like I am going to have to go and whip up some dummy data to finalise out this series – but that’s fine, dummy data can be made. How terribly disappointing from a demonstration point of view. Looking at the data between the databases, that also appears to be totally unchanged, apart from a slightly anomalous change in the last modified date on the ‘Transaction’ table. Hooray for backwards compatibility, but boo for trying to demonstrate what I wanted. ![]() There are some changes to indexes and index methods, but in all major ways, the AdventureWorks database hasn’t changed in 15 years. At least not from a data relations point of view. ![]() However, this isn’t a hard-ball exercise, and so we can just use the in-built ‘Schema Compare’ function in Azure Data Studio.Ĭomparing the 2008 database to the 2012 database to the 2022 database, literally nothing has changed apart from the version numbers. I’ve got some basic scripts that dump all the metadata out to a database that I can then carry forward for checking schema drift, and building up lineage pathways. If you’re hard-balling it gives some great starting places that can be automated to SQL scripts. There’s a lot to look at, depending on how far you want to go with this inspection. We need to see if the data has changed to see if there is more, less, or different data in the tables. We need to see how the database has changed, in case there are new tables, changed fields, etc. Primarily, there are two things that need to be looked at the database, and the data. Following on from the installation, we can now have a look at what we’ve got.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |