publish settings and ETL load strategy

[adityashah27]

I would like to know if we dont publish zeros and UPDATE datawarehouse tables, will it work?
currently we dont publish zeros and we have TRUNCATE-INSERT method to datawarehouse tables.
but i wanted to know what others do? i think even if you dont publish zeros, UPDATE statements should work.

[sascha]

That's an interesting question. Even not beeing a warehouse expert I wouldn't go the UPDATE way as this should mess up the data is old and new values do not exactly match. Also I don't like to publish all zeros as this may slow down the publish process and will grow the database which isn't really neccessary.

Preferrably I would use the truncate method or just apend the published data to your warehouse with adding a getdate() function to distinguish between the different versions.

[adityashah27]

well TRUNC/DELETE-INSERT will grow your fact tables like anything. and also the downside is, you will loose all your data or it wont be available for reporting for some time, if ETL fails or partial works or publish fails etc.

i would expect cognos to provide some flags or settings which can make UPDATE to DW possible w/o publishing zeros.
yesterday i was browsing publish tables and saw some tables which could make UPDATE possible w/o zeros.
i need to prove it out and than will share here.

[mrobby]

Im sure that this is possible in some way.  The issue is that if a user zero's out a data item that previously had data in it then the warehouse update sql would not necessarily update that item to zero's.  Through a series of SQL statements it should be possible though.  You just need to know what was added, deleted, and changed.  Append, Delete, Update.

[sascha]

well TRUNC/DELETE-INSERT will grow your fact tables like anything.

Why that? The fact table should have nearly the same size after TRUNC/DELETE-INSERT.

and also the downside is, you will loose all your data or it wont be available for reporting for some time, if ETL fails or partial works or publish fails etc.

You could perform some test before the TRUNC/DELETE-INSERT to check if the publish was successful, e.g. SUM(values) <> 0