Learn, Build, Grow...Intelligent Collaboration: January 2008

Tuesday, January 29, 2008

CIO India - HP Forms BI, Information Management Unit

CIO India - HP Forms BI, Information Management Unit: "Hewlett-Packard Co. announced Wednesday the formation of a new business unit in its software division to go after the business intelligence and information management markets. HP's new Business Information Optimization unit's flagship product will be internally developed data warehousing technology, said Ben Barnes, the newly appointed vice president and general manager of the BI group."

Saturday, January 26, 2008

Multi-Environment Deployments Using Team Edition for Database Professionals - SQL Server Central

Multi-Environment Deployments Using Team Edition for Database Professionals - SQL Server Central: "Microsoft's introduction of the Team Edition for Database Professionals (VSDB) turned around our development and deployment processes. For a long time we had been working on methods of deploying from source control in the same way that the development teams work with their code. We tried several tools from various vendors and had finally decided to build our own. We had been using the home grown tool for a little while when VSDB came out. It solved a number of problems that we had run into with our in-house tool and offered a bunch of functionality that we had previously not had. So we went with VSDB. Now, almost all of our major development projects are using VSDB for development and deployment. The road to implementation contained more than a few bumps."The road to implementation contained more than a few bumps. The biggest of these was figuring out how exactly to set up our projects so that we could automate deployment between multiple environments; Development, QA, Staging, Training, Performance Testing and, finally, Production.
There are any number of possible differences between environments. The two that created difficulties for us were the different disks defined on different servers, and the different security requirements between servers. We also needed the builds to be automated so that we can schedule when they're run as well as run them manually. Finally we needed to be able to share projects between different DBA's so that more than one of use could run the same deployment on more than one environment.
In the following, I'm assuming you already create your VSDB projects and understand the basics of working within the Visual Studio environment as well as the basics of working with VSDB. We're also going to concentrate on scenarios where we're recreating the database every deployment. Incremental deployments have a whole host of issues that may require another article. This is targeted at the Visual Studio 2005 version. A new version has come out with Visual Studio 2008, but according to Gert Drapers blog, there are probably few differences between what's outlined here and the new version.

Slashdot | Smartphones Patented — Just About Everyone Sued 1 Minute Later

Slashdot Smartphones Patented — Just About Everyone Sued 1 Minute Later: "This week the US Patent and Trademark Office issued a surprisingly (although I guess it shouldn't be) broad patent for a 'mobile entertainment and communication device'. Upon closer inspection you may notice that it pretty much outlines the ubiquitous smartphone concept."

"It's a patent for a mobile phone with removable storage, an internet connection, a camera and the ability to download audio or video files. The patent holding firm who has the rights to this patent wasted no time at all. At 12:01am Tuesday morning, it filed three separate lawsuits against just about everyone you can think of, including Apple, Nokia, RIM, Sprint, ATT, HP, Motorola, Helio, HTC, Sony Ericsson, UTStarcomm, Samsung and a bunch of others. Amusingly, the company actually first filed the lawsuits on Monday, but realized it was jumping the gun and pulled them, only to refile just past the stroke of midnight. "

Thursday, January 24, 2008

How to stress test virtual machines

virtualization.info: How to stress test virtual machines: "How to stress test virtual machines
Sunday, January 15, 2006 0 Comments Performances are the greatest concerns CIO/CTO usually have approaching virtualization.
You surely would compare a virtual machine performance against a physical server, but you could also be in need of exploring how different virtualization technologies perform.
The first aspect you should test is I/O performances: physical raw partitions, proprietary filesystems, remoted SANs systems, local virtual IDE or SCSI disk subsystem. All of these configurations should be tested and compared with each other and against physical machines I/O performances.
Another second aspect you could test is network performances since virtual network adapters devices can handle traffic in different ways and be more or less efficient.
The best way to stress test a VM is to use standard tool for physical machines stress testing."

Scrum, Agile development methodologies mix with VSTS projects

Scrum, Agile development methodologies mix with VSTS projects: "NET developers who want to adopt more agile development methodologies, while still towing the line with their project management offices, may soon begin to tap into templates available with Visual Studio 2005 Team System."

What Do You Care About PerformancePoint Server 2007?

This is an August07 article but it ties to the current article just below-

What Do You Care About PerformancePoint Server 2007?: "In a recent poll, 78 percent of SQL Server Magazine readers said they wanted to learn more about Microsoft’s new business-performance management application, PerformancePoint Server (PPS) 2007, and another 4 percent said they were already testing it. Those numbers indicate that a lot of database implementers are interested in a product Microsoft is pitching to business decision makers (BDMs) rather than technical decision makers (TDMs). According to Bill Baker, general manager of Microsoft’s business intelligence (BI) applications division, just about the only involvement that SQL Server professionals should have with PPS is making sure their data is “right, clean, consistent, integrated…. That’s the domain of SQL Server.” Baker and Microsoft are emphatic that PPS is “just an application” like any other business application and that its intended importance is in giving end users access to BI capability without the need for IT intervention. . . ."

My PerformancePoint Server Wish List

My PerformancePoint Server Wish List: "Microsoft Office PerformancePoint Server 2007 (PPS) has now settled into the market, and customers who need a consolidated business intelligence (BI) and performance management platform are well advised to consider adopting it. (For more information about PPS, see “What Do You Care About PerformancePoint Server?” August 2007, InstantDoc ID 96367, and the Microsoft PerformancePoint Server Developer Portal at msdn2.microsoft.com/en-us/office/bb660518 .aspx. An upcoming article by Craig Utley in SQL Server Magazine dives deeper into PPS as well.) Future releases of PPS will arrive close together as Microsoft works to better integrate the various components. Looking to those future releases, here’s my list of top requests for changes I’d like to see Microsoft make to PPS."

Tuesday, January 22, 2008

Solution Accelerators

Solution Accelerators: "Solution Accelerators
Take the guesswork out of your deployments. Use these tested and proven automation tools and guides to assess your network and confidently roll out new services employing architectures with baked-in security and manageability."

SharePoint Monitoring Toolkit

Download details: SharePoint Monitoring Toolkit: "SharePoint Monitoring Toolkit
Brief Description
This Solution Accelerator provides a set of System Center Ops Manager 2007 Management Packs for Windows SharePoint Services 3.0 and Microsoft Office SharePoint Server 2007 (including SP1). The Toolkit also includes user documentation for each of the Management Packs."

Beyond the Data Model: Designing the Data Warehouse

Register NOW for this Webcast.

Beyond the Data Model: Designing the Data Warehouse Date: Wednesday, February 13 Live Time: 2 PM Eastern / 11 AM Pacific

This FREE Webcast delivers the critical guidelines for the overall design of your complete data warehouse solution.

Designed specifically for developers, architects, and managers, this technical presentation explores the:• Concept and practice of building data warehouse solutions • Schema of a data warehouse versus an OLTP system • Critical role metadata plays in building effective data warehouses • Extract, transform, and load process • Role of data modeling tools and how they fit into the design of a data warehouse
Don't miss this opportunity to learn from experts: Eric Johnson (MCSE, MCDBA, MCSD, CCNP) and Joshua Jones (MCTS: SQL Server 2005, MCITP: Database Administrator); co-owners of Consortio Systems.

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ PASS IT ON Do you know someone else who would benefit from this webinar? Do them a favor and forward this free invitation.
ON-DEMANDI f you can't make the live event, go ahead and register. You will be notified when the on-demand Webcast is available.

FYI - Microsoft Business Intelligence Bootcamp Series

To follow up on the email below I would like to provide you with the link to access the presentation slides

The link to the materials is below:

http://access.asentus.net/doc/BI_Bootcamp.zip

Good Day,

On behalf of the USPR Readiness Team we would like to inform you about on ongoing BI webcast series that may be of interest to you.
Attend this upcoming series of live web seminars and find out how you can help organizations quickly, effectively, and economically get access to, analyze, report on, and share the information they need to achieve corporate objectives. In this series you will learn how Microsoft Business Intelligence—a complete, fully integrated set of BI technologies, can help reduce the complexity of organizing and distributing information and lead to competitive advantages, overall better decisions, and an improved bottom line.
Learn what the Partner Opportunities are related to selling and implementing Microsoft BI and how you can position Microsoft BI effectively. Hear about Microsoft’s Business Intelligence Vision and Roadmap and how you can demonstrate Microsoft’s current BI offerings. Understand what is available in Microsoft Office PerformancePoint Server 2007 and what’s in store regarding BI in the Microsoft Partner Program.
For more information or to register for the webcast please use the following link:
Microsoft Business Intelligence Bootcamp Series:
http://www.msreadiness.com/promotiondetails.aspx?promoid=6024

Thank you,
US Partner Readiness Team

Wednesday, January 16, 2008

Jason Morales' Microsoft BI Update v.16 - January

Mining Fact tables in PPS databases

The following is a simple script that was developed by a team member to help dig into PPS fact tables. When data is validation necessary, or when enhancing PPS with real-time extensions, it is often necessary to see/use data in a PPS fact table prior to joining dimensions. The attached view takes the fact table name (automatically prefixes the MG_) and generates the SELECT with appropriate JOINs to the dimensions. This eliminates the repetitive and error prone SQL generation from fact view building.
“As-is” code, but it has been tested and provides some depth:

/****** Object: StoredProcedure [dbo].[spUTIL_CreateFactView] Script Date: 01/16/2008 04:56:27 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[spUTIL_CreateFactView] (@TableNameID nvarchar(10), @JoinType nvarchar(10) = 'INNER', @debug int = 0)
AS
-- Create view to PPS Fact table procedure, for diagnostic and real time extension use
-- Jim Lacenski, Solver Inc.
-- supply fact table name (no MG_) as parameter
-- customize / tailor result set SQL as needed
BEGIN
DECLARE @ViewName varchar(30), @FactTable varchar(80), @CrLf char(2)
DECLARE @sSQL varchar(3000), @sSQLRow varchar(100)
If OBJECT_ID('tempdb..#ColumnList') is not null DROP TABLE #ColumnList
If OBJECT_ID('tempdb..#CompleteSQL') is not null DROP TABLE #CompleteSQL

SET NOCOUNT ON
CREATE TABLE #ColumnList
( [ID] [int] IDENTITY(1,1),
SrcCol varchar(40),
ColType varchar(2),
DimTable varchar(40),
DimLabel varchar(40),
AliasName varchar(3),
JOINclause varchar (140)
)

CREATE TABLE #CompleteSQL
(
[ID] [int] IDENTITY(1,1),
sSQL varchar(200)
)
-- get fact table name
SELECT @FactTable = TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'MG_' + @TableNameID + '%'
PRINT @FactTable
SET @sSQL = 'SELECT TOP 5000'
SET @CrLf = CHAR(13)
INSERT INTO #CompleteSQL (sSQL) VALUES (@sSQL)

-- get dimensions
INSERT INTO #ColumnList (SrcCol, ColType, DimTable)
SELECT LEFT(COLUMN_NAME,CHARINDEX('_MemberId',COLUMN_NAME)-1), 'D',
'D_' + LEFT(COLUMN_NAME,CHARINDEX('_MemberId',COLUMN_NAME)-1)
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @FactTable
AND COLUMN_NAME LIKE '%_MemberID'

-- add time dimension
INSERT INTO #ColumnList (SrcCol, ColType)
SELECT COLUMN_NAME,'T'
FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @FactTable
AND COLUMN_NAME LIKE 'Time_%' AND NOT COLUMN_NAME LIKE '%_MemberId'

-- create alias names, column names and JOIN clauses
UPDATE #ColumnList SET AliasName = 'D' + cast([ID] as nvarchar(2))
UPDATE #ColumnList SET DimLabel = AliasName + '.Label [' + SrcCol + ']'
WHERE ColType = 'D'
UPDATE #ColumnList SET JOINclause = @JoinType + ' JOIN '+ DimTable + ' ' + AliasName + ' ON Fct.[' + SrcCol + '_MemberId] = ' + AliasName + '.MemberId'
WHERE ColType = 'D'
UPDATE #ColumnList SET DimLabel = SrcCol
WHERE ColType = 'T'

IF @Debug = 1 SELECT * from #ColumnList

-- iterate through and build sql
DECLARE Col_cursor CURSOR FOR SELECT DimLabel FROM #ColumnList
OPEN Col_cursor
FETCH NEXT FROM Col_cursor INTO @sSQLRow
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sSQL = @sSQLRow + ', ' + @CrLf
INSERT INTO #CompleteSQL (sSQL) VALUES(@sSQL)
FETCH NEXT FROM Col_cursor INTO @sSQLRow
END
CLOSE Col_cursor

-- trim last row
UPDATE #CompleteSQL SET sSQL = LEFT(sSQL,LEN(sSQL)-3)
WHERE [ID] = (SELECT MAX([ID]) FROM #CompleteSQL)

INSERT INTO #CompleteSQL (sSQL) VALUES(' FROM [' + @FactTable + '] Fct ')

-- Insert JOIN clauses
DECLARE JOIN_cursor CURSOR FOR SELECT JOINclause FROM #ColumnList
OPEN JOIN_cursor
FETCH NEXT FROM JOIN_cursor INTO @sSQLRow
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sSQL = @sSQLRow + @CrLf
INSERT INTO #CompleteSQL (sSQL) VALUES(@sSQL)
FETCH NEXT FROM JOIN_cursor INTO @sSQLRow
END
CLOSE JOIN_cursor

-- preseed WHERE
INSERT INTO #CompleteSQL (sSQL) VALUES('WHERE 1 = 1')
-- show results
IF @Debug = 0 SELECT sSQL FROM #CompleteSQL WHERE sSQL is not NULL

END

Microsoft BI VPC 5.1

Download details: Microsoft BI VPC 5.1: "Microsoft Business Intelligence (BI), a complete suite of server, client, and developer products, supports decision makers in developing deeper knowledge and insight that helps them make better business decisions, faster. Microsoft BI is delivered through three layers or workloads: data warehousing, reporting and analysis, and performance management and R5 has demos across these three workloads."

Tuesday, January 15, 2008

X-Lite SIP VoIP Softphone Download - Daily Download News - Digital Trends

X-Lite SIP VoIP Softphone Download - Daily Download News - Digital Trends: "CounterPath's X-Lite 3.0 is the market's leading free SIP based softphone available for download. Based on the advanced architecture of eyeBeam 1.5 (CounterPath's carrier grade telephony client), the newX-Lite is designed to showcase some of the feature rich capabilities available with our commercial softphone such as superior audio and video quality, zero-touch configuration, IM & Presence, and a comprehensive personal address book."

Nimbus Partners

Home - Nimbus Partners: "Capture, analyze and communicate business processes easily with Control 2007 software
Understanding the end-to-end processes of an organization is at the heart of
delivering high performance, compliance and robust operations.
The challenge is to develop a process framework that is common to
these goals.
Control 2007 enables organizations to capture, analyze and
communicate a common set of processes that can be used to
manage and transform your business.
The use of Control 2007 dramatically reduces the time, effort and cost
required to deliver effective process management. Capture, analyze and communicate business processes easily with Control 2007 software
Understanding the end-to-end processes of an organization is at the heart of
delivering high performance, compliance and robust operations.
The challenge is to develop a process framework that is common to
these goals.
Control 2007 enables organizations to capture, analyze and
communicate a common set of processes that can be used to
manage and transform your business.
The use of Control 2007 dramatically reduces the time, effort and cost
required to deliver effective process management."

SQL table scripter

When migrating/supporting databases transferring configuration tables can be troublesome… without the need for SSIS, and with compatibility to VSS. The attached script can help.
-- from site http://vyaskn.tripod.com/code

CREATE PROC sp_generate_inserts( @table_name varchar(776), -- The table/view for which the INSERT statements will be generated using the existing data @target_table varchar(776) = NULL, -- Use this parameter to specify a different table name into which the data will be inserted @include_column_list bit = 1, -- Use this parameter to include/ommit column list in the generated INSERT statement @from varchar(800) = NULL, -- Use this parameter to filter the rows based on a filter condition (using WHERE) @include_timestamp bit = 0, -- Specify 1 for this parameter, if you want to include the TIMESTAMP/ROWVERSION column's data in the INSERT statement @debug_mode bit = 0, -- If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination @owner varchar(64) = NULL, -- Use this parameter if you are not the owner of the table @ommit_images bit = 0, -- Use this parameter to generate INSERT statements by omitting the 'image' columns @ommit_identity bit = 0, -- Use this parameter to ommit the identity columns @top int = NULL, -- Use this parameter to generate INSERT statements only for the TOP n rows @cols_to_include varchar(8000) = NULL, -- List of columns to be included in the INSERT statement @cols_to_exclude varchar(8000) = NULL, -- List of columns to be excluded from the INSERT statement @disable_constraints bit = 0, -- When 1, disables foreign key constraints and enables them after the INSERT statements @ommit_computed_cols bit = 0 -- When 1, computed columns will not be included in the INSERT statement )ASBEGIN/***********************************************************************************************************
Procedure: sp_generate_inserts (Build 22) (Copyright © 2002 Narayana Vyas Kondreddi. All rights reserved.)
Purpose: To generate INSERT statements from existing data. These INSERTS can be executed to regenerate the data at some other location. This procedure is also useful to create a database setup, where in you can script your data along with your table definitions.

NOTE: This procedure may not work with tables with too many columns. Results can be unpredictable with huge text columns or SQL Server 2000's sql_variant data types Whenever possible, Use @include_column_list parameter to ommit column list in the INSERT statement, for better results IMPORTANT: This procedure is not tested with internation data (Extended characters or Unicode). If needed you might want to convert the datatypes of character variables in this procedure to their respective unicode counterparts like nchar and nvarchar

ALSO NOTE THAT THIS PROCEDURE IS NOT UPDATED TO WORK WITH NEW DATA TYPES INTRODUCED IN SQL SERVER 2005 / YUKON
Example 1: To generate INSERT statements for table 'titles': EXEC sp_generate_inserts 'titles'
Example 2: To ommit the column list in the INSERT statement: (Column list is included by default) IMPORTANT: If you have too many columns, you are advised to ommit column list, as shown below, to avoid erroneous results EXEC sp_generate_inserts 'titles', @include_column_list = 0
Example 3: To generate INSERT statements for 'titlesCopy' table from 'titles' table:
EXEC sp_generate_inserts 'titles', 'titlesCopy'
Example 4: To generate INSERT statements for 'titles' table for only those titles which contain the word 'Computer' in them: NOTE: Do not complicate the FROM or WHERE clause here. It's assumed that you are good with T-SQL if you are using this parameter
EXEC sp_generate_inserts 'titles', @from = "from titles where title like '%Computer%'"
Example 5: To specify that you want to include TIMESTAMP column's data as well in the INSERT statement: (By default TIMESTAMP column's data is not scripted)
EXEC sp_generate_inserts 'titles', @include_timestamp = 1
Example 6: To print the debug information: EXEC sp_generate_inserts 'titles', @debug_mode = 1
Example 7: If you are not the owner of the table, use @owner parameter to specify the owner name To use this option, you must have SELECT permissions on that table
EXEC sp_generate_inserts Nickstable, @owner = 'Nick'
Example 8: To generate INSERT statements for the rest of the columns excluding images When using this otion, DO NOT set @include_column_list parameter to 0.
EXEC sp_generate_inserts imgtable, @ommit_images = 1
Example 9: To generate INSERT statements excluding (ommiting) IDENTITY columns: (By default IDENTITY columns are included in the INSERT statement)
EXEC sp_generate_inserts mytable, @ommit_identity = 1
Example 10: To generate INSERT statements for the TOP 10 rows in the table: EXEC sp_generate_inserts mytable, @top = 10
Example 11: To generate INSERT statements with only those columns you want: EXEC sp_generate_inserts titles, @cols_to_include = "'title','title_id','au_id'"
Example 12: To generate INSERT statements by omitting certain columns: EXEC sp_generate_inserts titles, @cols_to_exclude = "'title','title_id','au_id'"
Example 13: To avoid checking the foreign key constraints while loading data with INSERT statements: EXEC sp_generate_inserts titles, @disable_constraints = 1
Example 14: To exclude computed columns from the INSERT statement: EXEC sp_generate_inserts MyTable, @ommit_computed_cols =
1***********************************************************************************************************/
SET NOCOUNT ON
--Making sure user only uses either @cols_to_include or @cols_to_excludeIF ((@cols_to_include IS NOT NULL) AND (@cols_to_exclude IS NOT NULL)) BEGIN RAISERROR('Use either @cols_to_include or @cols_to_exclude. Do not use both the parameters at once',16,1) RETURN -1 --Failure. Reason: Both @cols_to_include and @cols_to_exclude parameters are specified END
--Making sure the @cols_to_include and @cols_to_exclude parameters are receiving values in proper formatIF ((@cols_to_include IS NOT NULL) AND (PATINDEX('''%''',@cols_to_include) = 0)) BEGIN RAISERROR('Invalid use of @cols_to_include property',16,1) PRINT 'Specify column names surrounded by single quotes and separated by commas' PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_include = "''title_id'',''title''"' RETURN -1 --Failure. Reason: Invalid use of @cols_to_include property END
IF ((@cols_to_exclude IS NOT NULL) AND (PATINDEX('''%''',@cols_to_exclude) = 0)) BEGIN RAISERROR('Invalid use of @cols_to_exclude property',16,1) PRINT 'Specify column names surrounded by single quotes and separated by commas' PRINT 'Eg: EXEC sp_generate_inserts titles, @cols_to_exclude = "''title_id'',''title''"' RETURN -1 --Failure. Reason: Invalid use of @cols_to_exclude property END
--Checking to see if the database name is specified along wih the table name--Your database context should be local to the table for which you want to generate INSERT statements--specifying the database name is not allowedIF (PARSENAME(@table_name,3)) IS NOT NULL BEGIN RAISERROR('Do not specify the database name. Be in the required database and just specify the table name.',16,1) RETURN -1 --Failure. Reason: Database name is specified along with the table name, which is not allowed END
--Checking for the existence of 'user table' or 'view'--This procedure is not written to work on system tables--To script the data in system tables, just create a view on the system tables and script the view instead
IF @owner IS NULL BEGIN IF ((OBJECT_ID(@table_name,'U') IS NULL) AND (OBJECT_ID(@table_name,'V') IS NULL)) BEGIN RAISERROR('User table or view not found.',16,1) PRINT 'You may see this error, if you are not the owner of this table or view. In that case use @owner parameter to specify the owner name.' PRINT 'Make sure you have SELECT permission on that table or view.' RETURN -1 --Failure. Reason: There is no user table or view with this name END ENDELSE BEGIN IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = @table_name AND (TABLE_TYPE = 'BASE TABLE' OR TABLE_TYPE = 'VIEW') AND TABLE_SCHEMA = @owner) BEGIN RAISERROR('User table or view not found.',16,1) PRINT 'You may see this error, if you are not the owner of this table. In that case use @owner parameter to specify the owner name.' PRINT 'Make sure you have SELECT permission on that table or view.' RETURN -1 --Failure. Reason: There is no user table or view with this name END END
--Variable declarationsDECLARE @Column_ID int, @Column_List varchar(8000), @Column_Name varchar(128), @Start_Insert varchar(786), @Data_Type varchar(128), @Actual_Values varchar(8000), --This is the string that will be finally executed to generate INSERT statements @IDN varchar(128) --Will contain the IDENTITY column's name in the table
--Variable InitializationSET @IDN = ''SET @Column_ID = 0SET @Column_Name = ''SET @Column_List = ''SET @Actual_Values = ''
IF @owner IS NULL BEGIN SET @Start_Insert = 'INSERT INTO ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' ENDELSE BEGIN SET @Start_Insert = 'INSERT ' + '[' + LTRIM(RTRIM(@owner)) + '].' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' END
--To get the first column's ID
SELECT @Column_ID = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) WHERE TABLE_NAME = @table_name AND(@owner IS NULL OR TABLE_SCHEMA = @owner)

--Loop through all the columns of the table, to get the column names and their data typesWHILE @Column_ID IS NOT NULL BEGIN SELECT @Column_Name = QUOTENAME(COLUMN_NAME), @Data_Type = DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) WHERE ORDINAL_POSITION = @Column_ID AND TABLE_NAME = @table_name AND (@owner IS NULL OR TABLE_SCHEMA = @owner)

IF @cols_to_include IS NOT NULL --Selecting only user specified columns BEGIN IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_include) = 0 BEGIN GOTO SKIP_LOOP END END
IF @cols_to_exclude IS NOT NULL --Selecting only user specified columns BEGIN IF CHARINDEX( '''' + SUBSTRING(@Column_Name,2,LEN(@Column_Name)-2) + '''',@cols_to_exclude) <> 0 BEGIN GOTO SKIP_LOOP END END
--Making sure to output SET IDENTITY_INSERT ON/OFF in case the table has an IDENTITY column IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsIdentity')) = 1 BEGIN IF @ommit_identity = 0 --Determing whether to include or exclude the IDENTITY column SET @IDN = @Column_Name ELSE GOTO SKIP_LOOP END --Making sure whether to output computed columns or not IF @ommit_computed_cols = 1 BEGIN IF (SELECT COLUMNPROPERTY( OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name),SUBSTRING(@Column_Name,2,LEN(@Column_Name) - 2),'IsComputed')) = 1 BEGIN GOTO SKIP_LOOP END END --Tables with columns of IMAGE data type are not supported for obvious reasons IF(@Data_Type in ('image')) BEGIN IF (@ommit_images = 0) BEGIN RAISERROR('Tables with image columns are not supported.',16,1) PRINT 'Use @ommit_images = 1 parameter to generate INSERTs for the rest of the columns.' PRINT 'DO NOT ommit Column List in the INSERT statements. If you ommit column list using @include_column_list=0, the generated INSERTs will fail.' RETURN -1 --Failure. Reason: There is a column with image data type END ELSE BEGIN GOTO SKIP_LOOP END END
--Determining the data type of the column and depending on the data type, the VALUES part of --the INSERT statement is generated. Care is taken to handle columns with NULL values. Also --making sure, not to lose any data from flot, real, money, smallmomey, datetime columns SET @Actual_Values = @Actual_Values + CASE WHEN @Data_Type IN ('char','varchar','nchar','nvarchar') THEN 'COALESCE('''''''' + REPLACE(RTRIM(' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')' WHEN @Data_Type IN ('datetime','smalldatetime') THEN 'COALESCE('''''''' + RTRIM(CONVERT(char,' + @Column_Name + ',109))+'''''''',''NULL'')' WHEN @Data_Type IN ('uniqueidentifier') THEN 'COALESCE('''''''' + REPLACE(CONVERT(char(255),RTRIM(' + @Column_Name + ')),'''''''','''''''''''')+'''''''',''NULL'')' WHEN @Data_Type IN ('text','ntext') THEN 'COALESCE('''''''' + REPLACE(CONVERT(char(8000),' + @Column_Name + '),'''''''','''''''''''')+'''''''',''NULL'')' WHEN @Data_Type IN ('binary','varbinary') THEN 'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')' WHEN @Data_Type IN ('timestamp','rowversion') THEN CASE WHEN @include_timestamp = 0 THEN '''DEFAULT''' ELSE 'COALESCE(RTRIM(CONVERT(char,' + 'CONVERT(int,' + @Column_Name + '))),''NULL'')' END WHEN @Data_Type IN ('float','real','money','smallmoney') THEN 'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ',2)' + ')),''NULL'')' ELSE 'COALESCE(LTRIM(RTRIM(' + 'CONVERT(char, ' + @Column_Name + ')' + ')),''NULL'')' END + '+' + ''',''' + ' + ' --Generating the column list for the INSERT statement SET @Column_List = @Column_List + @Column_Name + ','
SKIP_LOOP: --The label used in GOTO
SELECT @Column_ID = MIN(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK) WHERE TABLE_NAME = @table_name AND ORDINAL_POSITION > @Column_ID AND (@owner IS NULL OR TABLE_SCHEMA = @owner)
--Loop ends here! END
--To get rid of the extra characters that got concatenated during the last run through the loopSET @Column_List = LEFT(@Column_List,len(@Column_List) - 1)SET @Actual_Values = LEFT(@Actual_Values,len(@Actual_Values) - 6)
IF LTRIM(@Column_List) = '' BEGIN RAISERROR('No columns to select. There should at least be one column to generate the output',16,1) RETURN -1 --Failure. Reason: Looks like all the columns are ommitted using the @cols_to_exclude parameter END
--Forming the final string that will be executed, to output the INSERT statementsIF (@include_column_list <> 0) BEGIN SET @Actual_Values = 'SELECT ' + CASE WHEN @top IS NULL OR @top < include_column_list =" 0)" actual_values =" 'SELECT"> 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END + '''' + RTRIM(@Start_Insert) + ' '' +''VALUES(''+ ' + @Actual_Values + '+'')''' + ' ' + COALESCE(@from,' FROM ' + CASE WHEN @owner IS NULL THEN '' ELSE '[' + LTRIM(RTRIM(@owner)) + '].' END + '[' + rtrim(@table_name) + ']' + '(NOLOCK)') END
--Determining whether to ouput any debug informationIF @debug_mode =1 BEGIN PRINT '/*****START OF DEBUG INFORMATION*****' PRINT 'Beginning of the INSERT statement:' PRINT @Start_Insert PRINT '' PRINT 'The column list:' PRINT @Column_List PRINT '' PRINT 'The SELECT statement executed to generate the INSERTs' PRINT @Actual_Values PRINT '' PRINT '*****END OF DEBUG INFORMATION*****/' PRINT '' END PRINT '--INSERTs generated by ''sp_generate_inserts'' stored procedure written by Vyas'PRINT '--Build number: 22'PRINT '--Problems/Suggestions? Contact Vyas @ vyaskn@hotmail.com'PRINT '--http://vyaskn.tripod.com'PRINT ''PRINT 'SET NOCOUNT ON'PRINT ''
--Determining whether to print IDENTITY_INSERT or notIF (@IDN <> '') BEGIN PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' ON' PRINT 'GO' PRINT '' END
IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL) BEGIN IF @owner IS NULL BEGIN SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily' END ELSE BEGIN SELECT 'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' NOCHECK CONSTRAINT ALL' AS '--Code to disable constraints temporarily' END
PRINT 'GO' END
PRINT ''PRINT 'PRINT ''Inserting values into ' + '[' + RTRIM(COALESCE(@target_table,@table_name)) + ']' + ''''
--All the hard work pays off here!!! You'll get your INSERT statements, when the next line executes!EXEC (@Actual_Values)
PRINT 'PRINT ''Done'''PRINT ''
IF @disable_constraints = 1 AND (OBJECT_ID(QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + @table_name, 'U') IS NOT NULL) BEGIN IF @owner IS NULL BEGIN SELECT 'ALTER TABLE ' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints' END ELSE BEGIN SELECT 'ALTER TABLE ' + QUOTENAME(@owner) + '.' + QUOTENAME(COALESCE(@target_table, @table_name)) + ' CHECK CONSTRAINT ALL' AS '--Code to enable the previously disabled constraints' END
PRINT 'GO' END
PRINT ''IF (@IDN <> '') BEGIN PRINT 'SET IDENTITY_INSERT ' + QUOTENAME(COALESCE(@owner,USER_NAME())) + '.' + QUOTENAME(@table_name) + ' OFF' PRINT 'GO' END
PRINT 'SET NOCOUNT OFF'
SET NOCOUNT OFFRETURN 0 --Success. We are done!END
GO
PRINT 'Created the procedure'GO
--Mark procedure as system objectEXEC sys.sp_MS_marksystemobject sp_generate_insertsGO
PRINT 'Granting EXECUTE permission on sp_generate_inserts to all users'GRANT EXEC ON sp_generate_inserts TO public
SET NOCOUNT OFFGO
PRINT 'Done'

Apple: MacBook Air

http://www.apple.com/macbookair/

MacBook Air os ultrathin, ultraportable, and ultra unlike anthing else. But you dont lose inches and pounds overnight. It's the result of rethinking conventions. Of multiple wireless innovations. And of breakthrough design. With MacBook Air, mobile computing suddenly has a new standard.

Microsoft Business Intelligence Conference 2008

Microsoft Business Intelligence Conference 2008:

"Save the Date for the 2nd Annual Microsoft Business Intelligence Conference 2008!

Annual event to be held in Seattle at the Washington State Convention & Trade Center
from October 6 – 8, 2008
We invite you to join us for this exciting and informative global conference for customers and partners. The 2nd Annual Microsoft Business Intelligence Conference 2008 will showcase Microsoft’s market-leading BI products, solution expertise and customer successes."

- Microsoft Office System Developer Conference 2008

- Microsoft Office System Developer Conference 2008: "LIVE DEMOS. REAL CODE. HOT NEW INFO.
The 2008 Microsoft Office System Developer Conference
February 10-13, San Jose California
ODC 2008 is the premier event for Microsoft Office and SharePoint developers, bringing together architects, developers, industry technical experts, Microsoft insiders, and key partners to redefine what it means to build on the Office system."

Performance Dashboards - For More Productive Employees

Performance Dashboards

"Employees are expected to understand and positively impact an organization's goals and objectives, but often lack the insight into how their role aligns with these goals. Learn how one company is delivering this insight in personalized dashboards that directly reach operational data from existing sources, and then present it as intuitive visual information in context to each employee's role as it relates to its corporate goals."

NRF Annual Convention 2008 Handouts

Am I the Leader I Need to Be? - Microsoft Word (290 KB)
Execute... or Be Executed_Handout 1 - Microsoft PowerPoint (27.9 MB)
Real Estate Forum: Understanding the Real Estate Cycle_Handout - Adobe PDF (365 KB)Retailing’s Race for Talent: Solving the Problem - Microsoft PowerPoint (19.8 MB)
Retailing’s Race for Talent: Solving the Problem_Handout 1 - Microsoft Word (180 KB)
Retailing’s Race for Talent: Solving the Problem_Handout 2 - Microsoft PowerPoint (6.5 MB)
State of Online Retailing - Microsoft PowerPoint (2.5 MB)
Sustainable Retailing: An Industry Overview and Case Study - Microsoft PowerPoint (23.0 MB)
The Evolution of the Merchandising Processes - Microsoft PowerPoint (926 KB)
Universal Design: Resources and Revenue - Microsoft Word (336 KB)
Visual Brand Curators: The Art of Placement in a Retail Brand - Adobe PDF (480 KB)

Saturday, January 12, 2008

Operational Business Intelligence – A Prescription for Operational Success

Operational Business Intelligence – A Prescription for Operational Success: "Business intelligence has “invaded” the operational space in a big way, offering in-line analytics, real-time or near real-time decision-making support for all employees in the enterprise."

Data Mining Web Controls Library

Microsoft SQL Server Product Samples: Analysis Services - Home: "Data Mining Web Controls Library

11/14/2007 11:17:50 PM
[This topic is pre-release documentation and is subject to change in future releases. Blank topics are included as placeholders.]

This sample works only with SQL Server 2005 and SQL Server 2008. It will not work with any version of SQL Server earlier than SQL Server 2005.
Data Mining Web Controls Library is a library intended to extend the data mining user experience in Microsoft SQL Server Analysis Services to the Web. This library provides a lightweight version of the data mining model viewers. Using Data Mining Web Controls, you can browse complex mining models from any computer that has Microsoft Internet Explorer installed.
The controls are server side. That is, the controls are located in the memory space of a Web server. The Web server can be, but does not have to be, the computer where Analysis Services is installed. However, Internet Information Services (IIS) and ASP.NET 2.0 (and later versions) must be installed and running on the computer where Data Mining Web Controls will be installed.
The documents and source code associated with each of these samples are located in the C:\Program Files\Microsoft SQL Server\100\Samples\Analysis Services\DataMining\Data Mining Web Controls folder."

Download details: Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques

Download details: Analysis Services Many-to-Many Dimensions: Query Performance Optimization Techniques: "Many-to-many dimension relationships in SQL Server 2005 Analysis Services (SSAS) enable you to easily model complex source schemas and provide great analytical capabilities. This capability frequently comes with a substantial cost in query performance due to the runtime join required by Analysis Services to resolve many-to-many queries. This best practices white paper discusses three many-to-many query performance optimization techniques, including how to implement them, and the performance testing results for each technique. It demonstrates that optimizing many-to-many relationships by compressing the common relationships between the many-to-many dimension and the data measure group, and then defining aggregations on both the data measure group and the intermediate measure group yields the best query performance. The results show dramatic improvement in the performance of many-to-many queries as the reduction in size of the intermediate measure group increases. Test results indicate that the greater the amount of compression, the greater the performance benefits—and that these benefits persist as additional fact data is added to the main fact table (and into the data measure group)."

Give XP the performance tools of Vista

Give XP the performance tools of Vista: "Give XP the performance tools of Vista"

Windows Vista has an all-in-one window for monitoring the health of your system and tweaking its performance — but what if you use XP, not Vista?With just a couple of downloads and a few drags and drops, you can add a Vista-like performance center to Windows XP.

Wednesday, January 09, 2008

Importing Poorly Formatted Text Files using SSIS

Importing Poorly Formatted Text Files using SSIS: "Handling text files from SQL Server Integration Service (SSIS) is not new and I am not going to discuss about importing well formatted text files. There are many occasions when database developers must import text files which are not properly formatted. I will discuss three methods of doing this. For these situations we are going to use Script Component data flow task.
Knowledge-wise you need to have basic understanding of SSIS packages. If you have created SSIS package to import traditional text files that will be more than enough. We are going to do some .NET scripting, you need to have some understanding of .NET coding."

How to Dynamically and Iteratively Populate An Excel Workbook from SQL Server - SQL Server Central

How to Dynamically and Iteratively Populate An Excel Workbook from SQL Server - SQL Server Central: "How to Dynamically and Iteratively Populate An Excel Workbook from SQL Server"

By M. Deschene, 2008/01/09

Recently at a client, the need was identified to be able to send data from a database to multiple Excel workbooks dynamically depending on the number of different criteria identified by the client. In our case it was to be by large geographical area such as North America, South America etc.
Within the Excel workbooks there were to be individual spreadsheets/tabs for each country within the larger geographical areas. Within North America there would be a spreadsheet/tab for Canada, the United States and Mexico. Each of these spreadsheet would then be populated with data specific to that country from a database.
Since I needed to do this with little to no manual intervention, I decided to use some of the new tools in SQL Server. In this article, I will show you how to create a new Excel output file and populate the file with discrete spreadsheets containing specific data from a database. We will use Microsoft Visual Studio to develop an Integration Services that handles the processes involved from variable creation through various packages.
Before we begin, let me review the requirements for this process. We must be able to do the following:
1. Must be able to create the Excel file automatically including the file name (dependent on the identified naming convention).
2. Must be able to create each spreadsheet or tab, name it and have top line column headers.
3. Must be able to populate each spreadsheet or tab with discrete data for that spreadsheet or tab. This would be based on a select statement.
4. Must be re-runnable.

Test Data Generation with Data Dude

SQL Server Universe.com: "Test Data Generation with Data Dude by Dinesh Priyankara"

The Visual Studio 2005 Team Edition for Database Professionals (a.k.a. Data Dude) was released somewhere around December 2006 helping many Database Administrators and Database Developers. The release was huge for us, because it addresses many issues faced by us. The team system concept was introduced by Microsoft, understanding that the software developer is not the only key person in the development life cycle and consolidating the entire team into one box, giving different tool sets for each role players. For example, while the developer uses the Visual Studio Team Edition for Software Developers, architect uses the Visual Studio Team Edition for Software Architects. At the beginning, there was no proper place for the DBA (and DBE) to sit with the team and work; he plays his role in an isolated environment with great difficulties. Some of the problems faced by both Database Administrators and Database Developers (or Engineers) were versioning the changes, making changes directly to production environment, inability to find the errors early before running changes in production environment and doing the unit tests. There were various tools for addressing these issues by supporting the DBA and DBE but there was no tool that addressed everything. Finally Microsoft decided to make him part of the team by providing a tool that contains everything DBA and DBE wanted, introduced Microsoft Visual Studio 2005 Team Edition for Database Professionals. This tool gives us many things, mainly giving us an environment where the “truth” of the schema resides, allowing us to manage and test the changes without harming the production environment. It supports build changes and deployment. There are some articles / blog posts describing the tool where you can get a clear picture of the tool. This article mainly focuses on one of the key features, Test Data Generator which is very important when the development life cycle comes into unit testing.

Microsoft Buys Fast, Plays Catch-Up

Microsoft Buys Fast, Plays Catch-Up: "Microsoft Buys Fast, Plays Catch-Up
Can Microsoft's acquisition of a company that sells specialized search engines help it close the overall Web search gap with Google?"

Tuesday, January 08, 2008

Microsoft® SQL Server™ 2008

Microsoft® SQL Server™ 2008 provides a trusted, productive, and intelligent data platform that enables you to run your most demanding mission critical applications

http://download.microsoft.com/download/B/F/2/BF24C54E-5635-4C79-AFB4-0C3F840E79F4/SQLServer2008_Datasheet_Final.pdf

Enterprise Systems | Microsoft's Virtualization Strategy Coming Into Focus

Enterprise Systems Microsoft's Virtualization Strategy Coming Into Focus: "Microsoft's Virtualization Strategy Coming Into Focus
Will Microsoft's server virtualization push change the rules of the game?"

Given the dominance of VMWare Inc. in the x86 virtualization space, Microsoft Corp.'s own efforts -- which until recently have seemed either fitful or faltering -- have fallen curiously flat.
That's set to change with the forthcoming release of Windows Server 2008 and Microsoft's long-promised Hyper-V virtualization technology -- although just how much it will change is anyone's guess.
Things became more clearly focused in December, when Microsoft unveiled its first Hyper-V public beta. On top of this, Redmond announced that it's already developing the next version of its System Center Virtual Machine Manager (SCVMM), which allows customers to quickly provision and configure new virtual machines and centrally manage virtual infrastructures running on Hyper-V, Microsoft Virtual Server 2005 R2, VMware ESX Server and Virtual Infrastructure 3 (V13). (The first version of SCVMM shipped in October 2007.)
Microsoft confirmed that the final version of Hyper-V will ship within 180 days of Windows Server 2008's RTM date.

Monday, January 07, 2008

Using Prediction Markets to Track Information Flows: Evidence from Google

http://bocowgill.com/GooglePredictionMarketPaper.pdf

In the last 2.5 years, Google has conducted the largest corporate experiment with prediction markets we are aware of. In this paper, we illustrate how markets can be used to study how an organization processes information. We document a number of biases in Google’s markets, most notably an optimistic bias. Newly hired employees are on the optimistic side of these markets, and optimistic biases are significantly more pronounced on days when Google stock is appreciating. We find strong correlations in trading for those who sit within a few feet of one another; social networks and work relationships also play a secondary explanatory role. The results are interesting in light of recent research on the role of optimism in entrepreneurial firms, as well as recent work on the importance of geographical and social proximity in explaining information flows in firms and markets.

Thursday, January 03, 2008

SQL Server Data Mining > Home

SQL Server Data Mining; Home: "Microsoft SQL Server 2005 Data Mining Add-Ins for Office 2007
The add-ins allow you to take advantage of the power of SQL Server Data Mining in the familiar environment of Excel and Visio whether you are a data mining expert or a novice."