v1.5 - component hangs when outputs not defined and warnings not suppressed

Topics: Issues 2: Using the Component (Design-Time)
Jun 23, 2010 at 12:24 PM

I'm just getting up and running with the using the compent, but after serveral attempts to configure the component I found that I have to tick the suppress warnings options for each of the unattached outputs.  Otherwise, when clicking finnish, the component hangs BIDS.

 

 

Coordinator
Jun 25, 2010 at 6:50 PM

I believe I had some conditions in the editor where some fast editing (cliking around by the user) would cause problems.  I think :)

The root of the problem is that I run validation while you're editing the configuration - asynchronously on another thread.  When you dismiss the editor (by pressing finish or cancel) it tells the validation thread to stop, then it will exit.  The problem occurred when the validation thread was already stopped - the editor will wait forever to get a response.

Jun 28, 2010 at 3:04 PM

Hi todd,

looked at this some more and it seems that these happens when my source tables are empty, so I guess that links in with the validation your doing.

One other question I have relates to SCD type 2 records. The component has a configurable option on SCD 2 Date Handling tab to define the todays date.

Here our data is extracted based upon a working day which runs from 6am day1 to 6am day 2.  I calculate these and store these in LogicalDate_Start and LogicalDate_End

At present the component appears to be ignoring the time of the LogicalDate_start, so where I define the date as e.g the 01/06/2010 06:00:00 the start and end dates of the updated dimensions are being set to 01/06/2010 00:00:00 and 31/05/2010 00:00:00.

Is there a way I can change this behaviour to do a simple DATEADD -1 ?  Otherwise this is something that could really cause a problem.

Thanks.

Coordinator
Jul 6, 2010 at 7:57 PM

Ahh.  Tough problem.

Unfortunately, that's due to the "day" granularity of the SCD 2 handling.  When it's set to that granularity, it discards the time component - literally strips it away.

I'm not exactly sure I understand how you're setting/using/manipulating those fields based on your brief description - but here are the only things I can think of so far:

  1. Use a "minute" granularity.  If you set the "time" to be 06:00, then the "new" record would get 06:00, and the old record would expire at 05:59 - on the "previous day" (actually the same date, but logically the previous day).
  2. Use a derived column before (on EACH input - yuck) to do a DATEADD("hour", -6, [datecolumn]) to subtract six hours, and then the inverse on EACH output (double-yuck).
Jul 7, 2010 at 10:10 AM

Hi Todd,

I had more time to think about the problem. 

At the very start of the ETL process (for all extracts) I set 3 logical dates.

Example Date

1

LogicalDate

the date for which the end user will analyse the information by

2010-07-01 00:00:00

2

LogicalDate_Start

this is the date and time the ‘day’ starts

2010-07-01 06:00:00

3

LogicalDate_End

this is the date and time the ‘day’ ends

2010-07-02 06:00:00

So after the KSCD, for the Expired SCD Type2 I replace the end date with DATEADD(“ss”,-1, @User::LogicalDate_Start) which will give me of course 2010-07-01 59:59:59

You mention possible setting the granularity to minutes.  Where would this be done?

Also a couple of quick questions hopefully:

1) Where a record has type 1 and type 2 changes, is the record sent down both output paths? 

2) There is a New output and a New SCD 2 output – I assume you only need use the latter if its a SCD type 2 dimension.

3) Deleted – Is this for dimension records where the source has been deleted?

Many thanks and appreciation for your time.

Regards

David Lumley­

From: toddmcdermid [mailto:notifications@codeplex.com]
Sent: 06 July 2010 19:58
To: dave_lumley@hotmail.com
Subject: Re: v1.5 - component hangs when outputs not defined and warnings not suppressed [kimballscd:217018]

From: toddmcdermid

Ahh. Tough problem.

Unfortunately, that's due to the "day" granularity of the SCD 2 handling. When it's set to that granularity, it discards the time component - literally strips it away.

I'm not exactly sure I understand how you're setting/using/manipulating those fields based on your brief description - but here are the only things I can think of so far:

1. Use a "minute" granularity. If you set the "time" to be 06:00, then the "new" record would get 06:00, and the old record would expire at 05:59 - on the "previous day" (actually the same date, but logically the previous day).

2. Use a derived column before (on EACH input - yuck) to do a DATEADD("hour", -6, [datecolumn]) to subtract six hours, and then the inverse on EACH output (double-yuck).

Read the full discussion online.

To add a post to this discussion, reply to this email (kimballscd@discussions.codeplex.com)

To start a new discussion for this project, email kimballscd@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com

Coordinator
Jul 16, 2010 at 5:29 PM

I really, really need to redo the documentation I killed from the last edit of my wikis!

Anyway - to answer your questions:

Setting granularity to minutes - go to the SCD 2 Date Handling tab.  There's an item there called "granularity" you can play with.

When a record has type 1 and 2 changes - it goes out several outputs, possibly.  The "current" version of the record - the one that's alive that we've determined has SCD 2 changes on it - goes out TWO outputs.  The "new" version goes out the New SCD2 output - with SCD1 changes.  The "old" version goes out the Expired SCD2 output - with SCD1 changes. If there are any history records - records for that Business Key that were already expired before this processing, those records will go out the Updated SCD1 output.

The New SCD2 output is ONLY used when SCD2 columns are configured on the component.

The Deleted output is ONLY used when there are NO SCD2 columns configured on the component.  Rows go out there when they aren't detected on the source input.

Jul 23, 2010 at 2:03 PM

I don’t seem to have the granularity item on the SCD 2 handling tab I only have:

· Variable container today’s date

· Record changes by marking

· First record’s effective date is

· Last record’s expiry date is

I am running version 1.5.  (I know how I can solve the problem, but it would be nice if I could get the granularity option).

From: toddmcdermid [mailto:notifications@codeplex.com]
Sent: 16 July 2010 17:29
To: dave_lumley@hotmail.com
Subject: Re: v1.5 - component hangs when outputs not defined and warnings not suppressed [kimballscd:217018]

From: toddmcdermid

I really, really need to redo the documentation I killed from the last edit of my wikis!

Anyway - to answer your questions:

Setting granularity to minutes - go to the SCD 2 Date Handling tab. There's an item there called "granularity" you can play with.

When a record has type 1 and 2 changes - it goes out several outputs, possibly. The "current" version of the record - the one that's alive that we've determined has SCD 2 changes on it - goes out TWO outputs. The "new" version goes out the New SCD2 output - with SCD1 changes. The "old" version goes out the Expired SCD2 output - with SCD1 changes. If there are any history records - records for that Business Key that were already expired before this processing, those records will go out the Updated SCD1 output.

The New SCD2 output is ONLY used when SCD2 columns are configured on the component.

The Deleted output is ONLY used when there are NO SCD2 columns configured on the component. Rows go out there when they aren't detected on the source input.

Read the full discussion online.

To add a post to this discussion, reply to this email (kimballscd@discussions.codeplex.com)

To start a new discussion for this project, email kimballscd@discussions.codeplex.com

You are receiving this email because you subscribed to this discussion on CodePlex. You can unsubscribe on CodePlex.com.

Please note: Images and attachments will be removed from emails. Any posts to this discussion will also be available online at CodePlex.com

Coordinator
Jul 31, 2010 at 2:41 AM

I swear I answered this already - must have been a problem with the interweb that day.

Anyway - sorry about that - I must have added that granularity option in v1.6... which I'm using at the moment :)  That doesn't help you, except to know that it'll be up here eventually.  One always hopes sooner rather than later - but that tends not to be how things go.  If you've found a workaround using a Derived Column - I'd say use that for now.  When you upgrade to v1.6 when it comes out, you should be able to get rid of that.