Peddireddy's

TFS 2008: Message that solution not currently configured for integrated source control in Visual Studio

2011-08-11T11:42:00.000-07:00

http://support.microsoft.com/kb/969271

Try these steps to fix the problem:

1. Open Visual Studio 2008.
2. Open the local copy of the solution file using File > Open Project/Solution. (Note that no source control glyphs are visible.)
3. Choose the File -> Source Control menu in Visual Studio, and the "Go online" menu item. The source control glyphs now appeared.
4. Choose File> Source Control> Change Source Control and make sure the binds are correct. Unbind and rebind if they are not pointing to the TFS server.
5. Check out a file, modified it, and checked it back in to source control.
6. Exit Visual Studio and restart. (This time Visual Studio connect to Team Foundation Server automatically.)
7. Open the local copy of the solution file. The solution should be under source control now

Business Objects

2011-06-01T20:30:00.001-07:00

http://gerardnico.com/wiki/dat/bobj/universes#articles_related

Linked Server to SSAS

2011-04-29T13:51:00.000-07:00

EXEC sp_addlinkedserver
@server='CALXXXX',
@srvproduct='CALXXXX' ,
@provider='MSOLAP',
@datasrc='calCALXXXX',
@catalog='JournalEntryPosting'

/* Add login credentials for linked server */
EXEC sp_addlinkedsrvlogin
@rmtsrvname= 'CALXXXX',
@useself= 'False',
@locallogin= NULL,
@rmtuser= 'corp\nbkXXX',
@rmtpassword= 'rajXXX'

SSAS MetaData Info

2011-04-28T11:07:00.000-07:00

MDSCHEMA_CUBES Lists the cubes in an SSAS database
MDSCHEMA_MEASUREGROUPS Lists measure groups
MDSCHEMA_DIMENSIONS Lists dimensions
MDSCHEMA_LEVELS Dimension attributes
MDSCHEMA_MEASUREGROUP_DIMENSIONS Enumerates dimensions of measure groups
MDSCHEMA_MEASURES Lists measures

http://www.purplefrogsystems.com/blog/2010/09/olap-cube-documentation-in-ssrs-part-1/

http://tssasm.codeplex.com/

2011-02-06T16:36:00.003-08:00

1. Partition -- instead of select into ... use alter table switich partition
to move partition data.

alter table PartitionedTable switch partition partitionnr
to dumpPartitionedTable partition partitionnr

2. Data Compression -- Page level , Row level

3. DMV's

4. Dimensional modeling versus ER modeling -- Raphl Kimball

5. http://peddireddy999.blogspot.com/

2011-02-06T16:36:00.001-08:00

Data Page Level Compression on a Heap Table with Statistics

2011-01-14T10:34:00.000-08:00

SELECT * FROM sys.dm_db_index_physical_stats(DB_ID(N'DBArchive'),object_id('FactMTDPrior_Dec2010'), 0, NULL , 'DETAILED');

Compression Whole Table
USE DBArchive
go
ALTER TABLE FactMTDPrior_Dec2010
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO

Compressing Specific Partition
ALTER TABLE PartitionTable1
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION = NONE) ;
GO

Important values are taken into consideration

Pagecount Min Max Avg Compression page count
Record Size
b4e compression 1945864 171 174 171.987 0
after compression 777966 29 133 63.759 777959
after comp & shrinking 800030 29 134 65.574 759933

Before Compression
dbarchive.mdf 15,586,304 kb
dbarchive.ldf 353,216 kb

After Compression
dbarchive.mdf 21,815,296 kb
dbarchive.ldf 353,216 kb

After Shrinking the compressed database
dbarchive.mdf 6,402,560 kb
dbarchive.ldf 1,024 kb

1. (Pagecount before compression / Pagecount afer page compression and shrinking)
(1945864.00 / 800030.00) = 2.43223879104
Conclusion >>> We can Say PageCount was reduced to 60% after applying Page Level Compression and Shrinking on the Heap Table (which does not have any indexes)

2. Calculated the mdf file size after pagelevel compression and shrinking.mdf file reduced by 60% on the Heap Table (which does not have any indexes)
select 6402560 * 2.43223879104 = 15572554.79 (almost equal to 15,586,304 kb)

Partition Number for a specific Partition Table

2011-01-07T15:45:00.001-08:00

select PartitionNbr = $Partition.PartitionFunction('01/01/2011')

Merge / Split Partition Script

2011-01-07T15:43:00.000-08:00

DECLARE @DateDt Datetime
, @SQLTXT VARCHAR(256)
SELECT @DateDt = '2011-12-26T00:00:00.000'

while @DateDt < '01/01/2012'
begin
ALTER PARTITION SCHEME SCHEME_SnapShotDt NEXT USED FG_SnapShotDt
ALTER PARTITION FUNCTION FUNCTION_SnapShotDt() SPLIT RANGE (@DateDt);
--ALTER PARTITION FUNCTION FUNCTION_SnapShotDt() MERGE RANGE (@DateDt);
SELECT @DateDt = @DateDt + 1
SELECT @DateDt
end

bcp IN and OUT

2010-12-01T12:07:00.000-08:00

EXEC xp_cmdshell 'bcp "select * from Reporting..FactMTDPrior with (nolock) where DataDt = ''2010-10-31'' " queryout "\\server\k$\Test\bcptest2.txt" -T -Sserver -c'
GO

GO

EXEC xp_cmdshell 'bcp Reporting..bcpTest in "\\server\k$\Test\bcptest2.txt" -T -Sserver -c'
go

SQL Partitions

2010-10-12T16:25:00.000-07:00

/** Table Name + Schema Name + Function Details. **/
SELECT OBJECT_NAME(SI.object_id) AS PartitionedTable
, DS.name AS PartitionScheme
, PF.name AS PartitionFunction
FROM sys.indexes AS SI
JOIN sys.data_spaces AS DS
ON DS.data_space_id = SI.data_space_id
JOIN sys.partition_schemes AS PS
ON PS.data_space_id = SI.data_space_id
JOIN sys.partition_functions AS PF
ON PF.function_id = PS.function_id
WHERE DS.type = 'PS'
AND OBJECTPROPERTYEX(SI.object_id, 'BaseType') = 'U'
AND SI.index_id IN(0,1);

GO

/** Function Insight Details. **/
SELECT
PF.function_id
, PF.name
, PF.fanout AS NumPartitions
, CASE WHEN PF.boundary_value_on_right = 0
THEN 'LEFT' ELSE 'RIGHT' END AS RangeType
, PP.parameter_id
, CASE WHEN PP.system_type_id = PP.user_type_id
THEN T1.name ELSE T2.name END AS ParameterDataType
, PRV.boundary_id
, PRV.value
, CASE WHEN PF.boundary_value_on_right = 0
THEN PRV.boundary_id ELSE PRV.boundary_id + 1 END AS PartitionNumber
FROM sys.partition_functions AS PF
JOIN sys.partition_parameters AS PP
ON PF.function_id = PP.function_id
JOIN sys.types AS T1
ON T1.system_type_id = PP.system_type_id
JOIN sys.types AS T2
ON T2.user_type_id= PP.user_type_id
JOIN sys.partition_range_values AS PRV
ON PP.function_id = PRV.function_id
AND PP.parameter_id = PRV.parameter_id
order by PF.name,PRV.Value

go

/** switch out the parition **/

alter table switch partition @partitionnr to partition @partitionnr

go

/** Adding a Partition on the existing Table **/

CREATE CLUSTERED INDEX [ix_DataDt_tab1] ON [dbo].[tab1]([DataDt] ASC)ON [SCHEME_Name]([PartitionKey])
CREATE NONCLUSTERED INDEX [ix_LoanId_tab1] ON [dbo].[tab1]([LoanId] ASC) ON [SCHEME_Name]([PartitionKey])

SQL SERVER 2005 Alert System

2010-07-06T14:54:00.000-07:00

Declare @i int
select @i = count(*) from dbname.dbo.tab1
if @i = 0
begin
exec msdb.dbo.sp_send_dbmail @recipients = 'vpeddireddy@alert.com',@subject = 'test email'
end

Understanding SQL 2005 DMV and DMF

2010-06-24T14:02:00.000-07:00

sys.dm_exec_query_stats -

Returns performance statistics for cached query plans. This contains one row per query plan so if a stored procedure or batch contains two SELECT statements you may get two rows here.

sys.dm_exec_sql_text -

Returns the text of the sql statement based on the SQL handle

sys.dm_exec_query_plan -

Returns the showplan in XML format for a batch or module based on the plan handle.

dm_db_index_usage_stats –

displays how often each index is used, show Read Seeks, Read Scans, Write Seeks and Write Scans

dm_db_index_physical_stats –

replace DBCC SHOWCONTIG used to check index fragmentation.

dm_db_index_operational_stats –

use to check for index contention and blocking

dm_os_wait_stats –

Used to determine the types of waits (signal or resource that are occurring)

dm_os_waiting_tasks –

Used to help identify blocking

dm_tran_locks –

Used to help indentify blocking

dm_os_performance_counters –

Query Access to SQL Server Performance counters

List of SQL 2005 DMVs

2010-06-23T14:35:00.000-07:00

-- OS Related
select * from sys.system_objects
where name like 'DM_OS%'
GO

-- Exec Related
select * from sys.system_objects
where name like 'DM_exec%'
GO

-- Transaction Related
select * from sys.system_objects
where name like 'DM_tran%'
GO

-- Replication related
select * from sys.system_objects
where name like 'DM_repl%'
GO

-- IO Releated
select * from sys.system_objects
where name like 'DM_IO%'
GO

-- clr Releated
select * from sys.system_objects
where name like 'DM_CLR%'
GO

-- db index related

select * from sys.system_objects
where name like 'DM_db%'
GO

-- broker related

select * from sys.system_objects
where name like 'DM_broker%'
GO

-- fts related
select * from sys.system_objects
where name like 'DM_fts%'
GO

-- qn related
select * from sys.system_objects
where name like 'DM_qn%'
GO

SQL 2005

2010-06-22T10:43:00.000-07:00

DSQuery

2010-06-02T09:21:00.000-07:00

Following command gives OU details of a server
c:\winnt\system32>dsquery computer -name xxxxxxxxx

SQL 2005 Transaction Logs

2010-05-26T17:24:00.000-07:00

The transaction log is really a circular file—as long as the log records at the start of the transaction log have been truncated (or cleared).

A log record is no longer needed in the transaction log if all of the following are true:
 The transaction of which it is part has committed.
 The database pages it changed have all been written to disk by a checkpoint.
 The log record is not needed for a backup (full, differential, or log).
 The log record is not needed for any feature that reads the log (such as database mirroring or replication).

A log record that is still needed is called active, and a VLF that has at least one active log record is also called active. Every so often, the transaction log is checked to see whether all the log records in a full VLF are active or not; if they are all inactive, the VLF is marked as truncated (meaning the VLF can be overwritten once the transaction log wraps). When a VLF is truncated, it is not overwritten or zeroed in any way—it is just marked as truncated and can then be reused.
This process is called log truncation—not to be confused with actually shrinking the size of the transaction log. Log truncation never changes the physical size of the transaction log—only which portions of the transaction log are active or not.

The check whether log truncation can take place under either of the following circumstances:

 When a checkpoint occurs in the SIMPLE recovery model or in other recovery models when a full backup has never been taken. (This implies that a database will remain in a pseudo-SIMPLE recovery model after being switched out of SIMPLE until a full database backup occurs.)
 When a log backup completes.

There are two common issues that can prevent log truncation:
 A long-running active transaction. The entire transaction log since the first log record from the oldest active transaction can never be truncated until that transaction commits or aborts.
 Switching to the FULL recovery model, taking a full backup, and then never taking any log backups. The entire transaction log will remain active, waiting to be backed up by a log backup.

Changing Database COLLATION

2010-05-20T16:54:00.000-07:00

1. Litespeed uninstall.

2. set Password and collation execute the following.

D:\Installs\SQL2005ENTSELX64dvd\Servers>start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=******* SQLCOLLATION=SQL_Latin1_General_CP1_CI_AS

3. APPLY CU3

SQL 2005 execute permissions

2010-05-07T18:43:00.000-07:00

SQL2005

SQL Server 2005 improves on the current situation by making the EXECUTE permission grantable at the database scope. This means that we can issue a statement like the example below and this will GRANT execute permissions on all existing stored procedures and scalar functions AND all subsequently created ones. Thus it acts very much like the current fixed database roles such as db_datareader

/* CREATE A NEW ROLE */
CREATE ROLE db_executor

/* GRANT EXECUTE TO THE ROLE */
GRANT EXECUTE TO db_executor

Good Website

2010-05-07T14:55:00.000-07:00

http://www.jenunderwood.com/resources.htm

Not able to Add Windows Global Group to SQL SERVER 2005

2010-05-07T14:47:00.000-07:00

Problem : Not able to Add Windows Global Group to SQL SERVER 2005
Solution : Make sure that net logon service is running

Locks,Blocks and Latches.

2010-03-25T17:28:00.000-07:00

Locks ensure logical consistency
o Example: A row cannot exist in table A unless a corresponding row exists in table B (a foreign-key constraint). When inserting a row into table B,
a shared lock will be obtained on the corresponding row in table B until the insert in table A completes, to ensure that nobody deletes the
corresponding row from table B and thereby ensure that the foreign key constraint (a logical consistency rule) is enforced when the transaction completes.

Latches ensure physical consistency
o To say that data is "physically consistent" is to state that the structure of the data on disk or in memory follows the defined patterns
that SQL Server expects (i.e. is not corrupt) and that the right data is stored in the right place (e.g. the value for column Y of row X is not
inadvertently stored in column W of row Z).

Sp_lock shows all outstanding locks, the sessions owning those locks, the resource being locked, the type of lock, and the state of the lock (granted or waiting).
Sys.dm_tran_locks is a data management view in SQL Server 2005 and SQL Server 2008 that shows additional detail about locks held by transactions in SQL Server.
When a process is blocked because it is waiting on a lock, you will see summary detail about the lock it is awaiting in sys.dm_os_waiting_tasks. And sys.dm_os_wait_stats provides a high level summary of time spent waiting on the various types of locks (regardless of the resource being locked).

-------------------------------------------------------------

-- Code and scripts from Chapter 6:
-- Concurrency Problems

-- You can detect table locks at a given point in time using the
-- sys.dm_tran_locks DMV. The following query shows an example:

SELECT
request_session_id,
resource_type,
DB_NAME(resource_database_id) AS DatabaseName,
OBJECT_NAME(resource_associated_entity_id) AS TableName,
request_mode,
request_type,
request_status
FROM sys.dm_tran_locks AS TL
JOIN sys.all_objects AS AO
ON TL.resource_associated_entity_id = AO.object_id
WHERE request_type = 'LOCK'
AND request_status = 'GRANT'
AND request_mode IN ('X','S')
AND AO.type = 'U'
AND resource_type = 'OBJECT'
AND TL.resource_database_id = DB_ID();

-- For example, the following code will prevent lock escalation
-- on the table for one hour.

BEGIN TRAN
SELECT *
FROM Sales.SalesOrderDetail WITH (UPDLOCK, HOLDLOCK)
WHERE 1=0;
WAITFOR DELAY '1:00:00';
COMMIT TRAN

-- For example, the following query shows just those waits that
-- have been occurring for more than 5 seconds:

SELECT
WT.session_id AS waiting_session_id,
WT.waiting_task_address,
WT.wait_duration_ms,
WT.wait_type,
WT.blocking_session_id,
WT.resource_description
FROM sys.dm_os_waiting_tasks AS WT
WHERE WT.wait_duration_ms > 5000;

-- The following query returns a subset of the columns and shows
-- all those locks that are in a WAIT state:

SELECT
TL.resource_type,
DB_NAME(TL.resource_database_id) as DatabaseName,
TL.resource_associated_entity_id,
TL.request_session_id,
TL.request_mode,
TL.request_status
FROM sys.dm_tran_locks AS TL
WHERE TL.request_status = 'WAIT'
ORDER BY DatabaseName, TL.request_session_id ASC;

-- The sys.dm_tran_locks DMV uses both the
-- resource_associated_entity_id along with the
-- resource_description to identify the locked resource, so we
-- just need to use that as the method for joining the view with
-- itself to pick out each waiting and granted lock for each
-- given resource. The following query shows how you can do it:

SELECT
TL1.resource_type,
DB_NAME(TL1.resource_database_id) AS DatabaseName,
TL1.resource_associated_entity_id,
TL1.request_session_id,
TL1.request_mode,
TL1.request_status
FROM sys.dm_tran_locks as TL1
JOIN sys.dm_tran_locks as TL2
ON TL1.resource_associated_entity_id = TL2.resource_associated_entity_id
AND TL1.request_status <> TL2.request_status
AND (TL1.resource_description = TL2.resource_description
OR (TL1.resource_description IS NULL
AND TL2.resource_description IS NULL))
ORDER BY TL1.request_status ASC;

-- You might want to also query the view and return the actual
-- object represented by the resource_associated_entity_id,
-- whether it be a RID, key, page or table.

SELECT
TL1.resource_type,
DB_NAME(TL1.resource_database_id) AS DatabaseName,
CASE TL1.resource_type
WHEN 'OBJECT'
THEN OBJECT_NAME(TL1.resource_associated_entity_id,
TL1.resource_database_id)
WHEN 'DATABASE' THEN 'DATABASE'
ELSE
CASE
WHEN TL1.resource_database_id = DB_ID() THEN
(SELECT OBJECT_NAME
(object_id, TL1.resource_database_id)
FROM sys.partitions
WHERE hobt_id = TL1.resource_associated_entity_id)
ELSE NULL
END
END AS ObjectName,
TL1.resource_description,
TL1.request_session_id,
TL1.request_mode,
TL1.request_status
FROM sys.dm_tran_locks AS TL1
JOIN sys.dm_tran_locks AS TL2
ON TL1.resource_associated_entity_id =
TL2.resource_associated_entity_id
WHERE TL1.request_status <> TL2.request_status
AND (TL1.resource_description = TL2.resource_description
OR (TL1.resource_description IS NULL
AND TL2.resource_description IS NULL))
ORDER BY TL1.resource_database_id,
TL1.resource_associated_entity_id,
TL1.request_status ASC;

-- First you use the sp_configure command to set the advanced
-- option blocked process threshold to a value, let's say 60
-- seconds:

EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
EXEC sp_configure 'blocked process threshold', 60;
RECONFIGURE;

--You can join on the sys.dm_tran_locks. Here's an example:

SELECT
WT.session_id AS waiting_session_id,
DB_NAME(TL.resource_database_id) AS DatabaseName,
WT.wait_duration_ms,
WT.waiting_task_address,
TL.request_mode,
TL.resource_type,
TL.resource_associated_entity_id,
TL.resource_description AS lock_resource_description,
WT.wait_type,
WT.blocking_session_id,
WT.resource_description AS blocking_resource_description
FROM sys.dm_os_waiting_tasks AS WT
JOIN sys.dm_tran_locks AS TL
ON WT.resource_address = TL.lock_owner_address
WHERE WT.wait_duration_ms > 5000
AND WT.session_id > 50;

-- By adding a subquery that joins the sys.dm_exec_requests DMV
-- and the sys.dm_exec_sql_text() DMF, correlating back to the
-- joining on the waiting task's session_id, you can extract the
-- query text. Here's an example, adapted from a similar query
-- in A Troubleshooting Methodology, Chapter 1 of this volume.

SELECT
WT.session_id AS waiting_session_id,
DB_NAME(TL.resource_database_id) AS DatabaseName,
WT.wait_duration_ms,
WT.waiting_task_address,
TL.request_mode,
(SELECT SUBSTRING(ST.text, (ER.statement_start_offset/2) + 1,
((CASE ER.statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE ER.statement_end_offset
END - ER.statement_start_offset)/2) + 1)
FROM sys.dm_exec_requests AS ER
CROSS APPLY sys.dm_exec_sql_text(ER.sql_handle) AS ST
WHERE ER.session_id = TL.request_session_id)
AS waiting_query_text,
TL.resource_type,
TL.resource_associated_entity_id,
WT.wait_type,
WT.blocking_session_id,
WT.resource_description AS blocking_resource_description,
CASE
WHEN WT.blocking_session_id > 0 THEN
(SELECT ST2.text
FROM sys.sysprocesses AS SP
CROSS APPLY sys.dm_exec_sql_text(SP.sql_handle) AS ST2
WHERE SP.spid = WT.blocking_session_id)
ELSE NULL
END AS blocking_query_text
FROM sys.dm_os_waiting_tasks AS WT
JOIN sys.dm_tran_locks AS TL
ON WT.resource_address = TL.lock_owner_address
WHERE WT.wait_duration_ms > 5000
AND WT.session_id > 50;

-- Here's a skeletal example of how you can rewrite your code to
-- retry the transaction using Transact-SQL:

DECLARE @Tries tinyint, @Error int;
SET @Tries = 1;
WHILE @Tries <= 3
BEGIN
BEGIN TRANSACTION;
BEGIN TRY
--


      IF XACT_STATE() = 1 COMMIT;
        BREAK;
      END TRY
      BEGIN CATCH
        SET @Error = ERROR_NUMBER();
        IF @Error = 1205
        BEGIN
          IF XACT_STATE() = -1 ROLLBACK;
        END
        SET @Tries = @Tries + 1;
        CONTINUE;
      END CATCH;
    END;




-- Assume you have two tables, a header table called H1 and a 
-- detail table called D1, and you have declared a foreign key 
-- from D1 to H1 referencing H1's primary key. Here's the code 
-- to create that scenario:

USE Scratch;
GO
ALTER DATABASE Scratch
  SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE Scratch
  SET READ_COMMITTED_SNAPSHOT OFF;
GO
IF OBJECT_ID('dbo.D1') IS NOT NULL DROP TABLE D1;
IF OBJECT_ID('dbo.H1') IS NOT NULL DROP TABLE H1;
GO
CREATE TABLE H1 (H1ID int NOT NULL PRIMARY KEY, H1Name varchar(10));
CREATE TABLE D1 (D1ID int NOT NULL PRIMARY KEY, H1ID int);
GO
ALTER TABLE D1 ADD CONSTRAINT FK_D1ID_D1
FOREIGN KEY (H1ID) REFERENCES H1(H1ID);
GO
INSERT H1 VALUES (1,'Test');
GO





-- Now let's change the database to READ COMMITTED SNAPSHOT 
-- (sometimes abbreviated to RCSI):

ALTER DATABASE Scratch
  SET READ_COMMITTED_SNAPSHOT ON;

-- You'll have to make sure for a moment that you only have one 
-- connection to the database so that this change will take 
-- affect. You also need to reinsert the row into the H1 table.

INSERT H1 VALUES (1,'Test');




-- To reproduce this, alter the database to disable RCSI and 
-- enable SNAPSHOT isolation (momentarily taking all other 
-- connections out of the database):

ALTER DATABASE Scratch
  SET READ_COMMITTED_SNAPSHOT OFF;
ALTER DATABASE Scratch
  SET ALLOW_SNAPSHOT_ISOLATION ON;


-- Now reinsert the row into the H1 table one more time.

INSERT H1 VALUES (1,'Test');




-- For this example, you'll need to recreate the same header and 
-- detail tables but this time create a simple trigger that will 
-- ensure referential integrity for single-row INSERTs. 
-- (Using foreign key constraints is the recommended way to 
-- enforce referential integrity, but many databases still use 
-- triggers.)

USE Scratch;
GO
IF OBJECT_ID('dbo.D1') IS NOT NULL DROP TABLE D1;
IF OBJECT_ID('dbo.H1') IS NOT NULL DROP TABLE H1;
CREATE TABLE H1 (H1ID int NOT NULL PRIMARY KEY, H1Name varchar(10));
CREATE TABLE D1 (D1ID int NOT NULL PRIMARY KEY, H1ID int);
GO
CREATE TRIGGER tr_D1_H1
  ON D1
  FOR INSERT
AS
BEGIN
-- Single-row inserts only
  IF (SELECT H1ID FROM inserted) NOT IN (SELECT H1ID FROM H1)
  BEGIN
    PRINT 'Rolling back insert'
    ROLLBACK
  END
END;
GO
INSERT H1 VALUES (1, 'Test')
ALTER DATABASE Scratch SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE Scratch SET READ_COMMITTED_SNAPSHOT OFF;
GO




-- Remove all other users in the database and issue the following
--  commands, putting the database into the 
-- READ COMMITTED SNAPSHOT state:

ALTER DATABASE Scratch SET ALLOW_SNAPSHOT_ISOLATION OFF;
ALTER DATABASE Scratch SET READ_COMMITTED_SNAPSHOT ON;
GO




-- To clean up, empty out the D1 detail table and reinsert the 
-- header row into H1:

DELETE FROM D1;
INSERT H1 VALUES (1,'Test');





-- For this example, remove all other users in the database, set 
-- READ COMMITTED SNAPSHOT to OFF in the database, set the 
-- database to allow SNAPSHOT isolation:

ALTER DATABASE Scratch SET READ_COMMITTED_SNAPSHOT OFF;
ALTER DATABASE Scratch SET ALLOW_SNAPSHOT_ISOLATION ON;
GO




-- For example, you could rewrite the initial RI trigger as 
-- follows, using the READCOMMITTEDLOCK hint:

CREATE TRIGGER tr_D1_H1
  ON D1
  FOR INSERT
AS
BEGIN
  -- Single-row inserts only
  IF (SELECT H1ID FROM inserted) NOT IN
        (SELECT H1ID FROM H1 WITH (READCOMMITTEDLOCK))
  BEGIN
    PRINT 'Rolling back insert'
    ROLLBACK
  END
END;




-- In all the examples so far of lost updates, you can apply the 
-- UPDLOCK hint to the SELECT statement that reads the value 
-- from Sales.SalesOrderDetail:

SET @OrderQty =
 (SELECT OrderQty
  FROM Sales.SalesOrderDetail WITH (UPDLOCK)
  WHERE SalesOrderID = 43659
    AND SalesOrderDetailID = 1);



--Finally the UPDLOCK hint will also prevent the lost update 
-- shown in Table 6-12. Just add the UPDLOCK hint to the SELECT 
-- statement in step 2 as follows:

SELECT OrderQty
FROM Sales.SalesOrderDetail WITH (UPDLOCK)
WHERE SalesOrderID = 43659
  AND SalesOrderDetailID = 1;




-- Using the example from the transaction shown in Table 6-11,
-- you could rewrite the SNAPSHOT transaction as:

SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
WAITFOR TIME '00:15:00.0';

DECLARE @Tries tinyint, @Error int;
DECLARE @OrderQty smallint;
SET @Tries = 1;

WHILE @Tries <= 3
BEGIN
  BEGIN TRANSACTION
    BEGIN TRY
      SELECT OrderQty
      FROM Sales.SalesOrderDetail
      WHERE SalesOrderID = 43659
       AND SalesOrderDetailID = 1;
     WAITFOR DELAY '00:00:00.2';
     UPDATE Sales.SalesOrderDetail
       SET OrderQty = OrderQty + 2
     WHERE SalesOrderID = 43659
       AND SalesOrderDetailID = 1;
     IF XACT_STATE() = 1 COMMIT;
     BREAK;
   END TRY;
   BEGIN CATCH
     SET @Error = ERROR_NUMBER();
     IF @Error = 3960
       BEGIN
         PRINT '3960 encountered';
         IF XACT_STATE() = -1 ROLLBACK;
       END
     SET @Tries = @Tries + 1;
     CONTINUE
   END CATCH;
 END;




-- For monitoring Full SNAPSHOT isolation level transactions in 
-- particular, you can use sys.dm_tran_active_snapshot_database_transactions. 
-- The following query shows one example:

SELECT
  transaction_id,
  session_id,
  transaction_sequence_num,
  is_snapshot,
  max_version_chain_traversed,
  elapsed_time_seconds
FROM sys.dm_tran_active_snapshot_database_transactions
ORDER BY elapsed_time_seconds DESC;




-- The following query shows one way of using this DMV to inspect
-- the free space in tempdb:

SELECT
  SUM(user_object_reserved_page_count) * 8.192 AS UserObjectsKB,
  SUM(internal_object_reserved_page_count) * 8.192 AS InternalObjectsKB,
  SUM(version_store_reserved_page_count) * 8.192 AS VersionStoreKB,
  SUM(unallocated_extent_page_count) * 8.192 AS FreeSpaceKB
FROM sys.dm_db_file_space_usage;



Maintainance Plan -- Exception from HRESULT: 0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)
2010-03-08T13:31:00.000-08:00
When editing a maintenance plan (that I am newly creating) I got:
Exception from HRESULT:  0xC0010014 (Microsoft.SqlServer.DTSRuntimeWrap)
The issue is the client tools installed in %ProgramFiles(x86)% instead of %ProgramFiles%  but instead of reinstalling client tools, I ran:

%windir%\syswow64\regsvr32 "D:\MSSQL (x86)\90\DTS\Binn\dts.dll" 
which resolved the issue.


SQL SERVER 2005 Permissions to View Stored Procedures
2010-02-17T19:26:00.000-08:00
To turn on this feature across the board for all databases and all users you can issue the following statement:

USE master 
GO 
GRANT VIEW ANY DEFINITION TO PUBLIC  

To turn on this feature across the board for all databases for user "User1" you can issue the following statement:

USE master 
GO 
GRANT VIEW ANY DEFINITION TO User1  

To turn this feature on for a database and for all users that have public access you can issue the following:

USE AdventureWorks 
GO 
GRANT VIEW Definition TO PUBLIC  

If you want to grant access to only user "User1" of the database you can do the following:

USE AdventureWorks 
GO 
GRANT VIEW Definition TO User1  

To turn off this functionality you would issue the REVOKE command such as one of the following:

USE master  
GO  
REVOKE VIEW ANY DEFINITION TO User1  

-- or 

USE AdventureWorks  
GO  
REVOKE VIEW Definition TO User1  
 

If you want to see which users have this access you can issue the following in the database.

USE AdventureWorks 
GO 
sp_helprotect


Linked Server >>> Unable to begin a distributed transaction
2010-02-11T17:18:00.000-08:00
Error Message:

OLE DB provider "SQLNCLI" for linked server "agosqltqtest01_link" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 1
The operation could not be performed because OLE DB provider "SQLNCLI" for linked server "test01_link" was unable to begin a distributed transaction.



Resolution: Change to No Authetication Required as follows 



















Multiple SSIS SQL Server Instances
2010-01-04T16:31:00.000-08:00

Multiple SSIS installs of SQL server on the same machine.

There is a configuration change that needs to be made to the SSIS file MsDtsSrvr.exe. You will need to add an entry for each of the installed instances on the server so that it can look at all of the msdb’s available. Once you have made the changes, you will need to stop and restart the ssis service.




SQL 2005 Blocking Issues
2009-12-25T18:38:00.000-08:00
sp_who2 active
sp_blocked
dbcc inputbuffer(74)


Coping SSIS Jobs to different Server
2009-12-24T18:17:00.000-08:00
SELECT 'dtutil /COPY SQL;' + NAME + ' /SourceS servera /SQL ' + NAME + ' /DestS serverb
FROM msdb.dbo.sysdtspackages90


Disable and Enable Jobs
2009-12-24T13:17:00.000-08:00
set nocount on
set quoted_identifier off
select "exec sp_update_job @job_name = '" + name + "', @enabled = 0 " + "
" + "" from sysjobs where enabled=1 order by 1


Understanding "login failed" (Error 18456) error messages in SQL Server 2005
2009-10-27T17:27:00.000-07:00




List of users in a Windows Global group
2009-10-06T14:31:00.001-07:00

C:\>net group [GroupName] /domain

ex: NET GROUP DBAGroup /DOMAIN


SQL 2005 - Query to retrive SQL Agent Jobs scheduled time.
2009-10-06T13:58:00.000-07:00
select j.name,sc.active_start_time,active_end_time,*
from sysjobs j
inner join dbo.sysjobschedules jsc
on j.job_id = jsc.job_id
inner join dbo.sysschedules sc
on jsc.schedule_id = sc.schedule_id
where j.name like '%ssis%'


MDAC - SNAC components
2009-07-02T11:23:00.000-07:00
MDAC is a two side component. i.e; on infrastructure (windows) side and driver (sqlserver) side. MDAC is replaced by SNAC. SNAC is on the "drivers" side.

In a nutshell, SQL Native Client is a stand alone data access Application Programming Interface (API) that is used for both OLE DB and ODBC. It combines the SQL OLE DB provider and the SQL ODBC driver into one native dynamic link library (DLL) while also providing new functionality above and beyond that supplied by the Microsoft Data Access Components (MDAC). SQL Native Client can be used to create new applications or enhance existing applications that need to take advantage of new SQL Server 2005 features such as Multiple Active Result Sets (MARS), User-Defined Types (UDT), and XML data type support.

MDAC now ships as a component of the Windows operating system and as such there are a number of setup, redistribution, and deployment issues that have occurred as a result of this. you have developed an application based on the latest MDAC release only to discover that when you deploy it, the users in your organization do not have the latest MDAC release so your app won’t function correctly. By wrapping the OLE DB and ODBC technologies into a single library, we are able to avoid these issues by making a clean break from MDAC so that you can effectively deploy SQL Native Client as needed, without concern about if it will “play nicely” with other versions of MDAC.

Data Access Technology Road Map
http://msdn.microsoft.com/en-us/library/ms810810.aspx


SQL Server Consolidation
2009-06-30T18:03:00.000-07:00
What is SQL Server Consolidation
Reducing the Number of Physical Sql Servers and also Sql Server Instances.

What are Business Drivers for Sql Server Consolidations.
1. Reduction of Total Cost of Ownership. (Maintaining Personal / Patch Management / Data centre Costs/ Energy Consumption (GreenIT)).
2. Best practices like database maintaince, back up,high availbility,DR strategies etc.

-----------------------------------------
Features of SQL SERVER 2005 for Consolidation

1. Large Scale Performance (Scale UP)
2. non-uniform memory access (NUMA) architecture
3. Work Load Governerance.
4. Multiple applications connecting through two different IP Address.

-----------------------------------------

Microsoft Consolidation Options

1. Hardware Partioning
                                High Throughput Applications
                                Complete Isolation
                                 Reboot after resizing
                                 

2.  Resource Management
                             Medium-High Throughput Applications
                              Manage Resource Usuage
                               Same Operating system level                               

3. Virtualization
                             Medium-Low throughput
                             Isolating legac applications
                             Complex management
                             Limed scale-up


OLAP vs OLTP
2009-06-16T15:47:00.000-07:00



Data Quality Process
2009-06-14T11:16:00.000-07:00
Profiling (Identifying data quality issues).
Generalized Cleansing (tests to meet business rules).
Parsing and standardization (restructing data into a common format).
Matching (finding unique identifiers and performint de-duplication).
Enrichment (phone and email validation)
Monitoring (checking conformance to data quality requirements).

----------------------------------------------------------------------------------------

The construction of data warehouses involves data cleaning, data integration, and data transformation.

OLAP operations such as roll-up, drill-down, slicing, and dicing.

A data warehouse is a subject-oriented, integrated, time-variant, and nonvolatile collection of data in support of management’s decision making process

-----------------------------------------------------------------------------------------


SSRS 2008 Tablix -- Repeating Column Header for Tablix (Table)
2009-03-12T14:17:00.000-07:00

Go to the Tablix advanced mode, select the static member from the Row Groups and set the properties as shown in the figure



Relational VS MultiDimensional (OLAP) Databases
2009-01-08T16:16:00.000-08:00
The key difference between OLAP dimensions and simple relational dimensions is the central role played by hierarchies in OLAP implementations.An OLAP dimension is strongly structured around its hierarchies, and the metadata of a cube definition includes the hierarchical levels.
This is one of the great strengths of an OLAP implementation.

OLAP is a sibling of dimensional models in the relational database, with intelligence about relationships and calculations defined on the server, that enable faster query performance and more interesting analytics from a broad range of query tools.

The recommended architecture for most purposes feeds the OLAP server from a dimensional data warehouse in the relational DBMS.

Overall the following points are important in OLAP.
1. Meta Data (like semi additive and non additive info),
2. Calculations Defined (Pre-aggregated data) and
3. Analaytical Functions defined on the Server

Example explaining the advantage of  Hierarichies in OLAP
A query such as total sales for Q1 2002 is simple to formulate and should return from an OLAP server nearly instantaneously. But  the user who wants total sales for an arbitrary period such as January 3 through March 12, 2002, for which no predefined hierarchy exists.

Advantages are as follows
1.  It provides an intuitive user interface for browsing data.
2.  It gives you spectacular query performance, primarily owing to the intelligent navigation of aggregates and partitions.
3.  Parent-child dimension structures are easy and intuitive to implement.
3.  It gives you server-defined rules for handling semiadditive and nonadditive measures.


The above explanation holds good for  SSAS vs Sql Server Relational,  Sql Server Relational vs Microstrategy,


Fact and Dimenisional Tables Relationships
2009-01-08T14:43:00.000-08:00
Star schema, which maintains one-to-many relationships between dimensions and a fact table is widely accepted as the most viable data representation for dimensional analysis.

Realworld DW schema, however, frequently includes many-to-many relationships between a dimensionand a fact table.

Issues of many-to-many relationships.
1. losing the simplicity of the star schema structure
2. increasing complexity in forming queries, and
3. degrading query performance by adding more joins.

Two ad-hoc methods to resolve

1.  Denormalizing the dimension tables.
2.  Building the Bridge Table.

To find more information :  go to the white paper An Analysis of Many-to-Many Relationships Between Fact andDimension Tables in Dimensional Modeling


Dimensional Modeling - Design Changes
2009-01-08T14:29:00.000-08:00
The following changes are anticipated to the design after the data warehouse is up and running

1. Adding new unanticipated facts (that is, new additive numeric fields in the fact table), as long as they are consistent with the fundamental grain of the existing fact table.

2. Adding completely new dimensions, as long as there is a single value of that dimension defined for each existing fact record.

3. Adding new, unanticipated dimensional attributes.

4. Breaking existing dimension records down to a lower level of granularity from a certain point in time forward.

--------------------------------------------------------------


SPSS Links
2008-12-21T20:29:00.000-08:00
http://courses.csusm.edu/resources/spss/
http://www.stat.tamu.edu/spss.php
http://www.ats.ucla.edu/stat/spss/
http://www.ats.ucla.edu/stat/seminars/
http://faculty.vassar.edu/lowry/webtext.html


OLAP Versus Data Mining
2008-11-24T15:51:00.000-08:00
OLAP is a data summarization/aggregation tool that helps simplify data analysis, while data
mining allows the automated discovery of implicit patterns and interesting knowledge hidden in large amounts of data.
Key is OLAP

OLAP tools are targeted toward simplifying and supporting interactive data analysis,
whereas the goal of data mining tools is to automate as much of the process as possible,
while still allowing users to guide the process. In this sense, data mining goes one step beyond traditional on-line analytical processing.
Key is OLAP tools

OLAP functions are essentially for user-directed data summary and comparison
(by drilling, pivoting, slicing,dicing, and other operations). Data mining covers a much broader spectrum than simple OLAP operations because it performs not only data summary and comparison but also association, classification, prediction, clustering, time-series analysis, and other data analysis tasks.
Key is OLAP Operations

Data mining is not confined to the analysis of data stored in data warehouses. It may
analyze data existing at more detailed granularities than the summarized data provided
in a data warehouse. It may also analyze transactional, spatial, textual, and multimedia
data that are difficult to model with current multidimensional database technology. In
this context, data mining covers a broader spectrum than OLAP with respect to data
mining functionality and the complexity of the data handled.
Key is OLAP data analysis

Because data mining involves more automated and deeper analysis than OLAP,
data mining is expected to have broader applications. Data mining can help business
managers find and reach more suitable customers, as well as gain critical
business insights that may help drive market share and raise profits. In addition,
data mining can help managers understand customer group characteristics
and develop optimal pricing strategies accordingly, correct item bundling based
not on intuition but on actual item groups derived from customer purchase patterns,
reduce promotional spending, and at the same time increase the overall net
effectiveness of promotions.


Table Expressions
2008-11-11T14:21:00.000-08:00
Four types of table expressions:
1.  Derived tables
2.  Common table expressions (CTEs),
3.  Views
4.  Inline table-valued functions (inline TVFs) also known as Parametrized views.

Beneifts.

Table expressions are virtual Tables.
The benefi ts of using table expressions uses a modular approach and not to performance.

Any Table Expression should meet the following three requirements.

1.   Order is not guaranteed because the rows in a relational table are not ordered except  with     one restriction using Top Clause with Order By. Here the Order by is used for restricting the rows.
2.  All columns must have names.
3.  All column names must be unique.

To reuse the definitions , use views and inline TVFs.
Not to reuse the definitions, use dervied tables and CTEs.

APPLY operator

Multiple instances of the same CTE The APPLY operator operates on two input tables, the second of which may be a table expression; I’ll refer to them as the left and right tables. The right table is usually a derived table or an inline TVF.

If the right table expression returns an empty set, the CROSS APPLY operator does not returnthe corresponding left row. To return the empty set use the OUTER Apply.

--------------------------------------------------------------------------------

In its non-recursive form, a CTE is an alternative to derived tables, views, and inline user-defined functions (UDFs).

A derived table is a named table expression that exists for the duration of a query.
Views are also named table expressions that persist in the database until you explicitly drop them. Unlike with derived tables
1. a query can refer to a view name multiple times;
2. the view can't refer to variables.

Inline UDFs have the same characteristics as views except that they can refer to the function's input arguments.

CTEs have the best features of derived tables, views, and inline UDFs—they are named table expressions that exist only for the duration of the query, that an outer query can refer to multiple times, and that can refer to variables defined in the calling batch. . . .


Good Sites -- 1
2008-10-31T10:58:00.001-07:00
http://ssas-info.com/

www.microsoft.com/bi


Data Mining Books
2008-10-03T16:08:00.000-07:00
Data Mining Books to buy
1. maraaret h. dunham 
2. Jaiwei hau 
Introduction to Data Mining By Pang-Ning Tan, Michael Steinbach, Vipin Kumar
Data Mining Videos from Google Talk
http://www.youtube.com/watch?v=-tWS0tN8sW0 -- Day 13
http://www.youtube.com/watch?v=fmZYH3rmqDQ
http://www.youtube.com/watch?v=l4a3e__QzoY 
http://www.youtube.com/watch?v=CzvgrcQhWGg
http://www.youtube.com/watch?v=xpuB9ydmBsM
http://www.youtube.com/watch?v=N5i85v0ckzY

http://www.youtube.com/watch?v=zRsMEl6PHhM
 
-----------------------------------------------------------
Course from stanford university
http://www.newstats202.com/ 
 



Statistics topics needs to cover for Data Mining
2008-09-21T15:33:00.001-07:00
Data Analytics Competence Spectrum
From Pricing, Risk, Cross-selling, Retention, Time-to-Market, Ad Efficiency and Campaign Management to Acquisition and Growth, and Web Analytics, our Data Analytics capabilities cover a number of possible analyses, using different programming tools and software such as SAS, SPSS, Excel, SQL, Matlab and Siebel. The following is a sample of the techniques used for various types of analysis.
Link Analysis
Multivariate, Linear, Ordinal and Multinomial Regression
Conjoint Analysis
Logistic Regression
Factor Analysis
Survival Analysis
Decision Trees
Clustering
Bayesian Analysis
Neural Networks
Kohonen Maps
Simulation Techniques
Discriminant Analysis
Time Series Analysis
Vendor Performance Management Tool
Sales and Retail Dashboards
Contact Search Tool
Launch Performance Tracking Tool
Market Basket Analysis
Econometric Forecasting
Cycle Research for Technical Analysis
Reporting and Analysis
Optimising Cash and Accounts Payable Management


Microsoft BI sites
2008-08-28T13:36:00.000-07:00
http://www.microsoft.com/sql/technologies/reporting/default.mspx
http://www.microsoft.com/sql/technologies/analysis/default.mspx
http://www.microsoft.com/sql/technologies/dm/default.mspx
http://www.microsoft.com/sql/technologies/integration/default.mspx
 



Sql Server Data Mining
2008-08-20T14:31:00.000-07:00
http://www.sqlserverdatamining.com/ssdm/Home/tabid/36/Default.aspx


Sql Server 2005 DMV's
2008-08-07T16:26:00.000-07:00
Some interesting DMV's in SQL SERVER 2005

sys.dm_tran_locks
sys.dm_exec_cached_plans
sys.dm_exec_query_plan
sys.dm_exec_sessions
sys.dm_tran_session_transactions
sys.dm_exec_requests


SSRS - Improvoing Performance
2008-07-27T09:20:00.000-07:00
1. Avoid long running reports,  by creating a report snapshot to run nightly or during low activity on your system.

2. Define pagination to hide overhead. for example, if a user requests a report that returns 1000 rows of data, you can use page breaking to render the initial page or results while the report server renders the rest of the pages in the background. without pagination, the user would have to wait until it rendered all 1000 rows.

3. Implement filters for performance.
    If you use report-level filtering on data regions and data grouping rather than using query parameters as filters.

4. Enable drill down for detail.
     Instead of giving users a report that shows all the data at once, design a summary report that
     presents a subset of data and let usrs drill down to get more detail. using master detail 
     reports that take advantage of SSRS's navigation capabilities. Many parts of the report
     controls have navigation property, which we use to create a hyperlink on the control for
     passing the specific values.


Dimensional Vs ER Modeling
2008-07-26T22:07:00.000-07:00
1. The relationship in a Dimensional model don't represent business rules instead they are navigational paths used to help write reports or create graphs. But whereas relationship in ER modeling represents business rules.
2. The primary goal of the ER modeling is to remove all non key data redundancy.
But Dimensional modeling controls data redundancy by confirming dimension and fact tables.
The table that has been confirmed can be used in more than one dimensional data model.

How to Create a Dimensional Model

1.  Identity business process by business process and Each business process  can be expressed
     as a data mart
     ---- a modular , highly focused, richly detailed, incrementally designed   componenet
            of the   datawarehouse.
     Initially try to focus on the Single-Source data mart not on multiple source datamat.
     example of single data marts are retail sales, purchase orders, shipments  and payments.
     example of multiple data mart is Customer profitability which combines revenue and costs that often come from sales and inventory databases.

2.  Grain of the Fact table which is the level of detail that the table captures.


SSRS -- Reporting
2008-06-28T13:04:00.000-07:00
Report Structure Types
Structured Reports       -- Relational
Structured Grouping  -- OLAP
Structured Attributes  -- Data mining

Structured Reports    -- Relational
a. Pre-Define Report structure(i.e., Report Structure is Static).
b. Allow Interaction (drill down,slicing) but notdynamic structing. 

Structured Grouping  -- OLAP 
a. Pre-define grouping buckets --
b. Report Structure is Dynamic  (Slice & Dice, Dynamic Pivot, Sort and filter)
c. Pre-Summarize data for speed.

Structured Attributes  -- Data Mining
a.  Pre-define attributes (and model)
b.  Mining model calculates grouping and structure.
c.  Use for Prediction , exploration and analysis.

Reporting Approaches
1.  OLTP Data  ODS -- Report
2.  Star Schema  ODS -- Staging Table -- Start Schema -- Reeport
3.  OLAP Data  ODS -- Staging Table -- Star Schema -- cube -- Report    
Here we use Extract, Transform and Load.
4.  UDM


Junk dimensions
2008-05-30T17:25:00.000-07:00
OLTP tables that are full of flag fields and yes/no attributes, many of which are used for operational support and have no documentation except for the column names.
 There are three conventional ways to deal with these attributes: discard all of the miscellaneous attributes, eliminating them from the dimensional design; incorporate the miscellaneous attributes into the fact table; or make each miscellaneous attribute a separate dimension. However, all of these options are less than ideal.
Discarding the data can be dangerous because the miscellaneous values, flags, and yes/no fields might contain valuable business data.
Including the miscellaneous attributes in the fact table could cause the fact table to swell to alarming proportions, especially if you have more than just a few miscellaneous attributes. The increased size of the fact table could cause serious performance problems because of the reduced number of records per physical I/O. Even if you tried to index these fields to minimize the performance problems, you still wouldn’t gain anything because so many of the miscellaneous fields contain flag values such as 0 and 1; Y and N; or open, pending, and closed.
And if you make each miscellaneous attribute a separate dimension, it will most likely result in a complicated dimensional designyou’re looking at a much more complicated star schema and associated cube.
Column names such as Completed, Packed, Shipped, Received, Delivered, and Returned (each with yes/no data values) are very common, and they do have business value. Instead of discarding flag fields and yes/no attributes, I suggest placing them all into a junk dimension that’s organized
The junk dimension shown in Figure 1 represents an order-fulfillment system; the column headers show some of the possible statuses an item that has been ordered can have. Row 1 indicates that the item ordered has been picked out of the warehouse, packed for shipment, shipped, delivered, received, returned for a refund, and restocked in the warehouse. Row 9 shows an item on order that’s waiting to begin the order-fulfillment process. The rows in between indicate items that are in various stages of the orderfulfillment process. This example is very simple because the process is so linear and sequential,


Degenerate dimension
2008-05-29T12:54:00.000-07:00
Degenerate dimension is not one that lacks moral structure or integrity. Instead, a degenerate dimension is a dimension that doesn’t exist as a table but is represented in the data warehouse.
Data warehouse dimensional design requires you to include control documents such as invoices, orders, and warranties. Each of these control documents has a control number such as the invoice number, the order number, or the serial number of the item under warranty.

Degenerate dimensions are simply control numbers that are stored in the fact table of a data warehouse. These control numbers look like keys, but they don’t act like keys; they have no associated dimension to join with. Control numbers provide a way to identify which line items in the fact table were generated as a part of the same order or invoice. Let’s take a look at how to map control numbers from the OLTP database to the fact table in the data warehouse and associate them with each line item


Google's own Database
2008-05-05T09:58:00.000-07:00
http://labs.google.com/papers/bigtable-osdi06.pdf.


Any BI Platform should deliver 12 capabilities
2008-04-29T16:38:00.000-07:00
Magic Quadrant for Business Intelligence
Platforms, 2008

http://mediaproducts.gartner.com/reprints/microsoft/vol7/article3/article3.html

Gartner defines a BI platform as a software platform that delivers the 12 capabilities listed below.

Integration
BI infrastructure — All tools in the platform should use the same security, metadata, administration, portal integration, object model and query engine, and should share the same look and feel.

Metadata management — This is arguably the most important of the 12 capabilities. Not only should all tools leverage the same metadata, but the offering should provide a robust way to search, capture, store, reuse and publish metadata objects such as dimensions, hierarchies, measures, performance metrics and report layout objects.

Development — The BI platform should provide a set of programmatic development tools — coupled with a software developer's kit for creating BI applications — that can be integrated into a business process, and/or embedded in another application. The BI platform should also enable developers to build BI applications without coding by using wizard-like components for a graphical assembly process. The development environment should also support Web services in performing common tasks such as scheduling, delivering, administering and managing.

Workflow and collaboration — This capability enables BI users to share and discuss information via public folders and discussion threads. In addition, the BI application can assign and track events or tasks allotted to specific users, based on pre-defined business rules. Often, this capability is delivered by integrating with a separate portal or workflow tool.


Information Delivery
Reporting — Reporting provides the ability to create formatted and interactive reports with highly scalable distribution and scheduling capabilities. In addition, BI platform vendors should handle a wide array of reporting styles (for example, financial, operational and performance dashboards).

Dashboards — This subset of reporting includes the ability to publish formal, Web-based reports with intuitive displays of information, including dials, gauges and traffic lights. These displays indicate the state of the performance metric, compared with a goal or target value. Increasingly, dashboards are used to disseminate real-time data from operational applications.

Ad hoc query — This capability, also known as self-service reporting, enables users to ask their own questions of the data, without relying on IT to create a report. In particular, the tools must have a robust semantic layer to allow users to navigate available data sources. In addition, these tools should offer query governance and auditing capabilities to ensure that queries perform well.

Microsoft Office integration — In some cases, BI platforms are used as a middle tier to manage, secure and execute BI tasks, but Microsoft Office (particularly Excel) acts as the BI client. In these cases, it is vital that the BI vendor provides integration with Microsoft Office, including support for document formats, formulas, data "refresh" and pivot tables. Advanced integration includes cell locking and write-back.

Analysis
OLAP — This enables end users to analyze data with extremely fast query and calculation performance, enabling a style of analysis known as "slicing and dicing." This capability could span a variety of storage architectures such as relational, multidimensional and in-memory.

Advanced visualization — This provides the ability to display numerous aspects of the data more efficiently by using interactive pictures and charts, instead of rows and columns. Over time, advanced visualization will go beyond just slicing and dicing data to include more process-driven BI projects, allowing all stakeholders to better understand the workflow through a visual representation.

Predictive modeling and data mining — This capability enables organizations to classify categorical variables and estimate continuous variables using advanced mathematical techniques.

Scorecards — These take the metrics displayed in a dashboard a step further by applying them to a strategy map that aligns key performance indicators to a strategic objective. Scorecard metrics should be linked to related reports and information in order to do further analysis. A scorecard implies the use of a performance management methodology such as Six Sigma or a balanced scorecard framework.

Where the Microsoft BI stands

Strengths

Microsoft's pricing and integration with its Office (including its major CPM-led innovation of 2007, PerfomancePoint Server) and SQL Server products are especially attractive to organizations that have standardized on the Microsoft information infrastructure. The bundling and pricing of its BI products makes them an economically attractive offering that will be considered by many organizations.
Microsoft's BI products appeal to the large community of Microsoft application developers. Microsoft's BI platform provides developers with infrastructure, development tools, workflow and collaboration capabilities that are held in higher regard than those of many of its competitors.
Microsoft is benefiting from developing its indirect sales and services channel and market awareness of its SQL Server, Office and SharePoint Portal installed base. As a result, Microsoft estimates that it now has around 2,000 OEM/ISV partners for its BI products. Many departmental and business unit end users who hear the Office and SharePoint integration marketing messages for BI will likely ask for the products and associated support from their IT departments.
According to the customers we contacted as part of his research, Microsoft offers the best BI software quality of all the megavendors, with over half of them reporting no problems with software. This reflects Microsoft's focus on BI, the strength of its product line management team and the fact that much of its BI technology has been internally developed rather than acquired.


Cautions

Microsoft was late to join the BI platforms market and it is still playing catch up. According to customers, its still lags behind pure-play vendors in terms of metadata management, reporting, and dashboard and ad hoc query capabilities. However, Microsoft is in it for the long haul and Gartner expects that it will continue to grow its BI investments in order to become a stronger competitor. Organizations that have heterogeneous applications, information infrastructure and development environments will find Microsoft's BI-related marketing and announcements to be interesting but potentially distracting, since they may not easily integrate with their existing investments in infrastructure and applications. Despite its price advantage, Microsoft will face increasing competitive pressure as BI becomes a market where strategic sourcing, of more than just BI capabilities, takes precedence over features and functions, and as the other megavendors' acquisitions coalesce into their product stacks.


Good Sites for understanding Data-Mining Therotical Concepts
2008-04-16T17:48:00.000-07:00
Courtsey by Adnan Masood

http://ocw.mit.edu/OcwWeb/Sloan-School-of-Management/15-062Data-MiningSpring2003/CourseHome/index.htm

http://www.kdnuggets.com/courses/index.html

http://www.kdnuggets.com/education/usa-canada.html

http://www.kdnuggets.com/education/online.html

http://www.statistics.com/


Top 10 websites of Data Mining


kdnuggets.com 27
sas.com 24
kaushik.net 15
analyticbridge.com 10
datashaping.com 8
juiceanalytics.com 8
r-project.org 7
google.com 5
videolectures.net 5
analyticrecruiting.com 4
informs.org 4
webanalyticsdemystified.com 4


Dimensional Modeling Concepts to Learn
2008-03-16T18:14:00.001-07:00
To understand Dimensional Modeling ,
Following concepts are very very important

http://kimballgroup.com/html/kucourseMDWD.html

• Basic dimensional modeling concepts
• Conformed dimensions
• Slowly changing dimensions
• Additional concepts in dimensional modeling
-Hierarchies and snowflaking
-Degenerate and junk dimensions
-Many to many dimensions
• The dimensional modeling process

---------------------------------------------------------

• Populating dimension tables
-Using the slowly changing dimension wizard
-Avoiding the slowly changing dimension wizard

• Populating fact tables
-Basic fact table processing
-The surrogate key pipeline
-Advanced issues in fact table processing

--------------------------------------------------------------
A Good Link
http://kimballgroup.com/html/articles.html





Types of Dimensions
2008-03-16T14:52:00.000-07:00
Dimensions are of 3 types mainly in case of slowly changing dimensions: 

Type 1 ->Doesnt maintain any history and is update only.

Type 2-> Maintains full history.Insert row when certain attributes change else update row.The latest record is identified by a flag or date field which is called the MRI or Most Recent Indicator

Type 3->Maintains partial history.History is maintained as columns and not rows.Hence it is predecided as to till how many levels of history you want to maintain.

There are three types of Dimesions 

Confirmed Dimensios, Junk Dimesions, Degenerative Dimesions 

Comfirmed is some thing which can be shared by shared by multiple Fact Tables or multiple Data Marts.

Junk Dimensiions is grouping flagged values 

Degenerative Dimension is something dimensional in nature but exist fact table.


2008-02-12T17:30:00.000-08:00
SSRS - 2000 Query  to Retreive all the reports subscribed to a File Share 

select DeliveryExtension,ExtensionSettings
,CHARINDEX('\\', ExtensionSettings)
,substring(ExtensionSettings,CHARINDEX('\\', ExtensionSettings),80)
from CataLog c (nolock)inner join ReportSchedule r (nolock) 
on (c.ItemID = r.reportid)
inner join Subscriptions s  (nolock) 
on (s.SubscriptionID = r.SubscriptionID)
where DeliveryExtension = 'Report Server FileShare'
and ExtensionSettings like '%PATH%'


SQL SERVER 2005
2008-02-06T15:13:00.000-08:00
From Today, I will make sure to post something related to Sql Server 2005.