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 |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2008-02-13 : 17:43:10
|
| I have a fact table that has terminations. Fields include EmployeeName, TermDate, TermReason, and HireDate, et al. I need to make EmployeeName available to drillthrough, and since it's a varchar field I can't make it a measure, so it has to be a dimension attribute. My question is, should I leave the fact table as it is and use SSAS to create a dimension that contains only EmployeeName and the link to TerminationID? Or should I redesign the OLAP tables so that EmployeeName is in a separate table? |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2008-02-14 : 08:09:54
|
| Well, there are a couple ways to go with this. First of all, it seems obvious to me that you need an Employee dimension; however, keep in mind that the key to good dimensional modeling is allowing the information users to drive the design. Maybe you have a good reason for not wanting an employee slicer. Typically, EmployeeName is not a good sourcekey, so you'll want to take care when building your dimension. In my experience, HR folks love to slice by Bio/Demo when looking at Hires/Terms and (some of) those are good candidates for hierarchies on an Employee dimension.Here is how I accomplish what you are trying to do:Create a "Fact Drillthrough" dimension that is based on your fact table (not a dimension table). Include EmployeeName as an attribute on that dimension, but then set that AttributeHierarchyVisible to false. Then create a Drillthrough action associated with your fact table's measure group and add your "Fact Drillthrough" dimension EmployeeName attribute to the Drillthrough columns.Jayto here knows when |
 |
|
|
|
|
|
|
|
fact fields -> dimension attributes