September 10, 2014 at 10:57 am
Good Afternoon,
We've recently built a data warehouse using the Kimball methodology and are experiencing issues when trying to deploy our unknown values & other static data. We had been keeping these unknown value scripts in a folder where a batch file could be ran after deployment of the database objects to populate, unfortunately despite our best intentions this process isn't working, we've had a few instances where scripts hadn't been updated after changes to the dimension tables thus causing the scripts to fail. Normally these are picked up after some sense checking but on the odd occasion we've had to fix then re-apply the scripts and re-run any processing that had been done while these missing values were...well...missing.
I've had a few ideas of how I can achieve this;
- create a database of all unknown & static values and use this to deploy
- continue with the re-runnable scripts we have been using
- create a dynamic query to pick up all unknown values from our DEV environment and provide insert scripts to be ran after deployment
We have licences for the full Redgate Toolbelt and have been using SQL data compare to pick our < 0 dimension keys quite successfully
Do you have any idea of what would best practice be in this instance?
Thanks in advance
Chris
September 17, 2014 at 3:07 am
for anyone interested this is a part of the solution that i can share
DECLARE @t TABLE (id INT IDENTITY PRIMARY KEY,tbl_name VARCHAR(100))
DECLARE @id INT,
@tbl_name VARCHAR(100),
@sql VARCHAR(4000)
INSERT @t
SELECT name From sys.objects Where name like 'dim_%'
SELECT @tbl_name = tbl_name,@id = id FROM @t WHERE id =1
WHILE @id IS NOT NULL
BEGIN
SELECT @sql = 'select * from ' + @tbl_name + ' where ' + @tbl_name + '_key<=0'
PRINT @sql
EXEC (@sql)
SELECT @id +=1
SELECT @tbl_name = tbl_name,@id = id FROM @t WHERE id =(SELECT MIN(id) FROM @t WHERE id > @id)
IF @@ROWCOUNT = 0 BREAK
END
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply