Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sauce1979
Starting Member
47 Posts |
 Posted - 2012-01-30 : 11:00:10
|
| In our datawarehouse we are currently trying to track history for one of our dimensions called dimPortfolio. It is a dimension that contains information on the portfolios thatare used by the portfolio managers for trading. Currently we just have a spreadsheet, MasterPortfolioList.xls, which contains the source datawhich we cleanse and then upload into a staging table and then into the dimension.We have received an event spread sheet, PortfolioEvent.xls, from our data vendor which documents the events that have occurred per month on the fields in the dimension that ar deemed as historical attributes. For whateverreason we have been told that portfolio changes will be mapped in this way and can not entered on the MasterPortfolioListThe format of the excel spread sheet is as followsPortfolio(Buisness key) Dateofchange Attribute value__________________________________________________________VB0121 20120125 Manager JAMESVB0124 20120119 Region AsiaVB0123 20120125 Currency USDVB0125 20120125 Manager ThomasIn the above example Manager,Region,Currency are attributes that are historical and the values in value column are the new attributes of those attributes.I am just trying to work out the best way to implement a SCD TYPE 2 in ssis. I have currently created staging areas for both the event table and masterlist Spreadsheet. Withthese 2 data sources what is the best way to capture the type 2 changes? I have implemented the scd dimension for some of our other dimensions butI don't think it will suffice for this scenario. (Or maybe I am mistaken). The option of using open source components is closed as it is against our policy. I imagine I would have to use current ssis components possibly the lookup and conditional components.Any ideas or advice on how to proceed would be very much appreciated. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-30 : 11:35:53
|
| what you can do is to first pivot the data to get attributes in columns like Region,Currency etc then add it flattened to your dimension. then use scd type 2 to capture the history and use Dateofchange as opening date. closing date will be initially null and for each new change update it with Dateofchange for next arriving member------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
sauce1979
Starting Member
47 Posts |
Posted - 2012-01-30 : 17:46:28
|
| I am not quite sure how the data would look pivoted. Once it is pivoted do i just join it on the business key to the stage dimension and add the columns? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-30 : 19:45:14
|
| once its pivoted the data will b likeManager Region Currency JAMES Asia USD------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sauce1979
Starting Member
47 Posts |
Posted - 2012-01-31 : 00:39:10
|
| How will i then join it to the dimension? via the business key? the dimension will have extra columns now right? also what will happen in cases of portfolios that have had no events? Nothing can be joined to them. Apologies for asking what may appear to be simple questions. I am relatively new to this. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-31 : 13:31:09
|
quote:
Originally posted by sauce1979
How will i then join it to the dimension? via the business key? the dimension will have extra columns now right? also what will happen in cases of portfolios that have had no events? Nothing can be joined to them. Apologies for asking what may appear to be simple questions. I am relatively new to this.
yep..exactlyi assume portfolio is business key as shown belowthen you can use it to link to dimensionfor cases where portfolio has no events you can use left join logic so that it gets included but will have null values for event related fields------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
sauce1979
Starting Member
47 Posts |
Posted - 2012-02-01 : 01:49:47
|
| Thank you for clarifying. much appreciated. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-02-01 : 09:49:43
|
| welcome------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Slowly changing dimesnion Type 2 chnage