November 27, 2003 at 6:37 am
I am farily new to Olap cubes and data analysis services, but have been using the product for about 4 months. I found the exact problem desribed on the microsoft website "PRB: Drillthrough Queries Return More Rows than Expected If Cube Schema Is Optimized". I thought the solution would solve my problem, but I can not get the sytax correct. The solution was to manually join the dimension table to the fact table
example:
FactTbl.Dim1Key -> Dim1.Key
FactTbl.Dim2Key -> Dim2.Key
I was able to get the fact table syntax correct, but was not able to get the sytax correct for the dimmension table.
Any help would be greatly appreciated
November 28, 2003 at 6:12 am
Did you receive an error when setting the filter statement for the drill through?
Steve
Steve.
November 28, 2003 at 6:21 am
Yes:
I did recieve an error message, but only after I executed the newly saved que. It told me that dimension name was not a data base table which is true. It is the name of the dimension. I am not sure what the syntax is to declare the second paramerter as a Dimension of the cube.
November 28, 2003 at 6:36 am
In the Cube Drillthrough Options screen, on the Columns tab, you should see all of the columns contained in all of the tables used in your model. Assuming you have only 2 tables, a fact and a single dimension, the columns may be something like
COLUMN TABLE
fkCol1 tblFact
fact1 tblFact
fact2 tblFact
pkCol1 tblDim1
Desc tblDim1
Assuming that you have run the optimise schema, the model will now not have a join between tblDim1.pkCol1 and tblFact.fkCol1
What you need to do is on the Filter tab of the Cube Drillthrough Options screen, add a filter like
"tblDim1"."pkCol1" = "tblFact"."fkCol1"
So basically you're using the name of the tables to make the join/s in the drillthrough filter, you shouldn't be trying to use the (models') Dimension name, it won't work.
HTH,
Steve.
Steve.
November 28, 2003 at 8:13 pm
Steve:
Thank You!
this solved the problem
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply