November 15, 2002 at 1:07 am
i have cube with 2 tables. my fact table contains, the fields AdmId, Gender etc. Other table has 0 to many relation with table1 and contain. AdmId, Type where single admid can have multiple or no types at all. I have made a star schema but problem is arsing when i make "Type" as dimesion in analysis manager and AdmId as measure (count). The count comes invalid, it returns the count of AdmId of table2 while it must return count of Admid of table1.
Note: I have less rows in table2 as compare to table1 because some AdmId doesnt have any type.
Thanx
November 18, 2002 at 8:00 am
This was removed by the editor as SPAM
November 25, 2002 at 8:39 am
Good morning -
The value of the measure is the count of Table2 because, when processing a cube, the underlying (dynamically created) SQL query is an inner join query. Therefore, the result set counted will be only those that join to table2.
To correct this problem, insure that the fact table contains the Type member value (IE: the fact and Type dim tables join on the same value as the leaf level member key for Type). Then, choose 'Optimize Cube Schema' from the 'Tools' menu in Analysis Services.
This function will change the leaf level reference for the Type table to be the Fact table (rather than the Dim table).
This is done to remove the need to join to the Type table during cube processing (since the fact table already contains the member key value) but will have the side-effect of including all fact table rows in the data load. And should, therefore, give you the proper count.
Hope this helps.
Scot J Reagin
Scot J Reagin
sreagin@hitachiconsulting.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply