Windows Installer Cleanup Utility

As someone familiar with repairing installer issues manually, I was surprised to find they had issued a tool to help. Windows Installer Cleanup Utility crucialy zaps the files in the c:\windows\installer directory for the application you chose and removes registry entries so you can restart a failed installation.

1) Download & Install from http://support.microsoft.com/default.aspx?scid=kb;en-us;290301

2) Run from 'start > all programs > windows install cleanup'

3) Select the program you wish to cleanup.

Vista : Where is telnet?

For some bizarre reason, telnet is not installed by default on Vista. How to get it back >



1) Start > Control Panel > Programs and Features

2) Look on left hand task pane and click 'Turn Windows Features on or off'

3) Locate and enable 'Telnet Client' in the list that appears.

4) Click OK and wait (for what seems like an eon)

Proceed to command prompt and use telnet like in the good old days :)

Vista System Restore - Disk Space Hog

Sorting out Vista's rather heavy restore footprint...



1) Open Administrative Command prompt for dos prompt



2) vssadmin list shadowstorage [enter]



< Results show disk space reserved for Vista System Restore >



Given the default is 15% of drive capacity, you'll probably want to change this....



3) vssadmin Resize ShadowStorage /For=[drive letter]: /On=[drive letter]: /MaxSize=[space] [enter]

e.g. vssadmin Resize ShadowStorage /For=C: /On=C: /MaxSize=1GB



< Should get message saying success, or error if you get it wrong! >



4) use first command to confirm new setting >

vssadmin list shadowstorage [enter]

SQL 2005 : Moving objects between schemas!

ALTER SCHEMA newschema TRANSFER oldschema.tablename;

Lose a bloaty Transaction Log file

Shrinking Log files

-- find logical name of log file
SELECT * FROM sys.sysfiles

-- Change recovery model to SIMPLE.
ALTER DATABASE DATABASE_NAME SET RECOVERY SIMPLE

-- Shrink truncated log file to 1 MB.
DBCC SHRINKFILE (data_log, 1);
GO

-- Change recovery model to FULL.
ALTER DATABASE DATABASE_NAME SET RECOVERY FULL

!!! Take FULL backup as your chain of Transaction log backups will now be broken by the RECOVERY mode change.

Importing Trace files back into SQL

-- import first 3 files from trace series

select * into ServerSideTraceData
from fn_trace_gettable('C:\tracedata\2007-09-09.trc', 3);

-- use default flag to import all files.

select * into ServerSideTraceData
from fn_trace_gettable('C:\tracedata\2007-09-09.trc', default);

http://blogs.msdn.com/microsoft_dynamics_nav_sustained_engineering/archive/2007/10/19/diagnose-your-sql-server.aspx

Datawarehousing Notes

Fact

A fact could contain raw data or aggregated data with quantifiable measures.
Measures are numerical values defined by the granularity of the fact.

The ‘grain’ represents the unit at which information is stored and defines the type of the fact.

(1) Transactional
Data is stored at the transaction level, typically with little manipulation from the source OLTP system except cleanup and assigning of keys.

(2) Periodic Snapshot
Data is a picture of what happened at a moment in time i.e. more rows are added as time passes.

(3) Accumulating Snapshot
Data record is amended over time e.g. for an hourly sales fact, the data record would be updated during the hour real time transactions were occurring.

Fact tables have many rows and fewer columns when compared to dimensions.



A fact is uniquely identified by a set of foreign keys (linking to dimension tables). The primary key of a fact may be some or all of the foreign keys.

Fact Examples
Sales Transactions, Website Visitor Sessions


Dimension

A dimension is a table representing a category by which we organize facts.
They provide labeling, grouping and filtering functionality.

It is made up of attributes, the combinations of which represent a unique level of data within a dimension.

Dimension tables should have many attributes and typically less rows than related fact tables.






Dimensions are independent of one another.
To query multiple dimensions, you join through fact tables.
A dimension used by multiple facts is known as a Conformed Dimension.
There are 3 types of dimension, defined by the way data updates are handled >

Type 1
Data changes replace the existing row. No record of the previous attribute values exist.
‘Address’ is a good example as storing old address details serves little use.

Type 2
Data changes are added to the table as a new row. Therefore the history of attribute values are stored. ‘Interest Rate’ is an example where the history is important.

Type 3
Data changes overwrite the attribute values, but the old values are stored in additional columns in the dimension. Only 1 level of history can be retrieved therefore.


Dimension Examples
Calendar, Customer List, Product List




Monster Dimensions

An extremely large Dimension needs to be divided for the sake of performance.
Examples of this are >
1) splitting a calendar dimension into separate Date and Time dimensions on account of it’s shear size.
2) splitting a customer dimension out into 2 dimensions for mostly static and changeable data respectively.


Junk Dimensions

These are a way of storing commonly used flags (y/n) and indicators that have a low number of possible values. It is a tidier solution to store them centrally, rather than create clutter i.e numerous small dimensional tables.


Nulls

Nulls have no place in a data warehouse.
To cope for null values in dimensions, implement an ‘Unknown Value’ row in dimensions and ensure foreign keys in facts point to it.
-1 is a suggested primary key value for the Unknown data row.


Keys

Natural Key
A Natural key is a meaningful value.
For example, a National Insurance (NI) number uniquely identifies employees in the UK

Surrogate Key
This is a sequential number assigned to records.
Primary and Foreign keys in data warehouse designs should use surrogate values.

USP_TableScript

Procedure to generate script for an existing table ;

CREATE PROCEDURE USP_TableScript
@TableName varchar(100),
@NewTableName varchar(100),
@RetainNulls int,
@TableDef varchar(max) OUTPUT
AS          
/*
USP_TableScript
Paramters : 'originaltable' , 'newtablename', 0/1 - retain null settings, output variable
*/

IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @tableName)          
Begin          
declare @sql varchar(8000)          
declare @table varchar(100)          
declare @cols table (datatype varchar(50))        
insert into @cols values('bit')        
insert into @cols values('binary')        
insert into @cols values('bigint')        
insert into @cols values('int')        
insert into @cols values('float')        
insert into @cols values('datetime')        
insert into @cols values('text')        
insert into @cols values('image')        
insert into @cols values('uniqueidentifier')        
insert into @cols values('smalldatetime')        
insert into @cols values('tinyint')        
insert into @cols values('smallint')        
insert into @cols values('sql_variant')        
      
set @sql=''          
Select @sql=@sql+           
case when charindex('(',@sql,1)<=0 then '(' else '' end +Column_Name + ' ' +Data_Type +              case when Data_Type in (Select datatype from @cols) then '' else  '(' end+ case when Data_type in ('real','money','decimal','numeric')  then cast(isnull(numeric_precision,'') as varchar)+','+ case when Data_type in ('real','money','decimal','numeric') then cast(isnull(Numeric_Scale,'') as varchar) end  when Data_type in ('char','nvarchar','varchar','nchar') then cast(isnull(Character_Maximum_Length,'') as varchar)       else '' end+ case when Data_Type in (Select datatype from @cols)then '' else  ')' end+ case when (@RetainNulls = 1 AND Is_Nullable='NO') then ' NOT NULL,' + CHAR(10) else ' NULL,' + CHAR(10) end             from INFORMATION_SCHEMA.COLUMNS where Table_Name=@tableName               select @table=  'CREATE TABLE [' + @NewTableName + '] ' from INFORMATION_SCHEMA.COLUMNS where table_Name=@tableName             select @sql=@table + substring(@sql,1,len(@sql)-2) +' )'             select @TableDef = replace(@sql,'()','')          End    go 

Usage ;

DECLARE @SQLSTRING NVARCHAR(MAX)
EXEC USP_TableScript 'Contact','##temp_Contact', 0,@SQLSTRING OUTPUT
EXEC sp_executesql @SQLSTRING