-
-
Just had a meeting with some of the guys in charge of the source systems we require for the DW.
The good news is that most of the systems have a sensible audit trail of entries for each transaction/action, so if a quantity on order decreases by 1, an entry -1 appears, or cancelling an order has a correspending transaction to offset the earlier one. Some systems have a 'last modified' column so we can see the data has changed, which we can also work with in SSIS to get the data.
Where it gets difficult is where these things are not pesent, or where the system does a hard delete of data and leaves no audit trail (although most decent OLTP systems do 'soft deletes', so the row is marked as deleted, others do not).
Fixing the source systems is obviously the best solution, but it is also expensive. We could possibly put triggers into some of the source systems (where the DBA will let us) to track changes, or we could do it all at the ETL end and rebuild the affected fact table on every cycle.
Not sure what way this is going to go but I think we are going to spend a lot of time getting the ETL right...
-
-
A little more about the project.....
Basically we're producing a BI system for a major retailer (who has both shops and an on-line presence). They already have a good idea of what their KPIs are, and how they want their end-user environment to look.
The data come from a variety of sources, but mainly :
-
On-line sales system (this system also handles mail order/telephone orders sales)
-
EPOS (the tills in the shops)
-
Finance (particularly Supply Chain data)
-
Various local databases (HR, etc) in Access, Excel and FoxPro
We have started on the ETL and I don't think it's going to be pretty. I'll expand in another post.....
The reporting environment must be flexible and have the ability for the users to add their own charts/reports. These are not techncial users. We are currently looking at various front-ends including Excel 2007, Strategy Companion and Proclarity, but I think we are going to end up writing something specific for them.
-
-
Hello everyone
My name is Tim Gibbons and I run the SQL Help consultancy in London.
Over the coming weeks and months I'll be sharing my experiences of building a data warehouse environment for a client. We'll be using SSIS and SSAS, together with a new web front end. Hopefully it'll be informative and (perhaps) insightful.
Keep watching for updates....