The downside is an extra entity layer in the datamodel. Reference Tables will increase the number of HUBs and therefore reduce the number of LINKs and in the Data Vault datamodel, making the datamodel easier to understand. It explains the introduction of the Reference Tables. This was an outcome, which had not been foreseen when the principles of the Data Vault methodology were defined.
When a Data Vault ERD contains a lot of LINKs, it will not make the datamodel easy to understand. Quote: “Reference tables are a normal part of a healthy Data Vault model.” Recently the Data Vault methodology has been extended with a new feature: the Reference Tables.
The consequence of taking the Satellites into the datamodel design is that the result datamodel ends up with far more entities than the original ERD design of the transactional source system. A Hub or a Link can have more than one Satellite and a Satellite can consist of only one descriptive attribute. Attributes with a similar frequency rate are grouped together and form a satellite. The design of the surrounding Satellites is based on a physical aspect of attributes: the change frequency. This is how we convert the ERD of Adventureworks into a Data Vault: an entity in the Adventureworks ERD which did not contain foreign keys was turned into a Hub, otherwise the entity was turned into a Link. The reason that we result with more LINKs than HUBs is the fact that the Adventureworks transactional source system contains more tables, which are the ‘n’ tables in the “1:n” relationships and are the ‘m’ tables in the “n:m” relationships. Upfront we expected to model more HUBs than LINKs. Surprisingly each one of us comes up with more LINKs than HUBs. After completion of the datamodel we compare our results. Without making any agreements and defining principles to convert the ERD of Adventureworks to a Data Vault me and my colleague Mesa start individually. The datamodel of the Adventureworks transactional source system which Microsoft delivers with SQL Server 2005 is used. The result is an Entity Relationship Diagram (ERD), which consists of HUBs, LINKs and Satellites. SELECT Name, ProductNumber INTO dbo.product_name FROM Production.Author: René van Kooten Modeling the Adventureworks Data Vault Modeling the Data Vaultīuilding a Data Vault starts with modeling the Data Vault based on an existing datamodel of a transactional source system.
Adventureworks database tables code#
If you try to copy rows from one table to an existing table using the same code that we used to copy data to a non existing table: SELECT Name, ProductNumber INTO dbo.product_name FROM Production.Product To copy the contents of Name and ProductNumber columns from Production.Product table in AdventureWorks Database to a new table called dbo.product_name: You can create the new table using only a subset of columns in the original table. SELECT * INTO dbo.product_copy FROM Production.Product Here is an example that will copy the Production.Product table in AdventureWorks Database to a new table called dbo.product_copy (new table will be created with same data types as selected columns in an existing table): It will copy all the rows from an existing table into the new table that does not already exist. To create a copy of a table, you can use a Transact-SQL command SELECT INTO.
0 kommentar(er)
