July 18, 2008 at 5:59 am
ok first of I am very new to the world of BI so be kind 😉
I have a measure table that has a field called Work_Type. And I also have a table called dimWorkTypes.
Do design this I created a foreign key relationship between the tables. A simplified version of the structure can be seen below.
Create table DimworkTypes(Code varchar(15) not null primary key)
go
Create Table FacJobs (Job_id int not null , Emp_no int not null, Quantity numeric(10,4) null, Hours numeric(10,2) null , Worktype varchar(15) not null References Dimworktypes(Code))
go
alter table FacJobs add primary key (job_id , emp_no)
go
As I said this is an simplified version of the measure table , but the dimworktypes looks like this.
My problem however is when I’m looking up values in excel or browsing my cube. I only get on row in a drill down. That value is a value called no data that I use to exclude null values.
For example when I load up all emp_no's and want a break down from my worktype and the quantity per worktype.
Comes like this
EmpId
10
No Data 100
Instead of
EmpId
10
Billeble 10
Development 10
ect
ect
When I do this in SQL everything works fine. That is I can do an select an get the appropriate work_type in a single SQL statment.
Would be.
select j.emp_no , sum(Quantity) QTYSUM, w.Code
from dimWorktypes w
join facJobs J on j.worktype = w.code
group by j.emp_no , w.code
With the code above I do get the so wanted results with the correct work_types.
so I you have any idea on what could my problem or just tell me how in god’s name I can debug what the dimension or the cube is doing when it only finds the single work_type it would be much appreciated
kgunnarsson
Mcitp Database Developer.
July 18, 2008 at 8:19 am
Resolved by creating an named query table from the Data source view.
That is..
Dropped my table dimworktyped. Instead from the data source view of the cube I created a new named query Table that selected the distinct values from the fact table.
Then i created an dimension from that table and all my values showed up correctly .. joy joy 🙂
kgunnarsson
Mcitp Database Developer.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply