Archive

Archive for June, 2010

Lunch with Char and Judy and Wayeesha

June 30, 2010 Leave a comment

I had a good lunch at Claim Jumper (I-17 & 101) with Char, Judy and of cause, Wayeesha. Conversations range from business, to weathers in Canada, and music, and piano.

Asset Management seems to be a popular IT initiative for even mid-size companies, such as SRP, where consumable assets, such as electrical equipments, need to be better managed. IT assets are obviously popular assets for this kind of enterprise initiative.

It’s also interesting to know that the option of 1099 vs w2 seems to be always there for the contractors. 1099 has advantages for consultants who travel a lot, and 1099 is the only way for them to deduct their expenses as non-taxable income. And, of cause, the non-deducted upfront payment is also a good thing for most consultants.

Working from home is a nice option for many consultants. But we need to avoid the common pitfall, that is the isolation from the rest of the team. Email, IM, and phone should be used every day to make sure that you have somebody to bounce your ideas and questions. Make a vigorous to-do list daily, and communicate with supervisor and team members immediately when issues come up, instead of delaying the communication to later time.

Char told me that people at Wells miss me. I miss people over there too. I guess I really had a good time working over there. It’ll be nice to go back.

Wayeesha also got something out of the conversation. Char is also a piano player, and exchanged many good ideas with Wayeesha. Char also promised to check out piano teachers, music workshops at Theater Works, or Glendale Community college for Wayeesha.

Categories: Uncategorized

Restore a Database to a Different Database on the Same SQL Instance

June 29, 2010 Leave a comment

During ETL process development, especially for a commercial system such as an EAM system, it’s a good idea to create a separate database (but on the server on the same SQL instance)  for ETL testing.

Here are the steps to do that (to make sure that the restoration does not overwrite the original database). The Restore operation can be done from within SQL Management Studio on a remote PC (you should have access to the share drive where the backup file is).

Step 1: create a new empty database on the same server, same SQL instance.

EAMDev: the original database where the backup files are from

EAMPC: a new empty database

image

Step 2: Right click on EAMPC, and select Tasks—>Restore—>Database

Select “From device”, on the Specify Backup window, Add the backup file location.

image

Select Restore for the backup file where the restoration will perform on.

image

Step 3: On the Option tab, select “Overwrite the existing database”, and database file grid, change the Restore As part to the new mdf and ldf files (they are just created when the new EAMPC empty database is created).

Then click OK.

It takes less than 30 seconds to restore a backup file of about 400MB.

image

Step 4: On the newly created database EAMPC, we need to change two things.

1) Change the logical database file names to EAMPC and EAMPC_log. (Otherwise the original logical data file names are used.)

image

2) Change the recovery mode to Simple, instead of Full, to save on log file size.

image

Don’t forget to shrink the log file.

USE EAMPC
GO

backup log EAMPC with truncate_only
go

dbcc shrinkfile (EAMPC_log, 1)
go

Step 5: The newly restored database should be tested against the EMA client tool to make sure that the connection is ok (with those special login names and passwords).

Note: because we are low on the D drive (where SQL server is installed), we need to move the data and log files to E drive, where plenty of disk space is available. So I did the Restore second time by following the above steps (of cause my data is overwritten, but that’s ok). But in the Restore As grid on the Option tab, I changed the D to E drive. checked the original data and log files on D drive, they are no longer there, just as expected. EAMPC_Data.mdf and EAMPC_Log.ldf are created on E drive instead.

Categories: ETL Best Practices

SET IDENTITY_INSERT

June 29, 2010 Leave a comment

During ETL process, if we are allowed to directly using SQL insert (rather than vendor’s own ETL tool, most of which does a great job in maintaining referential integrity, but short on performance), then we need to worry about the primaries keys and referential integrity. Most primary keys are system generated identity columns.

This SQL statement allows explicit values to be inserted into the identity column of a table.

Remarks

At any time, only one table in a session can have the IDENTITY_INSERT property set to ON. If a table already has this property set to ON, and a SET IDENTITY_INSERT ON statement is issued for another table, Microsoft® SQL Server™ returns an error message that states SET IDENTITY_INSERT is already ON and reports the table it is set ON for.

If the value inserted is larger than the current identity value for the table, SQL Server automatically uses the new inserted value as the current identity value.

The setting of SET IDENTITY_INSERT is set at execute or run time and not at parse time.

This example from MSDN creates a table with an identity column and shows how the SET IDENTITY_INSERT setting can be used to fill a gap in the identity values caused by a DELETE statement.

-- Create products table.
CREATE TABLE products (id int IDENTITY PRIMARY KEY, product varchar(40))
GO
-- Inserting values into products table.
INSERT INTO products (product) VALUES ('screwdriver')
INSERT INTO products (product) VALUES ('hammer')
INSERT INTO products (product) VALUES ('saw')
INSERT INTO products (product) VALUES ('shovel')
GO

-- Create a gap in the identity values.
DELETE products 
WHERE product = 'saw'
GO

SELECT * 
FROM products
GO

-- Attempt to insert an explicit ID value of 3;
-- should return a warning.
INSERT INTO products (id, product) VALUES(3, 'garden shovel')
GO
-- SET IDENTITY_INSERT to ON.
SET IDENTITY_INSERT products ON
GO

-- Attempt to insert an explicit ID value of 3
INSERT INTO products (id, product) VALUES(3, 'garden shovel').
GO

SELECT * 
FROM products
GO
-- Drop products table.
DROP TABLE products
GO
Categories: ETL Best Practices

Business Intelligence: The Definitive Guide for Midsize Organizations

June 29, 2010 Leave a comment

This is the title of a recent article on SearchSQLServer.com.

Sponsored by: SAP America, Inc.

How do you know whether you need business intelligence (BI)? Furthermore, do you need dashboards and scorecards, and what functionality would you need in your query and analysis tools? If any of these terms are alien to you, or you just cannot answer them, this informative guide is for you.

Learn:

  • The BI spectrum, which will give you a complete understanding of the functionality of BI
  • How to begin the implementation process in your organization
  • What to look for in BI products
  • And much more!

Here are some excerpt from the article. Pay attention to some of the pointed words.

Nontechnical, that’s a very attractive word.

image

Spreadsheets, aren’t we all using them?

image

Don’t we all agree with these needs?

image

BI can do all of these?

image

What are the tools and their functionalities in BI?

image

image

Q(uery), R(eporting), A(nalysis)?

Adhoc Queries.

image

image

Aren’t we doing these analysis everday?

image

image

More analysis

image

Core BI Technology vs. Data Mining.

image

We all love graphics.

image

image

image

image

image

image

image

image

image

image

image

image

image

Categories: Uncategorized

Who Needs Version Compatibility?

June 28, 2010 Leave a comment

From: sherry
To: a
Subject: RE: Office 2007
Date: Mon, 28 Jun 2010 11:34:23 –0700

I think Microsoft has been very nice to mass users in terms of version compatibility, but very mean to developers. I also found out the hard way that SSIS packages developed using VS 2008 can not open in VS 2005. I had to totally re-develop in VS 2005.

Sherry

Date: Mon, 28 Jun 2010 10:27:37 -0700
From: a
Subject: Office 2007
To: Sherry

Is horrible!!  You’re never supposed to remove functionality!  Now my macro buttons can only have a picture instead of text.  And you can’t even create new custom menus.  I’d like to slap their marketing department around just to feel better.

A

Categories: Uncategorized

Save Chat from IBM Lotus Sametime Connect

June 28, 2010 Leave a comment

Sometimes it’s helpful to save the chat so I can go back to them. Here are two things I can do on Sametime connect:

1) At the end of the chat session (or during), save the chat to a HTML file.

image

2) I can set up my preference to always be prompted to save the chat.

image

Categories: Uncategorized

ETL Process Design – Naming Convention

June 26, 2010 Leave a comment

I found myself modify my naming convention quite often because I am not happy with them in the first few rounds of the design process. After a while, I kind of settled with the following set of rules.

1) Prefix for different objects. This is not unique for ETL process design. But it’s very important for any SQL development work. Typically, I use the following:

tbl: for all table objects
  • tblStandardPCMakeModel
  • tblTranslate
  • tblMarimbaMachine
usp: for all procedures
  • usp_PopulatePCAssetMarimba
  • usp_StandardizeMfg
  • usp_LoadRawDataMarimba
vw: for all views
  • vwPCAsset
  • vwSoftwareAsset
etl: for all SSIS packages
  • etlEAM_LoadRawData
job: for all SQL agent jobs
  • jobEAM_LoadRawData
ix: for all indexes
  • ix_Ast_lAstId

2) Reserve a few verbs, nouns, and adjectives for the project. This is not unique for ETL process design either. But for any ETL development work, using the meaningful verbs, nouns, and adjectives is the foundation for a good design and also a good start for your design documentation (if anybody asks for that).

Here are some of the words I reserve for the project.

image

Categories: ETL Best Practices
%d bloggers like this: