Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««1234»»

A faster way to prepare dimensional databases Expand / Collapse
Author
Message
Posted Thursday, February 25, 2010 12:16 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 8, 2014 10:08 PM
Points: 358, Visits: 397
No, I've never had a problem with using md5. I'm inspired to write my own article on how to use md5 in an update/insert strategy for loading data warehouses if I can get Steve the admin to accept it.
Post #872939
Posted Thursday, February 25, 2010 12:48 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, December 15, 2014 4:08 PM
Points: 40, Visits: 938
magarity kerns (2/25/2010)
No, I've never had a problem with using md5. I'm inspired to write my own article on how to use md5 in an update/insert strategy for loading data warehouses if I can get Steve the admin to accept it.



By all means, please write away. We currently truncate and replace, but would be extremely interested in a more incremental strategy. For example, my understanding is BIDS Slowly Changing Dimension transformation is a performance hound for large tables.



Post #872954
Posted Thursday, February 25, 2010 11:02 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, May 8, 2014 10:08 PM
Points: 358, Visits: 397
We don't need no stinkin' changing dimension widgets... Took me a while with the article-submitting editor, but I got my treatise on using MD5 submitted. We'll see how fast it gets rejected.
Post #873183
Posted Friday, February 26, 2010 1:51 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Saturday, December 6, 2014 9:55 AM
Points: 81, Visits: 908
Fabulous! I look forward to reading it!

Adam
Post #873250
Posted Friday, December 17, 2010 12:08 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 1:37 PM
Points: 18,064, Visits: 16,098
Thanks for the article.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1036354
Posted Friday, December 17, 2010 1:46 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 953, Visits: 2,626
Its a good article.

However, did you experience any problems with datatypes being incorrectly assigned using the SELECT..INTO.. method, I notice you mention that nulls are handled in the consolidation layer so it may mitigate the issue.

I'm also not sure how this can be adapted to an incremental DW unless you are not concerned about maintaining the history, as by dropping the fact table you loose the ability to maintain the historic Fact record, and thus you surely defeat the object one of the objectives of an incremental, which is to allow you to perform point in time analysis, which can be quite critical for DW's in the Finance industry.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1036380
Posted Friday, December 17, 2010 2:33 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, August 22, 2014 3:23 AM
Points: 112, Visits: 297
INSERT INTO <table> WITH (TABLOCK)

will minimally log in the same way
SELECT .... INTO

will by default.
Post #1036397
Posted Friday, December 17, 2010 3:25 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, December 8, 2014 7:59 AM
Points: 405, Visits: 1,139
For example, my understanding is BIDS Slowly Changing Dimension transformation is a performance hound for large tables.



FAO tskelly:

If your finding the standard SCD component of SQL slow, have a look at Todd McDermitts Kimball SCD component.

I've switched to using his Kimball SCD for a number of reasons. First of all it's faster. As mentioned in the details, it doesn't destroy the dataflow when you need to make changes. Also, it provide a wealth of auditing information. Well worth a look!

You can find it here: http://kimballscd.codeplex.com/


_____________________________________________________________________________
MCITP: Business Intelligence Developer (2005)
Post #1036423
Posted Friday, December 17, 2010 3:28 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, November 27, 2014 1:26 AM
Points: 205, Visits: 1,346
Thank you Adam.
At first it is almost necessary to have control of the the whole "ETL" process. Missing data or
bad quality is major problem in many cases. You can seldom avoid outer joins. I use ISNULL(expression,'MISSING') and have a default dimension member MISSING in most of the dimensions. Your approach is also usefull in "non OLAP" situations like with the new feature PowerPivot.
We do have dimension tables and one or more fact tables to import. PowerPivot then creates the "CUBE" by it self.

Gosta M
Post #1036424
Posted Friday, December 17, 2010 3:57 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Today @ 9:25 AM
Points: 953, Visits: 2,626
dave-dj (12/17/2010)
For example, my understanding is BIDS Slowly Changing Dimension transformation is a performance hound for large tables.



FAO tskelly:

If your finding the standard SCD component of SQL slow, have a look at Todd McDermitts Kimball SCD component.

I've switched to using his Kimball SCD for a number of reasons. First of all it's faster. As mentioned in the details, it doesn't destroy the dataflow when you need to make changes. Also, it provide a wealth of auditing information. Well worth a look!

You can find it here: http://kimballscd.codeplex.com/


We use Todds component as well, it has a few performance issues with large datasets, we did some alpha testing the v1.6 of the component and that seems to have fixed some of the perfomance issues, unfortuantely we're just waiting for the final release.


_________________________________________________________________________
SSC Guide to Posting and Best Practices
Post #1036439
« Prev Topic | Next Topic »

Add to briefcase «««1234»»

Permissions Expand / Collapse