none
Slowly changing dimensions and ODBC connection RRS feed

  • Question

  • Hi,

    I'm trying to implement a slowly changing dimension (SCD) with the wizard but noticed that it only allows for OLE DB type of connections. Since I have PostgresQL with an ODBC connection, I was looking at options to make this happen.

    I was thinking of creating one myself as I read that the standard SCD component does not scale well with dimensions that for example have 10-30 million records however most of the custom made solutions that involve lookups and conditional splits in the end also require an OLE DB object to perform an update or insert statement that an ODBC destination component does not offer (at least I didn't see any option for that). 

    Is the only solution to get a commercial license for an OLE DB connector to PostgresQL (corporate standard)?

    Roland

    Wednesday, June 17, 2020 8:35 PM

Answers

  • The SDC transform is just a convivence. You can certainly to things the old fashioned way by comparing new dimensional data with with old and updating the dimension accordingly, depending on what type of SCD you are using. You could do this with straight SQL, and would not necessarily require lookups. 

    If my reply solves your issue, please mark it as the answer

    Wednesday, June 17, 2020 9:49 PM
  • Hi Roland,

    It seems that we should use OLEDB connection in Slowly Changing Dimension Transformation .

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Roland613 Thursday, June 18, 2020 1:20 PM
    Thursday, June 18, 2020 10:03 AM

All replies

  • The SDC transform is just a convivence. You can certainly to things the old fashioned way by comparing new dimensional data with with old and updating the dimension accordingly, depending on what type of SCD you are using. You could do this with straight SQL, and would not necessarily require lookups. 

    If my reply solves your issue, please mark it as the answer

    Wednesday, June 17, 2020 9:49 PM
  • Hi Roland,

    It seems that we should use OLEDB connection in Slowly Changing Dimension Transformation .

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Roland613 Thursday, June 18, 2020 1:20 PM
    Thursday, June 18, 2020 10:03 AM
  • Hi Mona, thanks for replying (again) :). For now, I guess I'll try out the trial version of the PostgresQL OLE DB connector and see if the standard out of the box SCD component works on bigger datasets.

    I also came across a custom made SCD component on this website https://archive.codeplex.com/?p=dimensionmergescd, but it seems it hasn't been updated for a long time. In addition, I don't know how to install custom made components to even try it out.

    Roland

    Thursday, June 18, 2020 1:24 PM
  • Hi Roland,

    COZYROC has just released an enhancement to the commercial Database Destination component with support for the PostgreSQL database. We support both the ADO.NET and ODBC drivers. You might be able to avoid using of SCD type of component because the Database Destination component includes an Update and Upsert action. 


    SSIS Tasks Components Scripts Services | http://wwww.cozyroc.com/

    Tuesday, June 30, 2020 9:27 PM