Thought I'd ask - maybe I'm not doing this the best way.
Consider the "typical" employee database where each employee belongs in a department.
I created a fact table with employee, department code, salary, etc.
I created a separate table of department code and department name.
In MS Analysis Services, I created a cube with the fact table and the department table. I linked the two using department code.
My dimension "Department" is setup to show the department name. The idea was that when the user does analysis by department they can see a name rather than a cryptic code.
It appears to me that if a department is left out of the department table, then MSAS leaves out the corresponding fact table records. So if I have 100 employees and leave out the administration department (20 emps) from the department table, then I end up with a cube with only 80 employees.
OK, not that bad but in reality I have about 20 of these linked tables and when I discover that data is missing from the cube, it's a bear to try and find out which dimension is causing the problem. The only thing I have known to do is clone the cube and start removing dimensions until the figures come out right - then I know what dimension table is out of whack and can fix it.
Is this the only way to resolve this problem? Anyone have any better ways to skin the cat so to speak?
Consider the "typical" employee database where each employee belongs in a department.
I created a fact table with employee, department code, salary, etc.
I created a separate table of department code and department name.
In MS Analysis Services, I created a cube with the fact table and the department table. I linked the two using department code.
My dimension "Department" is setup to show the department name. The idea was that when the user does analysis by department they can see a name rather than a cryptic code.
It appears to me that if a department is left out of the department table, then MSAS leaves out the corresponding fact table records. So if I have 100 employees and leave out the administration department (20 emps) from the department table, then I end up with a cube with only 80 employees.
OK, not that bad but in reality I have about 20 of these linked tables and when I discover that data is missing from the cube, it's a bear to try and find out which dimension is causing the problem. The only thing I have known to do is clone the cube and start removing dimensions until the figures come out right - then I know what dimension table is out of whack and can fix it.
Is this the only way to resolve this problem? Anyone have any better ways to skin the cat so to speak?