The other day somebody asked me how I could implement Slowly Changing Dimension Type 2 (SCD Type 2) in SSAS. I wasn’t able to give the answer right there and then, so I decided to create a demo video to answer his question and as a note to myself for future uses.
This is my first ever demo video by the way and I agree that it could have been better in many parts. I do hope that despite my amateur recording and editing skills, I am still able to send the message across. 🙂
One thought on “How to Implement Slowly Changing Dimension Type 2 in SQL Server Analysis Services”
Thanks for this video. I have an Employee dimension very similar to your example but when I process it after making the changes you suggest it throws an error saying “Errors in the OLAP storage engine: A duplicate attribute key has been found when processing: Table: ‘dbo_dEmployees’, Column: ‘EmployeeAltKey’, Value: ‘27821’. The attribute is ‘Full Name’.” I have read a number of other posts where people are getting the same error but I cant seem to find one that explains the change i need to make clearly to resolve this….and I was quite surprised your dimension processed. Have you come across this at all? What you have done in your video is exactly what I am looking to implement but i just cant get it to work. It only starts to throw this error once I add a Hierarchy though. My Hierarchy is FullName -> PositionTitle -> Location -> State. I have a feeling its because the on the Employee table their is a PositionID which links to a Position table that holds the rest of the hierarchy information. eg If an employee changes their location they get a new PositionID. Any suggestions please?