2

I have a dimension table which captures demographics on Employees. The schema is similar to below:

    CREATE TABLE [dbo].[DimDemographics]
(
    [DemographicsKey] [int] NOT NULL,
    [AgeBand] [nvarchar](50) NULL,
    [LowerAge] [tinyint] NULL,
    [UpperAge] [tinyint] NULL,
    [SalaryBand] [nvarchar](100) NULL,
    [LowerSalary] [int] NULL,
    [UpperSalary] [int] NULL,
    [Gender] [nvarchar](50) NULL,
    [MaritalStatus] [nvarchar](50) NULL,
)

In the AgeBand, you'll have values such as '25 or Less', '26-30', 31-35'....'Over 65' The LowerAge and UpperAge are just to allow us to query, i.e '25 or Less' will have a LowerAge of 0 and an UpperAge of 25 and so on.

The SalaryBand, LowerSalary and UpperSalary follow the same principal as AgeBand.

In T-SQL, we've written a query which joins the Employee table to the Demographics table and can extract the DemograhicsKey based on their age, salary, marital status and gender.

The Fact table in question has an Employee Key and a Company Key and we've obtained these with lookups - No problem here.

The problem is how to get a DemographicsKey in SSIS. We've tried lookups, OLE DB Commands. The SQL Query is similar to

    SELECT d.DemographicsKey
FROM dbo.DimEmployee e
INNER JOIN dbo.DimDemographics d on 
    e.Age >= d.LowerAge and e.Age<=UpperAge
    and e.Gender=d.gender
    and e.Maritalstatus = d.MaritalStatus
    and CAST(e.SalaryReferenceLocal AS FLOAT) >= d.LowerSalary and CAST(e.SalaryReferenceLocal AS FLOAT) <= d.UpperSalary
    and DATEPART(YEAR, DATEADD(YEAR, -e.Age, GETDATE())) >= d.LowerYear AND DATEPART(YEAR, DATEADD(YEAR, -e.Age, GETDATE())) <= d.UpperYear
WHERE e.EmployeeID =?

The ? is because of the OLE DB Command. I was trying to do it here, but could not get any output from a T-SQL query input. One consideration was to use a Stored Procedure with OUTPUT, but at this point, I'd prefer to avoid that approach and solve it (if possible) with transformations!

I'm out of ideas now as to how to obtain a key from the demographics based on a list of criteria.

For reference, the table and code is scaled down and not the full version, but has enough information to describe the problem.

Any help would be greatly appreciated!

Thanks!

9
  • Quick Update - We've now managed to get it working, I think...Testing. We updated the Query in a lookup to pass in the EmployeeID and this I think works. It's been a long 2 weeks building our first SSIS project!!! Its amazing what a good nights sleep and looking at the problem the next day will do for you! :) Aug 6, 2015 at 7:28
  • Im curious about your design. I can't get my head around your dimension. To me it looks like it should be several dimensions. Do you have a row forceach possible combination/iteration of those attributes? What's the advantage of such a dimension? How do you use this? Aug 11, 2015 at 10:08
  • Hi Peter, Yes. One row for each possible combination. I'm working with the design we were given, but that's not to say it's correct. We have actually found a solution for this but I'd be interested in improving this design, where possible. It's my first BI project, so I expect it to be far from perfect. Aug 11, 2015 at 11:18
  • Hi. My suggestion would be to break the demographics out into separate dimensions. That way you can use them independently in analysis. For example a pivot table with age on one axis and salary on another. Aug 14, 2015 at 23:10
  • Also having an age dimension allows you to do detailed analysis by having many different groupings and hierarchies. Or you could do the grouping within your measures or within your queries. This is a good thing if there is a chance of your groups changing. In fact with your approach iif any of the groups change you will have to reassign and update your keys. Your approach might be useful if you had a massive number of employees and you where combining their demographics with sales. But otherwise i think your design will be too restrictive. Aug 16, 2015 at 5:20

1 Answer 1

2

You mention that you already have a piece of sql that does what you want.

So i would suggest you modify that to become an update statement and implement it as a sql task. Another option is to save the update within a stored proc and call/execute it from ssis.

With a bit of thought you could probably write an update statement that only sets the values for new records or for records that have changed. Or perhaps do it as part of your ETL insert/merge.

However going back to my other comments: i don't think it makes sense to have a single key or dimension for such diverse attributes. I suggest you have a look at some sample schemas to see if it could be done differently.

1
  • Agreed - Due some issues, we are now looking at a redesign of this dimension table to split things out. The query we had appeared to work, but it was not 100% correct. Sep 17, 2015 at 9:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.