There might be something in Kimball. The tiny DW I just built has just one feed. I made a LOAD dimension which has the details of the file loaded. Yes, you probably should have an id in the fact table which corresponds to the load the load has the details of the date/time/package/filename etc.
If you delete some facts and reload, the old load dim is there, but no facts actually attached to it.
In your case, perhaps there aren't files, but there has to be some kind of batching from the OLTP tables, even if it's just a run number.
Too bad it's been a couple years since I was doing this every day. I found my Perl was rusty as well. But I ended up getting my Perl script to work on both Windows and Ubuntu unchanged, putting data in SQL Server or Azure via DBI::ODBC with only DSN changes. All the real work of the ETL was in pure T-SQL, though.
Can you force it to install all files on a specific drive? I have a c drive with very limited space so I am trying to install everything on a separate drive. I have changed the path in the install process to use the other drive but it still wants to install some files on the c drive.
Assign Audit Dimension Key As we describe in Chapter 9, an enterprise class data warehouse should include an audit key on each fact row. The audit key points to an audit dimension that describes the characteristics of the load, including timings and measures of data quality. The audit dimension and auditing subsystem can be complex, consist of multiple tables, and contain many measures of data quality. At its simplest, the audit subsystem should contain at least one audit dimension table that captures the most basic information. That simple auditing system design would insert one row into t…
@AaronBertrand I was referring to this stackoverflow.com/questions/13039528/… I would have just said "a query". One-lined-ness or not, somewhat arbitrary
@JNK I think Kimball identified 34 subsystems in the data warehouse.
@JNK What Kimball talks about there is basically exactly what we did at Capital One. Looking at my new one from scratch, I probably need to add some things about duration and rows loaded (vs rows expected to be loaded from the header, which I am storing).
So it's best to use SSIS for load control if you can, because it can do the logging through a separate connection. The only way to guarantee robust log entries within SQL is to make a CLR sproc that does the logging through a separate connection that doesn't participate in the transaction. IIRC CLR sprocs are verboten in your environment.
You can use SSIS to wrap the sprocs if you want to do it with sprocs. You're not obliged to use data flow tasks in SSIS packages.
I've done quite a lot of work with SSIS where all the hard work was done in stored procedures and SSIS was just used for load control and logging. It's quite common to do things that way.
You can build a simple CLR sproc that opens a separate connection to the database, records a log entry and commits the transaction without having to be involved with the transaction the ETL code is running in.
You can't do that from SQL in SQL Server.
Audit logging architecture 101:
i. Separate the act of recording the control totals (sums or counts) from managing the controls.
(i.e. record a count or sum at the appropriate point in the ETL, but don't try to actually implement the control in the same place)
Each control point can have a unique code.
Then you can implement the actual controls through metadata (control point A - Control point B should = 0)
These can be as complex as you want, but they should be separate from the ETL code.
Yes, the control will be a sum or a row count. For example, you might check the row count in a source data file and then the row count of what was inserted into a staging area.
If you have suspense areas where records that fail data validation checks, this is an even bigger win, because you can implement controls like (Source - suspense - destination should = 0)
The control DB can have the metadata to configure the controls. All the ETL has to do is to record a control point for that batch with the appropriate total. The control system can then implement the contols as expressions based on the totals in various appropriate control points.
Just economic theory. Their interests will be in conflict with yours if something goes wrong, so if you have no audit trail it's your word against theirs.
Their is nothing quite like a robust audit trail to shut down office politics.
I don't give a shit whether the business wants it. I f-ing well want it. Sod the business.
Remember, if you're going to get involved in a knife fight, it's best to bring a machine gun if at all possible.
Interestingly, I've just done a code review of an ETL process based on a framework built by a large consultancy firm that you've probably heard of, and the framework itself violates a really fundamental principle of good audit control design.
(i.e. measure what you're acutally auditing, rather than something that should be the same if the process works correctly).
@JNK The way it works is to measure the rows leaving a step in the data flow, rather than the rows inserted in the table after the process has finished running.
@RichardTheKiwi well, I meant two things: (1) it could be written without a cursor (e.g. "a query") and (2) quite a simple query at that (hence the one line). While yes you could write a very complex query on one line, most of us don't do that.
@RichardTheKiwi are we going to start coming into chat to nit-pick each other's comments? That's ok, I just want to be prepared.
@AaronBertrand No, not really looking to do that. Just that it strikes me as funny, the phrasing of that comment. I don't EVER write a query on a single line.. At the least, I would break SELECT and FROM into two lines.
