May 16, 2003 at 2:53 pm
I have a fact table I need to do some calculations on and am having a hard time visualizing what I need to do. I am new at Analysis services.
the fact table has a bunch of columns grade, rs1, rs2, rs3, rs4, rs5 etc. These are scores such as 03,72,85,25,45,65,82 etc.
There is another table with what these scores mean. This table has grade, category, advanced,profcient,belowprofcient. The catagory relates to the columns in the fact table rs1, rs2, etc What I need to summarize is if the record is a third grade record, look at the rs1 score, go find third grade in the other table, look at the category column for the rs1 scores and if the fact score is <= belowProfcient, they are below, if <= advanced and >= prof they are profcient. If >= advanced they are advanced. I need to have the cube summarize by grade and rs1, rs2 etc... if they are bp, prof or advanced.
To try to clarify as the above is a bit confusing, here are a couple of rows in each
factTable:
grade rs1 rs2 rs3 rs4 rs5
03 52 64 85 45 25
06 45 48 54 85 22
Score Key Table
grade Category advanced prof below prof
03 rs1 85 65 32
03 rs2 75 60 38
03 rs3 78 62 45
06 rs1 68 54 32
06 rs2 78 62 31
Any help is appreciated!
Thanks,
Mike
May 18, 2003 at 3:48 pm
Nevermind, I ran the table through a dts to make it a bit more relational, created a view that included a prof bit based on the key table calculation and all is working as it should.
Thanks,
Mike
May 18, 2003 at 4:27 pm
Find a view quite handy, since one can throttle the visibility of the data during testing. Other words for testing we allowed only a few clients data throught. Going life we removed all the filters.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply