Archive

Archive for July, 2010

A SSIS Package Design Strategy – makes use of stored procedures as much as possible and also guarantees portability

July 30, 2010 Leave a comment

I decided to design a SSIS package using Execute SQL Task for the final baseline data loading. Both of the target database and the “transformed” source data are on the same SQL server.

Since I cannot create stored procedures on the target database, AND I don’t want to hardcode the target database location in any of my scripts or in any of my stored procedures, I have to do:

1) Wherever I need to retrieve from or write to the target database, I use direct SQL scripts with EAMTarget as the data source. The actual target database will not be hardcoded anywhere, except in the EAMTarget Connection Manager.

2) wherever I only need to manipulate the data without “touching” the target database, I call stored procedures that reside in the ETL database. The ETL database is also where the “transformed” data and the final staging tables are.

This strategy makes use of stored procedures as much as possible and also guarantees that my SSIS package is portable among different environments.

image

Categories: ETL Best Practices

LastId Dilemma – Resolved Finally

July 29, 2010 1 comment

We just need to understand what a commercial software’s Last Id strategy is and how they implement it.

First of all, we understand that the Last Id is THE Last ID that covers all the SQL tables, and there is no Last Id for any specific SQL table.

After some help from their developers, a stored procedure of GetId() was identified.

image

This  leads to the LastId table, which has an Identity column with 6706 as the seed value:

image

Use the DBCC command to get the actual seed value. It is still 6706,

DBCC checkident(LastId, noreseed)

This gives us a starting ID value of 1 + 6706 * 30 = 201,181. To increase our starting ID value, we just need to increase the seed value for the LastId table.

To increase the starting ID to 4,000,000, re-seed the LastId table:

DBCC checkident(LastId, reseed, 133333)

Done. Problem solved.

Categories: ETL Best Practices

Expose the PK and FK through HP Connect-It

July 29, 2010 Leave a comment

Just want to record this before I forget about this.

If we must use the Connect-It from HP to do the final loading of the data, I need to figure out how to set the FK values (otherwise, the mapping to the parent entities will drive anybody crazy).

In the advanced configuration, we can choose to expose both the PKs and FKs of the AM tables. The good news is that the FKs are indeed “exposed”, i.e. FKs can be “set” using the values I’ve populated into the source table. However, the PKs are not really “exposed”. I might be able to “get” the PK values, but was unable to “set” the PK values.

Anyway, make sure that these two options are checked.

image

In the mapping Edit Mapping window, make sure the mapping looks like this, with all the FKs mapped, and Name (or any other field that should be used as the reconciliation key) as the Reconciliation key. Note that the PK lModelId is not mapped. The tool will ignore it even if I have it mapped.

image

image

The only problem with this is that the Name field is ignored, and imported record looks like this. It has the vendor name prefixed to the model name. A SQL update should fix this.

image

Categories: ETL Best Practices

ETL Process Outside of HP Asset Manager API or any Software API – The Last Id Dilemma

July 28, 2010 Leave a comment

I bet anybody who has developed an ETL process outside of the target software API has encountered the following issues at some point. In the past, I’ve worked on Sage SalesLogix, and this time, HP Asset Manager. HP AM has it’s own data mapping/loading tool (I am not sure if I want to call it an ETL tool), called Connect-It. Here is an introduction of Connect-It. Ok, it’s an EAI type integration platform.

 

image

Here are some of the issues:

1) Performance issue with the connect-It tool: according to HP, we can expect a rate of 10 minutes for loading 5k records. If so, loading 200k records (this is how many assets I need to load initially) will take about 7 hours, and loading 2.5M records (this is how many software installations I need to load) will take 3 and a half days. I don’t think anybody can convince me that the performance is reasonable.

2) Due to the performance issue, we’ve decided to design an ETL process outside of the API, and PK values will be generated outside of the API, and the final data loading will be through a SQL push.

3) This avoids the performance issue that is inherent in the software API, but creates another issue, the Last ID issue. Apparently HP Asset Manager has kept an internal Last ID, which is now out of sync with the actual last used ID. Creating a new record in the AM client will generate a conflicting key error.  

We’ve talked about possible solutions, and so far, none looks good.

1) Can we find a way to increase the internal Last ID through some API functions? This seems to be a dead end so far, because the Last Id doesn’t seem to be exposed anywhere, not in any API functions, not within the Connect-It tool either.

2) Can we find a way to increase the internal Last ID through SQL? This also seems to be a dead end so far. None of the tables, stored procedures or functions expose that mysterious Last Id.

3) That leaves us the only option, that is, to use Connect-It to generate the PK values AND also load the data. My boss is aware of this issue. So I guess he can make the final call.

4) As a temporary solution, one of my co-workers suggested that we can start from a high enough ID, rather than the lowest possible seed value, and use it as the seed value to generate all our PK values outside of the API. For example, with an internal Last ID of 400K, we can use 9,000,000 as the seed value for the PK values. It will take a while for AM to reach from 400,000 to 9,000,000 when the conflicting key error will eventually occur. I would take this approach for now for the UAT phase, hoping that this will buy us some time to work out a permanent solution.

I’d love to hear from other people who had dealt with this kind of Last Id issue in a commercial software.

Categories: ETL Best Practices

HP Asset Manager – License Install

July 27, 2010 Leave a comment

I guess every commercial software needs tight control on the license. HP AM is no exception. My locally installed client complains about expired license. Here are the steps to fix the license issue.

1) Download all 4 license files from \\138theIPAddress\Disks\HP\Asset Manager 5.21 en\License Key

2) Then go to  start -> all programs -> HP -> AM 5.21 -> auto pass license management

Autopass License Management Window opens. Select "install/restore lic key".

3)  Browse and select each key one by one
click view contents
you will see a list
select each one and click install
do this for all 4 files

4) Activate database from Application Designer

5) Run the automated process

6) Now the client should run without any problems

Categories: Uncategorized

China’s Fake Credential Gate

July 26, 2010 Leave a comment

The Tang Jun scandal was recently dubbed the "fake credential gate" by Chinese media.

According to this article on ChinaDaily, the Ministry of Education has released a list of approved Chinese-foreign jointly run schools, including more than 400 schools for undergraduate education. The ministry also released a college/university list, which includes more than 10,000 colleges or universities from 33 countries, which the Chinese government accepts.

Neither the Pacific Western University, where Tang got his diploma, nor the California Miramar University – its new name – figure on the list.

Categories: Uncategorized

Report Model – Data Filter

July 26, 2010 Leave a comment

I am not talking about the security item filter that applies to a specific user (or a group of users) here. I want to create a data filter that applies to a specific entity(s) whenever that entity is used in a query, regardless of the userid.

Follow these steps.

Create a filter for the entity.

image

Select an entity and a field, and give it a condition.

image

The property “MandatoryFilter” is automatically set for the entity, and all the children entities. This will require users to always create a filter in the Report Builder. Not sure if I should change this behavior….

image

Categories: SSRS Expert
%d bloggers like this: