#Idatabase vs code#
Its better to prevent these kind of behaviors from the final database since the final database might be a 24/7 data source for other BI applications like SSAS or SSRS and in these cases you dont want your database stuck doing things that are not part of the code business. which in turn will cause a lot of IO as well as auto growth and shrink behavior. This means there is going to be a lot of inserts and updates against this table. Staging data is transient in nature and behaves similar to a temp table. This isn't the design I started with, but moved to it as the need for greater clarity asserted itself. Some would say it's too much anyway, but the ETL is the most crucial part of the DW and you want the design to be as self-explanatory as possible. If you primarily draw from one source, this might be too much. It stores the row counts and exception information to create the feedback reports. The warehouse contains only data needed for the warehouseįinally, there is an ETL database that is broken down by schema to match the four sources.
These views are assigned to a schema that matches the cube name. After some experimentation, I think this works well overall. Kimball suggested this in his most recent book. Lately I've been transferring my fact and dimension tables into views. It is also broken out into the source schemas. It's also the start for combining data that is in multiple sources.įrom here it goes to the data warehouse database. The data is put into 3NF if that's necessary, which it often is. All the tables are truncated.įrom the Staging schema (or the Landing if the staging is not necessary for a particular process) all imports lead to a common staging database broken out by a different schema name for each source. The Staging schema is used to start any necessary conversions or data break outs (time into it's hour and minute components is common). You will need to a way to identify those if you do that. If a row is bad, the reason will be noted in the comments section. This is to prevent one bad row from bringing the process to a halt. It will be set to one once all the data has been checked.
Each landing table also has a bit field that defaults to 0 and a comments field that defaults to an empty string. The landing schema has the same table and column names as the source database and to the extent possible the same data type. Each source has it's own staging database divided into two schema: Landing and Staging.
#Idatabase vs full#
To me a full answer depends on how many sources you are pulling from to populate your data warehouse.
0 kommentar(er)
