I assume that not being able to figure this out is my deficiency and not actually something hard. So, I wanted to toss it out to see if someone could help. I have a single data table right now as my source (good thing). However, I want to aggregate some of the columns in the table to use as dimensions in a bar chart. Let me walk through a sample of a row in the table to help explain:
Project Data Table:
Columns: Project ID, Methodology, Project Year, Project Hours, Project Cost, High Severity Defects, Medium Severity Defects, Low Severity Defects, Dumb Developer Defects, Lazy BA Defects, Vague Customer Defects, Goblins Living In Server Causing Defects, Defects_Total
Sample Data: 10001, Waterfall,2012,100,$100, 3,4,101,1000,50,50, 30,1130
To accomplish this I first load the Project Data Table (needed for other sheets in my QVW) and then created a cross tab in my load:
DefectSeverity:
CrossTable(Defect_Severity, DefectSeverityCount,1)
LOAD /Project ID, High as Defects_Severity_High,
/Med as Defects_Severity_Medium,
Low"> as Defect_Severity_Low
From ProjectDataSourcePlace, table is ProjectDataTab
Next, I create a nice bar chart that has demenions of Methodology & Defect Severity.
It just has a single expression to show the % of each defect severity sum(DefectSeverityCount)/
sum(/Defects_Total)
I end up with a nice little comparison across Project Methodology Types of defect severity frequency. The process is repeated with a cross tab for defect root cause. The end result of the charts I love.
The downside to this, and where I won't pass code review is I now have three Fact Tables or some other combination of a fact table and two other hanger-ons: ProjectData, DefectSeverity, and DefectRootCause
Each table has Project ID in it, which gives me many options, but none of them seem quite right to me.
So, what is the 'best' data model for this?
Should I just concatenate DefectSeverity & DefectCause to the end of Project_ID? If so, my calc isn't going to work since the defect_total is on the real project row, but I'm sure I could somehow aggregate, or something. Also, I end up with 8 rows for each project (1 data, 3 severity, 4 rootcause) which seems odd to me.
Is there another approach to be able to use dimensions of DefectSeverity & Defect Root Cause in my charts? Like some sort of expression based Crosstab?
or is the model "correct" with 3 tables?
Thanks for any help you can provide.
