sql server data warehouse:
Hi everyone,
I am currently developing an ETL process for a small data warehouse.
Because of its size, it doesn't warrant buying an ETL tool. My
question deals mainly with column and table mappings. Basically, we
have events that are piled in from various machines that track
financial data as well as user actions. Each one of these events has
an operational integer key. Depending on this key, the data gets
inserted into a certain fact table, for example, billpayment. I know I
can brute force this all with sql but I am trying to come up with a
design that makes this dynamic for the addition of future events. I
was thinking about making meta data tables that associate an event
with a certain fact table and then just run the insert. The problem is
that I then must create an sp for every fact table unless I use
dynamic sql. Events also need to be associated with type 2 dimension
tables. I was hoping to come up with a design that would map an
EventID with a dimension table column which is definitely doable not
not very dynamic. Our OLTP is relatively simple. All data from our
production system is inserted into a table with these columns:
AppEventID identitycolumn,
AppID int,
AppEventvalue varchar(500),
DateStamp datetime
additional columns .......
Basically the data gets pumped into this table and I have to direct
and type cast the AppEventvalues to a certain fact table based on an
eventID. Does anybody have any experience is creating a successful
dynamic staging application in this manner and would you recommend
doing it this way. Flexibility is a very important part of this and I
am trying to come up with the most efficient way. Any ideas would be
great. Thanks
BTW, I will be using T-SQL and DTS on SQL server 2000 for all
operations.
