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.
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
Step 2: Right click on EAMPC, and select Tasks—>Restore—>Database
Select “From device”, on the Specify Backup window, Add the backup file location.
Select Restore for the backup file where the restoration will perform on.
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.
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.)
2) Change the recovery mode to Simple, instead of Full, to save on log file size.
Don’t forget to shrink the log file.
backup log EAMPC with truncate_only
dbcc shrinkfile (EAMPC_log, 1)
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.
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.
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
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.
- 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.
Spreadsheets, aren’t we all using them?
Don’t we all agree with these needs?
BI can do all of these?
What are the tools and their functionalities in BI?
Q(uery), R(eporting), A(nalysis)?
Aren’t we doing these analysis everday?
Core BI Technology vs. Data Mining.
We all love graphics.
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.
Date: Mon, 28 Jun 2010 10:27:37 -0700
Subject: Office 2007
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.
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.
2) I can set up my preference to always be prompted to save the chat.
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
usp: for all procedures
vw: for all views
etl: for all SSIS packages
job: for all SQL agent jobs
ix: for all indexes
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.